PostgreSQL Performance Tuning & Operations - Complete Notes

A measurement-first, practical and interview-focused guide to server resources, memory, planner settings, connection pooling, observability, slow-query diagnosis, partitioning, capacity planning, and safe production operations.

00. The measurement-first mental model

Performance tuning is controlled problem solving. Define the user-visible problem, capture a baseline, locate the constrained resource, form one testable hypothesis, change the smallest relevant thing, and compare the same measurements again.

01. Workload, latency, throughput and queues

A useful baseline describes both demand and service. Latency says how long one operation takes; throughput says how many complete per unit time; concurrency says how many are in flight.

• Latency: report percentiles, not only averages. A 20 ms average can hide a 2 second p99 that harms real users.
• Throughput: requests, transactions, queries, rows, or bytes completed per second. Name the unit and whether failed work is counted.
• Concurrency: active requests, active database backends and transactions. More concurrency raises throughput only until a constrained resource saturates.
• Utilization: the fraction of resource capacity in use. Near saturation, small demand increases can create disproportionate queueing delay.
• Service time versus wait time: a query can be slow because it is doing work or because it is queued behind a lock, CPU, I/O, connection pool, or rate limit.

Little's Law gives a valuable consistency check for a stable system: concurrency = throughput × average time in system. If 200 requests finish each second and average end-to-end time is 0.05 seconds, about 10 requests are in flight on average. If latency grows while throughput is flat and concurrency rises, work is probably queueing.

02. Find the constrained resource

PostgreSQL performance is bounded by CPU, memory, storage, network, locks, connection slots, or the amount of work requested. Tuning the wrong resource often moves no useful metric.

CPU utilization alone is incomplete. One busy core can limit a serial query while total host CPU looks low. Conversely, 100 percent CPU can be healthy at peak if latency stays within target and headroom is deliberate. Storage IOPS alone is also incomplete: latency and queue depth show whether requests are completing promptly.

03. Shared buffers, OS cache and huge pages

PostgreSQL reads table and index blocks through its shared buffer cache, while the operating system also caches file data. Memory planning must leave room for both caches, backend memory, maintenance, the kernel, monitoring, poolers and other processes.

shared_buffers

shared_buffers is the amount of memory PostgreSQL dedicates to shared database blocks. It is allocated at server start. Current official guidance suggests about 25 percent of RAM as a reasonable starting point on a dedicated server with at least 1 GB, then measurement. Allocating more than roughly 40 percent is unlikely to work better because PostgreSQL also relies on the OS cache. These are starting points, not universal formulas.

• A buffer hit avoids a filesystem read but still consumes CPU, locking and memory bandwidth.
• A shared-buffer miss can still be satisfied quickly from the OS page cache.
• Increasing the setting does not repair a query that reads millions of unnecessary rows or lacks a suitable index.
• A larger dirty-buffer population can increase checkpoint work. Official guidance notes that a larger value often needs a corresponding max_wal_size increase to spread writes.
• The database-wide block hit ratio is not a sufficient target. Sequential analytics can have a low ratio and be healthy; a tiny hot OLTP working set can have a high ratio while one query is still bad.

Huge pages

Huge pages map the main shared memory region with fewer page-table entries. This can reduce CPU time spent managing memory mappings, especially with large shared memory. On supported systems, huge_pages = try requests them and falls back if unavailable, on makes failure fatal, and off disables the request. Check SHOW huge_pages_status after startup rather than assuming they were used.

04. Query and maintenance memory

PostgreSQL memory settings are different budgets with different multiplication rules. work_mem is not a per-session or per-query cap.

work_mem and multiplication

Each sort, hash table, hash aggregate, Memoize cache and similar executor operation can use a memory allowance based on work_mem before temporary-file work. One plan may contain several such nodes. Parallel workers may each build their own state, and many sessions may do this together. Hash operations derive their limit from work_mem × hash_mem_multiplier.

Potential executor memory
  ≈ active memory nodes
  × processes participating per query
  × concurrent heavy queries
  × applicable memory allowance

Example risk:
  3 sort/hash nodes × 3 processes × 20 queries × 64 MB
  = 11,520 MB before backend overhead and other memory

Nodes do not always peak simultaneously and may use less than their allowance, so this is not an exact reservation formula. It is a safety model showing why a global jump from 4 MB to 256 MB can exhaust a server. Prefer reducing input rows and row width. Then use a scoped experiment for a known reporting job.

