PostgreSQL Ops Checklist: 13 Pitfalls to Avoid (2026 Edition)

1. CPU

SQL performance is the most common cause of CPU spikes in PostgreSQL, often due to inefficient queries, missing indexes, sudden concurrency bursts, or plan changes. A DBA team that designs data models, access patterns, and indexes can dramatically reduce CPU usage.

1.1 Execution plan

PostgreSQL’s cost‑based optimizer can produce plan regressions. An example shows that the default sample size of 30 000 rows can underestimate distinct values for large tables, leading to inaccurate n_distinct estimates.

Table (re‑summarized):

Target statistics 50 → pages sample 0.00075, tuples sample 0.00001875, n_distinct 6 w, execution 2 s

Target statistics 100 → pages sample 0.0015, tuples sample 0.0000375, n_distinct 11 w, execution 5 s

Target statistics 1 000 → pages sample 0.015, tuples sample 0.000375, n_distinct 103 w, execution 58 s

Target statistics 3 000 → pages sample 0.045, tuples sample 0.001125, n_distinct 268 w, execution 3 min 1 s

Target statistics 10 000 → pages sample 0.15, tuples sample 0.00375, n_distinct 675 w, execution 7 min 21 s

From these results, n_distinct and ANALYZE time grow roughly linearly with sample size, while page and tuple ratios stay accurate.

1.2 Generic plan interference

PostgreSQL switches to a generic plan after five executions, using default cost estimates that may be far from reality. Two problem categories are identified: (1) the first five executions are not representative, and (2) the generic plan itself is inaccurate due to data skew or poor selectivity estimates.

Solutions include reducing unnecessary indexes, forcing a custom plan with SET plan_cache_mode='force_custom_plan'; or SET plan_cache_mode='force_generic_plan';, and on PostgreSQL 16+ using EXPLAIN (GENERIC_PLAN) to compare plans.

1.3 Row lock causing LWLock:Lockmanager

LWLock:Lockmanager contention can appear on partition tables under high concurrency or when updating the same row repeatedly. Although not a major issue, it can surface unexpectedly and should be considered when diagnosing lock‑related stalls.

2. Memory

Memory problems are subtle but critical. Large pages (hugepages) can reduce TLB pressure, shrink page‑table size, provide contiguous physical memory access, and eliminate multi‑level PTE lookups. However, they require pre‑allocation and careful sizing to avoid waste.

2.1 Large‑page benefits

Reduce TLB pressure

Reduce page‑table memory usage

Contiguous physical memory improves access speed

Direct mapping avoids multi‑level PTEs

2.2 Large‑page challenges

Must be allocated in advance

Size must be calculated to avoid memory waste

2.3 Idle connections

PostgreSQL 14 introduced significant improvements to snapshot handling, making idle‑connection overhead less severe. Nevertheless, many idle connections still consume backend resources, memory fragmentation, and context‑switch overhead. Empirical data from the author’s PG15 benchmark shows that increasing idle connections from 5 k to 10 k adds 2‑5 vCPU, and from 10 k to 20 k adds 5‑10 vCPU.

2.4 Idle‑in‑transaction

Common session states are active , idle , idle in transaction , idle in transaction (aborted) , fastpath function call , and disabled . “Idle in transaction” only means the backend is waiting inside a transaction; it does not indicate how long the transaction has been idle. Use state_change together with the state to measure true idle time.

3. Operating System

PostgreSQL runs on Linux and inherits many performance characteristics from the OS. Understanding CPU scheduling, memory allocation, and I/O paths is essential because PostgreSQL cannot exploit NUMA effectively. For CPU issues, focus on SQL and PostgreSQL’s internal stacks; for memory, refer to Section 2; for processes, monitor D‑state, wchan, RSS, and system calls.

Host‑level monitoring (CPU, memory, I/O, network, logs) is crucial. For example, an “I/O error while sending to the backend” often points to underlying storage problems, not PostgreSQL itself.

4. Physical reads

PostgreSQL does not expose true physical disk reads; metrics like pg_stat_database.blks_read reflect reads from the OS cache. The recommended approach is to monitor OS‑level I/O (e.g., iostat) rather than relying on database‑only counters.

5. Autovacuum

Use the provided SQL (e.g., SELECT * FROM pg_stat_progress_vacuum;) to monitor autovacuum progress. For large tables, age alerts should be addressed promptly, and the cost‑based delay can be tuned or disabled to reduce impact. Example: disabling the delay on a 200 GB table with a 280 GB index can cut execution time by up to threefold.

6. Checkpoint and bgwriter

Checkpoint interval is controlled by checkpoint_timeout; the number of checkpoints triggered by WAL size is tracked by max_wal_size. Recommended practice: use checkpoint_timeout as the primary interval and increase max_wal_size if “checkpoints_req” appears.

