Whether you manage PostgreSQL, MySQL, or MariaDB, these 10 SQL queries are essential tools for daily database administration. Save this list — you will reference it regularly.
1. Find the Largest Tables
-- PostgreSQL: Table sizes with indexes
SELECT
schemaname || '.' || tablename AS table_name,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS data_size,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;2. Identify Slow Queries
-- PostgreSQL: Top 10 slowest queries (requires pg_stat_statements)
SELECT
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS avg_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percent,
left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;3. Check Active Connections
-- PostgreSQL: Active connections by state
SELECT
state,
count(*) AS connections,
max(now() - state_change) AS longest_duration
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY state
ORDER BY connections DESC;4. Find Missing Indexes
-- PostgreSQL: Tables with high sequential scans (potential missing indexes)
SELECT
schemaname || '.' || relname AS table_name,
seq_scan,
seq_tup_read,
idx_scan,
CASE WHEN seq_scan > 0
THEN round((seq_tup_read::numeric / seq_scan), 0)
ELSE 0
END AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;5. Monitor Replication Lag
-- PostgreSQL: Check replication status
SELECT
client_addr,
state,
sent_lsn,
write_lsn,
replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;6. Database Size Overview
-- PostgreSQL: All database sizes
SELECT
datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;7. Find Duplicate Rows
-- Generic: Find duplicates based on specific columns
SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;8. Table Row Estimates
-- PostgreSQL: Fast row count estimates
SELECT
schemaname || '.' || relname AS table_name,
n_live_tup AS estimated_rows,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;9. Lock Monitoring
-- PostgreSQL: Find blocking locks
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;10. Index Usage Statistics
-- PostgreSQL: Index usage and size
SELECT
schemaname || '.' || indexrelname AS index_name,
relname AS table_name,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;These queries form the foundation of effective database administration. Practice them regularly and adapt them to your specific environment.