BEGIN;
SET LOCAL work_mem = '128MB';

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, SUM(total)
FROM orders
WHERE ordered_at >= DATE '2026-01-01'
GROUP BY customer_id;

COMMIT;

maintenance_work_mem

maintenance_work_mem is used by operations such as VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY. A larger budget can make index creation or vacuum work faster, but concurrent maintenance still multiplies memory. Each autovacuum worker can use memory up to autovacuum_work_mem, or inherit maintenance_work_mem when the former is -1. Budget for the configured and observed worker count.

05. Planner cache, I/O costs and statistics

Planner settings describe PostgreSQL's cost model. They do not make hardware faster. Change them only when measurements show the model consistently misrepresents the environment.

effective_cache_size should consider shared buffers plus the useful portion of the OS file cache, adjusted for concurrent queries and overlap. It changes estimated index costs but allocates nothing. A higher estimate can make index scans look more attractive.

effective_io_concurrency tells PostgreSQL how much parallel I/O the storage can usefully handle and controls prefetch distance on supported systems. Current releases allow values from 1 to 1000, with 0 disabling asynchronous I/O requests for this purpose. A value that helps a high-IOPS device can overload slower or shared storage, so compare query latency and device queueing. Tablespaces can override relevant I/O and cost settings.

Lowering random_page_cost relative to seq_page_cost favors index access. Equal values can make sense for a fully cached database. A random cost below the sequential cost is not physically sensible. Calibrate with a family of representative queries, not one plan.

Statistics quality before cost tuning

The planner estimates row counts from table size and statistics produced by ANALYZE. Stale statistics, skew, correlated columns and parameter-sensitive distributions can cause wrong scan, join and memory choices. First run or confirm auto-analyze, compare estimated and actual rows, and inspect pg_stats.

ALTER TABLE orders
  ALTER COLUMN tenant_id SET STATISTICS 1000;

ANALYZE orders (tenant_id);

-- Return to the database default if the larger target is no longer useful.
ALTER TABLE orders
  ALTER COLUMN tenant_id SET STATISTICS -1;

Larger targets allow larger most-common-value lists and histograms and use a larger sample, at the cost of more analysis time and catalog space. Use extended statistics for cross-column dependencies, multivariate common values, or distinct counts. Increasing an unrelated global target cannot fix missing correlation knowledge by itself.

06. Checkpoints and WAL performance overview

WAL protects durability and checkpoints bound recovery work. Operational tuning aims to avoid bursty writes while preserving the required durability and recovery objectives.

• checkpoint_timeout bounds time between automatic checkpoints. A longer interval can mean more crash-recovery work.
• max_wal_size is a soft WAL-size threshold that can trigger checkpoints. It is not a strict disk-space cap.
• checkpoint_completion_target, default 0.9, spreads checkpoint writes across most of the interval. Lowering it usually creates a sharper I/O burst.
• Frequent requested checkpoints suggest WAL volume is reaching the threshold or explicit checkpoints are occurring. Compare timed and requested counters, WAL rate and storage latency.
• wal_compression can reduce full-page-image WAL volume at a CPU cost. Measure both.
• Relaxing synchronous_commit can reduce commit wait for selected transactions but changes the window in which recently acknowledged transactions can be lost after an OS crash. It does not make the database inconsistent. Use only with an explicit durability decision.

07. Connection model, backend cost and pooling

PostgreSQL starts a separate server process for each accepted client connection. Connections are useful sessions, but active SQL concurrency must be bounded by what CPU, memory and storage can serve efficiently.

Each backend has process and session state, can allocate private executor memory, participates in shared structures, and adds scheduling work. Idle connections are cheaper than active heavy queries but are not free. Hundreds of clients all becoming active can multiply work_mem, lock contention and CPU run queues at once.

max_connections is a hard server admission limit, not a target concurrency. Raising it also increases some shared resource allocations and can convert a clean connection rejection into host-wide memory pressure and timeouts. Keep emergency slots through reserved_connections and superuser_reserved_connections as appropriate, and test the actual administrative path.

• Size pools across all application instances, jobs, consoles and replicas, not per instance.
• Reserve capacity for migrations, monitoring, autovacuum and emergency administration.
• Use finite pool-acquisition and statement timeouts aligned with the request deadline.
• Monitor pool wait time and queue length. A pool can protect PostgreSQL while exposing demand.
• Avoid one large pool per tenant or role without calculating the total server connections.

