PostgreSQL DBA Advanced Cheat Sheet

---

🧠 **PostgreSQL DBA Advanced Cheat Sheet **

1️⃣ Performance Tuning Parameters

Key settings from postgresql.conf that directly affect speed and memory efficiency.

Parameter	Description	Typical Value
shared_buffers	Amount of memory for caching data pages.	25% of system RAM
work_mem	Memory used for sort/hash operations per query.	64MB – 256MB
maintenance_work_mem	Used for VACUUM, CREATE INDEX.	256MB – 1GB
effective_cache_size	How much memory the planner assumes is available for caching.	50–75% of RAM
max_connections	Controls total concurrent sessions.	100–300
checkpoint_timeout	Time between automatic checkpoints.	5–15 minutes
wal_buffers	Memory for WAL writes before disk flush.	16MB – 64MB
synchronous_commit	Wait for WAL confirmation or not.	on for safety, off for performance
random_page_cost	Planner’s cost estimate for random I/O.	Lower if on SSD (e.g. 1.1–1.5)

---

2️⃣ Index Tuning and Optimization

Indexes are double-edged — they speed reads but slow writes.

Type	Description	Use Case
B-TREE	Default index type	For equality and range queries
HASH	Hash-based	Fast equality only
GIN	Generalized Inverted Index	For JSONB, full text, arrays
GiST	Generalized Search Tree	For geometric or complex data
BRIN	Block Range Index	Large, append-only tables (e.g., logs)
Partial Index	Index with WHERE condition	When you query subset of rows
Expression Index	Index on computed value	e.g. LOWER(name)

Example:

CREATE INDEX idx_active_users ON users (last_login)
WHERE active = true;

---

3️⃣ Vacuum, Analyze & Autovacuum

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Old row versions remain after updates/deletes — VACUUM removes them.

Command	Purpose
VACUUM	Cleans up dead tuples (frees space)
ANALYZE	Updates query planner statistics
VACUUM FULL	Rewrites table (reclaims disk space)
AUTOVACUUM	Automatic cleanup background process

Check vacuum activity:

SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_all_tables
WHERE n_dead_tup > 1000;

---

4️⃣ WAL (Write-Ahead Logging) & Checkpoints

WAL ensures durability — changes are written to WAL logs before disk. This allows recovery after crashes.

Concept	Description
WAL Segment	Default 16MB log file containing changes
Checkpoint	Writes dirty buffers from memory to disk periodically
Archive Mode	Enables continuous archiving for PITR (Point-In-Time Recovery)
Replication Slot	Prevents WAL removal before standby has consumed it

View WAL files:

ls $PGDATA/pg_wal/

Force checkpoint:

CHECKPOINT;

---

5️⃣ Replication & High Availability

PostgreSQL supports streaming replication (asynchronous/synchronous).

Setup overview:

1. Enable replication in postgresql.conf

   wal_level = replica
   max_wal_senders = 5
   

2. Add replication user:

   CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';
   

3. Configure pg_hba.conf to allow replication.

4. Clone base backup:

   pg_basebackup -h primary_host -D /data/replica -U replicator -Fp -Xs -P
   

5. Start replica.

Monitor replication lag:

SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replication_lag;

---

6️⃣ Query Optimization & EXPLAIN

EXPLAIN and EXPLAIN ANALYZE help identify slow queries.

Example:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

You’ll see:

• Seq Scan → full table scan (needs index)
• Index Scan → efficient
• Nested Loop / Hash Join → shows join strategy
• Actual time → how long each step took

Use these to:

• Add missing indexes
• Re-analyze tables
• Rewrite queries for better plans

---

7️⃣ Monitoring and Maintenance Queries

Purpose	SQL
Long-running queries	SELECT pid, query, now() - query_start FROM pg_stat_activity WHERE state='active';
Index usage ratio	SELECT relname, idx_scan, seq_scan FROM pg_stat_user_tables;
Table bloat	SELECT relname, n_dead_tup FROM pg_stat_all_tables;
Connection count	SELECT count(*) FROM pg_stat_activity;
I/O stats	SELECT * FROM pg_statio_user_tables;

---

8️⃣ Backup & PITR Strategy

Incremental / PITR backup flow:

1. Enable WAL archiving

   archive_mode = on
   archive_command = 'cp %p /archive/%f'
   

2. Use pg_basebackup for base snapshot.

3. Restore + replay WAL logs for recovery to a specific timestamp.

---

9️⃣ Security Essentials

Setting	Purpose
pg_hba.conf	Controls host-based authentication
ssl = on	Enables SSL connections
password_encryption = scram-sha-256	Secure password hashing
ALTER ROLE user PASSWORD 'xxx';	Rotate passwords
REVOKE CONNECT ON DATABASE	Restrict access

---

🔟 Useful Extensions

Extension	Use Case
pg_stat_statements	Tracks slow queries
pg_partman	Automates table partitioning
pgcrypto	Encryption and hashing
postgis	Geospatial support
pg_repack	Online table reorganization

---