# Database ## What it is A database is a body of data kept in a fixed structure, with rules about what may go into it, so that software can find, change and check its contents; the same word, often shortened to DB, is also used loosely for the program that does the keeping, whose exact name is a database management system or DBMS. NIST's glossary prints two definitions, and the broader one describes a repository of information or data "which may or may not be a traditional relational database system". The word does three jobs, and a conversation goes in circles when two people mean different ones. It names the data: the customers, orders and sensor readings an organisation keeps. It names the engine that keeps them: PostgreSQL, SQL Server, SQLite. And inside a server it names one container among several. PostgreSQL's tutorial sets out the levels: rows with named, typed columns make up tables, tables are grouped into databases, and the databases that one server instance manages together form a database cluster. SQL Server draws the same line between an instance and the databases it hosts. "The database is slow" can mean one table, one container or one whole machine. Most engines in daily use are relational: they store data as relations, the mathematical term for tables, and take questions in SQL. The PostgreSQL tutorial names other arrangements. The files and directories of a Unix-like system form a hierarchical database, and object-oriented databases came later. Redis calls itself a data structure server: in place of tables it offers strings, hashes, lists, sets and other native types, put to work for caching, queuing and event processing. Engines also differ in where they run. A client/server engine is a long-running process that many programs reach over a network. An embedded engine is a library inside one program, and SQLite keeps a whole database, tables, indexes, triggers and views included, in one ordinary file; the project says it competes with `fopen()` rather than with server databases. The difference shows in how a program connects: ```sh # client/server: connect to a service that is already running psql "host=db.internal dbname=shop user=app" # embedded: the program opens a file and the engine runs inside it sqlite3 shop.db ``` A third family is built for analysis. BigQuery stores each column separately, where many traditional systems keep a row's fields together on disk. Row storage suits looking up one record; column storage suits summing one field across millions of rows, because the query reads that column and leaves the rest alone. ## Why it matters Files hold data too. A database adds promises about that data for the moment many programs use it at once. The first promise is a consistent picture under concurrent use. PostgreSQL gives each statement a snapshot of the data as it stood at a moment, a design called multiversion concurrency control, or MVCC, so a read never waits for a write and a write never waits for a read. SQLite allows any number of readers at once and one writer at any instant, with the other writers waiting their turn. The two suit different numbers of writers, and that number does much of the choosing. The second promise is that data keeps its shape. A schema states which columns a table has and what type each holds; keys and constraints state which rows may exist. When the database enforces those rules, every program that writes to it obeys them. When the rules live only in application code, the first program that skips them leaves rows behind that every later query has to defend against. The third is a shared language, with accents. SQL is the standard ISO/IEC 9075, last revised as SQL:2023, yet PostgreSQL's conformance appendix notes that no current system claims full conformance to its Core. A schema and its queries belong to their engine, and moving them to another is a migration with its own testing. SQLite's own documentation turns the choice of engine into a short checklist: data across a network from the code that issues the SQL, many processes writing at the same instant, or content heading for the terabyte range each point to client/server, and otherwise SQLite is the simpler answer. Reporting at scale adds one more question: whether heavy reads belong on a separate analytical copy, filled by ETL, so that a long report never competes with the queries customers wait on. ## What it looks like in practice At the large end of the scale, a database engineer role at a rapidly growing tech company [created, managed and optimised 100+ databases across PostgreSQL, Microsoft SQL Server and Google BigQuery](/portfolio/architected-created-and-managed-100-postgresql-ms-sql-12/), most of them holding GIS data and time-series data such as sensor readings, logs and real-time metrics. The work produced normalised and denormalised schemas for PostgreSQL and SQL Server, with spatial indexing (PostGIS on the PostgreSQL side) and time-series partitioning, while BigQuery carried large time-series data in time-partitioned, clustered tables. Once those databases had indexing, materialised views and caching, GIS and time-series queries were answered in 40–60% less time, and automated monitoring reduced downtime by 50%. At the small end, one SQLite file [holds the facts behind a CV, a reference sheet, a portfolio and a cover letter](/portfolio/built-a-database-driven-cv-and-portfolio-generator-144/) in three languages. That database has twenty-three tables, and one command builds them in nineteen ordered steps, any of which can safely run again. Six output formats are rendered from the database by 41 Python modules totalling 10,580 lines. A document aimed at one kind of reader is "a query rather than a copy": a profile selects which achievements appear and in what order. In exchange, a document can now come from that database and nowhere else. Between those sizes sits the question every busy database meets, and [a note on optimising before sharding](/notes/optimise-before-you-shard/) answers it with an order of work: look, tighten, take the reads off the primary, partition the tables around the hot spot, and shard only when one machine is full of good work. Looking means asking PostgreSQL itself: `pg_stat_statements` for the statements that cost most in total, then `EXPLAIN (ANALYZE, BUFFERS)` for what the costliest one does. ## Where it goes wrong Most database trouble starts in a decision made early, when the data was small and the decision looked free. **Two copies of one fact drift apart.** The CV generator above exists because facts kept as four documents get corrected four times, until two of them disagree and nothing says which is current. The same happens to a table exported to a spreadsheet and edited there, or a second service with its own list of customers. Give each fact one store that owns it, and make every other copy say where it came from and when. The note's cache follows this rule strictly: it may tell a buyer an item is fully reserved, and every yes still comes from the database. **More machines arrive before anyone reads the plans.** Splitting data across servers spreads the slow statements over more hardware without making any of them cheaper, and it puts a lasting bill on everything else: as the note lists it, every migration, backup, restore and report runs once per shard, and a join across shards becomes application code. Read `pg_stat_statements` and the plans first, and keep what they show as the baseline. **A read lands on a copy that is behind.** A streaming replica takes reads off the primary and runs a little behind it. A user who has just written something and then reads it back from the replica may see the old value. Read a user's own recent writes from the primary. **Reports and customers share one engine.** A report that scans two years of history holds memory and disk that the shop's own queries need, and the people buying feel it. Move heavy analysis to a warehouse copy, and keep the working database for the many small reads and writes it serves. ## Further reading - [NIST glossary: database](https://csrc.nist.gov/glossary/term/database) — two short definitions from NIST publications, one of which allows for databases that are not relational. - [PostgreSQL tutorial: Concepts](https://www.postgresql.org/docs/current/tutorial-concepts.html) — tables, rows, columns, databases and the database cluster, in one page. - [PostgreSQL: Introduction to MVCC](https://www.postgresql.org/docs/current/mvcc-intro.html) — how snapshots let readers and writers work without blocking each other. - [PostgreSQL: SQL Conformance](https://www.postgresql.org/docs/current/features.html) — the parts and versions of ISO/IEC 9075 and what Core conformance means. - [Appropriate Uses For SQLite](https://www.sqlite.org/whentouse.html) — where an embedded engine fits, where a client/server one fits better, and a checklist for deciding. - [Overview of BigQuery storage](https://cloud.google.com/bigquery/docs/storage_overview) — row-oriented and column-oriented storage, and why analysis favours the second.