PostgreSQL performance best practices

SECTION 1

Introduction: PostgreSQL is still the reference in 2026

PostgreSQL is an open-source object-relational database known for its robustness, on the market for over 30 years. It has established itself as a reference for demanding workloads, driven by an active community, a steady annual release cadence and a rich extension ecosystem, from pgvector for vector search to TimescaleDB for time series.

Version 18, released in late 2025, illustrates this momentum: a new asynchronous I/O subsystem capable of tripling read throughput on some workloads, skip scan on multicolumn B-tree indexes, the uuidv7() function for more index-friendly identifiers, and preserved planner statistics across a major pg_upgrade. These advances reduce operational friction, but they do not remove the need for foundational tuning work.

Optimising PostgreSQL's performance remains a complex but essential task. There is no universal recipe: each deployment, multi-tenant SaaS, analytical warehouse, mission-critical transactional backend, calls for its own tuning. This guide covers the levers to know, from hardware to high availability, to get the best out of PostgreSQL in production.

SECTION 2

Tuning the server: memory, storage and CPU

It all starts with server configuration and understanding the underlying hardware. PostgreSQL exposes several hundred adjustable settings depending on the use case, hardware and data volume; tools like PGTune give a reasonable starting point before manual fine-tuning. The default auto-commit mode isn't a problem in itself, but write-heavy workloads often benefit from tuning wal_buffers and commit_delay to smooth out pressure on the WAL (write-ahead log).

### Memory, the first lever of performance

shared_buffers (typically 25% of available RAM) and effective_cache_size (often 50 to 75% of RAM) are the two settings that most influence execution plans: the more the planner estimates that data fits in cache, the more it favours index scans over sequential scans. work_mem, allocated per sort or hash operation, deserves particular attention: too low, it forces costly on-disk sorts; too high, it exposes the system to memory spikes on heavily parallelised queries.

### Storage: NVMe, IOPS and separating the WAL

NVMe storage is now the standard for demanding transactional workloads, with sub-millisecond latency and tens of thousands of sustained IOPS, far ahead of legacy SATA or SAN setups. Physically separating the WAL directory (pg_wal) from the data helps avoid write contention during commit spikes. On cloud platforms (RDS, Cloud SQL, Azure Database for PostgreSQL), the choice of storage tier and provisioned IOPS has a direct, measurable impact on p99 latency.

### CPU and query parallelism

CPUs only bring a real benefit for complex algorithms or parallelised queries: max_parallel_workers_per_gather and max_worker_processes let you exploit multiple cores on large aggregations and joins. Outside of those cases, prioritise RAM or faster storage over a more powerful CPU.

SECTION 3

Reading execution plans: EXPLAIN and statistics

### EXPLAIN ANALYZE, BUFFERS and the real cost of a query

The EXPLAIN command displays how the PostgreSQL optimiser will execute a query. In practice, EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) is the form to favour: ANALYZE actually runs the query and provides measured times, while BUFFERS reveals the number of blocks read from cache (shared hit) versus read from disk (shared read), a low cache-hit ratio on a frequent query is often the most reliable signal of a memory-sizing issue rather than a badly written query.

A tool like explain.dalibo.com or explain.depesz.com makes it easy to visualise the costliest nodes of a complex plan. Hunting down Seq Scans on large tables, poorly estimated Nested Loops and external sorts (external merge) remains the first step of any slowness diagnosis.

### Indexes, statistics and pg_stat_statements

The pg_stat_statements extension, enabled by default on most managed offerings, aggregates cumulative time and call count per normalised query: it's the entry point for prioritising optimisations on the queries that actually weigh on overall load, rather than those that occasionally look slow. Planner statistics (ANALYZE, autovacuum analyze) must stay up to date, especially after a bulk import or a version migration, or the planner ends up choosing plans based on stale estimates.

On the index side, multicolumn B-tree indexes have benefited since PostgreSQL 18 from skip scan, which allows an index to be used even when the query omits an equality condition on the prefix columns, an improvement that reduces the need to duplicate indexes to cover varying filter combinations.

SECTION 4

Mastering connections: pooling and scaling

