Every developer working with a database faces a fundamental design choice: how to structure their transactions. Should a complex business operation, involving multiple database writes, be wrapped in a single, all-or-nothing transaction? Or is it better to break it down into a series of smaller, faster commits? On the surface, the long_running_transaction seems safer, guaranteeing perfect atomicity. But as traffic grows, a mysterious performance degradation can set in. Latency spikes, throughput drops, and users start seeing errors. The culprit is often not the queries themselves, but the invisible contention they create.
This isn’t just a theoretical debate. The choice between a short_transaction and a long_running_transaction has a profound and measurable impact on your database’s health, particularly concerning lock contention and the deadlock_rate. Holding locks for even a few extra milliseconds can cascade into system-wide blocking_time, crippling your application under load. In this guide, we’ll move beyond theory and design a practical postgres transaction_benchmark using pgbench to demonstrate precisely how and why short, targeted transactions are crucial for building scalable, high-performance systems.
The Anatomy of a PostgreSQL Transaction: More Than Just a Query
To understand the performance implications, we must first understand what happens when you type BEGIN;. A PostgreSQL transaction is a resource-intensive event. It creates a “snapshot” of the database, ensuring a consistent view of the data for its duration. More importantly, as it modifies data, it acquires locks. These locks are the mechanism that ensures data integrity, preventing two transactions from overwriting the same row in a chaotic way.
- A
short_transactionacquires a lock, performs its update, and commits, releasing the lock within a few milliseconds. The window for another transaction to conflict with it is tiny. This is the principle behind anautocommit_testor highcommit_frequencymodel. - A
long_running_transactionacquires a lock and holds onto it. It might perform other queries, wait for application logic to complete, or even pause for an external API call. During this entire time, that lock is held. Any other transaction needing that same row must wait, creatingwrite_contention. The longer the transaction, the higher the probability of a conflict.
Furthermore, long transactions have a sinister side effect on database maintenance. PostgreSQL’s VACUUM process cannot clean up “dead” row versions that are still visible to an open transaction. A single long_running_transaction can prevent VACUUM from reclaiming space on entire tables, leading to table bloat, slower queries, and a general decline in database performance over time.
Designing a Realistic Transaction Benchmark with pgbench
To make this tangible, we need a repeatable benchmark_script. PostgreSQL’s built-in tool, pgbench, is perfect for this. While it comes with a default TPC-B-like workload, its real power lies in custom scripts that can simulate your application’s specific access patterns.
Our goal is to create a scenario with high_write_tables—specifically, high contention on just a few rows, which is a common bottleneck in many applications (e.g., updating inventory for a popular product, or changing the status on a central configuration table).
Setting Up the Test Environment
First, we set up pgbench with a custom table that will be our point of contention. A dedicated database for the test is created, and then pgbench is initialized with a small scale factor. Finally, a custom products table is created to serve as the point of high contention, populated with just a couple of products with an inventory count.
The “Long Transaction” Benchmark Script
Our first script simulates a complex operation that updates two products within a single, long-running transaction. Crucially, it includes a short, 50-millisecond sleep between the two updates. This pause doesn’t represent a database-intensive operation; rather, it simulates application processing time, network latency for another service call, or any other work that holds the transaction open and its locks in place for longer than necessary.
The “Short Transaction” Benchmark Script
Our second script performs the same logical work—updating two products—but does so by committing each update immediately in its own separate transaction. This approach breaks the work into two distinct, atomic operations, minimizing the total time that any single lock is held.
Running the Benchmark and Analyzing the Results
Both custom scripts are then run using pgbench. The tool is configured with a high number of concurrent clients and worker threads to deliberately create a high-contention environment. Each test runs for a fixed duration, such as 60 seconds.
Key Metrics to Watch
While pgbench provides transaction throughput (TPS) as its main output, the real story is in the database’s internal state. This is where a real-time monitoring tool is indispensable.
- Transaction Throughput (TPS): The raw performance number from
pgbench. - Lock Waits: The number of processes stuck waiting for a lock to be released. This is the most direct measure of
blocking_time. - Deadlocks: Tracking the cumulative number of deadlocks detected by the database.
- Statement Performance: Identifying which specific queries are spending the most time executing versus waiting.
Visualizing Contention with Netdata
Instead of manually running queries, a tool like Netdata provides immediate, visual feedback. During the lock_wait_benchmark, the netdata_postgres collector automatically tracks and visualizes these critical metrics.
When running the “Long Transaction” test, a Netdata dashboard would light up:
- The PostgreSQL Locks chart would show a dramatic and sustained spike in waiting processes.
- The PostgreSQL Deadlocks chart, which tracks the cumulative count, would start ticking upwards.
- The Transactions chart would show a high number of rollbacks, as PostgreSQL aborts transactions to resolve deadlocks.
In stark contrast, the “Short Transaction” test would show minimal lock waits and likely zero deadlocks, even with the same number of concurrent clients. The contention_results become visually undeniable.
Analyzing the Contention_Results
The results from this postgres_performance_test are typically night and day.
- Short Transactions: Achieves a significantly higher TPS. The database remains healthy, with minimal
blocking_timeand virtually noserialization_failuresor deadlocks. - Long Transactions: Throughput plummets.
pgbenchwill report a very high average latency per transaction. The Netdata dashboard will confirm that this latency is not due to CPU or I/O load, but almost entirely due to processes waiting for locks. Thedeadlock_ratebecomes a serious issue, causing work to be thrown away.
This benchmark proves a critical principle:total transaction time, not the number of queries, is the primary driver of lock contention.The 50ms sleep in our long transaction script was enough to create a traffic jam, where dozens of clients ended up waiting in a queue for locks held by others.
Final Recommendations
The evidence is clear. For building scalable applications that perform under pressure, managing transaction scope is paramount.
- Keep Transactions Short: A transaction should live for milliseconds, not seconds. Encapsulate the smallest possible logical unit of work.
- Separate Fetches from Writes: Never hold a transaction open while waiting for user input, application processing, or network calls. Fetch the data you need, close the transaction, perform your logic, and then start a new transaction to write the result.
- Batch Strategically: For large data imports or updates, break the work into smaller chunks of a few hundred or thousand rows, with a
COMMITafter each chunk. This releases locks periodically and allows maintenance processes likeVACUUMto do their work.
Understanding this behavior is the first step, but seeing it in your own environment is what truly drives change. To discover hidden long_running_transaction problems and visualize their impact on your database, you need comprehensive, real-time monitoring.