PostgreSQL — Performance

OWNER: boris, yoanna ALSO_USED_BY: urszula, maxim, piotr, floris, ron LAST_VERIFIED: 2026-03-26 GE_STACK_VERSION: PostgreSQL 16.x

---

Overview

Performance tuning for GE PostgreSQL deployments. Covers connection pooling, vacuum management, bloat prevention, slow query analysis, and monitoring queries. Agents building data-intensive features MUST read this page.

---

Connection Pooling

PgBouncer (GE standard for production)

GE uses PgBouncer between the application and PostgreSQL to multiplex connections. PostgreSQL's max_connections is limited; each connection consumes ~10MB of RAM.

# pgbouncer.ini
[databases]
admin_ui = host=127.0.0.1 port=5432 dbname=admin_ui

[pgbouncer]
listen_port = 6432
pool_mode = transaction          # REQUIRED for RLS (SET LOCAL)
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300

CHECK: Agent is configuring PgBouncer. IF: pool_mode is session. THEN: Change to transaction. Session mode pins connections and defeats the purpose of pooling. IF: The application uses RLS with SET LOCAL. THEN: transaction mode is REQUIRED. SET LOCAL scopes to the current transaction — session mode would leak tenant context.

Application-Level Pooling (postgres.js)

const client = postgres(connectionString, {
  max: 20,               // Pool size — match PgBouncer default_pool_size
  idle_timeout: 30,      // Close idle connections after 30s
  max_lifetime: 300,     // Recycle after 5 min
  connect_timeout: 10,   // Fail fast on connection issues
});

ANTI_PATTERN: Setting max higher than PgBouncer's default_pool_size. FIX: Application pool max should equal PgBouncer pool size. If application max > PgBouncer max, connections queue at PgBouncer anyway.

ANTI_PATTERN: Creating multiple postgres() clients in one application. FIX: Single client in lib/db/index.ts. Each client creates its own pool. READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md

---

VACUUM and Autovacuum

PostgreSQL uses MVCC — old row versions are not immediately deleted. VACUUM reclaims dead row space.

Autovacuum Tuning (GE defaults)

autovacuum = on
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1       # Vacuum when 10% of rows are dead
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05     # Analyze when 5% of rows changed
autovacuum_vacuum_cost_delay = 2ms         # Reduce IO impact

CHECK: Agent notices slow queries on a previously fast table. IF: Table has heavy UPDATE/DELETE activity. THEN: Check autovacuum status:

SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

Manual VACUUM

-- Non-blocking vacuum (preferred)
VACUUM (VERBOSE) tablename;

-- Full vacuum — rewrites table, LOCKS entire table
-- Use ONLY during maintenance windows
VACUUM FULL tablename;

ANTI_PATTERN: Running VACUUM FULL during business hours. FIX: VACUUM FULL acquires an exclusive lock. Schedule for maintenance windows or use pg_repack for online table rewrites.

---

Table Bloat

Bloat occurs when dead rows accumulate faster than vacuum reclaims them.

Detecting Bloat

-- Estimate table bloat (simplified)
SELECT
  schemaname,
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) as total_size,
  n_dead_tup,
  n_live_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

CHECK: Agent sees dead_pct above 20% for a table. IF: Autovacuum is running but not keeping up. THEN: Lower autovacuum_vacuum_scale_factor for that specific table:

ALTER TABLE hot_table SET (autovacuum_vacuum_scale_factor = 0.02);

---

Slow Query Analysis

pg_stat_statements (REQUIRED)

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  substring(query, 1, 100) as query_preview,
  calls,
  round(total_exec_time::numeric, 2) as total_ms,
  round(mean_exec_time::numeric, 2) as mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top 10 most called queries
SELECT
  substring(query, 1, 100) as query_preview,
  calls,
  round(mean_exec_time::numeric, 2) as mean_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Reset stats (after optimization round)
SELECT pg_stat_statements_reset();

CHECK: Agent is investigating performance issues. IF: pg_stat_statements is not enabled. THEN: Enable it immediately. It is GE mandatory.

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM tasks
WHERE agent_id = 'urszula' AND status = 'running'
ORDER BY created_at DESC
LIMIT 10;

Key metrics to check: - Seq Scan on large table — missing index. - High Buffers: shared read — data not in cache (cold query). - Nested Loop with high loops — N+1 join pattern. - Sort with external disk — work_mem too low for the sort.

---

Query Optimization Patterns

Avoid SELECT *

-- BAD: Fetches all columns including JSONB blobs
SELECT * FROM agents;

-- GOOD: Fetch only needed columns
SELECT id, name, role, status FROM agents;

Use EXISTS instead of COUNT for existence checks

-- BAD: Counts all matching rows just to check existence
SELECT count(*) > 0 FROM tasks WHERE agent_id = 'urszula';

-- GOOD: Stops at first match
SELECT EXISTS (SELECT 1 FROM tasks WHERE agent_id = 'urszula');

Batch operations

-- BAD: N individual inserts
INSERT INTO learnings (agent_id, content) VALUES ('urszula', 'Learning 1');
INSERT INTO learnings (agent_id, content) VALUES ('urszula', 'Learning 2');

-- GOOD: Single multi-row insert
INSERT INTO learnings (agent_id, content) VALUES
  ('urszula', 'Learning 1'),
  ('urszula', 'Learning 2');

Cursor pagination

-- BAD: OFFSET re-scans all skipped rows
SELECT * FROM tasks ORDER BY created_at DESC OFFSET 1000 LIMIT 50;

-- GOOD: Cursor-based — O(1) regardless of page depth
SELECT * FROM tasks
WHERE created_at < '2026-03-25T10:00:00Z'
ORDER BY created_at DESC
LIMIT 50;

ANTI_PATTERN: OFFSET-based pagination on tables with > 1000 rows. FIX: Always use cursor-based pagination. OFFSET cost grows linearly with page depth.

---

Monitoring Queries

Active connections

SELECT
  state,
  count(*),
  max(now() - state_change) as max_duration
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

Long-running queries

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle'
ORDER BY duration DESC;

Lock contention

SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.query AS blocked_query,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database = blocked_locks.database
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Cache hit ratio

SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit) as heap_hit,
  round(
    sum(heap_blks_hit)::numeric /
    (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2
  ) as cache_hit_ratio
FROM pg_statio_user_tables;

CHECK: Agent sees cache hit ratio below 95%. IF: shared_buffers is less than 25% of available RAM. THEN: Increase shared_buffers. IF: shared_buffers is already 25%+ of RAM. THEN: The working set exceeds available memory. Consider query optimization or data archival.

---

GE-Specific Conventions

1. PgBouncer in transaction mode for all production deployments.
2. pg_stat_statements enabled on every database.
3. Slow query log threshold: 200ms.
4. Cache hit ratio target: >99%.
5. Autovacuum always on — never disable it.
6. Cursor pagination for all list endpoints.
7. EXPLAIN ANALYZE before and after query optimization.

---

Cross-References

READ_ALSO: wiki/docs/stack/postgresql/index.md READ_ALSO: wiki/docs/stack/postgresql/indexing.md READ_ALSO: wiki/docs/stack/postgresql/pitfalls.md READ_ALSO: wiki/docs/stack/drizzle/queries.md READ_ALSO: wiki/docs/stack/drizzle/pitfalls.md