sharkitect-solutions/database

Database Design and Management

File Index

| File | Load When | Do NOT Load | |------|-----------|-------------| | references/engine-comparison.md | Choosing or comparing database engines | Already decided on engine | | references/indexing-deep-dive.md | Designing indexes, diagnosing slow queries, index maintenance | Simple single-column lookups | | references/migration-patterns.md | Planning schema changes on live systems, zero-downtime deploys | Greenfield with no existing data |

Scope Boundary

IN SCOPE: Schema design, normalization decisions, index strategy, query optimization, engine selection, migration planning, connection management, partition strategy, performance diagnostics, data modeling, backup planning, N+1 detection

OUT OF SCOPE: Supabase-specific RLS/Edge Functions (use supabase-postgres-best-practices), ORM configuration unrelated to database design, application-level caching architecture, frontend state management, infrastructure provisioning (Terraform/Pulumi), database-as-a-service platform operations

---

Database Engine Selection Framework

Choose the engine based on workload characteristics, not popularity. See references/engine-comparison.md for the full decision matrix.

Quick Decision Thresholds:

| Criteria | SQLite | Postgres | MySQL | MongoDB | Redis | DynamoDB | |----------|--------|----------|-------|---------|-------|----------| | Concurrent writers | <100 | Thousands | Thousands | Thousands | Single-threaded* | Unlimited | | Data size ceiling | <1TB | Petabytes | Petabytes | Petabytes | RAM-bound | Unlimited | | Query complexity | Simple | Complex joins, CTEs, window functions | Moderate joins | Document lookups, aggregation pipeline | Key lookups, sorted sets | Single-table, key-condition | | Consistency model | Serializable (WAL mode: concurrent reads) | Full ACID, MVCC | ACID per-engine (InnoDB) | Tunable (single-doc atomic) | Single-threaded atomic | Eventually consistent default, strong optional | | Operational overhead | Zero (embedded) | Moderate | Moderate | Moderate-High | Low | Zero (managed) |

*Redis uses single-threaded command processing but handles thousands of concurrent connections via I/O multiplexing.

Selection Rules:

• Embedded/edge/mobile with no server: SQLite. Period.
• Relational data with complex queries, need correctness guarantees: Postgres.
• Read-heavy web apps with simple schemas, MySQL ecosystem tools needed: MySQL.
• Rapidly evolving schema, document-oriented data, no cross-document joins: MongoDB.
• Sub-millisecond key-value lookups, ephemeral data, pub/sub, rate limiting: Redis.
• Predictable latency at any scale, willing to model everything as single-table: DynamoDB.

When two engines seem equal, default to Postgres. Its extension ecosystem (PostGIS, pg_trgm, timescaledb, pgvector) covers more future requirements than any other engine.

---

Schema Design Principles

Normalization Decision Framework

Start at Third Normal Form (3NF). Denormalize only with measured evidence.

Stay normalized when: write-heavy workloads, data integrity is non-negotiable (financial/medical/legal), schema is still evolving, query patterns are diverse.

Denormalize when: a query joins 4+ tables at >50ms under load, the field is read 100x more than written, you have measured write amplification cost, and you will maintain the copy (triggers, CDC, or application logic).

Crossover: if a JOIN query executes fewer than 100 times/minute, denormalization rarely justifies the consistency maintenance cost.

Composite Key Design

Order composite primary keys by selectivity (most selective column first) unless the access pattern demands otherwise. For time-series data, lead with the partition dimension (tenant_id, device_id) to enable efficient range scans within a partition.

Enum Handling

| Approach | When to Use | |----------|-------------| | CHECK constraint | Fewer than 10 values, rarely change, no metadata needed | | Lookup table (FK) | Values change over time, need display labels, i18n, sort order, or soft-disable | | Application enum | Values are purely presentational and never queried by the database |

Never store enums as integers without a mapping table. "The Magic Number Trap" -- six months later nobody knows what status=7 means.

JSON/JSONB Columns

Use JSONB when: schema varies per row (user preferences, plugin config, form submissions), you query specific keys with GIN indexes (@> containment), or data is consumed as a blob by the application.

Do NOT use JSONB when: you JOIN on nested fields regularly, the "flexible" schema is actually stable (avoiding a migration, not solving a real problem), or you need referential integrity on nested values.

Temporal Data Patterns

SCD Type 2: Add valid_from, valid_to columns. Query WHERE valid_to IS NULL for current state. Best for dimension tables where history matters (pricing, customer segments).

Event Sourcing: Append-only event log, derive state via projection. Best for audit-critical domains (financial, compliance) needing point-in-time reconstruction. Higher read complexity -- adopt only when audit requirements justify it.

Temporal Tables (SQL:2011): Postgres and MariaDB support system-versioned temporal tables natively. Use when available and you need automatic history without application logic.

---

