PostgreSQL for Backend Developers: 7 Patterns I Use Every Day

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

-- In postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
 
-- Restart PostgreSQL, then:
CREATE EXTENSION pg_stat_statements;

SELECT
    substring(query, 1, 100) as short_query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 100) as short_query,
    calls,
    total_exec_time,
    mean_exec_time,
    (total_exec_time / sum(total_exec_time) OVER ()) * 100 AS percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_ratio
FROM pg_statio_user_tables;

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
 
    -- Flexible attributes in JSONB
    attributes JSONB NOT NULL DEFAULT '{}',
 
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- GIN index for fast JSONB search
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Index for specific field (even faster)
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- Insert product with attributes
INSERT INTO products (name, category, price, attributes)
VALUES (
    'iPhone 15 Pro',
    'electronics',
    999.99,
    '{
        "brand": "Apple",
        "storage": "256GB",
        "color": "Natural Titanium",
        "screen": "6.1 inch",
        "processor": "A17 Pro"
    }'::jsonb
);
 
-- Find all black M-sized t-shirts
SELECT id, name, price, attributes
FROM products
WHERE category = 'clothing'
  AND attributes @> '{"color": "black", "size": "M"}';
 
-- Update specific attribute (without overwriting others)
UPDATE products
SET attributes = attributes || '{"discount": 15}'::jsonb
WHERE id = 42;
 
-- Remove attribute
UPDATE products
SET attributes = attributes - 'discount'
WHERE id = 42;
 
-- Search nested fields
SELECT name, attributes->'specs'->>'processor' as cpu
FROM products
WHERE attributes->'specs'->>'processor' LIKE '%A17%';

-- ❌ SLOW: Search via ->> (doesn't use GIN index)
SELECT * FROM products
WHERE attributes->>'color' = 'red';
 
-- ✅ FAST: Search via @> (uses GIN index)
SELECT * FROM products
WHERE attributes @> '{"color": "red"}';

-- ❌ SLOW: B-tree index on JSONB column (almost useless)
CREATE INDEX idx_bad ON products (attributes);
 
-- ✅ FAST: GIN index for searching inside content
CREATE INDEX idx_good ON products USING GIN (attributes);
 
-- ✅ EVEN FASTER: Index on specific field
CREATE INDEX idx_best ON products ((attributes->>'color'));

-- ❌ VERY SLOW: SUM on JSONB field
SELECT SUM((attributes->>'price')::numeric) FROM products;
-- Doesn't use indexes, casts type for each row
 
-- ✅ SOLUTION: Move critical fields to separate columns
ALTER TABLE products ADD COLUMN price NUMERIC(10,2);
UPDATE products SET price = (attributes->>'price')::numeric;
-- Now SUM(price) works fast

-- ❌ BAD: Updating one field rewrites entire JSONB
UPDATE products
SET attributes = attributes || '{"views": 1001}'::jsonb
WHERE id = 123;
-- Each UPDATE creates new version of ENTIRE JSONB object
 
-- ✅ SOLUTION: Counters and frequently changing fields — separate columns
ALTER TABLE products ADD COLUMN views INTEGER DEFAULT 0;
UPDATE products SET views = views + 1 WHERE id = 123;
-- UPDATE of only one INT — 10x faster

-- These are THREE DIFFERENT things:
INSERT INTO products (attributes) VALUES (NULL);              -- NULL
INSERT INTO products (attributes) VALUES ('{}');              -- Empty object
INSERT INTO products (attributes) VALUES ('{"color": null}'); -- Key with null
 
-- Search works differently:
WHERE attributes IS NULL               -- Only first
WHERE attributes = '{}'                -- Only second
WHERE attributes->>'color' IS NULL     -- Second and third
WHERE attributes ? 'color'             -- Only third

-- Index on entire table (95% garbage)
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index only for active orders (5% of table)
CREATE INDEX idx_orders_active
ON orders (created_at, user_id)
WHERE status IN ('pending', 'processing');

-- Index only for non-deleted records (soft delete)
CREATE INDEX idx_users_active
ON users (email)
WHERE deleted_at IS NULL;
 
-- Index only for future events
CREATE INDEX idx_events_upcoming
ON events (start_time)
WHERE start_time > NOW();
 
-- Index only for unpaid invoices
CREATE INDEX idx_invoices_unpaid
ON invoices (due_date, customer_id)
WHERE paid_at IS NULL;
 
-- Composite partial index for complex conditions
CREATE INDEX idx_orders_priority
ON orders (created_at DESC)
WHERE status = 'processing'
  AND priority = 'high'
  AND assigned_to IS NULL;

-- PostgreSQL automatically uses partial index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
  AND user_id = 12345
ORDER BY created_at DESC
LIMIT 10;
 
-- Index Scan using idx_orders_active on orders
-- Buffers: shared hit=42

-- Was
CREATE INDEX idx_shipments_status ON shipments (status);
-- Size: 40GB
 
-- Became
CREATE INDEX idx_shipments_active
ON shipments (status, estimated_delivery)
WHERE status IN ('pending', 'in_transit', 'out_for_delivery');
-- Size: 2GB

SELECT * FROM users
WHERE LOWER(first_name || ' ' || last_name) LIKE '%john doe%';
-- Slow, index not used

-- Lots of code for trigger
-- Easy to forget to update
-- Another source of bugs

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
 
    -- Generated column (automatically computed)
    full_name TEXT GENERATED ALWAYS AS (
        first_name || ' ' || last_name
    ) STORED,
 
    -- Can be indexed!
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Index for fast full name search
CREATE INDEX idx_users_full_name ON users USING GIN (
    to_tsvector('english', full_name)
);

-- Full-text search works instantly
SELECT id, full_name, email
FROM users
WHERE to_tsvector('english', full_name) @@ to_tsquery('english', 'john & doe');
 
-- Generated column updates automatically
UPDATE users
SET first_name = 'Jane'
WHERE id = 42;
-- full_name automatically becomes "Jane Doe"

-- Calculate age from birth date
CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    birth_date DATE NOT NULL,
 
    age INTEGER GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(birth_date))
    ) STORED
);
 