Bgwriter parameters: bgwriter_delay – pause between writes bgwriter_lru_maxpages – maximum pages written per cycle (high‑load ceiling) bgwriter_lru_multiplier – scales writes based on recent buffer allocation bgwriter_flush_after – triggers fsync after a certain amount of data

Default logic: write min(2*new_buffers, 100) dirty buffers, delay 200 ms, fsync every 64 buffers. Under heavy load, bgwriter_lru_maxpages caps the write rate, while bgwriter_lru_multiplier smooths low‑load activity.

7. DB4AI

AI task scheduling can generate bursty writes that spike CPU and other resources. The author references pgvector best practices and shows that HNSW index construction can be extremely slow (hours for millions of rows). Tuning parameters such as maintenance_work_mem=3g, max_parallel_maintenance_workers=2, ef_construction=100 can accelerate builds.

Performance difference when the HNSW index is cached vs not cached can be >4000× (execution time 82 s vs 20 ms). Use

SELECT c.relname, pg_size_pretty(count(*)*8192) AS buffered, round(100.0*count(*)/ (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffer_percent FROM pg_class c JOIN pg_buffercache b ON b.relfilenode=c.relfilenode GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 10;

to verify index caching.

8. Online DDL

Online DDL tools like pg‑osc and pg_migrate lack partition support and have other limitations. Practical DDL tricks include:

Ensuring no long‑running transactions before a change

Disabling autovacuum that prevents wraparound during the operation

Setting lock_timeout=2000 to avoid prolonged blocking

Using ALTER TABLE … ALTER COLUMN … TYPE … carefully (e.g., int→bigint)

For partitioned tables, prefer CIC with USING INDEX and attach indexes to child tables

Validate constraints with VALIDATE CONSTRAINT instead of recreating them

9. Concurrent index creation

Parallel index creation can reduce build time for large tables. Set max_parallel_maintenance_workers and ensure sufficient max_parallel_workers and max_worker_processes. Increase maintenance_work_mem (e.g., to several GB). Works for B‑tree and BRIN indexes; benefit plateaus after ~8 workers. For partitioned tables, create indexes on each child manually rather than relying on native parallelism.

10. Cached plan errors

Changing a column type after a prepared statement leads to “cached plan must not change result type”. Workarounds: DEALLOCATE ALL; (or DISCARD ALL;) to drop prepared statements

Restart the application or connection pool

In production, rely on JDBC’s ability to detect and retry after

DEALLOCATE ALL

11. Physical replication – query conflicts

Query conflicts on replicas increase replication lag, especially when long queries push the standby past max_standby_streaming_delay. Recommendations:

Adjust max_standby_streaming_delay based on RTO/SLO targets

Separate short‑running analytical queries from long‑running reporting queries onto different replicas

Monitor replica log‑apply delay and query performance

12. Logical replication

Older PostgreSQL versions (≤ 13) parse certain DDL/DCL statements slowly, affecting walsender performance. Upgrading to newer versions removes the costly PostmasterIsAlive() loop and improves concurrency. Versions < 13 cannot automatically sync new partitions; each child must be added to the publication manually.

13. Migration and upgrade

glibc upgrades can break collation and sorting, especially when moving to Unicode 9.0 (glibc 2.28). PostgreSQL 17 introduces a builtin locale provider to avoid OS‑dependent collation issues (e.g.,

initdb --locale-provider=builtin --builtin-locale=C.UTF-8 mydb

).

Zero‑downtime major upgrade strategy (“silky upgrade”) combines physical base‑backup, logical replication, and switchover:

Pre‑check: verify extensions, triggers, foreign keys, unlogged tables, etc.

Physical sync: set up a low‑version replica via pg_basebackup Logical sync preparation: create publication, ensure primary keys, disable DDL during sync

Recover new replica to target LSN

Upgrade new replica to target major version

Finalize logical sync, rebuild indexes, resolve errors

Switchover: stop traffic, promote new replica, re‑enable constraints and triggers

Optionally set up reverse logical replication for fallback

14. Partition table management

Prefer declarative partitioning (PostgreSQL 12+). Avoid pathman or inheritance‑based partitions. Ensure indexes and primary keys are defined on the parent and inherited automatically. Proactively create new partitions; avoid default partitions unless actively monitored. Queries should include the partition key to enable partition pruning; otherwise consider converting the table to a regular table.

15. Observability

Key metrics to watch:

pg_stat_bgwriter.buffers_alloc – number of shared‑memory buffer allocations (represents actual memory churn)

pg_stat_database.blks_read – OS‑cache reads

tup_fetched vs tup_returned – rows returned to the client vs rows fetched from tables (high tup_returned indicates wasted work)

idx_tup_read vs idx_tup_fetch – index entries examined vs rows returned after index lookup

Understanding these helps pinpoint whether bottlenecks lie in I/O, CPU, or inefficient query plans.