PostgreSQL Best Practices

Core Principles

• Leverage PostgreSQL's advanced features for robust data modeling

• Optimize queries using EXPLAIN ANALYZE and proper indexing strategies

• Use native PostgreSQL data types appropriately

• Implement proper connection pooling and resource management

• Follow PostgreSQL-specific security best practices

Schema Design

Data Types

• Use appropriate native types: UUID, JSONB, ARRAY, INET, CIDR

• Prefer TIMESTAMPTZ over TIMESTAMP for timezone-aware applications

• Use TEXT instead of VARCHAR when no length limit is needed

• Consider NUMERIC for precise decimal calculations (financial data)

• Use SERIAL or BIGSERIAL for auto-incrementing IDs, or UUID for distributed systems

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()

);

Table Design

• Always define primary keys

• Use foreign keys with appropriate ON DELETE/UPDATE actions

• Add NOT NULL constraints where appropriate

• Use CHECK constraints for data validation

• Consider partitioning for large tables

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 '{}'

);

Partitioning

• Use declarative partitioning for large tables (millions of rows)

• Choose appropriate partition strategy: RANGE, LIST, or HASH

• Create indexes on partitioned tables after partitioning

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');

Indexing Strategies

Index Types

• Use B-tree indexes (default) for equality and range queries

• Use GIN indexes for JSONB, arrays, and full-text search

• Use GiST indexes for geometric data and range types

• Use BRIN indexes for large, naturally ordered data

• Consider partial indexes for filtered queries

-- B-tree index for common lookups

CREATE INDEX idx_orders_customer ON orders(customer_id);

-- GIN index for JSONB queries

CREATE INDEX idx_orders_data ON orders USING GIN (order_data);

-- Partial index for active records only

CREATE INDEX idx_active_products ON products(name) WHERE status = 'active';

-- Covering index to avoid table lookup

CREATE INDEX idx_orders_covering ON orders(customer_id)

    INCLUDE (order_date, total_amount);

Index Maintenance

• Regularly run ANALYZE to update statistics

• Use REINDEX for bloated indexes

• Monitor index usage with pg_stat_user_indexes

• Remove unused indexes to reduce write overhead

-- Check index usage

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read

FROM pg_stat_user_indexes

ORDER BY idx_scan ASC;

Query Optimization

EXPLAIN ANALYZE

• Always analyze query plans for slow queries

• Look for sequential scans on large tables

• Identify missing indexes from query plans

• Watch for high row estimates vs actual rows

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)

SELECT c.name, COUNT(o.order_id)

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE c.created_at > '2024-01-01'

GROUP BY c.customer_id, c.name;

Common Table Expressions (CTEs)

• Use CTEs for complex query organization

• Note: CTEs are optimization fences in older PostgreSQL versions

• Use MATERIALIZED/NOT MATERIALIZED hints in PostgreSQL 12+

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_spent

FROM customers c

JOIN recent_orders ro ON c.customer_id = ro.customer_id

WHERE ro.total_spent > 1000;

Window Functions

• Use window functions for analytics queries

• Leverage PARTITION BY and ORDER BY for complex calculations

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_rank

FROM orders;

JSONB Best Practices

• Use JSONB over JSON for better performance and indexing

• Create GIN indexes for JSONB columns you query

• Use containment operators (@>, <@) for efficient queries

• Extract frequently queried fields to regular columns

-- Efficient JSONB query with GIN index

SELECT * FROM products

WHERE metadata @> '{"category": "electronics"}';

-- Extract specific fields

SELECT

    product_id,

    metadata->>'brand' AS brand,

    (metadata->>'rating')::numeric AS rating

FROM products

WHERE metadata ? 'rating';

Connection Management

Connection Pooling

• Use PgBouncer or pgpool-II for connection pooling

• Set appropriate pool sizes based on workload

• Use transaction pooling mode for short-lived connections

Connection Settings

-- Recommended session settings

SET statement_timeout = '30s';

SET lock_timeout = '10s';

SET idle_in_transaction_session_timeout = '60s';

Transactions and Locking

• Use appropriate transaction isolation levels

• Keep transactions short to reduce lock contention

• Use advisory locks for application-level locking

• Monitor and resolve lock conflicts

-- Use advisory locks for application coordination

SELECT pg_advisory_lock(hashtext('resource_name'));

-- Do work

SELECT pg_advisory_unlock(hashtext('resource_name'));

-- Check for blocking queries

SELECT blocked_locks.pid AS blocked_pid,

       blocking_locks.pid AS blocking_pid,

       blocked_activity.query AS blocked_query

FROM pg_catalog.pg_locks blocked_locks

JOIN 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.pid

JOIN pg_catalog.pg_stat_activity blocked_activity

    ON blocked_activity.pid = blocked_locks.pid;

Maintenance

Vacuum and Analyze

• Enable autovacuum and tune for your workload

• Run manual VACUUM ANALYZE after bulk operations

• Monitor table bloat

-- Check table bloat

SELECT 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_pct

FROM pg_stat_user_tables

WHERE n_dead_tup > 1000

ORDER BY n_dead_tup DESC;

Backup Strategies

• Use pg_dump for logical backups

• Use pg_basebackup for physical backups

• Implement point-in-time recovery (PITR) with WAL archiving

• Test backup restoration regularly

Security

• Use SSL/TLS for connections

• Implement row-level security (RLS) for multi-tenant applications

• Use roles and GRANT/REVOKE for access control

• Audit sensitive operations with pgAudit extension

-- Enable row-level security

ALTER 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 privileges

GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

GRANT USAGE ON SEQUENCE orders_order_id_seq TO app_user;

Monitoring

• Monitor with pg_stat_statements extension

• Track slow queries and optimize regularly

• Set up alerts for replication lag, connection count, and disk usage

• Use pg_stat_activity to monitor active queries

-- Enable pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slow queries

SELECT query, calls, mean_exec_time, total_exec_time

FROM pg_stat_statements

ORDER BY mean_exec_time DESC

LIMIT 10;