-- Automatic total calculation for order
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
 
    total NUMERIC(10,2) GENERATED ALWAYS AS (
        quantity * price
    ) STORED
);
 
-- URL slug from title
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
 
    slug TEXT GENERATED ALWAYS AS (
        LOWER(
            REGEXP_REPLACE(
                REGEXP_REPLACE(title, '[^a-zA-Z0-9\\s-]', '', 'g'),
                '\\s+', '-', 'g'
            )
        )
    ) STORED UNIQUE
);
 
-- JSON field for search (from multiple fields)
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
 
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english',
            COALESCE(name, '') || ' ' ||
            COALESCE(description, '') || ' ' ||
            COALESCE(category, '')
        )
    ) STORED
);
 
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('russian', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('russian', COALESCE(body, '')), 'B') ||
    setweight(to_tsvector('russian', COALESCE(array_to_string(tags, ' '), '')), 'C')
) STORED;
 
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Enable RLS for table
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
 
-- Policy: user sees only their company's documents
CREATE POLICY documents_isolation_policy ON documents
    USING (company_id = current_setting('app.current_company_id')::BIGINT);

# Python (psycopg2/psycopg3)
def get_db_connection(user):
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
 
    # Set user's company_id in session
    cursor.execute(
        "SET app.current_company_id = %s",
        (user.company_id,)
    )
 
    return conn
 
# Now all queries are automatically filtered!
# Forgetting WHERE company_id = ... is IMPOSSIBLE
cursor.execute("SELECT * FROM documents WHERE title LIKE %s", ('%report%',))
# PostgreSQL automatically adds: AND company_id = current_company_id

-- Policy for SELECT (read)
CREATE POLICY documents_select_policy ON documents
    FOR SELECT
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
    );
 
-- Policy for INSERT (only to your company)
CREATE POLICY documents_insert_policy ON documents
    FOR INSERT
    WITH CHECK (
        company_id = current_setting('app.current_company_id')::BIGINT
    );
 
-- Policy for UPDATE (edit only your documents + only authors)
CREATE POLICY documents_update_policy ON documents
    FOR UPDATE
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
        AND created_by = current_setting('app.current_user_id')::BIGINT
    );
 
-- Policy for DELETE (only company admins can delete)
CREATE POLICY documents_delete_policy ON documents
    FOR DELETE
    USING (
        company_id = current_setting('app.current_company_id')::BIGINT
        AND current_setting('app.user_role') = 'admin'
    );
 
