Postgresql Best Practices

CREATE TABLE orders (    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),    customer_id UUID NOT NULL REFERENCES customers(customer_id),    order_data JSONB NOT NULL DEFAULT '{}',    tags TEXT[] DEFAULT '{}',    total_amount NUMERIC(12, 2) NOT NULL,    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW());

CREATE TABLE products (    product_id SERIAL PRIMARY KEY,    sku VARCHAR(50) NOT NULL UNIQUE,    name TEXT NOT NULL,    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),    status VARCHAR(20) NOT NULL DEFAULT 'active'        CHECK (status IN ('active', 'inactive', 'discontinued')),    metadata JSONB DEFAULT '{}');

CREATE TABLE events (    event_id BIGSERIAL,    event_type VARCHAR(50) NOT NULL,    payload JSONB,    created_at TIMESTAMPTZ NOT NULL) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_q1 PARTITION OF events    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- B-tree index for common lookupsCREATE INDEX idx_orders_customer ON orders(customer_id);
-- GIN index for JSONB queriesCREATE INDEX idx_orders_data ON orders USING GIN (order_data);
-- Partial index for active records onlyCREATE INDEX idx_active_products ON products(name) WHERE status = 'active';
-- Covering index to avoid table lookupCREATE INDEX idx_orders_covering ON orders(customer_id)    INCLUDE (order_date, total_amount);

-- Check index usageSELECT schemaname, relname, indexrelname, idx_scan, idx_tup_readFROM pg_stat_user_indexesORDER BY idx_scan ASC;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)SELECT c.name, COUNT(o.order_id)FROM customers cLEFT JOIN orders o ON c.customer_id = o.customer_idWHERE c.created_at > '2024-01-01'GROUP BY c.customer_id, c.name;

WITH recent_orders AS MATERIALIZED (    SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent    FROM orders    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'    GROUP BY customer_id)SELECT c.name, ro.order_count, ro.total_spentFROM customers cJOIN recent_orders ro ON c.customer_id = ro.customer_idWHERE ro.total_spent > 1000;

SELECT    order_id,    customer_id,    total_amount,    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS order_rankFROM orders;

-- Efficient JSONB query with GIN indexSELECT * FROM productsWHERE metadata @> '{"category": "electronics"}';
-- Extract specific fieldsSELECT    product_id,    metadata->>'brand' AS brand,    (metadata->>'rating')::numeric AS ratingFROM productsWHERE metadata ? 'rating';

-- Recommended session settingsSET statement_timeout = '30s';SET lock_timeout = '10s';SET idle_in_transaction_session_timeout = '60s';

-- Use advisory locks for application coordinationSELECT pg_advisory_lock(hashtext('resource_name'));-- Do workSELECT pg_advisory_unlock(hashtext('resource_name'));
-- Check for blocking queriesSELECT blocked_locks.pid AS blocked_pid,       blocking_locks.pid AS blocking_pid,       blocked_activity.query AS blocked_queryFROM pg_catalog.pg_locks blocked_locksJOIN pg_catalog.pg_locks blocking_locks    ON blocking_locks.locktype = blocked_locks.locktype    AND blocking_locks.relation = blocked_locks.relation    AND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocked_activity    ON blocked_activity.pid = blocked_locks.pid;

-- Check table bloatSELECT schemaname, relname,       n_live_tup, n_dead_tup,       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pctFROM pg_stat_user_tablesWHERE n_dead_tup > 1000ORDER BY n_dead_tup DESC;

-- Enable row-level securityALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY documents_tenant_policy ON documents    FOR ALL    USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Grant minimal privilegesGRANT SELECT, INSERT, UPDATE ON orders TO app_user;GRANT USAGE ON SEQUENCE orders_order_id_seq TO app_user;

-- Enable pg_stat_statementsCREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find slow queriesSELECT query, calls, mean_exec_time, total_exec_timeFROM pg_stat_statementsORDER BY mean_exec_time DESCLIMIT 10;