Engineering ReferenceEngineering Reference

Databases

On this page

A database is durable storage with a query language and a concurrency model. Pick the model that matches your access pattern — that decision dwarfs almost everything else.

Overview

Two big families dominate: relational (tables, SQL, schemas) and NoSQL (document, key-value, wide-column, graph). For industrial systems, add time-series historians.

Relational (SQL)

  • Tables, rows, columns; foreign keys enforce relationships.
  • Normalization (1NF–3NF, BCNF) reduces redundancy; denormalize for read perf.
  • Joins: inner, left/right outer, full outer, cross.
  • Set operations: UNION, INTERSECT, EXCEPT.
  • Window functions for analytics (ROW_NUMBER, RANK, LAG/LEAD).
  • CTEs (WITH ...) and recursive CTEs.

NoSQL

  • Document — MongoDB, Couchbase. JSON-like records.
  • Key-value — Redis, DynamoDB, Memcached.
  • Wide-column — Cassandra, HBase, ScyllaDB.
  • Graph — Neo4j, Neptune, ArangoDB.
  • Time-series — InfluxDB, TimescaleDB, OSIsoft PI, AVEVA Historian.
  • Search — Elasticsearch, OpenSearch, Meilisearch.

Indexes & Query Plans

  • B-tree index — default; range + equality.
  • Hash index — equality only.
  • Composite / covering indexes — match query predicates.
  • EXPLAIN / EXPLAIN ANALYZE — read the plan; chase the slow node.
  • Avoid SELECT *, leading wildcard LIKE, functions on indexed columns.

Transactions & ACID

  • Atomicity — all or nothing.
  • Consistency — constraints preserved.
  • Isolation — read uncommitted → read committed → repeatable read → serializable.
  • Durability — committed data survives crash.
  • CAP theorem — pick two of consistency, availability, partition tolerance under partition.

Common Products

  • OLTP: PostgreSQL, MySQL/MariaDB, SQL Server, Oracle.
  • OLAP / warehouse: Snowflake, BigQuery, Redshift, Databricks.
  • Embedded: SQLite, DuckDB.
  • Historians: AVEVA (OSIsoft) PI, GE Proficy, Ignition Tag History.
reference page