:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Deploying pgcollector
1. Database users (posthog-cloud-infra)
aurora_user_management/v3.0.0 has a monitor tier for telemetry agents:
pg_monitor plus CONNECT, no table data. readonly is the wrong tier for
this job: it grants SELECT on every table and still hides other users' rows in
pg_stat_statements / pg_stat_activity (that needs pg_read_all_stats,
which pg_monitor includes).
Per monitored cluster, add "pgcollector" to users_monitor in that cluster's
own user config. The dev cloud cluster does this in
terraform/environments/aws-accnt-dev/us-east-1/postgres/users.tf; keep it out
of the shared posthog_app_db_users registry unless every cluster should get it.
The sink database, its pgcollector (ddl) and pgapi (readonly) users,
the PostHogAppDatabaseId tag the golden chart selects nodes by, and the
k8s-pgcollector IRSA role all come from terraform/modules/pgcollector. One
module call per environment; see its README for the inputs.
Extensions and Aurora functions
Nothing here requires a parameter-group change or a reboot.
pg_stat_statements— already inshared_preload_librarieson every cluster, butCREATE EXTENSIONis per database and the collector reads cluster-wide stats through the database named in the server URL (theaurora_user_managementcredentials point at the application database, sod63d9unoslck8qon the PostHog clusters, notpostgres). RunCREATE EXTENSION IF NOT EXISTS pg_stat_statementsthere (DDL user) if it hasn't been. This is the only step that is genuinely required. A cluster upgraded in place keeps the old extension definition, so also runALTER EXTENSION pg_stat_statements UPDATEthere after a major upgrade. The collector reads the columns the installed version has and emits onepgss_staleevent (plus a warning log) until the extension catches up. Below 1.8 (the PG13 definition)query_statsandaurora_planscollect nothing until the extension is updated.pg_proctab— optional, supported on both Aurora and RDS for PostgreSQL.CREATE EXTENSION pg_proctabin the same database as above enablessystem_cpu,system_memory,system_disk,backend_cpu(host CPU, memory, load, disk counters and CPU per backend, grouped by user, code path and query in pgapi). Until then those four log oneskipping: prerequisite not metline and re-check every 10 minutes. Its functions may needGRANT EXECUTE ... TO pgcollector.aurora_stat_*/aurora_replica_status— built in, no setup.aurora_compute_plan_id(default on, 14.10+/15.5+) must stay on foraurora_plansandplan_idin activity samples.
Parameter-group niceties (dynamic, no reboot): the log sampling, lock-wait and
CloudWatch-export settings the logs collector relies on are already set
fleet-wide. RDS and Aurora refuse to modify log_line_prefix, so there is no
%Q (query id) on log lines; the collector joins log rows to cur_queries by
text fingerprint instead. Clusters that don't preload auto_explain simply
produce no ts_log_plans.
Load profile on the monitored cluster
Every session runs with statement_timeout = 5s and lock_timeout = 1s, so a
misbehaving collector is cut off, not merely slow. Per tick:
| collector | interval | cost | notes |
|---|---|---|---|
activity_samples, activity_sessions |
10s | one pg_stat_activity scan each, plus a regexp over each active backend's query text for query tags |
pg_blocking_pids() only for lock waiters |
lock_waits |
10s | pg_stat_activity scan; pg_blocking_pids() per lock waiter only |
empty unless something is blocked |
query_stats |
60s | pg_stat_statements(false) (no text); text for ≤500 new ids per tick |
no query text on the hot path |
database_stats, bgwriter, wal, replication*, vacuum_progress, aurora_system_waits, aurora_db_latency, aurora_replica_status |
60s | shared-memory counter reads | negligible |
aurora_plans |
5m | aurora_stat_plans(false); plan text for new plan ids |
≤ pg_stat_statements.max rows |
table_stats, index_stats, vacuum_needed |
10m | one catalog/stats scan per database | linear in relations; on the writer |
sizes |
1h | four size functions per relation | reader endpoint when configured |
schema_*, settings, extensions |
1h | catalog reads | reader for schema |
system_*, backend_cpu |
60s | /proc reads via pg_proctab |
only if the extension exists |
aurora_backend_waits, aurora_memctx |
— | off by default | per-backend function calls; enable per server via overrides.<name>.enabled = true |
logs |
30s | no database load | CloudWatch Logs API; bounded per tick |
Discovering every database on a cluster
databases = ["*"] lists pg_database and connects to each one with the same
credentials. That works because v3 grants CONNECT on the module's
database_name only — so a cluster with several databases needs the monitor
user granted CONNECT on each, or (simpler) GRANT CONNECT ON DATABASE x TO pgcollector handled by the same lane. For clusters with one application
database this is a non-issue.
2. Helm (charts/posthog-app)
The charts app is apps/pgcollector, onboarded through platformctl: a
service.yaml per app declares the Stages, and platformctl generates the Argo CD
Application and the Kargo project, warehouse and stage. Do not hand-write an
ApplicationSet. See deploy/values.example.yaml
for the values. Key points:
pgbouncer: falseon everypsql:entry. pgcollector sets session GUCs and needs stable backends; it refuses to start if it detects PgBouncer.- Monitored servers come from env var names, not from the TOML. A
psql:entry whosewrite.envmapsPGCOLLECTOR_SERVER_<ID>_URL: POSTGRES_URLis all it takes; addread.env: {PGCOLLECTOR_SERVER_<ID>_READER_URL: POSTGRES_URL}for the reader endpoint. Adding a cluster = onepsql:entry + one Terraform user. configFiles:carries the TOML (sink URL, retention, defaults). Explicit[[servers]]blocks are still allowed for per-server overrides andinstances; they merge with env-discovered servers byid.- Probes and metrics on
:9187(/healthz,/readyz,/metrics), scraped via the chart'smonitoring.prometheuspod annotations.
3. Logs (statement durations, plans, autovacuum, checkpoints, errors)
The logs collector reads the Postgres log and writes typed rows —
ts_query_latency is where real per-query latency quantiles come from: one row
per statement fingerprint and minute holding log-spaced histograms of the sampled
and of the always-logged durations (20 buckets per decade from 0.01 ms to 100 s),
with the sample rate they were collected under, so a statement's cost is
independent of how often it runs and the weighting survives a settings change. Durations at or above sample_rows_over_ms
(default 100) also keep their own ts_query_durations row for the slowest-samples
view; the statement text itself is stored once per fingerprint in cur_query_texts.
On Aurora the log is already exported to CloudWatch Logs
(enabled_cloudwatch_logs_exports = ["postgresql"]), one group per cluster,
one stream per instance:
PGCOLLECTOR_SERVER_CLOUD_LOG_GROUP=/aws/rds/cluster/<cluster-id>/postgresql
PGCOLLECTOR_SERVER_CLOUD_LOG_LINE_PREFIX='%t:%r:%u@%d:[%p]:' # only if not the RDS default
or in TOML: logs = { source = "cloudwatch", log_group = "...", log_line_prefix = "..." }.
The IRSA role needs logs:FilterLogEvents (and logs:DescribeLogStreams) on
those groups. The collector polls every 30s with a 10s lag, keeps its cursor in
collector_state, and warns when it falls behind.
Parameter-group settings that make this worthwhile (mostly already set):
| parameter | value | gives |
|---|---|---|
log_min_duration_sample / log_statement_sample_rate |
1000 / 0.01 |
sampled per-statement durations → p50/p95/p99 |
log_min_duration_statement |
10000 |
every slow statement |
log_line_prefix |
RDS default (not modifiable) | no query id on log lines, so log rows join cur_queries on a text fingerprint; self-managed Postgres can append %Q for an exact join |
auto_explain.* |
json, sample 0.01 | plans for sampled slow statements → ts_log_plans |
log_lock_waits, log_temp_files=0, log_checkpoints, log_autovacuum_min_duration=0 |
on | events + ts_temp_files, ts_checkpoints, ts_autovacuum_runs |
Validate a prefix against a downloaded log file with
pgcollector --parse-log file.log --log-line-prefix '%t:%r:%u@%d:[%p]:%Q:'.
The collector's own sessions try SET log_min_duration_statement = -1 etc. so
they don't log themselves; grant SET on those GUCs to the monitor role if you
see pgcollector's queries in the log.
4. Replicas / multiple instances
Aurora replicas each have their own pg_stat_statements,
pg_stat_activity, bgwriter and I/O counters. The cluster reader endpoint
load-balances across them, so stats read through it are from "whichever
replica answered" and are useless as a time series. To see reader workload,
give pgcollector each instance endpoint:
PGCOLLECTOR_SERVER_CLOUD_INSTANCE_READER_1_URL=postgres://.../ (instance endpoint)
PGCOLLECTOR_SERVER_CLOUD_INSTANCE_READER_2_URL=postgres://.../
or in TOML:
[[servers]]
id = "cloud"
url = "${PGCOLLECTOR_SERVER_CLOUD_URL}"
[servers.instances]
reader-1 = "${CLOUD_READER_1_URL}"
reader-2 = "${CLOUD_READER_2_URL}"
Cluster-scoped collectors then run against the writer and each instance,
and every row carries instance (writer, reader-1, …). Database-scoped
collectors (table/index stats, sizes, schema) run on the writer only by
default; set per_instance: true on a collector to change that.
Instance endpoints aren't something the psql: harness produces today; the
credentials are the same as the writer's, so they can be passed via
secret_env_app_specific, or (phase 4) discovered from the RDS API
(rds:DescribeDBClusters on the IRSA role) so new replicas are picked up
automatically. Aurora failover keeps the cluster endpoint pointing at the
current writer, so the writer series survives a failover; the promoted
instance's series moves from reader-N to writer at that point.
5. pgapi
The API + MCP server over this stats DB lives in rust/pgapi (separate PR) with its own README.