Understand MVCC and routine maintenance

PostgreSQL uses MVCC so readers and writers can often work at the same time. An update creates a new row version. The old version stays until no transaction needs it.

Autovacuum reclaims reusable space and runs ANALYZE to refresh planner statistics. Do not disable it as a performance shortcut. Long transactions can prevent cleanup.

Refresh statistics after a large test-data load:

ANALYZE app.notes;

Inspect current sessions and waits:

SELECT pid, usename, application_name, state,
       wait_event_type, wait_event, query_start, query
FROM pg_stat_activity
WHERE datname = current_database();

Look for long idle in transaction sessions, old queries, and lock waits. Diagnose them before increasing connection limits or disabling maintenance.