Indexing Strategy

See references/indexing-deep-dive.md for the complete decision tree, recipes, and maintenance procedures.

Index Type Selection (Postgres-centric, principles apply broadly):

| Index Type | Use When | Storage Overhead | |------------|----------|-----------------| | B-tree (default) | Equality, range, sorting, LIKE 'prefix%' | Moderate (20-30% of table) | | Hash | Equality-only lookups, no range/sort needed | Smaller than B-tree | | GIN | Full-text search, JSONB containment, array operations | 2-3x column size | | GiST | Geometric/spatial, range types, nearest-neighbor | Moderate | | BRIN | Large naturally-ordered tables (timestamps, auto-increment) | Tiny (1-5% of B-tree) |

Composite Index Column Ordering Rule: Place columns in this order: Equality conditions first, then Range conditions, then Sort columns. This is the "ERS" rule. A composite index on (status, created_at, name) serves WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY name optimally.

Partial Indexes Save Space:

CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

If only 5% of orders are pending, this index is 95% smaller than a full index. Use partial indexes on boolean flags, status columns, and soft-delete markers.

Covering Indexes Eliminate Table Lookups:

CREATE INDEX idx_users_email_name ON users(email) INCLUDE (first_name, last_name);

Postgres returns results directly from the index (Index Only Scan) without visiting the heap. Measure with EXPLAIN ANALYZE -- look for "Index Only Scan" in the plan.

Index Maintenance Overhead: Every index adds write amplification. Each INSERT updates every index on the table. Rule of thumb: more than 8 indexes on a write-heavy table is a smell. Profile before adding index number 6+.

---

Query Optimization

EXPLAIN ANALYZE Interpretation

Always run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) in a transaction you roll back.

Key signals:

• Seq Scan on large table: Not always bad. If returning >5-10% of rows, Seq Scan is faster than Index Scan (avoids random I/O). Add index only when selectivity <5%.
• Nested Loop with inner Seq Scan: N+1 at database level. Fix with Hash Join (add index) or restructure query.
• High actual loops: Repeated subplan execution. Materialize subquery or convert to JOIN.
• Buffers shared read >> shared hit: Data from disk, not cache. Increase shared_buffers or reduce working set.

N+1 Detection and Elimination

One query to fetch a list, then N queries for related data. Costs O(N) round trips.

Detection: If the same parameterized query executes >10 times within one request, you have N+1.

Elimination: Eager JOIN, batch loading (IN clause with collected IDs), LATERAL subquery, or DataLoader pattern (batch + cache at application layer).

Pagination Strategies

| Method | Best For | Breaks When | |--------|----------|-------------| | OFFSET/LIMIT | Small datasets, random page access | >1000 offset (scans and discards rows) | | Cursor (keyset) | Large datasets, sequential navigation | Need to jump to arbitrary page | | Seek method | Time-series, sorted feeds | Multi-column sort with NULLs |

Crossover: Switch from OFFSET to cursor at ~10,000 rows or when users never jump to page 500. OFFSET degrades linearly -- page 1000 scans and discards 999 pages.

---

Migration Architecture

See references/migration-patterns.md for the zero-downtime cookbook and rollback checklist.

Zero-Downtime Migration Principles

The Expand-Contract Pattern:

1. Expand: Add the new column/table/index alongside the old one. Application writes to both.
2. Migrate: Backfill existing data from old to new.
3. Contract: Remove the old column/table/index after all readers have switched.

Never combine expand and contract in a single deployment. The gap between them should be at least one full deployment cycle.

Dangerous Operations (require expand-contract):

• Renaming a column (old code still references old name)
• Changing a column type (implicit cast may fail or lose precision)
• Adding NOT NULL constraint to existing column (must backfill first)
• Dropping a column (old code may still SELECT it)

Safe Operations (single-step):

• Adding a nullable column with no default
• Adding an index with CREATE INDEX CONCURRENTLY (Postgres)
• Adding a new table
• Adding a CHECK constraint as NOT VALID then validating separately

Data Backfill for Large Tables

Never run UPDATE large_table SET new_col = computed_value on a table with 1M+ rows in a single transaction. It locks the table and bloats WAL.

Batched backfill pattern:

UPDATE target_table SET new_col = source_value
WHERE id IN (
  SELECT id FROM target_table
  WHERE new_col IS NULL
  LIMIT 5000
)

Run in a loop with 100ms sleep between batches. Monitor replication lag if running against a primary with replicas.

---

Connection Management

Pool Sizing Formula

The PostgreSQL wiki formula, validated by benchmarks: connections = (core_count * 2) + effective_spindle_count. For SSD-backed systems, effective spindle count is 1. A 4-core server: (4 * 2) + 1 = 9 connections.

Most applications over-provision connections. A pool of 20-30 connections handles thousands of concurrent application threads via queuing. PgBouncer or application-level pooling prevents the "500 connections from 50 application instances" problem.

