Software Engineer Intern

What I worked on:

There were a lot of things I worked on during this internship, but one of the highlight is a database optimization I delivered.

Read a more detailed write-up about this here:

Database is underrated. Go check your clustered index.My journey to dive deeper into Relational Database!trungn21.substack.com ↗ (opens in a new tab)

Summary:

The supervisor hit me up with a simple task: improve the processing time of an ingestion pipeline. At first, it was just a JSON parsing bug that was duplicated, which did reparsing. Though when I digged in a bit deeper, the bottleneck seemed to be lying in the database schema itself.

Problems:

• Duplicate writes: We used to write to a staging table, then write from the staging table to the final table - 2x writes.
• Not utilizing cluster index: downstream services read from a composite of the primary key and another variable.

My solutions:

1. Composite PK clustered index with hot variable, so all relevant rows are physically closer togther, reducing page read and fragmentation during query.
2. Partition switching for the load: the staging table mirrors the main table's schema, constraints, and indexes, then a metadata-only switch replaces the second write.

Results (benchmarks)

Metric	Before	After	Change
Table merge	255.05s	75.24s	~70% faster
p95 downstream aggregation	221.19s	108.50s	~51% faster
Fragmentation (5M-row load)	16.85%	0.27%	~98% lower
Page count (5M-row load)	~54k	~33k	~39% fewer

My biggest takeaway is the optimization did not come from more complex infrastructure or services, rather a change in the schema itself. Sometimes less is more.

Other notable achievements:

• Decreased database query latency by 15% across 20 stored procedures by decoupling an asynchronous logging solution using SQL Server Service Broker.
• Redesigned a blocking job-submission endpoint to async: submit returns right away, and the client polls for status until the job finishes.