08. PgBouncer modes and compatibility

PgBouncer is a lightweight connection pooler. Its pool mode decides when a PostgreSQL server connection may be reassigned to another client, which also decides which session features remain safe.

In transaction pooling, consecutive transactions from one client can run on different server backends. Session-level SET, LISTEN, session advisory locks, holdable cursors, and temporary tables that preserve rows cannot be assumed to follow the client. Transaction-local state such as SET LOCAL remains tied to its transaction.

PgBouncer can support protocol-level named prepared statements in transaction and statement modes when max_prepared_statements is nonzero. SQL-level PREPARE, EXECUTE and DEALLOCATE do not receive that tracking and remain incompatible with transaction pooling. Confirm client driver behavior and the deployed PgBouncer version. DDL that changes prepared result types may require a planned RECONNECT.

09. Monitoring semantics and safe observation

PostgreSQL exposes current activity and cumulative counters. Know whether a value is a live state, a counter since reset, a transaction-scoped snapshot, or an optional timing metric before drawing conclusions.

• pg_stat_activity is current per-process state. It is not a historical query log.
• Cumulative views such as pg_stat_database describe totals since stats_reset. Compare rates over equal intervals.
• Statistics fetched inside a transaction can be cached for consistency. Run monitoring queries in short transactions so repeated reads can see fresh values.
• I/O timing columns remain zero unless track_io_timing is enabled, and mixed periods before and after enabling it require caution.
• Access is permission-sensitive. Use pg_read_all_stats for an appropriate monitoring role instead of making tools superusers.
• Resetting statistics removes the baseline. Record the reset time and preserve monitoring data externally before intentional resets.

10. Activity, locks and wait events

Start an incident by separating active CPU/I/O work, lock waits, client waits and idle transactions. The state and wait_event columns answer different questions.

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS query_age,
       now() - xact_start AS transaction_age,
       backend_xid,
       backend_xmin,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

state = 'active' means the backend is executing a query, but a non-null wait event means it is currently waiting somewhere. idle in transaction is operationally dangerous because it can retain locks and an old snapshot even though no statement is executing. ClientRead usually means PostgreSQL is waiting for the client to send more work, not that storage reads are slow.

SELECT blocked.pid AS blocked_pid,
       blocked.wait_event_type,
       blocked.wait_event,
       blocker.pid AS blocker_pid,
       blocker.state AS blocker_state,
       now() - blocker.xact_start AS blocker_xact_age,
       left(blocked.query, 120) AS blocked_query,
       left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL
     unnest(pg_blocking_pids(blocked.pid)) AS b(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = b.blocker_pid;

pg_locks provides lockable-object detail and granted status, while pg_blocking_pids() handles blocker relationships, including wait-queue effects, more reliably than a simplistic self-join. Termination is a last step: identify the owner, business effect, transaction and rollback cost. Prefer fixing transaction scope, stable lock order and timeouts.

11. Database, I/O and checkpoint statistics

Cumulative views answer cluster and database trend questions. Convert counters into interval rates and pair them with workload volume.

SELECT datname,
       xact_commit,
       xact_rollback,
       blks_read,
       blks_hit,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_written,
       deadlocks,
       stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

A block hit percentage from blks_hit / (blks_hit + blks_read) describes PostgreSQL shared-buffer activity, not the OS cache and not physical-device reads. A high percentage does not prove queries are efficient. Use pg_stat_io to distinguish backend types and I/O contexts, and use host telemetry for device-level latency and saturation.

12. Finding expensive SQL with pg_stat_statements

pg_stat_statements groups structurally equivalent statements and accumulates planning and execution metrics. It helps rank total workload cost, average cost, calls, I/O and variability.

# postgresql.conf, then restart because shared memory is required
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

-- Run in every database where the view should be available.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The library tracks across the cluster, while the SQL extension objects are installed per database. Entries are keyed by database, user, query identifier and top-level status. Literal constants are normalized, so a representative query text can contain placeholders. queryid is valuable for joining telemetry but is not guaranteed stable across PostgreSQL major versions.

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_exec_ms,
       round(mean_exec_time::numeric, 2) AS mean_exec_ms,
       round((total_exec_time / NULLIF(sum(total_exec_time) OVER (), 0)
              * 100)::numeric, 2) AS exec_share_pct,
       rows,
       shared_blks_read,
       temp_blks_written,
       left(query, 180) AS sample_query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

• Total time: best starting point for overall capacity savings.
• Mean time: finds individually slow calls but can hide tail latency.
• Calls: a tiny query can dominate CPU through extreme frequency.
• Rows: relate returned/affected rows to calls and application expectations.
• Shared blocks: reveal cache and read demand; timing needs I/O timing enabled.
• Temporary blocks: reveal spill volume by statement family.
• WAL bytes: reveal write amplification for modifying statements.
• Standard deviation: large variation suggests skew, cache, locks, plans or parameter-dependent behavior needing traces or logs.

Planning columns remain zero unless pg_stat_statements.track_planning is enabled. That option can add noticeable contention for frequently planned identical queries, so enable it after evaluating overhead. Track reset time and query-entry deallocations from pg_stat_statements_info. Reset only with a deliberate measurement window.

13. A repeatable slow-query workflow

Query tuning starts with the exact workload context and ends with production verification. It is not a contest to make EXPLAIN display an index.

1. Define impact: endpoint/job, time window, p95/p99, timeout rate, users, and SLO.
2. Check waits: determine whether time is in a pool queue, lock, client, I/O, or execution.
3. Identify the statement family: use trace context, logs, query ID and pg_stat_statements.
4. Reproduce context: representative parameters, role, search path, prepared-plan behavior, data volume, statistics and relevant settings.
5. Capture the plan safely: start with EXPLAIN; use EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) only when execution is safe.
6. Find the first cause: earliest major estimate error, excessive rows/width, repeated loops, bad access path, spill, or lock.
7. Change one cause: SQL, index, statistics, data model, transaction scope, or a scoped configuration.
8. Compare: result correctness, plan, buffers, temp I/O, WAL, latency under concurrency, CPU and storage.
9. Roll out and watch: canary where possible, preserve rollback, and inspect fleet and tail metrics.

