Running Databases in Production: Pooling, Maintenance, Partitioning, and Backups

A well-tuned query on a poorly run database is still a slow query. Connection exhaustion, bloated tables, runaway disk growth, and untested backups cause more production incidents than any single missing index. This article covers the operational habits that keep a relational database, PostgreSQL in the examples, healthy for years.

Connection management

Why connections are expensive

Each PostgreSQL connection is a separate operating-system process with its own memory. Hundreds of mostly idle connections use RAM, increase context switching, and make lock management more expensive. A database that handles 10,000 queries per second over 50 connections can struggle with 2,000 connections doing the same work.

Pool in the application

Every service should use a connection pool with explicit limits:

db.SetMaxOpenConns(20)                  // hard ceiling per instance
db.SetMaxIdleConns(10)                  // keep some warm
db.SetConnMaxLifetime(30 * time.Minute) // recycle to rebalance after failovers
db.SetConnMaxIdleTime(5 * time.Minute)

Do the multiplication: 30 pods × 20 connections = 600 connections. That's often more than the database can handle efficiently. Autoscaling makes it worse, because more pods means more connections at exactly the moment of peak load.

Sizing the pool

A useful starting point is that the database does its best work with only a few active connections per CPU core. More concurrency beyond that adds contention without adding throughput. Start small, measure wait time for a connection versus query latency, and increase only if requests queue for connections while the database still has idle capacity.

Pool in front of the database

With many services or serverless functions, add a connection pooler such as PgBouncer between the apps and the database. In transaction pooling mode, thousands of client connections share a small set of server connections. Be aware of what transaction mode breaks: session-level features like SET without LOCAL, advisory locks held across transactions, and (depending on version and driver settings) server-side prepared statements.

Vacuum, bloat, and statistics

Why vacuum exists

PostgreSQL uses MVCC. An UPDATE writes a new row version, and a DELETE only marks the row. The old versions ("dead tuples") stay until VACUUM reclaims the space for reuse. Without it, tables and indexes bloat and queries read more pages to find the same live data.

Autovacuum does this automatically, but its defaults are tuned for modest tables. On a 200-million-row table, the default threshold (roughly 20% of rows changed) means waiting for 40 million dead rows before cleanup starts.

Tune it per table for large, busy tables:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000
);

Watch for these

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

• Dead tuples growing faster than they're cleaned means autovacuum needs more aggressive settings or more workers.
• Long-running transactions block cleanup, because vacuum can't remove row versions an open transaction might still need. One forgotten BEGIN in an idle session can bloat the entire database. Set idle_in_transaction_session_timeout.
• Transaction ID wraparound is PostgreSQL's hard limit. Autovacuum normally prevents it, but monitor age(datfrozenxid) and alert well before it approaches the limit.

Fresh statistics

Vacuum's sibling, ANALYZE, refreshes the statistics the query planner relies on. After bulk loads or big deletes, run ANALYZE explicitly instead of waiting for autovacuum.

Partitioning large tables

When a table grows to hundreds of millions of rows, especially time-series data like events, logs, or transactions, declarative partitioning splits it into smaller physical tables behind one logical table:

CREATE TABLE events (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    occurred_at TIMESTAMPTZ NOT NULL,
    tenant_id   BIGINT NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

What partitioning gives you

• Partition pruning. A query with WHERE occurred_at >= '2026-09-15' only scans the relevant partitions.
• Cheap retention. Removing old data becomes DROP TABLE events_2025_09, which is instant, instead of a huge DELETE that produces gigabytes of dead tuples.
• Smaller maintenance units. Vacuum and reindex work on one partition at a time.

What it doesn't give you

• It doesn't speed up queries that don't filter on the partition key. Those scan every partition and can be slower than before.
• Unique constraints must include the partition key.
• Too many partitions (thousands) add planning overhead. Choose a granularity, such as monthly or weekly, that keeps partitions reasonably large.

Automate partition creation ahead of time, with tools like pg_partman or a scheduled job, so an insert never fails for lack of a partition.

Capacity planning

Track the trends before they become incidents:

• Disk: growth rate per week, and projected days until full. Alert at 70–80%, not 95%.
• Memory: the buffer cache hit ratio for hot tables. A falling ratio often means the working set has outgrown RAM.
• CPU and I/O: sustained utilization at peak, not average.
• Connections: peak active connections compared with the limit.
• Replication lag: replicas serving stale reads cause subtle bugs.

Scale reads with read replicas for reporting and read-heavy endpoints, and route writes and read-after-write paths to the primary. Before sharding, which adds a lot of complexity, check whether partitioning, archiving cold data, better indexes, or a bigger instance would solve the problem.

Schema changes without downtime

Some ALTER TABLE operations take locks that block all traffic. Safer patterns:

• Adding a column with no default, or a constant default in modern PostgreSQL, is fast. Adding a column with a volatile default rewrites the table.
• Adding a NOT NULL constraint: add it as NOT VALID first, then VALIDATE CONSTRAINT separately, so the full check runs without a blocking lock.
• Renaming or dropping columns: use expand and contract. Add the new column, dual-write, backfill in batches, switch reads, and remove the old column in a later release.
• Set lock_timeout for migrations, so a migration waiting behind a long query fails fast instead of queuing all traffic behind it.

Backups you can trust

A backup you've never restored is a hope, not a backup.

Layers of protection

• Continuous WAL archiving + base backups enable point-in-time recovery (PITR). You can restore to any moment, such as the second before someone ran DELETE without a WHERE.
• Logical dumps (pg_dump) are portable and handy for single tables or migrations, but slow for large databases and not a substitute for PITR.
• Replicas are not backups. They replicate your mistakes instantly.

Define the targets

• RPO (Recovery Point Objective): how much data can you afford to lose? WAL archiving gets this down to seconds.
• RTO (Recovery Time Objective): how long can you be down? This depends on database size and restore speed, so measure it.

Test restores regularly

Automate a scheduled job that:

1. Restores the latest backup to an isolated instance.
2. Runs sanity checks, such as row counts and the most recent timestamps.
3. Records how long the restore took.
4. Alerts on failure.

Store backups in a separate account or region with restricted delete permissions, so an incident that compromises production can't also destroy the backups.

A monthly health checklist

• Top queries by total time reviewed
• Unused and duplicate indexes audited
• Bloat and dead-tuple trends checked
• Oldest transaction ID age within safe margin
• Disk growth projection updated
• Connection peak vs. limit reviewed
• Restore test passed, with duration recorded

Summary

Keep connections pooled and bounded, keep vacuum and statistics healthy, partition large time-based tables, watch capacity trends, change schemas incrementally, and test restores regularly. None of this is glamorous, but it keeps a database reliable year after year.