Relational

Relational#

The default store for the operator’s structured records: case management, indicator catalogs, target inventories, evidence chain-of-custody, after-action logs. Relational databases store data as tables of typed rows, joined by keys, queried with SQL, and protected by ACID transactions, which is exactly the discipline an operator wants when the records have to survive audit, hand-off between teammates, and re-analysis months after the campaign closed.

The Major Engines#

The relational ecosystem clusters around five engines worth knowing. Postgres is the default modern recommendation; MySQL and MariaDB carry the LAMP heritage; SQLite is the embedded standard; SQL Server and Oracle hold the enterprise .NET and PL/SQL niches respectively.

  • PostgreSQL, general-purpose, extensible, the most-recommended default.

  • MySQL / MariaDB – high read throughput, broad ecosystem, common with PHP / Rails / WordPress.

  • SQLite, embedded; one file per database; the most-deployed database in the world.

  • Microsoft SQL Server common in enterprise / .NET stacks.

  • Oracle, enterprise heritage; PL/SQL.

PostgreSQL#

The default modern choice for new transactional workloads. Postgres ships standards-compliant SQL with rich extras (JSONB, arrays, window functions, partitioning, full-text search) plus a deep extension ecosystem covering geospatial, vector search, and time series.

Capabilities a typical product uses.

  • Strong defaults; standards-compliant SQL.

  • JSONB for semi-structured data (with GIN indexes).

  • Arrays, ranges, and composite types.

  • CTEs (recursive included), window functions, RETURNING, GENERATED columns, ON CONFLICT upsert.

  • Full-text search built in.

  • Partitioning, logical replication, foreign data wrappers.

  • Extensions: PostGIS (geospatial), pgvector (embeddings), TimescaleDB (time-series), pg_partman, pg_stat_statements.

Operational tools.

  • pg_dump / pg_restore, logical backups.

  • psql, the standard CLI.

  • pg_basebackup + WAL archiving, physical backups and PITR.

  • Connection pooling: pgbouncer is essentially required at scale.

MySQL / MariaDB#

The LAMP-era workhorse. InnoDB is the default storage engine for new work; replication tooling and ecosystem depth remain strong; MariaDB forked in 2009 and tracks Oracle MySQL with some divergence. Operators should know clustered indexes, GTID replication, and online schema-change tools.

  • Most often runs InnoDB (transactional) tables; older MyISAM is uncommon for new work.

  • Strong tooling and replication ecosystem; widely deployed in the LAMP era.

  • MariaDB forked in 2009 over Oracle stewardship of MySQL; large feature overlap.

Worth knowing.

  • InnoDB clustered indexes, the primary key is the physical row order.

  • GTID-based replication.

  • Online schema change tools (gh-ost, pt-online-schema-change) for big tables; native ALTER is often blocking.

SQLite#

Embedded, server-less, single-file. Excellent for.

  • Mobile and desktop applications.

  • Test databases and local development.

  • Edge / read-heavy services with simple deploy.

  • Small-to-medium production systems where one writer is fine.

Quirks.

  • Dynamic types, columns have type affinity but accept any type by default. Use STRICT mode for stricter behavior.

  • One writer at a time (WAL mode improves concurrency for readers).

  • Tooling: the sqlite3 CLI; many GUIs.

Distributed SQL#

The kind of database that scales horizontally while keeping ACID semantics. CockroachDB, Spanner, TiDB, and YugabyteDB all offer wire-protocol compatibility with familiar engines plus multi-region resilience. Adopt only when single-node truly can’t carry the load; often the answer is “not yet”.

When a single machine can’t keep up but ACID is non-negotiable.

  • CockroachDB, Postgres-wire compatible; serializable; multi-region.

  • Google Spanner, Google-cloud-only; TrueTime-backed strong consistency at planet scale.

  • TiDB, MySQL-wire compatible; Raft-based.

  • YugabyteDB, Postgres-wire compatible; hybrid Cloud / on-prem.

Adopt only when single-node Postgres / MySQL truly can’t carry the load. Most teams who think they need distributed SQL actually need vertical scaling, read replicas, or schema work.

Cloud-Managed Relational#

The cloud equivalents of running Postgres or MySQL on a VM. RDS / Aurora on AWS, Cloud SQL / AlloyDB / Spanner on GCP, and Database for PostgreSQL on Azure cover the major hosts; smaller specialists like Neon, Supabase, and Crunchy Data target Postgres-only workloads.

  • AWS: RDS (Postgres, MySQL, MariaDB, SQL Server, Oracle), Aurora.

  • GCP: Cloud SQL, AlloyDB (Postgres-compatible), Spanner.

  • Azure: Database for PostgreSQL / MySQL, SQL Database.

  • Smaller hosted Postgres: Neon, Supabase, Crunchy Data, ElephantSQL, Heroku Postgres.

Managed services trade some flexibility (extensions, kernel tuning) for backups, patching, HA, and read replicas as a checkbox.

Schema and Migrations#

  • Forward-only, online, in small steps.

  • Expand-and-contract for breaking changes (add column → backfill → switch readers → drop old).

  • Tools: Flyway, Liquibase, Alembic (Python), Goose, Atlas, sqlx-migrate (Rust), Prisma migrate, Drizzle migrate, Rails / Django built-ins.

  • Lock awareness, some ALTER operations block writes; check the database’s documentation, especially on large tables.

Choosing#

The decision tree for a new product. Postgres is the safe default; SQLite covers embedded, edge, and single-writer workloads; MySQL is the right call when team experience or tooling leans that way; distributed SQL waits until the data model and scale truly demand it.

For a new product.

  • Default to PostgreSQL unless a specific reason argues otherwise.

  • SQLite if it’s an embedded, edge, or local-first product, or if one-writer is fine and you want zero ops.

  • MySQL if the team has deep MySQL expertise or the ecosystem (specific frameworks / tools / hosts) leans that way.

  • Distributed SQL only when the data model and scale truly require it.

Operational Watchpoints#

The handful of operational details that separate a database that runs from a database that survives. Connection pooling, bounded transactions, online migrations, restore-tested backups, replica routing for reads, and slow-query analysis are the everyday discipline production demands.

  • Connection pooling is not optional in production.

  • Long transactions hold locks and bloat MVCC tables; bound them.

  • Migrations that lock tables hurt; use online tooling on big ones.

  • Backups must be tested, a backup that’s never been restored is a hope, not a backup.

  • Read replicas absorb reads; tolerate replication lag in the application.

  • Slow query logs and ``EXPLAIN`` are the most useful debugging tools available.

Contents