14. Slow logging and auto_explain

Statistics find aggregate offenders. Logs and traces preserve individual occurrences, parameters, timing context and errors. Capture enough evidence without leaking data or overwhelming storage.

• log_min_duration_statement = '250ms' logs every completed statement at or above the threshold. The default -1 disables it; zero logs every duration.
• log_min_duration_sample with log_statement_sample_rate samples qualifying traffic when logging every query would be too expensive.
• Include timestamp, process/session identity, database, user and application in log_line_prefix. Use structured CSV or JSON logs when the pipeline supports them.
• Parameter values and SQL can contain secrets or personal data. Apply access control, retention, redaction and a deliberate log_parameter_max_length_on_error policy.
• log_temp_files identifies large temporary files. Select a threshold that is useful at production volume.

auto_explain can log plans for statements crossing auto_explain.log_min_duration. Load it only into intended sessions or through a planned preload setting. auto_explain.log_analyze = on collects actual execution detail, but per-node timing can affect every statement even when it is not eventually logged. Sampling and auto_explain.log_timing = off can reduce overhead. Test overhead and log volume before production rollout.

15. Partitioning mental model and syntax

A partitioned table is a logical parent whose rows are routed into physical child tables according to partition bounds. Partitioning is primarily a data-management and pruning tool, not a universal substitute for indexes.

CREATE TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY,
  occurred_at timestamptz NOT NULL,
  tenant_id bigint NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (occurred_at, event_id)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

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

CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE TABLE invoices (
  region text NOT NULL,
  invoice_id bigint NOT NULL,
  amount numeric(12,2) NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE invoices_apac PARTITION OF invoices
FOR VALUES IN ('IN', 'SG', 'JP');

CREATE TABLE sessions (
  tenant_id bigint NOT NULL,
  session_id uuid NOT NULL,
  data jsonb
) PARTITION BY HASH (tenant_id);

CREATE TABLE sessions_h0 PARTITION OF sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- Create remainders 1, 2 and 3 as well.

Inserts through the parent are routed to a matching leaf. If no partition matches and no default exists, the insert fails. Updating a partition key can move a row between partitions and may produce serialization failures when concurrent sessions see the movement.

16. Pruning, indexes and constraints

Partition pruning removes child plans whose bounds cannot match the predicate. It is driven by partition bounds, not by indexes, and can occur both while planning and during execution.

EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, count(*)
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-07-10 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-07-11 00:00:00+00'
GROUP BY tenant_id;

A prepared parameter, subquery result, or nested-loop parameter may become known only during execution, so PostgreSQL can prune at plan initialization or repeatedly during execution. EXPLAIN may report Subplans Removed; execution-time pruning can appear as (never executed) or different loop counts.

• Functions or casts around the key can prevent the planner from proving the bound. Prefer direct, type-correct range predicates where semantics allow.
• An index on the partition key is not required for pruning. Add per-partition indexes for selective access inside partitions.
• Creating an index on the parent creates matching indexes on existing partitions and arranges them for future partitions.
• Parent CREATE INDEX does not support CONCURRENTLY. For a busy hierarchy, create an invalid index on ONLY the parent, build child indexes concurrently, then attach them.
• A primary key or unique constraint on a partitioned table must include all partition-key columns and use columns, not expressions, because PostgreSQL cannot enforce cross-partition uniqueness with independent child indexes otherwise.
• Foreign keys and triggers need explicit operational testing across the hierarchy.

17. Partition maintenance and lifecycle

The major operational benefit of partitioning is treating a bounded slice as a table: prepare it, load it, index it, attach it, detach it, archive it, or drop it.

1. Create future partitions before the first possible row arrives.
2. Apply owners, privileges, indexes, storage parameters and monitoring consistently.
3. Alert on rows entering a default partition and move them deliberately.
4. Analyze a newly loaded partition before serving plan-sensitive reads.
5. Test retention detach/drop locks and dependent objects before the production window.
6. Keep a catalog/runbook of bounds so gaps and overlaps are reviewed before deployment.

Attaching an existing table normally requires PostgreSQL to scan it while holding a strong lock to verify the proposed bound. Add a matching validated CHECK constraint first so the scan can often be avoided, then remove the redundant constraint after attachment if appropriate. If a default partition exists, add a constraint proving it excludes the new range; otherwise the default partition may be scanned under ACCESS EXCLUSIVE lock.

CREATE TABLE events_2026_09 (LIKE events INCLUDING DEFAULTS
                                        INCLUDING CONSTRAINTS
                                        INCLUDING STORAGE);

ALTER TABLE events_2026_09
  ADD CONSTRAINT events_2026_09_bound
  CHECK (occurred_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
     AND occurred_at <  TIMESTAMPTZ '2026-10-01 00:00:00+00');

-- Load, validate, index and analyze before the short attach operation.
ALTER TABLE events ATTACH PARTITION events_2026_09
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

DETACH PARTITION CONCURRENTLY, where its restrictions permit, reduces the parent lock level but uses multiple transactions and cannot run inside a transaction block. Version and hierarchy restrictions matter, so test the exact command. Detaching or dropping a whole old partition avoids row-by-row delete work and the vacuum burden that follows a massive DELETE.

18. When partitioning helps or hurts

Too many surviving partitions increase planning time and memory. PostgreSQL can handle thousands well when typical queries prune to a small subset, but that is not permission to create unlimited children. Model future tenant count, retention periods and subpartition growth. Benchmark inserts, point queries, broad reports, prepared statements and DDL at the expected partition count.

19. Capacity planning

Capacity planning connects business growth to resource demand and preserves headroom for bursts, maintenance, failover and degraded operation.

1. Forecast data rows, table/index bytes, WAL bytes, backups and logs over the planning horizon.
2. Measure peak and sustained transactions/s, query mix, concurrent active sessions and result volume, not only daily averages.
3. Record CPU seconds, read/write bytes, IOPS, WAL and temporary bytes per business unit such as 1,000 orders.
4. Model memory as shared allocations plus backend/session memory, query operations, autovacuum, maintenance, OS cache, kernel and safety margin.
5. Model storage for primary data, indexes, bloat allowance, pg_wal, temporary files, snapshots/backups and growth during maintenance.
6. Load test at future data size and realistic skew. Small synthetic tables produce misleading plans and cache behavior.
7. Test the degraded case: replica unavailable, backup running, autovacuum active, cache cold, or one storage path slower.

Alert on predicted exhaustion, not only current failure. Disk-space runway in days, WAL generation rate, connection-pool saturation, oldest transaction, replica lag, vacuum backlog and CPU headroom are leading indicators. Capacity also includes recovery time: a backup that cannot be restored within the objective is not adequate capacity.

20. OS, storage and network observations

PostgreSQL statistics explain database behavior; operating-system metrics confirm what the hardware actually did. Correlate them on the same timeline.

• CPU: per-core utilization, run queue, context switches and steal time in virtual environments. Separate user, system and I/O wait time.
• Memory: available memory, page faults, reclaim, swap activity and OOM events. Swap-in during database load is a serious latency warning.
• Storage: read/write latency percentiles, operations/s, bandwidth, queue depth, flush latency and device errors. Confirm filesystem and volume saturation.
• Space: data filesystem, WAL filesystem, temporary space, inode use and snapshot amplification. Keep emergency headroom and automatic growth alerts.
• Network: throughput, retransmits, connection establishment and cross-zone latency. Large result sets can make the client or network the bottleneck.

Storage durability claims must cover power-loss behavior, write caches, barriers and the complete stack. A fast benchmark does not prove durable fsync. For managed services, combine provider metrics with PostgreSQL views and document which knobs are unavailable.

21. Safe tuning rollout and runbooks

A performance change is a production change. It needs a hypothesis, owner, validation, rollback, observation window and clear stop conditions.

1. Capture configuration, plan and dashboard screenshots or exports before change.
2. Confirm whether the setting reloads, needs a new session, or needs restart.
3. Test syntax and startup in a non-production environment using the same major version.
4. Change one variable or one coherent change set with a known rollback.
5. Canary a role, session, replica, instance or traffic slice when semantics allow.
6. Compare both target queries and guardrails such as CPU, memory, WAL, locks and other endpoints.
7. Record the decision and remove temporary settings or instrumentation after the window.

Some settings apply at server start, some after reload, and some per session. Use pg_settings.context, pending_restart, source and sourcefile to verify the effective value and its origin. Do not assume editing a file changed the running server.

22. Common mistakes and edge cases

• A connection can be active and waiting, so state alone does not prove CPU use.
• Session statistics and settings can behave differently through transaction pooling.
• Statistics may lag or reset after restart depending on the facility and configuration.
• Generic prepared plans can hide tenant skew; compare representative custom and generic plans.
• A partition default can silently grow forever if no alert and migration process exists.
• Failover capacity must handle the surviving topology, not only normal balanced traffic.

23. Practice questions

24. Interview Q&A

25. One-screen cheat sheet

• Goal: meet latency, throughput, correctness, durability and cost targets.
• Method: baseline, constrain, hypothesize, one change, compare, monitor.
• Latency: use p50/p95/p99; averages hide tails.
• Queueing: flat throughput plus rising latency/concurrency is a warning.
• shared_buffers: PostgreSQL block cache; leave room for OS and processes.
• effective_cache_size: planner estimate only; allocates no memory.
• work_mem: per operation, multiplied by nodes/workers/sessions.
• maintenance_work_mem: maintenance budget; account for worker concurrency.
• huge pages: verify huge_pages_status after startup.
• I/O costs: relative planner units, not device milliseconds.
• Statistics: fix stale/skew/correlation estimates before forcing plans.
• Checkpoints: compare timed/requested, WAL rate and write/sync time.
• Connections: process per client; active concurrency is the expensive part.
• Pooling: bound server connections; monitor pool wait and queue.
• PgBouncer: transaction mode forbids relying on persistent session state.
• Activity: combine state, wait event, query age and transaction age.
• Locks: use pg_blocking_pids() plus activity and lock detail.
• Counters: calculate deltas/rates and preserve stats_reset.
• pg_stat_statements: total time, calls, mean, variability, I/O and WAL.
• Logging: threshold/sample safely; protect parameter data.
• Partitioning: require aligned pruning or lifecycle benefit.
• Pruning: driven by bounds, not indexes; can happen during execution.
• Default partition: monitor it and constrain it before new attachments.
• Capacity: test future size, peak mix, maintenance and degraded operation.
• Rollout: effective setting, canary, guardrails, stop condition and rollback.

26. Official reference map

• Resource consumption, memory, huge pages and I/O concurrency
• Planner cost constants, effective cache size and statistics target
• Statistics used by the planner
• WAL and checkpoint configuration
• Connection limits and reserved connection slots
• PostgreSQL server process and connection handling
• Activity, waits and cumulative statistics views
• pg_locks view
• Session information and pg_blocking_pids
• pg_stat_statements setup, metrics and query identifiers
• Statement duration, sampling and log configuration
• auto_explain configuration and overhead
• Declarative partitioning, pruning, indexes and maintenance
• Official PgBouncer configuration
• Official PgBouncer pooling-mode feature map