PostgreSQL Cheat Sheet: 100+ Commands with Real Examples

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- List all databases
\l

-- List all tables in current database
\dt

-- List tables with sizes and descriptions
\dt+

-- List all schemas
\dn

-- List all views
\dv

-- List all functions
\df

-- List all indexes
\di

-- List all sequences
\ds

-- List all users/roles
\du

1
2
3
4
5
-- Describe a table (columns, types, constraints, indexes)
\d table_name

-- Describe with extra info (size, storage, stats)
\d+ table_name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Switch to another database
\c database_name

-- Turn on expanded display (vertical output for wide tables)
\x

-- Toggle between regular and expanded output automatically
\x auto

-- Turn on query timing
\timing

-- Edit the last query in your editor
\e

-- Run SQL from a file
\i /path/to/file.sql

-- Show command history
\s

-- Get help on SQL commands
\h CREATE TABLE

-- Get help on psql commands
\?

-- Quit psql
\q

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Create a new database
CREATE DATABASE myapp;

-- Create with specific owner and encoding
CREATE DATABASE myapp
  OWNER appuser
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

-- Rename a database (no active connections allowed)
ALTER DATABASE oldname RENAME TO newname;

-- Change database owner
ALTER DATABASE myapp OWNER TO newuser;

-- Drop a database (careful, this is permanent)
DROP DATABASE myapp;

-- Drop only if it exists (avoids error)
DROP DATABASE IF EXISTS myapp;

-- Check database sizes
SELECT
  datname AS database,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Basic table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- With foreign key
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  total NUMERIC(10, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create table from a query result
CREATE TABLE active_users AS
SELECT * FROM users WHERE last_login > NOW() - INTERVAL '30 days';

-- Create table only if it does not exist
CREATE TABLE IF NOT EXISTS logs (
  id BIGSERIAL PRIMARY KEY,
  message TEXT,
  level VARCHAR(10),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

1
2
3
4
5
6
7
CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

-- If you need to override the generated value during inserts (e.g., data migration)
INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (999, 'Migrated User');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add a column with a default value
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change column type
ALTER TABLE users ALTER COLUMN full_name TYPE TEXT;

-- Set a column as NOT NULL
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Add a constraint
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total > 0);

-- Rename a table
ALTER TABLE users RENAME TO customers;

1
2
3
4
5
6
7
8
-- Drop a table
DROP TABLE users;

-- Drop only if it exists
DROP TABLE IF EXISTS users;

-- Drop and everything that depends on it (foreign keys, views)
DROP TABLE users CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Insert a single row
INSERT INTO users (name, email)
VALUES ('Ajit Singh', 'ajit@example.com');

-- Insert and return the new row
INSERT INTO users (name, email)
VALUES ('Ajit Singh', 'ajit@example.com')
RETURNING *;

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

-- Insert from a query
INSERT INTO archived_users
SELECT * FROM users WHERE last_login < '2024-01-01';

-- Insert or update on conflict (upsert) [PostgreSQL 9.5+]
INSERT INTO users (email, name)
VALUES ('ajit@example.com', 'Ajit Singh')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- Insert or do nothing on conflict [PostgreSQL 9.5+]
INSERT INTO users (email, name)
VALUES ('ajit@example.com', 'Ajit Singh')
ON CONFLICT (email) DO NOTHING;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with alias
SELECT name AS full_name, email AS contact FROM users;

-- Select distinct values
SELECT DISTINCT status FROM orders;

-- Select with limit and offset (pagination)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- Count rows
SELECT COUNT(*) FROM users;

-- Check if a row exists
SELECT EXISTS(SELECT 1 FROM users WHERE email = 'ajit@example.com');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Update specific rows
UPDATE users SET name = 'Ajit' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET name = 'Ajit', is_active = true
WHERE id = 1;

-- Update and return the changed rows
UPDATE users SET is_active = false
WHERE last_login < NOW() - INTERVAL '1 year'
RETURNING id, name, email;

-- Update using data from another table
UPDATE orders
SET status = 'cancelled'
FROM users
WHERE orders.user_id = users.id
  AND users.is_active = false;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete and return what was deleted
DELETE FROM users WHERE is_active = false
RETURNING *;

-- Delete all rows (use TRUNCATE for large tables)
DELETE FROM logs;

-- TRUNCATE is faster for clearing entire tables
TRUNCATE TABLE logs;

-- TRUNCATE and reset the auto-increment counter
TRUNCATE TABLE logs RESTART IDENTITY;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- WHERE with comparison
SELECT * FROM users WHERE age > 25;

-- Multiple conditions
SELECT * FROM orders
WHERE status = 'pending'
  AND total > 100
  AND created_at > '2025-01-01';

-- OR condition
SELECT * FROM users
WHERE role = 'admin' OR role = 'moderator';

-- IN list
SELECT * FROM users WHERE role IN ('admin', 'moderator', 'editor');

-- NOT IN
SELECT * FROM users WHERE status NOT IN ('banned', 'deleted');

-- BETWEEN range
SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

-- Pattern matching with LIKE
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Case insensitive pattern matching
SELECT * FROM users WHERE email ILIKE '%@Gmail.com';

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Sorting
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple sort columns
SELECT * FROM users ORDER BY role ASC, name ASC;

-- NULLS handling in sorting
SELECT * FROM users ORDER BY phone ASC NULLS LAST;

1
2
3
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

1
2
3
4
5
6
7
8
9
10
-- Find users and their orders (including users with no orders)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Find users who have never ordered anything
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

1
2
3
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

1
2
3
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

1
2
3
4
-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

1
2
3
SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Count, sum, average, min, max
SELECT
  COUNT(*) AS total_orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order,
  MIN(total) AS smallest_order,
  MAX(total) AS largest_order
FROM orders;

-- Group by
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status
ORDER BY count DESC;

-- Group by with filtering (HAVING)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- Group by date (count orders per day)
SELECT
  created_at::date AS order_date,
  COUNT(*) AS orders
FROM orders
GROUP BY created_at::date
ORDER BY order_date DESC;

-- Group by month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

-- String aggregation (concatenate values) [PostgreSQL 9.0+]
SELECT
  department,
  STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index (multi-column)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (only index rows matching a condition)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

-- Expression index (index on a function result)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- GIN index for JSONB columns
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- GIN index for full text search
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body));