Transaction Isolation Levels

| Level | Use Case | Anomalies Prevented | |-------|----------|-------------------| | READ COMMITTED (default) | General OLTP, most web apps | Dirty reads | | REPEATABLE READ | Reports that must see consistent snapshot | Dirty reads, non-repeatable reads | | SERIALIZABLE | Financial transactions, inventory, double-booking prevention | All anomalies (may abort on conflict) |

Default to READ COMMITTED. Use SERIALIZABLE only for operations where a race condition means monetary loss or data corruption. Serializable transactions may abort and must be retried -- your application code must handle SQLSTATE 40001.

Connection Lifecycle Best Practices

• Acquire connections as late as possible, release as early as possible
• Never hold a connection during external HTTP calls or user input waits
• Set idle_in_transaction_session_timeout to kill forgotten transactions (e.g., 30 seconds)
• Set statement_timeout to prevent runaway queries (e.g., 30 seconds for web requests, higher for batch jobs)

---

Named Anti-Patterns

"The God Table"

One table with 50+ columns covering unrelated domains. Symptoms: most columns NULL, schema changes touch unrelated features, row size exceeds 8KB triggering TOAST. Fix: decompose into focused tables joined by FK.

"The Stringly Typed"

Structured data stored as delimited strings: tags = "red,blue,green". Cannot query efficiently, no integrity, parsing bugs. Fix: proper columns, array types, or junction tables.

"The Index Carpet"

Indexing every column. Each index adds write overhead (page splits, WAL, vacuum). 15 indexes = 15x write amplification on inserts. Fix: index only WHERE/JOIN/ORDER BY columns from actual queries. Monitor pg_stat_user_indexes for unused indexes.

"The Soft Delete Swamp"

is_deleted BOOLEAN DEFAULT FALSE on every table. Every query needs WHERE is_deleted = FALSE (developers forget). Cascading soft-deletes are never consistent. Fix: hard deletes + audit log table, or SCD Type 2 if recovery needed.

"The UUID Everywhere"

UUID v4 PKs when auto-increment suffices. 16 bytes vs 4-8, random B-tree page splits destroy locality, 2-4x index bloat. Fix: BIGSERIAL for internal PKs, UUID as external-facing identifier in separate column. If UUID PKs required, use UUIDv7 (time-sorted) for locality.

"The Premature Optimization"

Materialized views, denormalized columns, and caching before measuring actual performance. A "slow" 50ms query gets a cache, creating consistency problems for nothing. Fix: EXPLAIN ANALYZE first, optimize the measured bottleneck.

---

Rationalization Table

| Temptation | Why It Feels Right | What Actually Happens | Do This Instead | |------------|-------------------|----------------------|-----------------| | "Let's use MongoDB because our schema might change" | Schema flexibility sounds productive | You move complexity from schema management to application-level validation; data inconsistencies emerge within months | Start with Postgres + JSONB columns for the genuinely flexible parts; keep the stable parts in typed columns | | "We need UUIDs for security -- auto-increment IDs are guessable" | Sequential IDs reveal record count and creation order | UUID v4 PKs fragment indexes, increase storage 2-4x, slow range queries. Sequential IDs in URLs are a presentation concern, not a database concern | Use BIGSERIAL PKs internally; add a UUID column for external APIs; never expose internal PKs in URLs | | "Add an index on every foreign key" | ORMs and guides recommend it | Foreign keys used only for INSERT/DELETE constraint checks (never in WHERE clauses) get indexes that consume space and slow writes for zero read benefit | Index FKs only if you query or JOIN on that column. Check pg_stat_user_indexes for scans = 0 | | "Denormalize now to avoid slow JOINs later" | JOINs have a bad reputation from the ORM era | You add write amplification and consistency bugs for a JOIN that takes 2ms. Denormalization is a measured response, not a preventive measure | Benchmark the normalized query first. Denormalize only when you have measured evidence of unacceptable latency under production load |

---

Red Flags

• Query returning >1000 rows to the application for filtering in code -- the database should filter, not the app. Push predicates down.
• No indexes on columns used in WHERE clauses of queries running >100 times per minute -- measure with pg_stat_statements or equivalent, then add targeted indexes.
• Connection count approaching max_connections -- you need connection pooling (PgBouncer), not a higher max_connections setting. Each connection costs ~10MB of RAM.
• Tables with >50 columns -- likely a God Table. Decompose before it becomes load-bearing.
• Migrations that combine DDL and large DML in one transaction -- the DDL acquires locks, the DML holds them for the duration. Split into separate deployments.
• No backup testing -- backups that have never been restored are not backups. Test restore quarterly at minimum.
• OFFSET pagination beyond page 100 -- performance is degrading linearly. Switch to cursor-based pagination.