Databases data core

Databases & Data Core

Shared model for the databases-data cluster. The engine spokes, the migration spoke, and the throughput spoke all depend on these decisions — keep them consistent here so no spoke contradicts another.

1. The store-selection model (the decision this cluster turns on)

Match the store to the access pattern before writing any schema. Picking the wrong store is the single most expensive mistake to undo.

Workload ──is──> { OLTP rows · OLAP scans · in-memory · ORM-managed } ──picks──> Store

Access pattern	Pick	Why	Spoke
Transactional reads/writes, single rows, strong consistency	PostgreSQL	RLS, rich types, GIN/BRIN, mature ecosystem	postgres-patterns
Transactional, MySQL/MariaDB shop or existing fleet	MySQL / MariaDB	replication, wide hosting; mind engine divergence	mysql-patterns
TypeScript app wanting typed models over PG/MySQL	Prisma ORM	typed client + migrations — atop a relational store, not instead of one	prisma-patterns
Aggregations/scans over huge append-only datasets	ClickHouse	column store, partition pruning, materialized views	clickhouse-io
Cache, locks, counters, queues, ephemeral state	Redis	in-memory, atomic ops, TTLs — not a system of record	redis-patterns

Rules: never run analytics off your primary OLTP store (replicate/ETL to ClickHouse); Redis is a cache/coordination layer, never the source of truth; an ORM is a convenience over a relational engine — you still own the index and pool decisions below.

2. Index & schema conventions (relational)

• Index by query shape: equality → B-tree; WHERE a = x AND b > y → composite (equality cols first); jsonb @>/full-text → GIN; time-series ranges → BRIN. → postgres-patterns, mysql-patterns
• Types: bigint IDs, text over varchar(n), timestamptz/UTC, numeric for money (never float).
• ID strategy: sequential bigint/auto-increment for internal joins; cuid/uuid for public/interop IDs — but random UUIDs as a PK fragment B-tree indexes on high-write tables. → prisma-patterns
• Index the FK side of every relation; an unindexed foreign key is the most common hidden table scan.

3. Connection & pooling

The pool, not the query, is what falls over first under load.

• One pool per process; size it to the DB's max_connections, not to request volume.
• Serverless (Lambda/Vercel/Workers): low per-instance limit (e.g. connection_limit=1) plus an external pooler (PgBouncer / RDS Proxy / Prisma Accelerate) — function fan-out otherwise exhausts connections. → prisma-patterns, postgres-patterns, mysql-patterns
• Set statement_timeout and idle_in_transaction_session_timeout; use SKIP LOCKED only for queue work, never for integrity-sensitive reads.

4. Migration model (expand / contract)

Every production schema change is a migration, applied through tooling, forward-only:

EXPAND (add nullable col / new table, concurrent index)
  → BACKFILL (separate data migration)
    → switch app reads/writes
      → CONTRACT (drop old column/table a later deploy)

• Never NOT NULL without a default; never an inline index on a large existing table — CREATE INDEX CONCURRENTLY.
• Schema (DDL) and data (DML) live in separate migrations; deployed migrations are immutable (fix forward). → database-migrations

5. Moving data correctly

• Move compute to where the data is; prefer warehouse-native scans/appends for large landed files.
• Make bulk writes idempotent (unique keys, manifests, replaceable staging) and batch small writes.
• Keep raw / derived / serving tables separately accountable; prove a job done with manifest + row-count + max-timestamp gates, not wall-clock. → data-throughput-accelerator

6. Version / tooling matrix

Store	Target	Watch for	Spoke
PostgreSQL	14+	RLS policy perf (wrap auth.uid() in SELECT), pg_stat_statements	postgres-patterns
MySQL / MariaDB	MySQL 8 / MariaDB 10.6+	VALUES() vs row-alias divergence in ON DUPLICATE KEY UPDATE	mysql-patterns
Prisma	5.x / 6.x	relationJoins row explosion; updateMany returns count; migrate dev resets DB	prisma-patterns
ClickHouse	recent LTS	MergeTree engine + ORDER BY/partition choice; avoid FINAL and small inserts	clickhouse-io
Redis	6+	atomicity needs Lua/MULTI; eviction policy + persistence (RDB/AOF)	redis-patterns
Migration tooling	Prisma · Drizzle · Kysely · Django · TypeORM · golang-migrate	forward-only in prod	database-migrations

7. Shared guardrails

• Match the store to the access pattern (§1); don't run OLAP on your OLTP primary; Redis is never the system of record.
• Index by query shape; index every FK; pick types deliberately (no float money, no varchar(255) reflex).
• Size pools to the DB, not the traffic; serverless = external pooler + low per-instance limit; always set timeouts.
• Every prod schema change is a forward-only migration via expand/contract; no NOT NULL without default; build indexes concurrently; DDL and DML separate; deployed migrations immutable.
• Validate inputs at the app boundary; never interpolate untrusted SQL.
• Bulk/ETL writes are idempotent and proven correct (manifests + counts) before completion.