Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
When and How to Scale a Database? Measurement, Bottlenecks and the Scaling Ladder
You don't decide to scale a database before measuring p50/p95/p99 latency, throughput and what active sessions are waiting on; the first rung is usually query and index. Bookstore's order history query saturated at 187 requests/s with 64 clients, and one index took p99 from 653 ms to 5.6 ms. Finding the bottleneck with Little's Law, pg_stat_statements and DMVs, the scaling ladder, capacity estimation and SLOs.
Connection Pooling in Depth: the ADO.NET Pool, Pool Exhaustion, Npgsql and PgBouncer
The "max pool size was reached" error almost never comes from a small pool but from connections that don't come back; the fix is finding the leak and calculating the instances × pools × Max Pool Size budget. The SqlClient pool key and counters, fragmentation into 50 pools via Application Name, PostgreSQL's 53300 error, PgBouncer carrying 250 clients on 20 connections at 5.5 times the throughput, SET leaking in transaction mode, and max_prepared_statements.
Living with Big Tables: Batched DELETE/UPDATE, Archiving, Online Index Builds and Online Schema Changes
The safe way to delete old data from a big table is not one giant DELETE but small keyset-based batches that write a checkpoint in their own transaction. The single transaction failed a customer read through lock escalation, grew the log to 200 MB and left the replica 22 MB behind; the batched job did the same work without blocking reads and resumed where it stopped. Resumable indexes, CREATE INDEX CONCURRENTLY and ALTERs that rewrite the table.
Database Replication: Physical vs Logical, Sync vs Async, Always On and PostgreSQL Streaming Replication
Replication means shipping the commit log to another server and applying it again; whether a commit waits for the replica decides both the data you can lose (RPO) and availability. In the lab sync commit cut throughput by 37%, writes stopped when the single sync replica went down, and a canceled INSERT turned out to be committed anyway. Streaming replication, measuring lag, log shipping, Always On, a replication slot filling the disk, and logical replication.
High Availability and Failover: RPO/RTO, Listeners, Patroni and Making the Application Survive Failover
An application survives failover only with idempotent writes and a retry that waits for the failover; an async replica is not lossless. In the lab an async replica lost 50 orders; the default EF Core retry didn't retry "No suitable host was found" and 98 orders failed, while a widened strategy and Host Recheck Seconds=1 gave zero errors and zero duplicates. RPO/RTO, HA and DR, AG listeners, Patroni and SqlClient ConnectRetryCount.
Read/Write Splitting in .NET: Routing to Read Replicas and the Read-Your-Writes Problem
Moving reads to a replica takes more than two contexts; you need a router that knows when the replica is behind. In the lab, customers reading from a replica with apparently zero lag missed 4 of 1,000 new orders, and 300 of 300 once replay was paused; a router that checks a lag threshold and the LSN of the user's last write missed none. Npgsql prefer-standby, SQL Server read-only routing and hot standby conflicts.
Partitioning in PostgreSQL: Range, List, Hash, Partition Pruning and Archiving with Attach/Detach
Partitioning in PostgreSQL doesn't speed queries up on its own, but it makes archiving and maintenance easier. In the lab a "My orders" query that doesn't use the partition key read 7 times as many buffers on 50 monthly partitions, and with 1,522 daily partitions its planning alone took 49 ms; in return we moved ten months to the archive with DETACH CONCURRENTLY under pgbench load, under 40 ms per month and without errors. Range, list, hash, pruning and PostgreSQL 18's ANALYZE ONLY.
Partitioning in SQL Server: Partition Functions, Schemes, Aligned Indexes, Partition Elimination and SWITCH
The real gain of partitioning in SQL Server is archiving in milliseconds with SWITCH; query gains only come if partition elimination works. In the lab we moved eleven months to the archive in under 10 ms per month; on the other hand, AddWithValue(DateTime), Scale = 0 and EF Core's datetime2(7) parameters turned elimination off on a datetime2(0) column and all 52 partitions were read. RANGE LEFT/RIGHT, aligned indexes, SPLIT/MERGE cost and LOCK_ESCALATION = AUTO.
Introduction to Sharding: Shard Keys, Application-Level Sharding, Citus and Distributed SQL
Sharding solves write scale and data size, but makes the global ID, cross-shard queries and transactions that are free on one server expensive. In the lab two shards produced the same order_id, and a "best sellers" list merged from each shard's top 10 shared no book with the correct list; the right answer came from partial totals. A bucket-based IShardResolver in .NET, choosing a shard key, co-location in Citus and distributed SQL.
End to End: Preparing Bookstore for 10x Growth — Measure, Decide, Apply, Verify
Preparing Bookstore for 10x growth isn't sharding; it's adding an index, a connection budget, archiving, a read replica and partitioning in order, each measured. Under a campaign load "My orders" p99 stayed at 4.0 ms (target 50 ms); when the primary was killed under load and the replica promoted, writes paused for 7.1 seconds, but no request failed and all 3,970 acknowledged orders survived. Gains and costs of each rung, ADRs, failover and restore drills, a checklist.