O9: Postgres internal observability (PG1-PG33) #35

New Issue

2026-09-08T05:01:39Z

rock commented

2026-09-08 05:01:39 +00:00

Monitor Postgres internals critical for vector search performance.

Categories:

Connection pool (PG1-PG4): active/idle/idle-in-transaction, max connections
Query performance (PG5-PG9): pg_stat_statements mean time, seq vs idx scans
Table health (PG10-PG17): live/dead tuples, bloat ratio, autovacuum timing, table/index sizes
HNSW specific (PG18-PG20): index size, build time, recall estimate
WAL/Replication (PG21-PG24): WAL rate, replica lag bytes/seconds
Transactions (PG25-PG29): commit/rollback ratio, deadlocks, lock counts
Cache (PG30-PG33): buffer cache hit ratio (SLO >= 99%), index cache hits

Tasks:

Deploy postgres_exporter with custom queries for PG5-PG9 (pg_stat_statements)
Add custom collector for HNSW index size (PG18)
Configure CNPG monitoring (PG21-PG24 via built-in metrics)
Create 6 monitoring SQL queries (see docs/OBSERVABILITY.md Section 9)

Critical Alerts:

PG8 seq_scan on memory_vector increments (Critical — HNSW not used)
PG12 dead_tuple_ratio > 0.20 (Warning — VACUUM needed)
PG32 cache_hit_ratio < 0.95 (Critical — cache thrashing)
PG23 replication_lag > 30s (Warning — stale reads)
PG27 deadlocks > 0 (Warning)
PG1/PG4 connection ratio > 0.8 (Critical — pool exhaustion)

Reference: docs/OBSERVABILITY.md Section 9

Monitor Postgres internals critical for vector search performance. Categories: 1. Connection pool (PG1-PG4): active/idle/idle-in-transaction, max connections 2. Query performance (PG5-PG9): pg_stat_statements mean time, seq vs idx scans 3. Table health (PG10-PG17): live/dead tuples, bloat ratio, autovacuum timing, table/index sizes 4. HNSW specific (PG18-PG20): index size, build time, recall estimate 5. WAL/Replication (PG21-PG24): WAL rate, replica lag bytes/seconds 6. Transactions (PG25-PG29): commit/rollback ratio, deadlocks, lock counts 7. Cache (PG30-PG33): buffer cache hit ratio (SLO >= 99%), index cache hits Tasks: 1. Deploy postgres_exporter with custom queries for PG5-PG9 (pg_stat_statements) 2. Add custom collector for HNSW index size (PG18) 3. Configure CNPG monitoring (PG21-PG24 via built-in metrics) 4. Create 6 monitoring SQL queries (see docs/OBSERVABILITY.md Section 9) Critical Alerts: - PG8 seq_scan on memory_vector increments (Critical — HNSW not used) - PG12 dead_tuple_ratio > 0.20 (Warning — VACUUM needed) - PG32 cache_hit_ratio < 0.95 (Critical — cache thrashing) - PG23 replication_lag > 30s (Warning — stale reads) - PG27 deadlocks > 0 (Warning) - PG1/PG4 connection ratio > 0.8 (Critical — pool exhaustion) Reference: docs/OBSERVABILITY.md Section 9

rock added the memory-observability label 2026-09-08 05:01:40 +00:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: riotpiao-poimen/poimen-memory#35