-- Superusers bypass RLS
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- Now even SUPERUSER won't bypass policies (except tables without policies)

-- All tables with client data
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE deals ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
 
-- Single policy for all tables
CREATE POLICY tenant_isolation_policy ON contacts
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- Same for other tables

-- ❌ ERROR: current_setting will return NULL or ERROR
SELECT * FROM documents;
-- ERROR: unrecognized configuration parameter "app.current_company_id"
 
-- ✅ CORRECT: Always set parameter at session start
SET app.current_company_id = 42;
-- Now RLS works

-- SUPERUSER sees EVERYTHING, ignoring RLS policies
-- Even if you connected as postgres
 
-- ✅ SOLUTION: FORCE ROW LEVEL SECURITY
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
-- Now even SUPERUSER won't bypass policies

-- You have policy:
CREATE POLICY select_policy ON documents FOR SELECT
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- ❌ INSERT doesn't work!
INSERT INTO documents (company_id, title) VALUES (42, 'Test');
-- ERROR: new row violates row-level security policy
 
-- ✅ SOLUTION: Need separate policy for INSERT
CREATE POLICY insert_policy ON documents FOR INSERT
    WITH CHECK (company_id = current_setting('app.current_company_id')::BIGINT);

-- You have policy on company_id
CREATE POLICY isolation ON documents
    USING (company_id = current_setting('app.current_company_id')::BIGINT);
 
-- ❌ SLOW: No index on company_id
-- PostgreSQL does Seq Scan + filtering
 
-- ✅ FAST: Index on company_id
CREATE INDEX idx_documents_company ON documents (company_id);
-- Now Index Scan

-- You have partitioned table with RLS
CREATE TABLE documents (...) PARTITION BY RANGE (created_at);
 
-- ❌ RLS DOESN'T WORK on direct partition queries!
SELECT * FROM documents_2024_01;
-- Returns ALL rows, ignoring policies
 
-- ✅ CORRECT: Query parent table
SELECT * FROM documents;
-- RLS applies, then partition pruning

-- Try to get exclusive lock
SELECT pg_try_advisory_lock(12345);
-- Returns true if lock acquired
-- Returns false if already taken
 
-- Lock with waiting
SELECT pg_advisory_lock(12345);
-- Waits until lock is released
 
-- Release lock
SELECT pg_advisory_unlock(12345);
 
-- Automatic release on connection close
-- (no need to manually unlock if process crashes)

import psycopg2
import hashlib
 
def process_payment_exclusively(payment_id):
    """
    Process payment only if nobody else is processing it
    """
    conn = psycopg2.connect(...)
    cursor = conn.cursor()
 
    # Generate numeric ID from payment_id (advisory lock takes bigint)
    lock_id = int(hashlib.md5(f"payment:{payment_id}".encode()).hexdigest()[:15], 16)
 
    # Try to acquire lock
    cursor.execute("SELECT pg_try_advisory_lock(%s)", (lock_id,))
    acquired = cursor.fetchone()[0]
 
    if not acquired:
        print(f"Payment {payment_id} already being processed by another worker")
        return False
 
    try:
        # Process payment
        process_payment_logic(payment_id)
        conn.commit()
        return True
    finally:
        # Release lock
        cursor.execute("SELECT pg_advisory_unlock(%s)", (lock_id,))
        conn.close()

-- Lock by two numbers (instead of one bigint)
SELECT pg_try_advisory_lock(user_id, task_id);
 
-- Shared lock (multiple processes can read)
SELECT pg_try_advisory_lock_shared(12345);
 
-- Transaction-level lock (released on COMMIT/ROLLBACK)
SELECT pg_try_advisory_xact_lock(12345);
 
-- Check existing locks
SELECT
    locktype,
    objid,
    mode,
    granted,
    pid,
    pg_blocking_pids(pid) as blocking_pids
FROM pg_locks
WHERE locktype = 'advisory';

from celery import Task
import hashlib
 