### PgBouncer, PgCat and transaction mode

Every PostgreSQL connection consumes a backend process and several megabytes of memory; beyond a few hundred active connections, contention becomes noticeable. A pooler like PgBouncer, or its more recent Rust-based alternative PgCat (which adds load balancing across replicas and logical sharding), lets you multiplex thousands of application connections onto a small number of real server connections. Transaction mode, the most common in production, reuses the server connection as soon as each transaction ends rather than waiting for the client to disconnect.

### Native connections, prepared statements and limits

max_connections should stay reasonable (often a few hundred): raising it without a pooler to absorb a traffic spike generally degrades overall performance instead of improving it, due to contention on internal locks and the plan cache. Modern ORM frameworks (Prisma, SQLAlchemy 2.x, Hibernate) expose their own application-level pools, which need to be sized consistently with the upstream pooler to avoid a double bottleneck.

SECTION 5

Autovacuum, partitioning and ongoing maintenance

### Autovacuum: tune it instead of enduring it

PostgreSQL uses MVCC (Multi-Version Concurrency Control): every UPDATE or DELETE leaves dead tuples that autovacuum must clean up. On tables with heavy write volume, the default thresholds (autovacuum_vacuum_scale_factor at 20%) are often too high and let bloat accumulate between two passes. Lowering this threshold table by table via ALTER TABLE ... SET, and monitoring the bloat ratio with views like pgstattuple, avoids progressive performance degradation and the risk of transaction ID wraparound.

### Declarative partitioning and data retention

Declarative partitioning, mature since PostgreSQL 12 and significantly enhanced since, is now standard practice on event, log or time-series tables that exceed several tens of millions of rows. Partitioning by date range lets you purge old partitions with a simple DROP TABLE rather than a costly DELETE, and lets queries target recent data without scanning the full history. Extensions like pg_partman automate partition creation and retention.

SECTION 6

Monitoring, logs and long-running transactions

Monitoring provides real-time visibility over queries, connections and locks. Solutions like pganalyze, Datadog Database Monitoring or native cloud-provider offerings leverage pg_stat_activity, pg_stat_statements and autovacuum metrics to alert before degradation becomes visible to end users. It's also worth watching long-running transactions and idle-in-transaction connections: a process left idle for more than a few dozen seconds blocks vacuum and may signal an application bug to fix quickly via idle_in_transaction_session_timeout.

Finally, generating logs provides a valuable history for diagnosis. log_checkpoints makes it possible to track checkpoint operations, which can trigger write spikes; log_min_duration_statement isolates slow queries without logging the entire traffic. These traces, combined with monitoring dashboards, help understand performance jolts and correct them before they turn into an incident.

@cite:database-as-a-service-dbaas

SECTION 7

Replication, high availability and failover

Beyond optimising a single instance, perceived performance also depends on availability. Streaming replication, a long-standing native feature, remains the basic building block; orchestration tools like Patroni, paired with etcd or Consul for leader election, automate failover when the primary fails and cut downtime to a few seconds instead of several minutes of manual intervention.

Read replicas offload analytical or reporting queries from the primary, provided replication lag and its impact on the application's perceived consistency are properly managed. On the most critical workloads, correctly sizing synchronous_commit and choosing between synchronous and asynchronous replication must be an explicit trade-off between durability and latency, not a default left to chance.

@cite:patterns-haute-disponibilite-postgresql

SECTION 8

The Adservio approach

At Adservio, we start from a simple observation: there is no universal optimisation. Each implementation requires tailored work that takes into account the hardware, the infrastructure, the data volume and the specific needs of the workload. We favour measuring before acting, starting with the analysis of execution plans and pg_stat_statements metrics, before adding any resource or index.

Our support combines server tuning, hardware sizing, pooling and partitioning implementation, continuous monitoring and transaction management, in a logic of continuous improvement. We also work on migration projects, in particular from proprietary databases to PostgreSQL, where performance behaviour differences need to be anticipated from the design stage. We transfer these practices to your teams so they can sustainably maintain the performance of their PostgreSQL databases on their own.

@cite:migrer-d-oracle-vers-postgresql

FAQ

Frequently asked questions