-- Create index without locking the table (use this in production)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Show indexes on a table (listed at the bottom of the output)
\d table_name

-- List all indexes in the database
\di

-- Check index sizes
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users';

-- Drop an index
DROP INDEX idx_users_email;

-- Drop without error if it does not exist
DROP INDEX IF EXISTS idx_users_email;

-- Rebuild an index
REINDEX INDEX idx_users_email;

-- Rebuild all indexes on a table
REINDEX TABLE users;

-- Rebuild an index without locking the table [PostgreSQL 12+]
REINDEX INDEX CONCURRENTLY idx_users_email;

1
2
3
4
5
6
7
8
9
10
-- Find unused indexes (wasting disk space and slowing down writes)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexname::regclass) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Create a view
CREATE VIEW active_orders AS
SELECT o.id, u.name, o.total, o.status, o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status IN ('pending', 'processing');

-- Use it like a table
SELECT * FROM active_orders WHERE total > 100;

-- Create or replace a view
CREATE OR REPLACE VIEW active_orders AS
SELECT o.id, u.name, u.email, o.total, o.status, o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status IN ('pending', 'processing');

-- Drop a view
DROP VIEW active_orders;

-- Materialized view (stores the result on disk) [PostgreSQL 9.3+]
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- Refresh the materialized view (re-runs the query)
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Refresh without locking reads [PostgreSQL 9.4+]
-- Requires a UNIQUE index on the materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

1
CREATE UNIQUE INDEX ON monthly_revenue (month);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Basic transaction
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- If something goes wrong, roll everything back
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  -- Oops, something went wrong
ROLLBACK;

-- Savepoints for partial rollbacks
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 99.99);
  SAVEPOINT order_created;
  
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- This fails, but we only roll back to the savepoint
  ROLLBACK TO SAVEPOINT order_created;
  
  -- The order insert is still good
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Create a table with JSONB column
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'
);

-- Insert JSON data
INSERT INTO events (name, metadata) VALUES
  ('signup', '{"source": "google", "campaign": "summer2025"}'),
  ('purchase', '{"amount": 49.99, "product_id": 123, "coupon": "SAVE10"}');

-- Query JSON fields
SELECT name, metadata->>'source' AS source
FROM events
WHERE metadata->>'source' = 'google';

-- Query nested JSON
SELECT metadata->'address'->>'city' AS city FROM users;

-- Check if a key exists
SELECT * FROM events WHERE metadata ? 'coupon';

-- Check if JSON contains a value
SELECT * FROM events WHERE metadata @> '{"source": "google"}';

-- Update a JSON field [PostgreSQL 9.5+]
UPDATE events
SET metadata = jsonb_set(metadata, '{campaign}', '"winter2025"')
WHERE id = 1;

-- Add a new key to JSON [PostgreSQL 9.5+]
UPDATE events
SET metadata = metadata || '{"processed": true}'
WHERE id = 1;

-- Remove a key from JSON [PostgreSQL 9.5+]
UPDATE events
SET metadata = metadata - 'campaign'
WHERE id = 1;

-- Aggregate JSON
SELECT jsonb_agg(name) FROM events;

1
CREATE INDEX idx_events_metadata ON events USING GIN(metadata);

1
2
3
4
5
6
-- Export a table to CSV
COPY users TO '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

-- Export a query result to CSV
COPY (SELECT name, email FROM users WHERE is_active = true)
TO '/tmp/active_users.csv' WITH (FORMAT csv, HEADER true);

1
2
3
4
-- Import CSV into a table
COPY users (name, email, created_at)
FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER true);

1
2
3
4
5
-- Client-side export
\copy users TO 'users.csv' WITH (FORMAT csv, HEADER true)

