Database Performance Baselines

In this lesson, you will learn how to establish a repeatable performance baseline to distinguish between expected system behavior and actual performance degradation during capacity planning.

Why Baselines Matter

In PostgreSQL, performance is relative. Without a recorded baseline, you cannot tell if a query is "slow" because of a recent schema change, a growth in data volume (bloat), or an underlying hardware bottleneck. A baseline acts as your "ground truth"—a snapshot of how your system performs under a known, representative workload when it is in a healthy, optimized state.

Establishing the Baseline

To create a meaningful baseline, you must perform a representative load test. This involves three steps:

1. Define the Workload Mix: Capture a profile of your typical daily operations (e.g., 70% SELECT, 20% INSERT, 10% UPDATE). Use pg_stat_statements to identify the most critical queries that consume the most total time.
2. Ensure a "Clean" State: Before running your test, ensure the database is properly tuned. Run VACUUM ANALYZE on your tables to ensure the planner has up-to-date statistics, as stale stats are the leading cause of baseline drift.
3. Execute and Measure: Run your workload in a controlled environment using tools like pgbench.

For example, you might run a custom script that executes your top 5 queries against a populated test database:

-- Example: Running a representative load test script
\set id random(1, 100000)
SELECT * FROM orders WHERE user_id = :id;
-- Measure total time and transactions per second (TPS)

Capacity Planning

Once you have this data, you have a reference point. If you plan to scale your user base by 50%, you can compare your current baseline against a projected load. If the execution plan for a critical query switches from an Index Scan to a Sequential Scan as the table grows, you know you need to address the STATISTICS target or implement partitioning before the production rollout.

Without a baseline, you are simply guessing at capacity; with one, you are performing engineering.

Key Takeaway: A performance baseline provides a repeatable, data-driven reference point that allows you to isolate whether system degradation is caused by query inefficiency, data growth, or infrastructure limitations.