class ExclusiveTask(Task):
    def __call__(self, *args, **kwargs):
        # Generate lock_id from task name + arguments
        task_hash = hashlib.md5(
            f"{self.name}:{args}:{kwargs}".encode()
        ).hexdigest()[:15]
        lock_id = int(task_hash, 16)
 
        with self.app.pool.acquire() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT pg_try_advisory_lock(%s)", (lock_id,))
 
            if not cursor.fetchone()[0]:
                self.app.log.warning(f"Task {self.name} already running")
                return None
 
            try:
                return self.run(*args, **kwargs)
            finally:
                cursor.execute("SELECT pg_advisory_unlock(%s)", (lock_id,))
 
@app.task(base=ExclusiveTask)
def send_email(user_id, template):
    # Guaranteed to execute only once
    pass

-- Create materialized view with aggregated data
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', created_at) as month,
    category,
    COUNT(*) as orders_count,
    SUM(total) as total_revenue,
    AVG(total) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, total_revenue DESC;
 
-- Index for fast access
CREATE INDEX idx_monthly_revenue_month ON monthly_revenue (month);
 
-- Refresh data (can be via cron)
REFRESH MATERIALIZED VIEW monthly_revenue;
 
-- Concurrently (without blocking reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Instant query instead of 30 seconds
SELECT * FROM monthly_revenue
WHERE month >= '2024-01-01'
ORDER BY total_revenue DESC;
 
-- Execution time: 2ms (was 30s)

-- Materialized view with JOINs
CREATE MATERIALIZED VIEW user_activity_summary AS
SELECT
    u.id,
    u.email,
    u.created_at as registered_at,
    COUNT(DISTINCT o.id) as total_orders,
    SUM(o.total) as lifetime_value,
    MAX(o.created_at) as last_order_date,
    COUNT(DISTINCT DATE(o.created_at)) as active_days
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
 
-- For CONCURRENTLY need UNIQUE index
CREATE UNIQUE INDEX idx_user_activity_id
ON user_activity_summary (id);
 
-- Now can refresh without blocking
REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary;

-- Function for refresh
CREATE OR REPLACE FUNCTION refresh_all_matviews()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
    REFRESH MATERIALIZED VIEW CONCURRENTLY user_activity_summary;
    RAISE NOTICE 'Materialized views refreshed at %', NOW();
END;
$$ LANGUAGE plpgsql;
 
-- Schedule via pg_cron (extension)
-- Every hour at :05 minutes
SELECT cron.schedule('refresh-matviews', '5 * * * *', 'SELECT refresh_all_matviews()');
 
-- Or via external cron
-- 5 * * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue"

-- 1. Materialized view for all metrics
CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
    'total_users'::text as metric,
    COUNT(*)::bigint as value,
    NULL::jsonb as details
FROM users
UNION ALL
SELECT
    'active_users_30d',
    COUNT(DISTINCT user_id),
    NULL
FROM events
WHERE created_at > NOW() - INTERVAL '30 days'
UNION ALL
SELECT
    'revenue_today',
    SUM(amount)::bigint,
    jsonb_build_object(
        'count', COUNT(*),
        'avg', AVG(amount)
    )
FROM payments
WHERE DATE(created_at) = CURRENT_DATE
-- ... 9 more metrics
;
 
-- 2. Refresh every 5 minutes
SELECT cron.schedule(
    'refresh-dashboard',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_metrics'
);

-- Parent table (partitioned)
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
 
-- Partitions by month
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
 
CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
 
-- Indexes created for each partition
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_type ON events (event_type);
 
-- PostgreSQL automatically creates:
-- idx_events_user_id_2024_01, idx_events_user_id_2024_02, ...

-- Queries work transparently
INSERT INTO events (user_id, event_type, payload, created_at)
VALUES (12345, 'page_view', '{"url": "/pricing"}', NOW());
-- PostgreSQL automatically puts in correct partition
 
-- Partition Pruning (automatic optimization)
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2024-03-01'
  AND created_at < '2024-04-01';
 
-- Seq Scan on events_2024_03
-- Reads ONLY needed partition (not all 400GB!)

-- Function to create future partitions
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    table_name TEXT,
    start_date DATE,
    end_date DATE
)
RETURNS void AS $$
DECLARE
    partition_date DATE := start_date;
    partition_name TEXT;
    start_range TEXT;
    end_range TEXT;
