PostgreSQL

Backend Fundamentals · Databases · 9 min read

1. What problem does this solve?

2. Mental model

Analogy – The Library

Imagine a library where every book is a row in a table. The library’s catalog (the schema) tells you what each book contains: title, author, ISBN, etc. Shelves (the indexes) let you find a book quickly by its ISBN. The librarian (the transaction manager) ensures that when you check out a book, the book’s status is updated atomically – you either get the book or you don’t.

Engineering model

• Tables – collections of rows with a fixed set of columns.
• Rows – individual records.
• Columns – typed attributes.
• Primary key – unique identifier for each row.
• Foreign key – enforces relationships between tables.
• Indexes – B‑tree structures that speed up lookups.
• Transactions – ACID guarantees via MVCC and WAL.
• Replication – read replicas for scaling reads.
• Partitioning – split large tables into smaller pieces.

3. Deep explanation

Internals

Important terminology

• Isolation level – READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• Deadlock – two transactions waiting on each other.
• Replication lag – delay between primary and replica.
• Partition – logical division of a table (range, list, hash).
• Foreign data wrapper (FDW) – access external data sources as tables.

Trade‑offs

Failure modes

• Deadlocks – require retry logic.
• Long‑running transactions – block other writers, cause bloat.
• Vacuum lag – leads to bloat, slower queries.
• Replication lag – stale reads on replicas.
• WAL corruption – catastrophic; requires recovery from backup.

Limitations

• Horizontal scaling – native sharding is limited; requires extensions (Citus) or application‑level sharding.
• Time‑series workloads – can be handled but may need specialized extensions (TimescaleDB).
• Unstructured data – JSONB is flexible but not a full NoSQL substitute.

Where it fits

┌───────────────┐
│  API Layer    │
│ (Next.js, FastAPI) │
└───────┬───────┘
        │
┌───────▼───────┐
│  Service Layer│
│ (Business logic) │
└───────┬───────┘
        │
┌───────▼───────┐
│  PostgreSQL   │
│ (Data layer)  │
└───────────────┘

It interacts with:

• ORMs (TypeORM, Prisma) – map objects to tables.
• Connection pools – pg.Pool.
• Caching – Redis for hot data, but PostgreSQL still source of truth.
• Replication – read replicas for scaling.
• Backup & restore – pg_dump, WAL archiving.

4. How it works

1. Client sends SQL via driver (pg).
2. Driver serializes the query and parameters, sends over TCP.
3. PostgreSQL receives → parses → plans → executes.
4. Planner chooses plan using statistics.
5. Executor runs plan, reading/writing pages.
6. WAL records changes before applying.
7. Transaction commits → WAL flushed → changes visible.
8. Result returned to client.

sequenceDiagram
    participant C as Client (Node.js)
    participant D as Driver (pg)
    participant P as PostgreSQL
    C->>D: sendQuery(sql, params)
    D->>P: parse & plan
    P->>P: execute plan
    P->>P: write WAL
    P->>P: commit
    P->>D: result set
    D->>C: return rows

5. Practical implementation

5.1 Setup

# Docker compose (postgres.yml)
version: '3.8'
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: appdb
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

docker compose -f postgres.yml up -d

5.2 Node.js example – user + profile transaction

// src/db.ts
import { Pool } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,          // connection pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// src/userService.ts
import { pool } from './db';

export async function createUserWithProfile(
  email: string,
  passwordHash: string,
  displayName: string
) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // 1️⃣ Insert user
    const insertUser = `
      INSERT INTO users (email, password_hash)
      VALUES ($1, $2)
      RETURNING id
    `;
    const { rows } = await client.query(insertUser, [email, passwordHash]);
    const userId = rows[0].id;

    // 2️⃣ Insert profile
    const insertProfile = `
      INSERT INTO profiles (user_id, display_name)
      VALUES ($1, $2)
    `;
    await client.query(insertProfile, [userId, displayName]);

    await client.query('COMMIT');
    return { userId };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err; // let caller handle
  } finally {
    client.release();
  }
}

Key points

• BEGIN/COMMIT/ROLLBACK ensures atomicity.
• Parameterized queries ($1, $2) prevent SQL injection.
• pool.connect() gives a dedicated connection; release() returns it to the pool.

5.3 Indexing

-- Unique index on email to enforce uniqueness and speed lookups
CREATE UNIQUE INDEX users_email_idx ON users(email);

5.4 Query example – join

