database-architecture

What this skill does

# PostgreSQL 18 Database Architecture

## Overview

PostgreSQL 18 introduces transformational changes: the AIO subsystem delivers 3x I/O performance, native UUIDv7 replaces UUID libraries, and temporal constraints enable bi-temporal data modeling. This skill ensures you leverage these capabilities correctly.

**Core principle:** Design for PostgreSQL 18's strengths. Don't port patterns from older versions or other databases.

**Announce at start:** "I'm applying database-architecture to ensure PostgreSQL 18 best practices."

## When This Skill Applies

This skill is MANDATORY when ANY of these patterns are touched:

| Pattern | Examples |
|---------|----------|
| `**/migrations/**` | migrations/001_create_tables.sql |
| `**/*schema*.sql` | db/schema.sql |
| `**/db/**/*.sql` | db/functions/calculate.sql |
| `**/*index*.sql` | db/indexes.sql |
| `**/models/**` | src/models/user.ts |
| `**/*entity*.ts` | src/entities/order.entity.ts |
| `**/*model*.py` | app/models/product.py |

## PostgreSQL 18 Features to Leverage

### 1. Asynchronous I/O (AIO) Subsystem

PostgreSQL 18's AIO subsystem delivers up to 3x I/O performance improvement. Design schemas to benefit:

```sql
-- Enable read_stream for sequential scans
-- PG18 automatically uses AIO for:
-- - Sequential scans
-- - COPY operations
-- - Vacuum operations
-- - Index builds

-- Design for larger, sequential access patterns
-- AIO benefits sequential operations more than random access
```

**Checklist:**
- [ ] Prefer sequential access patterns in hot paths
- [ ] Design tables to minimize random I/O
- [ ] Use partitioning to enable parallel sequential scans

### 2. Native UUIDv7 Support

PostgreSQL 18 includes native `uuidv7()` function. Use it instead of extensions:

```sql
-- DEPRECATED: Don't use extensions for UUIDs
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- SELECT uuid_generate_v4();

-- DEPRECATED: Don't use gen_random_uuid() for new tables
-- SELECT gen_random_uuid();

-- CORRECT: Use native UUIDv7 for new primary keys
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz DEFAULT now()
);

-- UUIDv7 benefits:
-- 1. Time-ordered: natural chronological sorting
-- 2. Index-friendly: sequential inserts, no page splits
-- 3. Distributed-safe: no coordination needed
-- 4. Sortable: first 48 bits are millisecond timestamp
```

**Migration pattern for existing tables:**

```sql
-- Add new UUIDv7 column alongside existing
ALTER TABLE legacy_table ADD COLUMN id_v7 uuid DEFAULT uuidv7();

-- Backfill with time-ordered UUIDs (preserves order)
UPDATE legacy_table SET id_v7 = uuidv7() WHERE id_v7 IS NULL;

-- For historical data, generate UUIDs that preserve timestamp order
-- Use application code to generate UUIDv7 from original created_at
```

**Checklist:**
- [ ] All new tables use `uuidv7()` for primary keys
- [ ] No new usage of `uuid-ossp` extension
- [ ] Migration plan for existing `uuid_generate_v4()` columns

### 3. Virtual Generated Columns

PostgreSQL 18 supports virtual (computed-on-read) generated columns:

```sql
-- STORED: Computed on write, stored on disk (PG12+)
ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

-- VIRTUAL: Computed on read, no storage (PG18+)
ALTER TABLE orders ADD COLUMN total_with_tax numeric
  GENERATED ALWAYS AS (subtotal * (1 + tax_rate)) VIRTUAL;

-- When to use VIRTUAL:
-- - Simple calculations
-- - Values that would bloat storage
-- - Infrequently accessed computed values
-- - Values dependent on runtime context

-- When to use STORED:
-- - Expensive computations
-- - Indexed columns (virtual columns cannot be indexed directly)
-- - Frequently accessed values
```

**Checklist:**
- [ ] Use VIRTUAL for simple, infrequently indexed calculations
- [ ] Use STORED for indexed computed columns
- [ ] Document why each generated column uses its storage type