-- Client-side import
\copy users (name, email) FROM 'users.csv' WITH (FORMAT csv, HEADER true)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create a new user
CREATE USER appuser WITH PASSWORD 'secure_password';

-- Create a user with specific privileges
CREATE USER readonly_user WITH PASSWORD 'password' NOSUPERUSER NOCREATEDB;

-- Change a user's password
ALTER USER appuser WITH PASSWORD 'new_password';

-- Drop a user
DROP USER appuser;

-- List all users
\du

1
2
3
4
5
6
7
8
9
10
11
-- Create a role (no login by default)
CREATE ROLE analyst;

-- Grant permissions to a role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Assign a role to a user
GRANT analyst TO appuser;

-- Remove a role from a user
REVOKE analyst FROM appuser;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Grant full access to a database
GRANT ALL PRIVILEGES ON DATABASE mydb TO appuser;

-- Grant specific table permissions
GRANT SELECT, INSERT, UPDATE ON users TO appuser;

-- Grant access to all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Grant access to future tables automatically [PostgreSQL 9.0+]
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

-- Revoke permissions
REVOKE INSERT, UPDATE ON users FROM appuser;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO appuser;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Application user: can read and write data, but cannot modify schema
CREATE USER webapp WITH PASSWORD 'app_password';
GRANT CONNECT ON DATABASE mydb TO webapp;
GRANT USAGE ON SCHEMA public TO webapp;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webapp;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO webapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO webapp;

-- Read-only user for analytics
CREATE USER analyst WITH PASSWORD 'analyst_password';
GRANT CONNECT ON DATABASE mydb TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

1
2
3
4
5
6
7
8
9
-- Show the execution plan (does not run the query)
EXPLAIN SELECT * FROM users WHERE email = 'ajit@example.com';

-- Show the plan WITH actual execution stats
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ajit@example.com';

-- More detailed output (BUFFERS option available in PostgreSQL 9.0+)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'ajit@example.com';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Vacuum a specific table
VACUUM users;

-- Vacuum and reclaim disk space (locks the table)
VACUUM FULL users;

-- Update table statistics for the query planner
ANALYZE users;

-- Do both at once
VACUUM ANALYZE users;

-- Check when tables were last vacuumed
SELECT
  relname AS table,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  n_dead_tup AS dead_rows
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Find currently running queries (state column requires PostgreSQL 9.2+)
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Find queries running longer than 5 minutes
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > INTERVAL '5 minutes';

-- Kill a specific query (graceful)
SELECT pg_cancel_backend(12345);

-- Kill a specific query (force)
SELECT pg_terminate_backend(12345);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- See all active connections
SELECT
  datname AS database,
  usename AS user,
  client_addr,
  state,
  query
FROM pg_stat_activity
ORDER BY state;

-- Count connections per database
SELECT datname, COUNT(*)
FROM pg_stat_activity
GROUP BY datname;

-- Check max connections setting
SHOW max_connections;

1
2
3
4
5
6
7
8
9
10
-- Top 10 largest tables
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
  pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
  pg_size_pretty(pg_total_relation_size(tablename::regclass) - pg_relation_size(tablename::regclass)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9
-- Should be above 99% for most workloads
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  CASE WHEN sum(heap_blks_hit) + sum(heap_blks_read) > 0
    THEN round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))::numeric * 100, 2)
    ELSE 0
  END AS ratio
FROM pg_statio_user_tables;

1
2
3
4
5
6
7
8
9
10
11
-- Index hit rate (should be high)
SELECT
  relname AS table,
  idx_scan AS index_scans,
  seq_scan AS sequential_scans,
  CASE WHEN idx_scan + seq_scan > 0
    THEN round(idx_scan::numeric / (idx_scan + seq_scan) * 100, 2)
    ELSE 0
  END AS index_usage_percent
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Find tables with lots of dead tuples (need vacuuming)
SELECT
  relname AS table,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  CASE WHEN n_live_tup > 0
    THEN round(n_dead_tup::numeric / n_live_tup * 100, 2)
    ELSE 0
  END AS dead_ratio_percent,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

1
2
3
4
5
6
7
8
-- Find blocked queries and what is blocking them [PostgreSQL 9.6+]
SELECT
  pid,
  pg_blocking_pids(pid) AS blocked_by,
  query,
  now() - query_start AS duration
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Find blocking queries (works on all versions)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks lo ON lo.locktype = bl.locktype
  AND lo.database IS NOT DISTINCT FROM bl.database
  AND lo.relation IS NOT DISTINCT FROM bl.relation
  AND lo.page IS NOT DISTINCT FROM bl.page
  AND lo.tuple IS NOT DISTINCT FROM bl.tuple
  AND lo.virtualxid IS NOT DISTINCT FROM bl.virtualxid
  AND lo.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND lo.classid IS NOT DISTINCT FROM bl.classid
  AND lo.objid IS NOT DISTINCT FROM bl.objid
  AND lo.objsubid IS NOT DISTINCT FROM bl.objsubid
  AND lo.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = lo.pid
WHERE NOT bl.granted;