BEGIN
    WHILE partition_date < end_date LOOP
        partition_name := table_name || '_' || TO_CHAR(partition_date, 'YYYY_MM');
        start_range := TO_CHAR(partition_date, 'YYYY-MM-DD');
        end_range := TO_CHAR(partition_date + INTERVAL '1 month', 'YYYY-MM-DD');
 
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
            partition_name,
            table_name,
            start_range,
            end_range
        );
 
        RAISE NOTICE 'Created partition: %', partition_name;
        partition_date := partition_date + INTERVAL '1 month';
    END LOOP;
END;
$$ LANGUAGE plpgsql;
 
-- Create partitions for year ahead
SELECT create_monthly_partitions('events', '2025-01-01', '2026-01-01');

-- Detach old partition from parent table
ALTER TABLE events DETACH PARTITION events_2022_01;
 
-- Now it's regular table, can:
-- 1. pg_dump and delete
pg_dump -t events_2022_01 mydb > events_2022_01.sql
DROP TABLE events_2022_01;
 
-- 2. Move to separate schema (archive)
CREATE SCHEMA archive;
ALTER TABLE events_2022_01 SET SCHEMA archive;
 
-- 3. Move to another database

CREATE TABLE products (
    id BIGSERIAL,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10,2)
) PARTITION BY LIST (category);
 
CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('laptop', 'phone', 'tablet');
 
CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('shirt', 'pants', 'shoes');
 
CREATE TABLE products_other PARTITION OF products DEFAULT;
-- DEFAULT partition for all other values

-- Useful when no natural partition key
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    data JSONB
) PARTITION BY HASH (user_id);
 
-- 4 partitions (hash mod 4)
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
 
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
 
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
 
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 1. Created new partitioned table
CREATE TABLE sensor_readings_new (
    id BIGSERIAL,
    sensor_id BIGINT NOT NULL,
    value NUMERIC,
    recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);
 
-- 2. Created monthly partitions (for 3 years)
SELECT create_monthly_partitions('sensor_readings_new', '2021-01-01', '2024-01-01');
 
-- 3. Data migration (in chunks to avoid killing DB)
-- Script ran for 3 days
DO $$
DECLARE
    start_date DATE := '2021-01-01';
    end_date DATE;
BEGIN
    WHILE start_date < '2024-01-01' LOOP
        end_date := start_date + INTERVAL '1 month';
 
        RAISE NOTICE 'Migrating data for %', start_date;
 
        INSERT INTO sensor_readings_new
        SELECT * FROM sensor_readings
        WHERE recorded_at >= start_date AND recorded_at < end_date;
 
        COMMIT;
        PERFORM pg_sleep(60); -- 1 minute pause between batches
 
        start_date := end_date;
    END LOOP;
END $$;
 
-- 4. Switched tables
BEGIN;
ALTER TABLE sensor_readings RENAME TO sensor_readings_old;
ALTER TABLE sensor_readings_new RENAME TO sensor_readings;
COMMIT;
 
-- 5. Deleted old data (older than 1 year)
DROP TABLE sensor_readings_old_2021_01;
DROP TABLE sensor_readings_old_2021_02;
-- ...

-- ❌ WRONG: Index only on parent table
CREATE INDEX ONLY idx_events_user_id ON events (user_id);
-- Creates index only on parent, not on partitions!
 
-- ✅ CORRECT: Index propagates to all partitions
CREATE INDEX idx_events_user_id ON events (user_id);
-- PostgreSQL automatically creates indexes on all existing and future partitions

-- You have partitions for 2024-2025
-- Someone tries to insert record for 2026:
INSERT INTO events (user_id, event_type, created_at)
VALUES (123, 'click', '2026-01-01');
-- ERROR: no partition of relation "events" found for row
 
-- ✅ Solution 1: DEFAULT partition
CREATE TABLE events_default PARTITION OF events DEFAULT;
-- All records not fitting explicit partitions go here
 
-- ✅ Solution 2: Trigger for automatic partition creation
-- Use pg_partman (extension)

-- ✅ FK FROM partitioned table — works
CREATE TABLE events (
    id BIGSERIAL,
    user_id BIGINT REFERENCES users(id),  -- OK
    created_at TIMESTAMPTZ
) PARTITION BY RANGE (created_at);
 
-- ❌ FK TO partitioned table — DOESN'T work (until PG 16)
CREATE TABLE notifications (
    id BIGSERIAL PRIMARY KEY,
    event_id BIGINT REFERENCES events(id)  -- ERROR in PG < 16
);