### 4. Temporal Constraints (SQL:2011)

PostgreSQL 18 introduces temporal primary keys and foreign keys:

```sql
-- Temporal table with validity period
CREATE TABLE product_prices (
  product_id uuid REFERENCES products(id),
  price numeric NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_to timestamptz NOT NULL,

  -- Temporal primary key: unique product per time period
  PRIMARY KEY (product_id, valid_from, valid_to WITHOUT OVERLAPS)
);

-- Temporal foreign key: reference must be valid at point in time
CREATE TABLE order_items (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  order_id uuid REFERENCES orders(id),
  product_id uuid,
  ordered_at timestamptz NOT NULL,

  -- Ensures product_id references a valid price at ordered_at time
  FOREIGN KEY (product_id, PERIOD(ordered_at, ordered_at))
    REFERENCES product_prices (product_id, PERIOD(valid_from, valid_to))
);
```

**Bi-temporal pattern:**

```sql
-- Track both validity time AND transaction time
CREATE TABLE contracts (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id uuid REFERENCES customers(id),
  terms jsonb NOT NULL,

  -- Validity time: when the contract is effective
  valid_from timestamptz NOT NULL,
  valid_to timestamptz NOT NULL DEFAULT 'infinity',

  -- Transaction time: when we recorded this version
  recorded_at timestamptz NOT NULL DEFAULT now(),
  superseded_at timestamptz NOT NULL DEFAULT 'infinity',

  -- Ensure no overlapping validity periods per customer
  EXCLUDE USING gist (
    customer_id WITH =,
    tstzrange(valid_from, valid_to) WITH &&
  ) WHERE (superseded_at = 'infinity')
);
```

**Checklist:**
- [ ] Use temporal constraints for time-varying data
- [ ] Consider bi-temporal design for audit requirements
- [ ] Use WITHOUT OVERLAPS for validity periods

### 5. Skip Scan on B-tree Indexes

PostgreSQL 18 can skip-scan B-tree indexes, making composite indexes more versatile:

```sql
-- This index now supports queries on BOTH columns
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- PG17 and earlier: Only efficient for status queries
SELECT * FROM orders WHERE status = 'pending';

-- PG18: Also efficient for date-only queries (skip scan)
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- Skip scan jumps between status values, checking dates in each
```

**Index design implications:**

```sql
-- Multi-column indexes are now more valuable
-- Put high-cardinality column second for skip scan benefit
CREATE INDEX idx_events_type_user ON events(event_type, user_id);

-- Both of these are now efficient:
SELECT * FROM events WHERE event_type = 'login';
SELECT * FROM events WHERE user_id = 'abc-123';
```

**Checklist:**
- [ ] Review existing indexes for skip-scan opportunities
- [ ] Consider composite indexes that benefit multiple query patterns
- [ ] Put low-cardinality columns first for skip-scan benefit

## Schema Design Principles

### Table Design

```sql
-- Standard table template for PG18
CREATE TABLE entity_name (
  -- Primary key: Always UUIDv7
  id uuid PRIMARY KEY DEFAULT uuidv7(),

  -- Foreign keys: Reference with ON DELETE behavior
  parent_id uuid REFERENCES parent_table(id) ON DELETE CASCADE,

  -- Required fields: NOT NULL with sensible defaults
  status text NOT NULL DEFAULT 'pending',

  -- Timestamps: Always timestamptz, never timestamp
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  -- Soft delete: Use validity period, not boolean
  deleted_at timestamptz,  -- NULL = not deleted

  -- JSON data: Use jsonb, never json
  metadata jsonb NOT NULL DEFAULT '{}',

  -- Constraints: Named for clarity
  CONSTRAINT entity_name_status_check CHECK (status IN ('pending', 'active', 'completed'))
);

-- Standard indexes
CREATE INDEX idx_entity_name_parent_id ON entity_name(parent_id);
CREATE INDEX idx_entity_name_created_at ON entity_name(created_at);
CREATE INDEX idx_entity_name_status ON entity_name(status) WHERE deleted_at IS NULL;
```

### Naming Conventions

| Element | Convention | Example |
|---------|------------|---------|
| Ta