Database performance: How to implement write-combining for hot rows

Last month, our analytics dashboard started timing out during peak traffic. We were tracking real-time user activity, and every single page view triggered an UPDATE statement on a shared "total_views" row. Under load, the database was spending more time managing lock queues than actually persisting data.

We were hitting a classic bottleneck: high-concurrency contention on a single row. If you've ever dealt with this, you know the feeling of watching your IOPS spike while your actual throughput plateaus. This article walks through how we solved it using write-combining, a technique that shifted our database workload from "death by a thousand updates" to a smooth, batched stream.

Why hot rows kill database performance

When multiple transactions try to update the same row, the database engine enforces isolation. In PostgreSQL or MySQL, this means row-level locking. If 500 requests hit the same row simultaneously, 499 of them wait in line.

The database isn't just writing data; it's managing a massive queue of sessions. This results in high CPU usage and blocked connections. We first tried a naive approach: we added a SELECT FOR UPDATE to serialize the increments in the application layer. That was a disaster. It actually made the latency worse because we were holding application-side connections open for longer while waiting for the database to resolve the lock.

We needed a way to decouple the intent to update from the actual write.

Implementing write-combining for high-concurrency spikes

Instead of writing to the database on every request, we implemented a write-combining buffer. The goal is to aggregate multiple increments in memory and flush them to the database in a single transaction.

Here is the strategy we used:

1. Buffer in Redis: Instead of hitting the SQL database, we use a Redis INCRBY operation. This is atomic, incredibly fast, and doesn't hold locks on your primary database.
2. Batch Flush: A background worker (or a periodic cron job) reads the aggregated values from Redis and performs a single bulk update.
3. Upsert or Batch Update: We use an INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) to merge the values.

The code approach

I’ve found that using a simple script to flush every 5-10 seconds works for most use cases. Here is a simplified version of what that looks like in a background process:

PHP
#6A9955">// Pseudo-code for a batch flusher
$pendingUpdates = $redis->hGetAll('page_view_buffer');

if (!empty($pendingUpdates)) {
    $db->beginTransaction();
    foreach ($pendingUpdates as $pageId => $count) {
        $db->statement("
            INSERT INTO page_stats(page_id, view_count) 
            VALUES(:id, :count) 
            ON CONFLICT(page_id) 
            DO UPDATE SET view_count = page_stats.view_count + EXCLUDED.view_count
        ", ['id' => $pageId, 'count' => $count]);
    }
    $db->commit();
    $redis->del('page_view_buffer');
}

This drastically reduces the number of round-trips to the DB. By batching these updates, we reduced our write IOPS by roughly 85%.

Trade-offs and considerations

This isn't a silver bullet. You’re trading immediate consistency for system throughput. If your background worker crashes before flushing Redis, you lose those increments. In our case, that was acceptable, but if you're tracking financial balances, you'll need to look at Database performance: Asynchronous Materialized Views for High-Load Reads or a more robust persistent queue like Kafka.

We also considered WordPress performance: Database-level request coalescing for REST API but found it didn't solve the write-side contention we were seeing. Coalescing reads is great for N+1 problems, but updates require a different architectural shift.

When to use this vs. other strategies

Don't over-engineer if you don't have to. If your traffic isn't hitting the point where locking becomes a bottleneck, standard updates are perfectly fine.

However, if you see your pg_stat_activity or processlist filled with connections waiting on RowExclusiveLock, you’ve reached the limit of standard updates. Before you implement write-combining, make sure your Database performance: Database proxy strategies for high concurrency aren't already handling this for you. Sometimes, a connection pooler or a proxy can mask the issue, but they won't fix the underlying row-level contention.

Looking ahead

I'm still tinkering with the flushing interval. Right now, it's fixed at 10 seconds. I suspect that during massive traffic bursts, I should dynamically decrease the interval to prevent Redis from growing too large, but I haven't implemented that yet.

Every time I touch this logic, I'm reminded that database performance is rarely about the query itself and usually about how the database handles concurrent access to the same resources. Keep your writes infrequent, batch them when possible, and your database will thank you.