Question 1

How do I find and remove unused indexes in PostgreSQL?

Accepted Answer

Query pg_stat_user_indexes joined with pg_index for indexes with idx_scan = 0, excluding unique and constraint-backing indexes. Always verify stats age and confirm with a human before dropping any index.

Question 2

How do I fix slow PostgreSQL queries?

Accepted Answer

Start with EXPLAIN ANALYZE to confirm index usage, then check for anti-patterns like functions on indexed columns, OFFSET pagination, and N+1 loops. Add composite or partial indexes matching your WHERE and ORDER BY clauses.

Question 3

When should I use PgBouncer instead of direct PostgreSQL connections?

Accepted Answer

Use PgBouncer on port 6432 for OLTP application workloads with high concurrency. Reserve direct connections on port 5432 for DDL, migrations, analytics, and session-dependent features like temp tables or LISTEN/NOTIFY.

Question 4

What is XID wraparound in PostgreSQL and how do I prevent it?

Accepted Answer

XID wraparound occurs when 32-bit transaction IDs exhaust ~2 billion values, making old rows invisible. Prevent it by never disabling autovacuum, keeping transactions short, and alerting when age(datfrozenxid) exceeds 40-50% of the wraparound limit.

Question 5

When should I partition a PostgreSQL table?

Accepted Answer

Partition general tables exceeding 100GB or 20M rows, and time-series or log tables exceeding 50GB or 10M rows. Partitioning mainly benefits maintenance and data retention rather than raw query speed.

Question 6

Why does PostgreSQL run out of memory with high concurrency?

Accepted Answer

work_mem is allocated per operation, so memory usage multiplies across operations, parallel workers, and connections. Reduce work_mem globally, add connection pooling, and set statement_timeout to prevent OOM kills.

postgres

SYSTEM DOCUMENTATION & REQUIREMENTS

Frequently Asked Questions about postgres

Related Skills

drizzle-setup

database-patterns

monitoring-database-health

neon-postgres