postgresql

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.