Cheatsheet for postgress DBA

🧩 1️⃣ Top SQL Queries for DBAs

This section contains the 10 most essential SQL queries a DBA uses to monitor, troubleshoot, and optimize PostgreSQL databases.

Purpose	Example Query	Why it’s important
Active Queries	SELECT pid, usename, datname, state, query FROM pg_stat_activity WHERE state != 'idle';	Shows currently running queries — useful to detect long-running or blocking operations.
Blocking Queries	Joins pg_locks on transaction IDs	Identifies which sessions are blocking others (deadlocks, waiting transactions).
Table Size	Uses pg_total_relation_size()	Helps find largest tables for tuning and partitioning.
Database Size	pg_database_size()	Lets you quickly see which database consumes most space.
Unused Indexes	SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;	Detects indexes that consume space but are never used — candidates for removal.
Slow Queries	pg_stat_statements	Finds top slow SQL queries by execution time — crucial for performance tuning.
Autovacuum Stats	pg_stat_all_tables	Shows if autovacuum is cleaning up dead tuples effectively.
Roles & Privileges	SELECT rolname, rolsuper, rolcreatedb, rolcanlogin FROM pg_roles;	Helps audit user roles and permissions.
Connections per DB	SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;	Ensures no single DB is hitting connection limits.
Table Bloat	Checks n_dead_tup	Identifies bloat — tables with too many dead tuples slowing down performance.

These queries are the backbone of any DBA’s daily health check or monitoring dashboard.

---

🧮 2️⃣ Common PostgreSQL Data Types

Knowing PostgreSQL data types is key for designing efficient schemas. The cheat sheet includes:

Category	Examples	Notes
Numeric	integer, bigint, decimal, numeric, serial	Used for numbers and auto-incrementing fields.
Character	char(n), varchar(n), text	Used for text and strings.
Boolean	boolean	True/False values.
Date/Time	date, timestamp, timestamptz, interval	For time zones and scheduling.
UUID	uuid	Great for unique IDs instead of integers (distributed systems).
Array	integer[], text[]	Stores multiple values in one column — handy but use with care.
JSON / JSONB	json, jsonb	Store structured data; jsonb supports indexing and search.
Range	int4range, numrange, daterange	Represents value ranges (e.g., date intervals).
Network	inet, cidr, macaddr	For IP and network data — useful in DevOps and firewall logs.
Full Text Search	tsvector, tsquery	For advanced text searching (search bars, document search).

---

💻 3️⃣ Useful psql Commands

These are shortcuts used inside the PostgreSQL CLI (psql) to explore and manage databases:

Command	Description
\l	List all databases.
\dt	List all tables in the current database.
\du	Show all roles and users.
\x	Toggle expanded output — great for wide columns.
\timing	Show execution time for queries (performance testing).
\conninfo	Show details of current connection.
\q	Quit psql.

These commands are like shortcuts that make your DBA workflow much faster.

---

💾 4️⃣ Backup & Restore Commands

A DBA must always be ready for disaster recovery. These commands show how to handle that safely.

Backup

pg_dump -U username -F c -f backup.dump dbname

• -F c creates a custom format (compressed and portable).
• Useful for incremental or schema-specific restores.

Restore

pg_restore -U username -d dbname backup.dump

• Restores from a custom-format dump created above.

Dump All Databases

pg_dumpall -U username > alldb_backup.sql

• Great for taking full backups during migrations.

---

🔐 Bonus Tips for PostgreSQL DBAs

1. Enable logging of slow queries (log_min_duration_statement) to identify performance issues.
2. Monitor autovacuum — stale tuples slow down reads.
3. Use pg_stat_statements for query performance insights.
4. Regularly reindex to avoid index bloat.
5. Set proper max_connections and work_mem in postgresql.conf for scaling.
6. Use pgAdmin or DBeaver for GUI-based monitoring.
7. Learn EXPLAIN ANALYZE to optimize slow queries.
8. Keep PostgreSQL updated — many versions bring huge performance and security improvements.