name	postgresql-best-practices
description	Comprehensive PostgreSQL best practices for database design, schema management, data types, indexing, query optimization, security, and maintenance. Use when working with PostgreSQL databases for: (1) Designing schemas and tables, (2) Choosing appropriate data types, (3) Creating and optimizing indexes, (4) Writing efficient SQL queries, (5) Implementing security and permissions, (6) Setting up backups and maintenance, (7) PostgreSQL Extensions. See PostgreSQL version at bottom. Written according to the Skill Creator guide. Contributors: Stefan Keller
license	Public Domain

PostgreSQL Best Practices

Best practices for PostgreSQL, covering schema design, data types, indexing, queries, security, and maintenance.

Note: PostgreSQL 17 (September 2024) delivers significant performance improvements over previous versions, including incremental sorting, parallel GIN index builds, and better I/O efficiency.

1. Database Design and Schema

Primary Keys and Unique Constraints

Always define a primary key on every table. Use UNIQUE and NOT NULL constraints to enforce data integrity at the database level.

CREATE TABLE users (
    user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

Foreign Keys

Use foreign key constraints to maintain referential integrity between tables.

CREATE TABLE orders (
    order_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Normalization

Design schemas according to Third Normal Form to minimize redundancy. Balance normalization with query performance—avoid excessive joins.

Naming Conventions

Use lowercase, alphanumeric names for schemas, tables, and columns. Avoid uppercase, spaces, and reserved keywords (select, user, order).

-- GOOD
CREATE TABLE customer_orders (order_id BIGINT, customer_id BIGINT);

-- AVOID
CREATE TABLE "CustomerOrders" ("OrderID" BIGINT);

2. Data Types

Numeric and Date/Time Types

Use INTEGER/BIGINT for whole numbers
Use NUMERIC(p,s) for monetary values (precise decimals)
Use TIMESTAMP WITH TIME ZONE (timestamptz) for all timestamps—avoid "without time zone"

CREATE TABLE events (
    price NUMERIC(10, 2),
    event_timestamp TIMESTAMPTZ NOT NULL
);

String Types

Avoid CHAR(n) (pads with spaces). Use TEXT or VARCHAR without length limits—they have no performance penalty.

-- GOOD
CREATE TABLE articles (title TEXT, description VARCHAR);

-- AVOID
CREATE TABLE articles (title CHAR(200));

Boolean, Auto-Increment, and Money

Use BOOLEAN for true/false flags (not integers or text)
Use GENERATED BY DEFAULT AS IDENTITY for auto-incrementing IDs (not SERIAL)
PostgreSQL 17+: Identity columns support CYCLE option for wraparound behavior
Use NUMERIC for currency (not the deprecated MONEY type)

CREATE TABLE products (
    product_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    price NUMERIC(12, 2),
    is_active BOOLEAN DEFAULT TRUE
);

JSON and Binary Data

Use JSONB (not JSON) for JSON documents—allows indexing and efficient querying
PostgreSQL 17+: JSON_TABLE provides SQL-standard JSON processing for transforming JSON to relational format
Use BYTEA for binary data, but consider external storage for large files

CREATE TABLE user_preferences (
    settings JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX idx_settings_theme ON user_preferences USING GIN ((settings->'theme'));

-- PostgreSQL 17: JSON_TABLE for SQL-standard JSON processing
SELECT * FROM JSON_TABLE(
    '{"users": [{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]}'::jsonb,
    '$.users[*]' COLUMNS (
        name TEXT PATH '$.name',
        age INT PATH '$.age'
    )
);

Special Types

Familiarize yourself with advanced types PostgreSQL offers:

ENUM Types: For small, fixed value sets (e.g., days of the week). Note: Changing ENUMs is cumbersome—consider a lookup table with foreign keys for more dynamic value sets.
Range Types: int4range, tstzrange, etc. for representing value ranges, including overlap operators and GiST indexing support.
Network Types: INET for IP addresses, MACADDR for MAC addresses.
Geometric Types: POINT, LINE, CIRCLE for basic geometry. Use PostGIS extension for complex geospatial data.

-- ENUM for fixed values
CREATE TYPE weekday AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
CREATE TABLE schedules (day weekday);

-- Range types
CREATE TABLE bookings (
    room_id INT,
    period TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)  -- Prevent overlaps
);

-- Network types
CREATE TABLE servers (
    server_id INT PRIMARY KEY,
    ip_address INET NOT NULL
);

-- Geometric types (or use PostGIS for advanced geo)
CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    coordinates POINT
);

3. Indexes

Index Primary and Foreign Keys

PostgreSQL auto-indexes primary keys. Manually index foreign key columns—PostgreSQL doesn't do this automatically.

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Index Types

B-tree (default): Most queries, equality, ranges
GIN: Full-text search, JSONB (containment queries), arrays
GiST: Geometric data, range types, full-text search, KNN (nearest neighbor) queries
BRIN: Large tables with natural ordering (time-series)

-- B-tree for standard queries
CREATE INDEX idx_users_email ON users(email);

-- GIN for JSONB containment (@>, ?, ?&, ?|)
CREATE INDEX idx_data_json ON products USING GIN (attributes);

-- GiST for JSONB (also supports @>, supports KNN distance operators)
CREATE INDEX idx_data_gist ON products USING GiST (attributes jsonb_ops);

-- GiST for geometric types and range types
CREATE INDEX idx_locations_coords ON locations USING GiST (coordinates);
CREATE INDEX idx_bookings_period ON bookings USING GiST (period);

-- GiST for KNN (k-nearest neighbor) queries
-- Find 10 nearest points to a location
SELECT * FROM locations 
ORDER BY coordinates <-> point '(50,50)' 
LIMIT 10;

-- BRIN for logs
CREATE INDEX idx_logs_time ON logs USING BRIN (created_at);

Index type selection guide:

GIN vs GiST for JSONB: GIN is faster for containment searches but larger and slower to build. GiST supports KNN operators and is smaller but slower for containment queries.
GiST use cases: Range overlaps, geometric searches, nearest neighbor queries, PostGIS spatial data.

Composite and Partial Indexes

Create composite indexes for multi-column filters. Use partial indexes to reduce size.

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

-- Partial index
CREATE INDEX idx_active_products ON products(product_id) WHERE is_active = TRUE;

Avoid Over-Indexing

Each index speeds reads but slows writes. Use EXPLAIN ANALYZE to verify indexes improve performance.

Unique Indexes

Use UNIQUE indexes to enforce uniqueness and help the query planner optimize.

CREATE UNIQUE INDEX idx_users_email ON users(email);

4. Query Optimization

Avoid SELECT *

Always specify needed columns. Reduces I/O and network overhead.

-- AVOID: SELECT * FROM orders WHERE user_id = 123;
-- GOOD:
SELECT order_id, order_date, total_amount FROM orders WHERE user_id = 123;

Filter on Server Side

Use WHERE, LIMIT, and proper pagination (keyset for large offsets).

Use JOINs, Not Loops

Combine data with JOINs instead of multiple sequential queries (avoid N+1 problem).

SELECT o.order_id, u.username FROM orders o
JOIN users u ON o.user_id = u.user_id WHERE o.status = 'pending';

Prepared Statements

Always use parameterized queries to prevent SQL injection and improve performance.

# GOOD
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

# DANGEROUS
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

Transactions

Group related changes in transactions. Keep transactions short to avoid lock contention.

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

PostgreSQL 17+: Use the enhanced MERGE command with better RETURNING support for upsert operations.

-- MERGE with RETURNING (PostgreSQL 17+)
MERGE INTO products p
USING new_products n ON p.product_id = n.product_id
WHEN MATCHED THEN UPDATE SET price = n.price
WHEN NOT MATCHED THEN INSERT VALUES (n.product_id, n.name, n.price)
RETURNING *;

Analyze Query Plans

Use EXPLAIN (ANALYZE, BUFFERS) to identify slow operations and missing indexes.

Bulk Operations

Use batch inserts or COPY for large data imports. PostgreSQL 17+ improves performance with incremental sorting in hash/merge joins.

INSERT INTO products (name, price) VALUES ('A', 19.99), ('B', 29.99);
-- Or: COPY products FROM '/data.csv' CSV HEADER;

Handle NULLs

Use IS NULL / IS NOT NULL (not = NULL). Remember COUNT(column) excludes NULLs.

5. Security

Roles and Privileges

Create application roles with minimum privileges (principle of least privilege). Never use superuser for applications.

CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;

Authentication

Use SCRAM-SHA-256 (default in modern PostgreSQL). Never use trust in production. Configure pg_hba.conf to require passwords.

Encryption

Enable SSL/TLS for connections (ssl = on in postgresql.conf, sslmode=require in clients).

Network Security

Don't expose PostgreSQL to the internet. Use firewalls/VPNs. Avoid listen_addresses='*' unless necessary.

Input Validation

Always validate inputs and use prepared statements to prevent SQL injection.

Row-Level Security (Optional)

Use RLS for multi-tenant applications to restrict row access per role.

ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_isolation ON customer_data
    FOR ALL TO app_user
    USING (customer_id = current_setting('app.current_customer_id')::integer);

6. Backup and Maintenance

Backups

Set up automated backups. Use pg_dump for small databases, pg_basebackup + WAL archiving (or pgBackRest) for larger ones. Always test restores.

pg_dump -U postgres -d myapp -F c -f backup.dump
pg_restore -U postgres -d myapp_new backup.dump

Point-in-Time Recovery (PITR)

Archive WAL segments to enable recovery to any point in time. Tools: pgBackRest, Barman.

Autovacuum

Let autovacuum run automatically. Configure thresholds for high-write workloads. Don't disable it.

VACUUM FULL

Use sparingly (requires exclusive lock). Prefer pg_repack for extreme bloat.

Statistics

Run ANALYZE after bulk data loads to update planner statistics.

ANALYZE orders;

Monitoring

Use pg_stat_statements to track slow queries. Monitor CPU, memory, I/O, connections, locks. Log slow queries (log_min_duration_statement).

PostgreSQL 17+: Use pg_stat_io for granular I/O monitoring across different backend types.

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, mean_exec_time FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- PostgreSQL 17: Detailed I/O statistics
SELECT backend_type, object, context, reads, writes 
FROM pg_stat_io 
WHERE reads > 0 
ORDER BY reads DESC;

Updates

Apply minor updates promptly. Plan major upgrades before EOL. Test in staging. Use pg_upgrade or dump/restore.

Configuration Tuning

Adjust shared_buffers, work_mem, maintenance_work_mem, effective_cache_size for your hardware. Use tools like pgtune.

# Example for 16GB RAM server
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 16MB
maintenance_work_mem = 512MB

Connection Pooling

Use PgBouncer for high-concurrency applications to reduce connection overhead.

7. PostgreSQL Extensions

PostgreSQL's extension system adds powerful functionality. Enable with CREATE EXTENSION.

Essential Extensions

pg_stat_statements: Query performance tracking (requires shared_preload_libraries = 'pg_stat_statements' in postgresql.conf, then restart).

pgcrypto: Cryptographic functions—crypt() for bcrypt hashing, encryption functions.

uuid-ossp or pgcrypto: UUID generation via uuid_generate_v4().

PostGIS: Advanced geospatial capabilities with geometry/geography types and spatial functions.

CREATE EXTENSION postgis;
CREATE TABLE cities (
    name VARCHAR(100),
    location GEOGRAPHY(POINT, 4326)
);

-- Find cities within 100km
SELECT name FROM cities 
WHERE ST_DWithin(location, ST_MakePoint(8.5417, 47.3769)::geography, 100000);

pg_trgm: Fuzzy text search via trigram similarity.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name % 'postgres';  -- similarity search

citext: Case-insensitive text type for emails, usernames.

hstore: Key-value store (consider JSONB for new projects).

Best Practices

Install only needed extensions
Check compatibility before major upgrades
Use \dx in psql to list installed extensions
Some extensions need shared_preload_libraries (restart required)

8. References

Official Documentation

PostgreSQL Docs - Complete reference for current version

Community Resources

Don't Do This - PostgreSQL Wiki - Common mistakes and anti-patterns
Performance Optimization Wiki - Tuning guidelines
r/PostgreSQL - Community Q&A

Tools

pgBackRest - Backup tool with PITR support
PgTune - Configuration calculator
PgBouncer - Connection pooler

Version: 0.1 | Target: PostgreSQL 17 | Updated: January 2025

sfkeller/PostgreSQL-Best-Practices-SKILL_v1.md