GUC guide
Configuration guide structure, pending verified tuning evidence
This page currently helps review readability and organization. Verified tuning data and measured outcomes should appear only after real PostgreSQL testing.
Verification pending
Current GUC entries are draft educational summaries. They are not yet backed by measured before/after PostgreSQL lab runs in this environment.
| Parameter | Category | Default | Range | Why it matters |
|---|---|---|---|---|
| shared_buffers | Memory | 128 MB | 16 - 2147483647 | Controls the size of PostgreSQL's shared buffer cache and influences cache hit rate, checkpoint behavior, and memory pressure. |
| work_mem | Executor memory | 4 MB | 64 - 2147483647 | Sets per-operation memory for sorts, hashes, and similar executor nodes, so one query can consume it many times over. |
| deadlock_timeout | Locking | 1000 ms | 1 - 2147483647 | Controls how long a backend waits while blocked on a lock before PostgreSQL runs its deadlock-detection cycle check. It does not control lock waiting itself — only when the cycle check runs. |
| lock_timeout | Locking | 0 ms | 0 - 2147483647 | Aborts any statement that waits longer than this many milliseconds while trying to acquire a lock. A value of zero, the default, disables the timeout entirely — the statement then waits as long as it takes. |
| statement_timeout | Client connection | 0 ms | 0 - 2147483647 | Aborts any statement that runs longer than this many milliseconds. A value of zero, the default, disables the timeout entirely. |
| idle_in_transaction_session_timeout | Client connection | 0 ms | 0 - 2147483647 | Terminates a session that has an open transaction but is genuinely idle (no query in flight) for longer than this many milliseconds. A value of zero, the default, disables it. |
work_mem
Tune when sorts and hashes spill to disk, but model concurrency before changing it globally.
| Category | Executor memory |
|---|---|
| Default | 4 MB |
| Range | 64 - 2147483647 |
Tradeoffs
- • Too low: more disk spills during sorts and hash operations.
- • Too high: runaway memory consumption when many concurrent queries each allocate multiple work_mem slots.
- • Per-session overrides are often safer than globally increasing the setting.
Workload profiles
Reporting queries
Raise selectively for large hash joins and sorts.
Use SET LOCAL or role-specific settings where possible.
High-concurrency APIs
Keep defaults modest and tune only targeted workloads.
Concurrency math matters more than a single fast query benchmark.
deadlock_timeout
Leave at the default for almost all workloads. Only raise it temporarily, in a non-production session, when you need a wider window to directly observe an in-progress wait-for cycle with pg_stat_activity or pg_locks before PostgreSQL auto-resolves it.
| Category | Locking |
|---|---|
| Default | 1000 ms |
| Range | 1 - 2147483647 |
Tradeoffs
- • Too low: the deadlock-detection cycle check runs more often, adding CPU overhead even during ordinary, non-deadlocked lock contention.
- • Too high: a real deadlock takes longer to be cancelled, so both blocked sessions — and anything queued behind them — stall longer before PostgreSQL aborts one transaction.
- • The deadlock-detected (40P01) lock-visibility lab on this platform raised this to 6s for a single test run only, to create a wide, reliable window for a concurrent session to query pg_stat_activity and pg_locks and capture the real wait-for cycle before it resolved — not a production recommendation.
Workload profiles
General OLTP
Keep the 1s default.
1s is long enough to avoid false-positive cycle checks on ordinary short lock waits, while still detecting real deadlocks quickly.
Diagnosing an active deadlock
Temporarily raise it only in a non-production session while capturing pg_stat_activity/pg_locks output.
Revert immediately afterward — raising it in production delays how quickly PostgreSQL cancels a genuine deadlock.
lock_timeout
Set per-session or per-transaction (never in postgresql.conf, which would affect every session) whenever a statement should give up on a lock after a short, bounded wait instead of blocking indefinitely or failing instantly.
| Category | Locking |
|---|---|
| Default | 0 ms |
| Range | 0 - 2147483647 |
Tradeoffs
- • Too low (a few ms): ordinary, brief lock waits that would have succeeded start failing with SQLSTATE 55P03 for no real reason.
- • Too high: it stops being meaningfully different from leaving it disabled — the app still ends up waiting a long time before giving up.
- • The could-not-obtain-lock-on-row (55P03) lab on this platform measured lock_timeout = 500ms causing a real ~501.8ms wait before cancellation — proof the GUC's wait window is honored almost exactly, not just approximately.
Workload profiles
General OLTP
Leave at the default (0, disabled) unless a specific statement needs a bounded wait.
Most row-lock contention resolves in milliseconds; a global timeout is rarely the right tool.
Queue-like or worker tables
Set a short lock_timeout (e.g. 250-1000ms) per session around the specific locking statement, or use NOWAIT/SKIP LOCKED instead if any wait at all is unacceptable.
Tested in the could-not-obtain-lock-on-row (55P03) lab: NOWAIT fails instantly with zero wait, while lock_timeout fails only after the configured wait elapses — pick based on whether the app can tolerate a short wait.
statement_timeout
Set per-session, per-role, or per-transaction (SET LOCAL) wherever a statement should be aborted if it runs unexpectedly long, rather than blocking a connection indefinitely.
| Category | Client connection |
|---|---|
| Default | 0 ms |
| Range | 0 - 2147483647 |
Tradeoffs
- • Too low: statements that are legitimately slow (large reports, migrations) get canceled with SQLSTATE 57014 before they can finish.
- • Too high or disabled (the default): a single runaway query can hold resources indefinitely with nothing to stop it.
- • The query-canceled (57014) lab on this platform measured a 2-second query being canceled under a 500ms timeout, and the identical query completing once the timeout was raised to 3s — the GUC's limit is honored almost exactly, not approximately.
Workload profiles
General OLTP
Set a conservative connection-wide value (e.g. a few seconds) as a safety net, and override narrower per known-slow operations.
Prevents one stuck query from holding a connection and its locks indefinitely.
Reporting / batch jobs
Use SET LOCAL statement_timeout inside the specific transaction that needs more time, rather than raising it globally.
Tested in the query-canceled (57014) lab: repeatedly retrying the identical query under an unchanged timeout never succeeds — only raising the timeout (or fixing the query) does.
idle_in_transaction_session_timeout
Set as a safety net so an application bug or a paused debugger can't leave a transaction open — and its locks and old snapshot — indefinitely.
| Category | Client connection |
|---|---|
| Default | 0 ms |
| Range | 0 - 2147483647 |
Tradeoffs
- • Too low: a legitimate pause between statements in the same transaction (waiting on an interactive client, a slow app-side step) gets the whole connection terminated with SQLSTATE 25P03.
- • Disabled (the default): a forgotten open transaction can sit idle forever, holding locks and an old MVCC snapshot that blocks vacuum from cleaning up dead rows.
- • The idle-in-transaction-session-timeout (25P03) lab on this platform measured this directly: with the default (0, disabled), a transaction idle for 3 seconds survived; with the timeout set to 1 second, the same 3-second idle pause got the connection terminated.
Workload profiles
General OLTP
Set a conservative value (seconds, not milliseconds) as a safety net against forgotten open transactions.
Tested in the 25P03 lab: a busy query (pg_sleep) running inside the transaction does not trigger this timeout — only genuine idle time between statements does.
Interactive / long-lived app sessions
Keep transactions short and commit before waiting on user input or an external call, rather than relying solely on this timeout.
The timeout is a safety net, not a substitute for closing transactions promptly.