core SQL queries you should know as a postgress DBA

As a PostgreSQL DBA, there are a handful of core SQL queries you must know for monitoring, performance tuning, and maintenance — plus a strong understanding of data types, since PostgreSQL supports more than most other RDBMSs (including JSON, arrays, hstore, UUIDs, etc.).

Let’s go through both in a clean, practical way:

---

🧩 Part 1: Top 10 SQL Queries Every PostgreSQL DBA Should Know

🧠 1️⃣ View Active Queries & Locks

See what’s running right now (who is blocking whom):

SELECT pid, usename, datname, state, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

🔍 Use this to find slow queries, long-running transactions, or blocked sessions.

---

⚙️ 2️⃣ Find Blocking and Blocked Queries

SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocking_locks.transactionid = blocked_locks.transactionid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid;

Helps identify deadlocks or blocking chains.

---

📊 3️⃣ Check Table & Index Size

SELECT relname AS object_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Shows largest tables/indexes — useful for space management and identifying bloated tables.

---

🔁 4️⃣ Check Table Bloat (Vacuum Candidates)

SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_all_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

High n_dead_tup means VACUUM is needed.

---

⚡ 5️⃣ Top Slowest Queries (pg_stat_statements)

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

🧩 Requires enabling pg_stat_statements in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

---

🔒 6️⃣ Check User Roles & Privileges

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles;

Use this to audit privileges or confirm who has superuser access.

---

📚 7️⃣ Check Database Size

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

Know which databases are growing fastest.

---

🧹 8️⃣ Find Index Usage (to detect unused ones)

SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Helps decide which indexes might be removed to save space.

---

🧮 9️⃣ Check Autovacuum Stats

SELECT relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count
FROM pg_stat_all_tables
ORDER BY autovacuum_count DESC;

Confirms whether autovacuum is actually working.

---

🧩 🔟 Check Connection & Session Info

SELECT datname, count(*) AS connections
FROM pg_stat_activity
GROUP BY datname;

To avoid exceeding max_connections limit or spotting connection leaks.

---

📦 Bonus Queries

🧱 Get PostgreSQL version:

SELECT version();

🧱 Show configuration parameter values:

SHOW work_mem;
SHOW shared_buffers;

🧱 Change a parameter at runtime:

SET work_mem = '64MB';

---

🧠 Part 2: PostgreSQL Data Types — What a DBA Should Know

PostgreSQL supports more data types than almost any other RDBMS. You should know the core categories, their use cases, and storage implications.

Category	Example Types	Notes
Numeric	smallint, integer, bigint, decimal, numeric, real, double precision, serial	Choose based on size and precision needs
Character	char(n), varchar(n), text	text = variable unlimited size, most common
Boolean	boolean	true, false, null
Date/Time	date, time, timestamp, timestamptz, interval	Use timestamptz for timezone-aware data
Monetary	money	Stores currency, locale-dependent
UUID	uuid	Great for distributed systems, ensures unique IDs
Array	integer[], text[]	Store multiple values in one column (careful with normalization)
JSON / JSONB	json, jsonb	For semi-structured data; jsonb supports indexing and better performance
Network	inet, cidr, macaddr	Store IP addresses and networks
Geometric	point, line, circle	Useful for spatial or coordinate data
Full Text Search	tsvector, tsquery	Built-in text search indexing
Range	int4range, numrange, daterange	Represent ranges of values (e.g., date ranges)
Hstore	hstore	Key-value storage (simpler than JSON)

---

🧩 Example Table Using Many Types

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC(10,2),
    joined_on TIMESTAMPTZ DEFAULT now(),
    active BOOLEAN DEFAULT true,
    skills TEXT[],
    address JSONB,
    emp_uuid UUID DEFAULT gen_random_uuid()
);

---

🧮 Data Type Insights for DBAs

Topic	Best Practice
Numeric vs Text	Use proper numeric types to avoid slow comparisons/sorts.
Timestamps	Prefer timestamptz for global apps.
JSON vs JSONB	Use jsonb — it's binary-parsed, indexable, and faster.
Arrays & Hstore	Use when schema flexibility is required, but avoid overuse.
UUIDs	Great for distributed systems (no sequence conflicts).
Indexing JSONB	Use GIN index for faster lookups: CREATE INDEX idx_jsonb ON table USING gin (jsonb_col);

---

🧩 In Summary

Area	Must-Know Queries	Key DBA Focus
Monitoring	pg_stat_activity, pg_stat_statements	Slow queries & locks
Performance	EXPLAIN ANALYZE, pg_stat_user_indexes	Query optimization
Space Mgmt	pg_total_relation_size()	Table/index bloat
Security	pg_roles, pg_hba.conf	Access control
Data Types	jsonb, uuid, timestamptz, numeric	Proper design choices