PostgreSQL Performance Basics: VACUUM, ANALYZE, and Connection Pooling

Beyond indexing and query plans, PostgreSQL has a few operational fundamentals that keep it running efficiently over time: routine maintenance via VACUUM and ANALYZE, and managing how client connections reach the server. Both matter more as an application grows, and both are frequently overlooked until performance quietly degrades.

Why it matters

PostgreSQL never overwrites a row in place: an update writes a new version and leaves the old one behind. Without clean-up, tables grow and slow down however good the queries are. Vacuuming, statistics and connection pooling are the three things that keep a busy PostgreSQL database healthy.

Why VACUUM Is Necessary

PostgreSQL uses a storage strategy called MVCC (Multi-Version Concurrency Control): an UPDATE or DELETE does not immediately erase the old row version, it marks it as "dead" so that other concurrent transactions reading it still see a consistent snapshot. Over time, dead row versions accumulate as table "bloat," wasting disk space and slowing scans. VACUUM reclaims that space for reuse (without necessarily shrinking the file on disk, unlike VACUUM FULL, which rewrites the whole table and requires a stronger lock).

ANALYZE and the Query Planner

ANALYZE collects statistics about the distribution of values in each column — how many distinct values, how common each is, roughly how the data is sorted — and stores them for the query planner to use when estimating plan costs. Outdated statistics (for example, after a large bulk load) are a common cause of the planner choosing a poor plan, since its row-count estimates become inaccurate. PostgreSQL runs both VACUUM and ANALYZE automatically in the background via autovacuum, but understanding what it does helps you diagnose when its defaults need tuning for a specific heavily-written table.

Connection Pooling with PgBouncer

Each PostgreSQL connection is a full operating-system process with meaningful memory overhead, so PostgreSQL handles at most a few hundred to low thousands of concurrent connections comfortably. Applications that open (and often leak) many short-lived connections — common with serverless functions or large web fleets — benefit from a connection pooler like PgBouncer sitting between the application and PostgreSQL. PgBouncer maintains a small pool of real server connections and multiplexes many client connections onto them, dramatically reducing the load a spiky application places on the database itself.

Example

VACUUM ANALYZE products;

SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'products';

VACUUM
   relname  | n_dead_tup | n_live_tup
------------+------------+------------
 products   |          0 |          3

Exercise

This exercise uses PostgreSQL maintenance commands, so it needs a PostgreSQL server. For the orders table, write the command that reclaims dead rows and refreshes the planner's statistics in one go. Then write a query against the statistics view that shows the table's number of live rows, its number of dead rows, and when autovacuum last ran on it.

Expected result: after the VACUUM, the dead row count for orders is at or near zero.

-- TODO: vacuum and analyze the orders table

-- TODO: live rows, dead rows and last autovacuum time for orders,
--       from pg_stat_user_tables

VACUUM (ANALYZE) orders;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

Quick quiz

Interview questions