const usersWithProfiles = await pool.query(`
  SELECT u.id, u.email, p.display_name
  FROM users u
  JOIN profiles p ON u.id = p.user_id
  WHERE u.email = $1
`, [email]);

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

Architecture decisions

• Single‑node vs sharded: For >10M rows and high write throughput, consider Citus or application‑level sharding.
• Read replicas: Use for analytics, reporting, or to offload API reads.
• Caching layer: Redis for hot keys; keep PostgreSQL as source of truth.
• Data model: Prefer normalized schema for consistency; denormalize only for performance hotspots.

Trade‑offs

• Consistency vs availability: In a multi‑region setup, you may sacrifice strict consistency for lower latency.
• Write amplification: More indexes = slower writes; balance based on workload.
• Operational cost: Cloud RDS vs self‑hosted; consider IOPS, backup retention, and scaling costs.

Bottlenecks

• Connection pool exhaustion – tune max_connections and use pgbouncer.
• Query planner inaccuracies – run ANALYZE after bulk loads.
• Vacuum lag – schedule autovacuum during low traffic or use VACUUM FREEZE.
• Replication lag – monitor and alert; consider read‑only replicas with pg_replication_origin.

Failure scenarios

• Deadlocks – detect via pg_locks; design transactions to acquire locks in consistent order.
• Replication failure – automatic failover with Patroni; test recovery.
• WAL corruption – maintain WAL archiving; use pg_basebackup for point‑in‑time recovery.

Monitoring

• pg_stat_activity – active queries.
• pg_stat_replication – replication status.
• pg_stat_user_tables – bloat, vacuum status.
• Export to Prometheus: pg_exporter.

Cost/performance

• Use SSDs for pg_wal.
• Scale shared_buffers to ~25% of RAM.
• Use pg_stat_statements to identify expensive queries and target them for indexing or rewriting.

When NOT to use

• Massive write throughput (>10k TPS) with no need for ACID – consider NoSQL or time‑series DB.
• Highly unstructured data – MongoDB or DynamoDB may be simpler.
• Real‑time analytics – use a dedicated OLAP engine (Snowflake, BigQuery) or TimescaleDB.

9. Interview questions

10. Hands‑on task

Goal: Build a small service that creates users and profiles in a single transaction, indexes the email, and measures query performance.

1. Setup

   • Spin up PostgreSQL with Docker (see 5.1).
   • Create tables users and profiles with appropriate constraints.

2. Implementation

   • Write createUserWithProfile (see 5.2).
   • Add a unique index on users.email.
   • Expose an HTTP endpoint (POST /users) using Express or FastAPI.

3. Benchmark

   • Write a script that inserts 100k users concurrently.
   • Measure time to insert and time to query a random user.
   • Experiment: remove the index, add a composite index, change isolation levels.

4. Observations

   • Record how query plans change.
   • Note the impact on CPU, I/O, and latency.

5. Deliverable

   • GitHub repo with Dockerfile, docker-compose.yml, TypeScript source, and a README explaining how to run the benchmark.

11. Quick revision

• PostgreSQL = relational DB with ACID guarantees.
• MVCC: readers see snapshots; writers create new tuples.
• WAL: ensures durability; required for crash recovery.
• Indexes: B‑tree (default), hash, GiST, GIN, BRIN – choose based on query.
• Transactions: BEGIN/COMMIT/ROLLBACK; isolation levels control visibility.
• Replication: streaming, logical, read replicas.
• Partitioning: range, list, hash; reduces table size per node.
• Vacuum: cleans dead tuples; autovacuum keeps tables lean.
• Connection pooling: pg.Pool or pgbouncer.
• Security: TLS, roles, least privilege, parameterized queries.
• Observability: pg_stat_statements, Prometheus exporter, logs.
• Common pitfalls: missing indexes, SELECT *, hardcoded secrets, no error handling.

12. References

• PostgreSQL Documentation – https://www.postgresql.org/docs/current/
• pg Node.js driver – https://node-postgres.com/
• PostgreSQL WAL – https://www.postgresql.org/docs/current/wal.html
• PostgreSQL MVCC – https://www.postgresql.org/docs/current/mvcc.html
• PostgreSQL Indexes – https://www.postgresql.org/docs/current/indexes.html
• PostgreSQL Replication – https://www.postgresql.org/docs/current/warm-standby.html
• pg_stat_statements – https://www.postgresql.org/docs/current/pgstatstatements.html
• pg_exporter – https://github.com/prometheus-community/postgres_exporter
• Citus – https://www.citusdata.com/
• TimescaleDB – https://www.timescale.com/