-- ❌ WRONG: VACUUM only parent table
VACUUM events;  -- Doesn't touch partitions!
 
-- ✅ CORRECT: VACUUM each partition
VACUUM events_2024_01;
VACUUM events_2024_02;
-- Or via automation
 
-- Or enable autovacuum for each partition
ALTER TABLE events_2024_01 SET (autovacuum_enabled = true);

-- You have partitions by created_at
-- ❌ Query WITHOUT partition key
SELECT * FROM events WHERE user_id = 123;
-- Scans ALL partitions (slow)
 
-- ✅ Query WITH partition key
SELECT * FROM events
WHERE user_id = 123
  AND created_at >= '2024-01-01'
  AND created_at < '2024-02-01';
-- Scans only one partition (fast)

-- ❌ SLOW and locks table
DELETE FROM events WHERE created_at < '2022-01-01';
-- If this is 100GB data — database will freeze for hours
 
-- ✅ FAST: detach and drop partition
ALTER TABLE events DETACH PARTITION events_2021_12;  -- instant
DROP TABLE events_2021_12;  -- fast, doesn't block parent table

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
 
# pg_stat_statements settings
pg_stat_statements.max = 10000              # Maximum records (default 5000)
pg_stat_statements.track = all               # Track all queries (top/all)
pg_stat_statements.track_utility = on        # Including DDL (CREATE, DROP, etc.)
pg_stat_statements.track_planning = on       # PostgreSQL 13+: planning time
pg_stat_statements.save = on                 # Save between restarts

sudo systemctl restart postgresql
# Or via pg_ctl
pg_ctl restart -D /var/lib/postgresql/data

-- Connect to database
\c mydb
 
-- Create extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- Check
SELECT * FROM pg_stat_statements LIMIT 5;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND(total_exec_time::numeric, 2) as total_ms,
    ROUND((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) as percent_total,
    ROUND(stddev_exec_time::numeric, 2) as stddev_ms,
    ROUND(max_exec_time::numeric, 2) as max_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(total_exec_time::numeric, 2) as total_ms,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) as percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT
    substring(query, 1, 80) as short_query,
    calls,
    ROUND(mean_exec_time::numeric, 2) as avg_ms,
    ROUND(stddev_exec_time::numeric, 2) as stddev_ms,
    ROUND((stddev_exec_time / NULLIF(mean_exec_time, 0)) * 100, 2) as cv_percent
FROM pg_stat_statements
WHERE calls > 100  -- Enough calls for statistics
ORDER BY stddev_exec_time DESC
LIMIT 10;

-- Reset all pg_stat_statements data
SELECT pg_stat_statements_reset();
 
