PostgreSQL

Designing and operating reliable PostgreSQL databases.

Quick Start

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:17
psql -h localhost -U postgres
# CREATE DATABASE app;

When to Use

• Relational data with strong consistency needs
• Transactions and complex joins
• Full-text search, JSONB, and geospatial (PostGIS)
• Analytics via materialized views and window functions

Best Practices

Schema Design

• Use proper types (uuid, timestamptz, jsonb) — avoid text-only columns
• Add foreign keys and constraints to enforce integrity
• Prefer normalized core; denormalize only for known hot reads
• Name tables in plural or singular consistently; use snake_case

Indexing

• Index columns used in WHERE, JOIN, ORDER BY
• Prefer B-tree for equality/range; GIN for JSONB/arrays; BRIN for huge tables
• Create composite indexes matching query column order
• Remove unused indexes; analyze with `pg_stat_user_indexes`

Performance

• Use `EXPLAIN (ANALYZE, BUFFERS)` to read plans
• Avoid `SELECT *`; fetch only needed columns
• Batch inserts with `COPY` or multi-row VALUES
• Use connection pooling (PgBouncer) in production

Dependencies

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:17
# psql client:  psql -h localhost -U postgres

Examples

-- Schema with types, constraints, and indexes
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  role text NOT NULL DEFAULT 'member',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_created ON users (created_at DESC);

-- Query optimization with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users
WHERE created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- Window function for analytics
SELECT
  category,
  revenue,
  RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM monthly_sales;

-- JSONB querying with GIN index
CREATE INDEX idx_meta ON orders USING gin (meta);
SELECT id FROM orders WHERE meta @> '{"status": "paid"}';

Step-by-Step

1. Model entities and relationships; decide types for each column.

2. Write the DDL with constraints (PK, FK, UNIQUE, NOT NULL).

3. Add indexes for the query patterns you actually use.

4. Write migrations (Alembic/Prisma/Flyway) and version them.

5. Load representative data and run `EXPLAIN (ANALYZE)` on hot queries.

6. Tune indexes and queries; add pooling for production.

7. Set up backups (pg_dump/WAL archiving) and a restore drill.

8. Monitor slow queries via `pg_stat_statements` and set alerts.

Validation

1. Schema applies cleanly via migrations

2. Constraints reject invalid data

3. Hot queries run within target latency

4. `EXPLAIN` shows index usage on large tables

5. Backup and restore verified in staging

Troubleshooting

• Slow query despite index: check composite index column order and `= NULL`.
• Lock waits: long transactions hold locks — keep them short.
• High memory: raise `shared_buffers` and `effective_cache_size` carefully.