This is the multi-page printable view of this section. .
Statistics
- 1: compute_query_id
- 2: log_executor_stats
- 3: log_parser_stats
- 4: log_planner_stats
- 5: log_statement_stats
- 6: stats_fetch_consistency
- 7: stats_temp_directory
- 8: track_activities
- 9: track_activity_query_size
- 10: track_cost_delay_timing
- 11: track_counts
- 12: track_functions
- 13: track_io_timing
- 14: track_wal_io_timing
Dossier URLs remain flat; this category exists only to organize browsing and the sidebar.
1 - compute_query_id
Fact — official short description: “Enables in-core computation of query identifiers.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- auto
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG14 |
| Present in | PG14–19 Beta 3 |
| Removed in | No |
| Introduction commit | 5fd9dfa5f50e — Move pg_stat_statements query jumbling to core. |
| Commit date | 2021-04-07 |
| Discussion | thread 1 |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG14–19 Beta 3 | auto |
— | auto |
How it works
compute_query_id controls PostgreSQL’s in-core normalized query identifier. The identifier can appear in pg_stat_activity, EXPLAIN, and logs, and is required by pg_stat_statements unless another module computes one.
auto lets a module request in-core computation, on always computes, off prevents it, and regress behaves like auto while suppressing the ID in EXPLAIN for stable regression output.
Only one provider should compute the identifier. An extension with an alternative algorithm must disable the in-core implementation and detect conflicts rather than silently publishing two incompatible IDs. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Use auto when pg_stat_statements or another module should request the in-core identifier, and use on only when logs, EXPLAIN, or monitoring require IDs without such a module. Measure query-normalization overhead at peak parse/plan rates. |
| OLAP | auto or on can make long analytical statements easier to correlate across pg_stat_activity, EXPLAIN, and logs. The cost is query-tree normalization and hashing, not clock reads; compare planning CPU on workloads with very large statements. |
| Small nodes | Keep auto unless an external query-ID provider requires off. regress is for stable regression output, not a smaller production setting, and off can remove IDs required by pg_stat_statements. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG14–19 Beta 3 unmodified; OLAP: PG14–19 Beta 3 unmodified; CRIT: PG14–19 Beta 3 unmodified; TINY: PG14–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling the in-core provider while an extension also attempts to compute a different query identifier.
- Setting off and silently losing IDs required by pg_stat_statements, logs, or correlation tooling.
- Treating regress as a production optimization rather than a test-output mode.
- Using query IDs as stable cross-major-version or cryptographic identifiers.
Related parameters
track_activities · shared_preload_libraries · log_line_prefix · track_activity_query_size
References
2 - log_executor_stats
Fact — official short description: “Writes executor performance statistics to the server log.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | off |
— | off |
How it works
log_executor_stats emits executor-stage resource usage for every query using a crude getrusage-style profiler. Output goes to the server log and is intended for short diagnostic sessions.
log_statement_stats reports the total statement, while parser, planner, and executor switches report individual phases. The total switch cannot be enabled together with any per-phase switch.
This is synchronous diagnostic logging rather than the cumulative statistics system. Volume and formatting make it unsuitable as routine production telemetry. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Do not enable log_executor_stats as normal OLTP tuning. Use it briefly on an isolated reproduction or one controlled session, collect the needed log, then disable it. |
| OLAP | For a specific analytical statement, EXPLAIN (ANALYZE, BUFFERS) and cumulative views are usually more actionable than cluster-wide log_executor_stats. Scope any use and budget log volume. |
| Small nodes | Leave log_executor_stats=off. A small host is especially vulnerable to diagnostic log I/O and storage exhaustion. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging log_executor_stats without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
log_statement_stats · log_parser_stats · log_planner_stats · log_min_duration_statement · track_io_timing
References
3 - log_parser_stats
Fact — official short description: “Writes parser performance statistics to the server log.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | off |
— | off |
How it works
log_parser_stats emits parser-stage resource usage for every query using a crude getrusage-style profiler. Output goes to the server log and is intended for short diagnostic sessions.
log_statement_stats reports the total statement, while parser, planner, and executor switches report individual phases. The total switch cannot be enabled together with any per-phase switch.
This is synchronous diagnostic logging rather than the cumulative statistics system. Volume and formatting make it unsuitable as routine production telemetry. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Do not enable log_parser_stats as normal OLTP tuning. Use it briefly on an isolated reproduction or one controlled session, collect the needed log, then disable it. |
| OLAP | For a specific analytical statement, EXPLAIN (ANALYZE, BUFFERS) and cumulative views are usually more actionable than cluster-wide log_parser_stats. Scope any use and budget log volume. |
| Small nodes | Leave log_parser_stats=off. A small host is especially vulnerable to diagnostic log I/O and storage exhaustion. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging log_parser_stats without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
log_statement_stats · log_planner_stats · log_executor_stats · log_min_duration_statement
References
4 - log_planner_stats
Fact — official short description: “Writes planner performance statistics to the server log.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | off |
— | off |
How it works
log_planner_stats emits planner-stage resource usage for every query using a crude getrusage-style profiler. Output goes to the server log and is intended for short diagnostic sessions.
log_statement_stats reports the total statement, while parser, planner, and executor switches report individual phases. The total switch cannot be enabled together with any per-phase switch.
This is synchronous diagnostic logging rather than the cumulative statistics system. Volume and formatting make it unsuitable as routine production telemetry. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Do not enable log_planner_stats as normal OLTP tuning. Use it briefly on an isolated reproduction or one controlled session, collect the needed log, then disable it. |
| OLAP | For a specific analytical statement, EXPLAIN (ANALYZE, BUFFERS) and cumulative views are usually more actionable than cluster-wide log_planner_stats. Scope any use and budget log volume. |
| Small nodes | Leave log_planner_stats=off. A small host is especially vulnerable to diagnostic log I/O and storage exhaustion. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging log_planner_stats without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
log_statement_stats · log_parser_stats · log_executor_stats · log_min_duration_statement · join_collapse_limit
References
5 - log_statement_stats
Fact — official short description: “Writes cumulative performance statistics to the server log.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | off |
— | off |
How it works
log_statement_stats emits whole-statement resource usage for every query using a crude getrusage-style profiler. Output goes to the server log and is intended for short diagnostic sessions.
log_statement_stats reports the total statement, while parser, planner, and executor switches report individual phases. The total switch cannot be enabled together with any per-phase switch.
This is synchronous diagnostic logging rather than the cumulative statistics system. Volume and formatting make it unsuitable as routine production telemetry. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Do not enable log_statement_stats as normal OLTP tuning. Use it briefly on an isolated reproduction or one controlled session, collect the needed log, then disable it. |
| OLAP | For a specific analytical statement, EXPLAIN (ANALYZE, BUFFERS) and cumulative views are usually more actionable than cluster-wide log_statement_stats. Scope any use and budget log volume. |
| Small nodes | Leave log_statement_stats=off. A small host is especially vulnerable to diagnostic log I/O and storage exhaustion. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging log_statement_stats without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
log_parser_stats · log_planner_stats · log_executor_stats · log_min_duration_statement · track_functions
References
6 - stats_fetch_consistency
Fact — official short description: “Sets the consistency of accesses to statistics data.”
Identity
Type,- Upstream pg_settings type
Context,- Settable by an ordinary user
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- cache
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG15 |
| Present in | PG15–19 Beta 3 |
| Removed in | No |
| Introduction commit | 5891c7a8ed8f — pgstat: store statistics in shared memory. |
| Commit date | 2022-04-06 |
| Discussion | thread 1 · thread 2 · thread 3 |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG15–19 Beta 3 | cache |
— | cache |
How it works
stats_fetch_consistency defines repeat-read behavior for cumulative statistics within one transaction. none refetches each object, cache retains each object after first access, and snapshot materializes all accessible database statistics on first access.
The cache or snapshot lasts until transaction end or pg_stat_clear_snapshot(). Changing the setting inside a transaction discards the current statistics snapshot.
none is efficient for monitoring queries that read each counter once; cache gives stable repeated object reads; snapshot gives a coherent interactive view at higher cost, especially with many objects. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Use cache for ordinary SQL, none for scrape queries that read each statistic once, and snapshot only for a deliberate coherent inspection. Do not hold a monitoring transaction open indefinitely. |
| OLAP | Large catalogs make snapshot expensive; choose stats_fetch_consistency from the monitoring query’s access pattern rather than workload label. |
| Small nodes | Keep cache unless a simple one-pass collector benefits from none. The setting changes read semantics, not collection accuracy. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG15–19 Beta 3 unmodified; OLAP: PG15–19 Beta 3 unmodified; CRIT: PG15–19 Beta 3 unmodified; TINY: PG15–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging stats_fetch_consistency without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
track_counts · track_activities · track_io_timing · track_functions
References
7 - stats_temp_directory
Fact — official short description: “Writes temporary statistics files to the specified directory.”
Identity
Type,- Upstream pg_settings type
Context,- Takes effect after configuration reload
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- pg_stat_tmp
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–14 |
| Removed in | PG15 |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–14 | pg_stat_tmp |
— | pg_stat_tmp |
How it works
Through PG14, stats_temp_directory selected where the old statistics collector wrote temporary statistics files. It did not store durable table statistics created by ANALYZE.
A fast local memory-backed directory could reduce collector file I/O, but loss of its temporary contents was expected. Permissions and directory availability still had to permit the server to operate.
PostgreSQL 15 replaced the collector’s file-based architecture with shared-memory cumulative statistics and removed this GUC. PG15+ migrations must delete the setting rather than map it to another directory. Its SIGHUP context allows configuration reload without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Do not tune stats_temp_directory on current PostgreSQL: remove it from upgrade targets and use the current replacement behavior described above. Retain it only when reproducing the historical version. |
| OLAP | Do not carry stats_temp_directory into a modern analytical cluster. Benchmark the supported current mechanisms instead of trying to emulate a removed implementation detail. |
| Small nodes | Delete stats_temp_directory during version migration; an unknown-parameter startup failure is more likely than a benefit. Historical test instances should keep the old upstream default. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG14; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–14 unmodified; OLAP: PG9.0–14 unmodified; CRIT: PG9.0–14 unmodified; TINY: PG9.0–14 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Changing stats_temp_directory without applying its documented unit and configuration context.
- Optimizing an isolated benchmark while ignoring concurrent aggregate resource use.
- Assuming a configured value guarantees operating-system or storage behavior.
- Failing to retest startup, failover, and workload latency after the change.
Related parameters
stats_fetch_consistency · track_counts · track_activities · data_directory
References
8 - track_activities
Fact — official short description: “Collects information about executing commands.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- on
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | on |
— | on |
How it works
track_activities records each session’s current command, query identifier, and timing metadata for pg_stat_activity. Access remains privilege-filtered even when collection is enabled.
Turning it off removes essential live observability but does not terminate or speed up the command itself. The text stored per session is bounded by track_activity_query_size.
The switch can be changed by authorized users, so monitoring should detect sessions reporting disabled activity. It is separate from cumulative counters controlled by track_counts. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Keep on in production so pg_stat_activity can show the current command, query ID, and timing needed for incident response. Restrict view privileges and size track_activity_query_size separately rather than disabling activity collection to hide text. |
| OLAP | Keep on for long analytical sessions; live phase and wait context is especially valuable during resource contention. If overhead is suspected, measure it explicitly before accepting the observability loss from off. |
| Small nodes | Keep on. The setting is a boolean collection gate, not a buffer size; reduce query-text memory with track_activity_query_size only after preserving enough text for diagnosis. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Turning it off and losing the current-command evidence needed to diagnose blocking or runaway sessions.
- Confusing live activity collection with cumulative counters controlled by track_counts.
- Assuming on makes every session’s query text visible to every role; visibility remains privilege filtered.
- Ignoring that an authorized session can disable its own activity reporting in the permitted context.
Related parameters
track_activity_query_size · track_counts · compute_query_id · log_line_prefix · log_min_duration_statement
References
9 - track_activity_query_size
Fact — official short description: “Sets the size reserved for pg_stat_activity.query, in bytes.”
Identity
Type,- Upstream pg_settings type
Context,- Requires a server restart
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- 1 KiB
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–10 | 1024 |
— | 1024 |
| PG11–19 Beta 3 | 1024 |
B |
1 KiB |
How it works
track_activity_query_size reserves real shared memory for the current-query text of every active session shown in pg_stat_activity.query. It is a byte count fixed at server start.
Long statements are truncated to this storage size; increasing it improves incident context but multiplies memory by the number of backend slots, including configured connection capacity.
It does not change log statement length, pg_stat_statements query-text storage, or application payload limits. Query identifiers can correlate truncated text with other telemetry when compute_query_id is available. Its postmaster context fixes the value at server start; changing it requires a restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Choose enough bytes to retain diagnostically useful SQL, then budget approximately that storage across all backend slots rather than active queries alone. Increasing it is a startup shared-memory decision and requires a restart. |
| OLAP | Long generated SQL often needs more than 1kB to remain identifiable. Compare truncation frequency and incident needs against the value multiplied by MaxBackends; this setting incurs memory, not repeated clock-read overhead. |
| Small nodes | Keep the smallest value that preserves useful statement identity. Do not copy a 32kB critical-profile value without multiplying it across connection and worker slots on the small host. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | 8192 |
different | 8192 |
| OLAP | 8192 |
different | 8192 |
| CRIT | 32768 |
different | 32768 |
| TINY | 8192 |
different | 8192 |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 = 8192 (dcs); OLAP: PG9.0–19 Beta 3 = 8192 (dcs); CRIT: PG9.0–19 Beta 3 = 32768 (dcs); TINY: PG9.0–19 Beta 3 = 8192 (dcs). Advice, pending human review — Editorial inference, pending maintainer review: The templates retain more query text than upstream for operations, with a larger critical-profile value intended to preserve full diagnostic context.
Common pitfalls
- Treating the value as memory only for currently active queries instead of storage reserved across backend slots.
- Forgetting that the unit is bytes and multibyte query text can contain fewer characters than the byte count suggests.
- Expecting it to change log statement length or pg_stat_statements query-text storage.
- Changing it without the required server restart.
Related parameters
track_activities · max_connections · compute_query_id · log_line_prefix · shared_buffers
References
10 - track_cost_delay_timing
Fact — official short description: “Collects timing statistics for cost-based vacuum delay.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG18 |
| Present in | PG18–19 Beta 3 |
| Removed in | No |
| Introduction commit | bb8dff9995f2 — Add cost-based vacuum delay time to progress views. |
| Commit date | 2025-02-11 |
| Discussion | thread 1 |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG18–19 Beta 3 | off |
— | off |
How it works
track_cost_delay_timing measures time actually spent in cost-based VACUUM and ANALYZE delay. The result appears in progress views, verbose command output, and eligible autovacuum logs.
Collection repeatedly reads the operating-system clock and can have platform-dependent overhead. It does not enable vacuum delay or alter vacuum_cost_* policy.
The metric helps distinguish useful work from intentional throttling. pg_test_timing can measure clock-read overhead before enabling it widely. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Enable it when operators need to distinguish VACUUM or ANALYZE work from intentional cost-delay sleep. Measure clock-read overhead with pg_test_timing and under autovacuum load; it is a boolean and does not size or enable cost-based delay itself. |
| OLAP | It is useful when long maintenance overlaps analytical work and throttle time must be quantified. Compare reported delay time with maintenance duration and foreground latency, then keep it on only if the evidence is used operationally. |
| Small nodes | Leave off unless vacuum-delay diagnostics are needed and clock reads are cheap on the platform. Enabling it does not make maintenance faster or slower by policy; vacuum_cost_* settings remain separate. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | on |
different | 'on' |
| CRIT | on |
different | 'on' |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG18–19 Beta 3 unmodified; OLAP: PG18–19 Beta 3 = on (dcs); CRIT: PG18–19 Beta 3 = on (dcs); TINY: PG18–19 Beta 3 unmodified. Advice, pending human review — Editorial inference, pending maintainer review: The OLAP/crit-only switch is intended to expose time spent in vacuum/analyze cost delays where long maintenance or critical observability justifies clock overhead.
Common pitfalls
- Assuming the switch enables cost-based vacuum delay rather than only timing existing delay.
- Ignoring platform-dependent clock-read overhead instead of measuring with pg_test_timing.
- Looking for the metric without enabling the relevant progress, verbose, or autovacuum-log output.
- Treating the boolean as a duration or buffer-size parameter.
Related parameters
vacuum_cost_delay · autovacuum_vacuum_cost_delay · log_autovacuum_min_duration · track_io_timing · vacuum_buffer_usage_limit
References
11 - track_counts
Fact — official short description: “Collects statistics on database activity.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- on
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | on |
— | on |
How it works
track_counts feeds PostgreSQL’s cumulative database, table, and index activity counters. Autovacuum depends on these counts to decide when tables need vacuum and analyze.
Turning it off removes planner and maintenance telemetry and can prevent normal autovacuum triggering. It does not reset already stored counters by itself.
The statistics are exposed through pg_stat views under snapshot rules controlled by stats_fetch_consistency. Collection is server-wide, while visibility remains privilege controlled. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Keep track_counts=on; normal autovacuum and capacity monitoring rely on it. Disable only in a controlled experiment that does not represent a production configuration. |
| OLAP | Keep track_counts=on for maintenance decisions even if queries are batch oriented. Counter collection is more valuable than the small saving from losing autovacuum inputs. |
| Small nodes | Keep track_counts=on. Small systems still need autovacuum and table-activity visibility. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 unmodified; OLAP: PG9.0–19 Beta 3 unmodified; CRIT: PG9.0–19 Beta 3 unmodified; TINY: PG9.0–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Enabling or enlarging track_counts without measuring collection and observation overhead.
- Confusing collection semantics with a performance-control or I/O-control setting.
- Assuming all statistics are immediately current inside a long transaction.
- Collecting sensitive query text or identifiers without matching access and retention policy.
Related parameters
autovacuum · stats_fetch_consistency · track_activities · default_statistics_target · log_autovacuum_min_duration
References
12 - track_functions
Fact — official short description: “Collects function-level statistics on database activity.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- none
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.0 (research boundary) |
| Present in | PG9.0–19 Beta 3 |
| Removed in | No |
| Introduction commit | Not asserted: predates the PG9.0 research boundary |
| Commit date | — |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.0–19 Beta 3 | none |
— | none |
How it works
track_functions counts calls and execution time for procedural functions with pl, and also SQL/C functions with all. none disables function-level cumulative statistics.
Simple SQL-language functions that the planner inlines disappear into the caller and are not tracked regardless of this setting. Nested function time is reported through the statistics system’s own total/self accounting.
all broadens instrumentation and can add overhead on function-heavy workloads. The counters are cumulative and observed through pg_stat_user_functions or related views. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Keep none unless function-level call counts and timing answer a concrete production question. Use pl to limit instrumentation to procedural languages, or all only after measuring overhead on function-heavy traffic. |
| OLAP | Enable pl or all for a bounded analysis when time inside functions must be separated from caller time. Remember that simple SQL functions may be inlined and remain invisible, so absence from the view is not proof of no execution. |
| Small nodes | Prefer none or a short scoped diagnostic interval. The enum controls instrumentation breadth; it is not a numeric sampling rate, and all can add disproportionate overhead on a CPU-limited host. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | all |
different | all |
| OLAP | all |
different | all |
| CRIT | all |
different | all |
| TINY | all |
different | all |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.0–19 Beta 3 = all (dcs); OLAP: PG9.0–19 Beta 3 = all (dcs); CRIT: PG9.0–19 Beta 3 = all (dcs); TINY: PG9.0–19 Beta 3 = all (dcs). Advice, pending human review — Editorial inference, pending maintainer review: The explicit all setting maximizes function-level observability across every profile, accepting instrumentation overhead and the SQL-inlining blind spot.
Common pitfalls
- Expecting inlined SQL-language functions to appear in function statistics.
- Enabling all cluster-wide without measuring instrumentation overhead on function-heavy workloads.
- Reading cumulative total and self time without accounting for nested calls and snapshot behavior.
- Treating none, pl, and all as levels of sampling rather than different instrumentation scopes.
Related parameters
track_counts · stats_fetch_consistency · compute_query_id · jit_expressions · log_statement_stats
References
13 - track_io_timing
Fact — official short description: “Collects timing statistics for database I/O activity.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG9.2 |
| Present in | PG9.2–19 Beta 3 |
| Removed in | No |
| Introduction commit | 309c64745ea1 — Rename track_iotiming GUC to track_io_timing. |
| Commit date | 2012-04-29 |
| Discussion | — |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG9.2–19 Beta 3 | off |
— | off |
How it works
track_io_timing measures database I/O wait time outside the WAL object. It populates pg_stat_database, pg_stat_io, pg_stat_get_backend_io(), EXPLAIN with BUFFERS, maintenance output, and supporting extensions.
The setting repeatedly reads the operating-system clock; overhead is platform dependent and measurable with pg_test_timing. It records elapsed wait, not device service time in isolation.
WAL I/O timing is controlled separately by track_wal_io_timing. Enabling timing adds observability but does not make I/O asynchronous or change planner costs. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Enable it when database I/O wait time is needed for pg_stat_io, EXPLAIN with BUFFERS, or incident analysis. Measure clock-read overhead with pg_test_timing and at peak statement rates; Pigsty’s on value is an observability choice, not a throughput tuning value. |
| OLAP | Long scans and spills make relation and temporary-file timing valuable. Keep it on when those counters drive diagnosis, but compare execution overhead and distinguish elapsed waits from device-only service time. |
| Small nodes | Use on only when the platform’s clock-read cost is acceptable and the measurements are consumed. The boolean has no conservative numeric size; off removes timing but does not remove I/O itself. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | on |
different | 'on' |
| OLAP | on |
different | 'on' |
| CRIT | on |
different | 'on' |
| TINY | on |
different | 'on' |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG9.2–19 Beta 3 = on (dcs); OLAP: PG9.2–19 Beta 3 = on (dcs); CRIT: PG9.2–19 Beta 3 = on (dcs); TINY: PG9.2–19 Beta 3 = on (dcs). Advice, pending human review — Editorial inference, pending maintainer review: The explicit on setting follows the source comment’s goal of collecting I/O statistics across all profiles, accepting platform-dependent clock overhead.
Common pitfalls
- Treating measured wait time as isolated device service time without queueing or scheduling effects.
- Expecting the switch to make I/O asynchronous or change planner cost estimates.
- Assuming it includes WAL timing, which is controlled by track_wal_io_timing.
- Enabling it without measuring clock-read overhead on the actual platform.
Related parameters
track_wal_io_timing · track_counts · stats_fetch_consistency · effective_io_concurrency · compute_query_id
References
14 - track_wal_io_timing
Fact — official short description: “Collects timing statistics for WAL I/O activity.”
Identity
Type,- Upstream pg_settings type
Context,- Settable at runtime by a superuser
Unit,- Raw unit
Range,- Raw limits in the last observed version
Enum values,- — for non-enum types
Category,- Upstream classification
Latest boot value,- off
Lifecycle
| Fact | Value |
|---|---|
| First observed | PG14 |
| Present in | PG14–19 Beta 3 |
| Removed in | No |
| Introduction commit | ff99918c625a — Track total amounts of times spent writing and syncing WAL data to disk. |
| Commit date | 2021-03-09 |
| Discussion | thread 1 |
Default history
| Versions | Raw boot_val |
Unit | Human value |
|---|---|---|---|
| PG14–19 Beta 3 | off |
— | off |
How it works
track_wal_io_timing measures wait time for WAL I/O and exposes it under the wal object in pg_stat_io and pg_stat_get_backend_io(). It is independent of ordinary database I/O timing.
Collection repeatedly reads the operating-system clock and can add platform-dependent overhead. pg_test_timing measures clock-read cost, while workload tests reveal aggregate impact.
It does not change wal_sync_method, durability, or WAL throughput. Pairing it with track_io_timing separates WAL waits from relation and temporary-file I/O. Its superuser context permits an authorized session change without a server restart.
Tuning advice
Advice. These are workload-specific starting points and must be validated with measurements.
| Workload | Guidance |
|---|---|
| OLTP | Enable it when WAL write and sync waits must be separated from relation I/O during commit-latency analysis. Measure platform clock overhead and correlate the counters with synchronous_commit, wal_sync_method, and storage behavior. |
| OLAP | Read-heavy analytics may gain little from WAL timing, while bulk loads and refresh jobs can benefit substantially. Enable it for the write phases that use the evidence rather than because the workload is labeled OLAP. |
| Small nodes | Keep off unless WAL latency is an active diagnostic need and clock reads are inexpensive. Pair it with track_io_timing only when both WAL and non-WAL wait separation is useful. |
Pigsty
Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.
| Template | Effective value | Versus upstream boot | Source expression |
|---|---|---|---|
| OLTP | Unmodified | — | — |
| OLAP | Unmodified | — | — |
| CRIT | Unmodified | — | — |
| TINY | Unmodified | — | — |
Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG14–19 Beta 3 unmodified; OLAP: PG14–19 Beta 3 unmodified; CRIT: PG14–19 Beta 3 unmodified; TINY: PG14–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.
Common pitfalls
- Expecting WAL timing to include relation and temporary-file I/O measured by track_io_timing.
- Treating elapsed WAL waits as pure device service time.
- Assuming the switch changes durability, wal_sync_method, or WAL throughput.
- Enabling it without measuring clock-read overhead on the production platform.
Related parameters
track_io_timing · wal_sync_method · synchronous_commit · track_counts · stats_fetch_consistency