Hi hackers,
I'd like to discuss raising the default track_activity_query_size,
currently 1024 bytes, unchanged since the 8.x era.
Query shapes have changed a lot since then: ORMs, auto-generated APIs
on top of Postgres -- REST (PostgREST / Supabase) and GraphQL (Hasura,
PostGraphile) compile client requests into large single SQL statements
with wide column lists, embedded joins, and long IN lists -- and
lately, AI-generated SQL. Multi-kilobyte queries are the norm in these
stacks. The truncation of pg_stat_activity.query is always there, but
people typically discover it during incidents -- the worst moment to
find out.
To put numbers on "query shapes have changed", I looked at recent
public submissions to explain.depesz.com -- 8320 plans covering
2026-04-20 .. 2026-07-17, of which 1962 included the query text.
This sample is biased toward queries people actually troubleshoot --
but troubleshooting is precisely what pg_stat_activity.query exists
for, so this is the right population to measure. Query text size
distribution (bytes):
p25 353
median 1837
p75 6176
p90 15220
p95 30778
p99 103763
max 747242
Share of these queries that pg_stat_activity would truncate, by
setting:
track_activity_query_size truncated
1024 (current default) 60%
4096 33%
8192 19%
16384 10%
32768 4%
So the current default truncates the majority of queries that end up
being investigated.
Two more observations:
1. Parts of the ecosystem already override it. AWS RDS and Aurora
have shipped a default of 4096 since PG10 [2], running that way at
fleet scale for years. Observability vendors' setup guides tell users
to raise it as well: Datadog DBM docs say raising to 4096 "captures
most queries for most workloads" [3]; Percona PMM setup recommends
2048. At PostgresAI, in our checkups and monitoring tooling, we
recommend at least 10k to everyone. Note that 4096 itself is a
PG10-era decision (2017) and looks outdated by now too: per the
distribution above, it still truncates a third of the sampled
queries.
2. The parameter is PGC_POSTMASTER. The people most hurt by truncation
-- mid-incident, needing full query text, unable to restart -- are
precisely those who cannot fix it when they discover the problem. A
better default is the only fix that works retroactively.
(pg_stat_statements is not a substitute here: it normalizes and
aggregates, while during an incident one needs this backend's current
statement with its actual constants.)
Cost, memory: the shared buffer is NumBackendStatSlots *
track_activity_query_size (slots = max_connections + autovacuum
workers + aux + prepared xacts). Measured via pg_shmem_allocations
("Backend Activity Buffer", HEAD, max_connections=100 -> 174 slots):
track_activity_query_size buffer size delta vs default
1024 (current default) 174 KiB --
16384 2.7 MiB +2.5 MiB
1048576 174 MiB +174 MiB
The same amount is palloc'd locally per backend when pg_stat_activity
is read; no other consumers of the GUC exist (all uses are in
backend_status.c).
Cost, runtime: when the max was raised to 1MB for v13 [1], the
overhead question was raised but not measured, so I measured. HEAD
(v20 devel, release build) on an 8-vCPU dedicated AMD EPYC-Milan VM
(Hetzner ccx33), shared_buffers=8GB, scale-100 pgbench select-only
workloads, client on the same host, -c 8 -j 8, -M prepared (highest
TPS -> overhead most visible; note exec_bind_message() still reports
the full stored query text on every execution), 3x30s runs per cell,
median TPS:
track_activity_query_size | -S (~40 B text) | 10 KiB text
--------------------------+-----------------+-------------
1024 (current default) | 298246 | 286175
4096 | 298090 | 288295
8192 | 296087 | 289586
16384 | 296787 | 285593
65536 | 298011 | 288636
1048576 | 295877 | 283735
(The 10 KiB workload is the same single-row PK lookup padded with a
comment to 10 KiB, isolating the query-text handling cost at constant
execution cost.)
The spread is 0.8% (short) / 2.0% (10 KiB) with no monotonic trend --
run-to-run noise. pgstat_report_activity() copies only the actual
query length (bounded by the GUC), so short queries pay nothing beyond
the status quo, and even a 10 KiB text reported ~286k times/sec (~2.9
GB/s of copying) shows no measurable penalty, including at the 1MB
setting. perf profiles of the 10 KiB-text runs agree:
pgstat_report_activity() and the underlying memcpy stay at 0.1-0.2%
of CPU cycles across the 1kB / 16kB / 1MB settings. An earlier -M
simple run of the same matrix (where the raw text travels and is
parsed per execution) was equally flat.
To find where the copy cost does become visible, I also ran extreme
query texts (100 KiB and 1 MiB, same construction), median TPS:
track_activity_query_size | 100 KiB text | 1 MiB text
--------------------------+--------------+-----------
1024 (current default) | 187114 | 37171
16384 | 184197 | 36946
131072 | 162459 | 34994
1048576 | 159809 | 24437
Overhead appears only when both the query text is huge and the GUC is
large enough to admit it (-13% for 100 KiB texts at the 128kB setting;
-34% for 1 MiB texts at the 1MB max). To be clear, I'm not proposing any
change to the max -- this table is here to show why a moderate
default is preferable to a large one: at 16kB even these extreme
workloads stay within noise of the current default, because the
truncation bound still protects pathological cases.
Proposal: raise the default to 16kB, which covers 90% of the sampled
queries (or at least 8kB, covering 81%). If there's agreement, I'll
follow up with a patch; the change is three touch points: the boot
value in guc_tables.c, postgresql.conf.sample, and config.sgml.
Thoughts?
[1]
https://www.postgresql.org/message-id/[email protected]
[2]
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.UsingDashboard.SQLTextLimit.html
[3]
https://docs.datadoghq.com/database_monitoring/setup_postgres/troubleshooting/
Nik