-- Reset for specific query (PostgreSQL 12+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

# Enable auto_explain
shared_preload_libraries = 'pg_stat_statements,auto_explain'
 
# auto_explain settings
auto_explain.log_min_duration = 1000         # Log queries > 1 second (in ms)
auto_explain.log_analyze = on                # Run ANALYZE (real numbers)
auto_explain.log_buffers = on                # Show shared buffers
auto_explain.log_timing = on                 # Show timing for each node
auto_explain.log_triggers = on               # Include triggers
auto_explain.log_verbose = off               # VERBOSE mode (lots of output)
auto_explain.log_nested_statements = on      # Log nested statements
auto_explain.log_format = text               # Format: text/json/xml/yaml

sudo systemctl restart postgresql

# Logs usually in /var/log/postgresql/
tail -f /var/log/postgresql/postgresql-15-main.log

# Production: log only very slow queries
auto_explain.log_min_duration = 5000         # 5 seconds
 
# Enable sample_rate to reduce load
auto_explain.sample_rate = 0.1               # Log 10% queries (PostgreSQL 13+)
 
# Disable timing to reduce overhead
auto_explain.log_timing = off                # Less overhead on production

# Slow query logging
log_min_duration_statement = 1000            # Log queries > 1 second
 
# Log details
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'none'                       # Don't log all queries
log_duration = off                           # Don't log duration of all queries
log_checkpoints = on                         # Log checkpoints
log_connections = on                         # Log connections
log_disconnections = on                      # Log disconnections
log_lock_waits = on                          # Log locks > deadlock_timeout

SELECT pg_reload_conf();

# Ubuntu/Debian
sudo apt install pgbadger
 
# macOS
brew install pgbadger
 
# Or via CPAN
cpan install pgBadger

# Analyze today's log
pgbadger /var/log/postgresql/postgresql-15-main.log
 
# Analyze multiple files
pgbadger /var/log/postgresql/postgresql-*.log -o report.html
 
# Incremental analysis (for large logs)
pgbadger --incremental --outdir /var/www/reports/ /var/log/postgresql/*.log

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0               -- Index never used
  AND indexrelname NOT LIKE '%pkey'  -- Except primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

-- Delete unused index
DROP INDEX CONCURRENTLY idx_unused;  -- CONCURRENTLY = without table lock

SELECT
    schemaname,
    tablename,
    heap_blks_read,
    heap_blks_hit,
    CASE
        WHEN heap_blks_hit + heap_blks_read = 0 THEN 0
        ELSE ROUND((heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read)) * 100, 2)
    END as cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY cache_hit_ratio ASC, heap_blks_read DESC
LIMIT 20;

SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::numeric / NULLIF(n_live_tup, 0)) * 100, 2) as dead_ratio,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000  -- More than 1000 dead rows
ORDER BY dead_ratio DESC, n_dead_tup DESC
LIMIT 20;

-- Manual VACUUM for specific table
VACUUM ANALYZE table_name;
 
-- Or aggressive VACUUM FULL (requires exclusive lock)
VACUUM FULL table_name;  -- CAUTION: locks table!

groups:
  - name: postgresql
    interval: 30s
    rules:
      # Critical: Disk space
      - alert: PostgreSQLDiskSpaceHigh
        expr: (node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql"} / node_filesystem_size_bytes) < 0.15
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL disk space < 15% ({{ $value }}%)"
 
      # Critical: Connection count
      - alert: PostgreSQLTooManyConnections
        expr: (pg_stat_database_numbackends / pg_settings_max_connections) > 0.8
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL connections > 80% of max"
 
      # Critical: Replication lag
      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 60
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL replication lag > 60s ({{ $value }}s)"
 
      # Warning: Cache hit ratio
      - alert: PostgreSQLLowCacheHitRatio
        expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.85
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL cache hit ratio < 85% ({{ $value }}%)"
 
      # Warning: Long running queries
      - alert: PostgreSQLLongRunningQueries
        expr: pg_stat_activity_max_tx_duration > 600
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL query running > 10 minutes"

docker run -p 8080:8080 -e DATABASE_URL=postgres://user:pass@host:5432/dbname ankane/pghero

# Docker
docker run -d \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://user:password@localhost:5432/mydb?sslmode=disable" \
  prometheuscommunity/postgres-exporter
 
# Or via systemd
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xvfz postgres_exporter-0.15.0.linux-amd64.tar.gz
sudo mv postgres_exporter /usr/local/bin/

scrape_configs:
  - job_name: "postgresql"
    static_configs:
      - targets: ["localhost:9187"]

-- Updated statistics
ANALYZE listings;
 
-- Recreated index just in case
REINDEX INDEX CONCURRENTLY idx_listings_status;

-- ❌ Learned this during incident
SELECT * FROM pg_stat_statements;
-- ERROR: extension "pg_stat_statements" is not available
 
-- ✅ Check BEFORE problem happens
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
-- If empty — install NOW

# ❌ DANGEROUS: Logs ALL queries > 10ms
auto_explain.log_min_duration = 10
 
# On high-load system this created:
# - 50GB logs in 2 hours
# - Disk full
# - Database crash

# ✅ SAFE: Log only really slow
auto_explain.log_min_duration = 5000         # 5 seconds for production
auto_explain.sample_rate = 0.1               # 10% queries (PostgreSQL 13+)

# PgBouncer config
application_name_add_host = 1  # Adds hostname to application_name

# Python (psycopg2)
conn = psycopg2.connect(
    "...",
    application_name="my-app-worker-1"  # Explicitly set application name
)

SELECT usename, application_name, query, calls
FROM pg_stat_activity;

# Monitor WAL directory
df -h /var/lib/postgresql/15/main/pg_wal
 
# Alert in Prometheus
node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql/15/main/pg_wal"}