What is a Database? The Foundation Every Application Is Built On
A database is organized data with a system for storing, retrieving, and modifying it reliably. Every application you have ever used depends on one. Here is how they actually work, what the different types do, and what separates a well-designed schema from one that will wreck you at scale.
Every application you have ever used has a database behind it. The social media post you wrote this morning, the bank balance you checked at lunch, the order confirmation sitting in your email, the playlist you are listening to right now. All of that is rows in tables, documents in collections, or key-value pairs in memory, stored in a database, retrieved and served by code running on someone's servers.
A database is not complicated to define. It is organized data with a system for storing, retrieving, and modifying it reliably. The complications arrive when you try to make that system fast, consistent, concurrent, and durable under real production load. That is where the decades of computer science research, the competing design philosophies, and the genuine tradeoffs between different database types come from.
Understanding databases is not optional for anyone who builds software. You can get away with not understanding the internals of your HTTP framework or the details of your cloud provider's networking layer. You cannot get away with not understanding your database. Slow queries, wrong indexes, improper transaction boundaries, and schema designs that seemed reasonable on day one but cannot support the queries you need on day one thousand: these problems end careers and destroy products.
This article covers what databases are, how the major types work, what the fundamental concepts mean in practice, and how to design schemas that do not make future-you miserable.
The Core Problem Databases Solve
Before databases existed as standalone systems, applications stored data in flat files. A billing program wrote customer records to a text file. Another program read that file to generate invoices. A third program updated balances in the same file.
This worked until two things happened simultaneously: more than one program tried to modify the file at the same time, and more than one person needed to query the data in different ways. Concurrent writes corrupted data. Scanning through an entire file to find one customer was slow and got slower as the file grew.
The relational model, proposed by Edgar Codd at IBM in 1970, solved both problems with a unified framework. Organize data into tables with rows and columns. Define relationships between tables through keys. Use a declarative query language (SQL) that describes what you want, not how to find it. Let the database engine optimize the retrieval. This design has been the dominant model for fifty-four years, which is an extraordinary run in a field that reinvents itself every decade.
The core problems every database system has to solve:
Persistence: Data survives process crashes and power failures. The database writes changes to durable storage (disk) and can recover a consistent state after an unexpected shutdown.
Concurrency: Multiple clients can read and write simultaneously without corrupting data or seeing inconsistent states.
Querying: You can retrieve specific subsets of data efficiently without reading every record in storage.
Integrity: The database enforces rules about what valid data looks like. A user record cannot exist without an email address. An order cannot reference a product that does not exist. A bank account balance cannot go below zero.
Durability: Once the database confirms that your write was committed, that data is not lost, even if the server immediately crashes.
Different database systems make different tradeoffs against these requirements. Understanding the tradeoffs is how you pick the right database for a given problem.
Relational Databases
The relational model organizes data into tables. A table has a name, a fixed set of columns (each with a name and data type), and any number of rows. Each row represents one entity. Each column represents one attribute of that entity.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(80) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(100) NOT NULL UNIQUE,
parent_id INTEGER REFERENCES categories(id) ON DELETE SET NULL
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
owner_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'active', 'archived')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')),
total_amount NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0),
shipping_address JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
UNIQUE (order_id, product_id)
);
The REFERENCES keyword creates a foreign key constraint. orders.user_id REFERENCES users(id) means every order must have a valid user ID. The database enforces this. You cannot insert an order with a user ID that does not exist in the users table. ON DELETE CASCADE means if you delete a user, their orders are automatically deleted. ON DELETE RESTRICT means you cannot delete a product that has order items referencing it.
The CHECK constraint enforces business rules at the database level. price > 0 means you cannot insert a product with a zero or negative price. The database rejects the insert before the data ever touches your application code. This is the second line of defense after your application validation, and it matters because applications can have bugs that bypass validation.
UNIQUE (order_id, product_id) in order_items creates a compound unique constraint. One product can appear at most once in any given order. If someone tries to add the same product to an order twice, the database rejects the second insert. You handle it in the application by updating the quantity instead.
SQL: The Query Language
SQL (Structured Query Language) is the interface to relational databases. It has been standardized since 1986 and every major relational database implements it, with vendor-specific extensions on top.
SELECT
u.username,
COUNT(DISTINCT o.id) AS order_count,
COUNT(DISTINCT oi.product_id) AS unique_products,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order_value,
MAX(o.created_at) AS last_order_at,
EXTRACT(DAY FROM NOW() - MAX(o.created_at)) AS days_since_last_order
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'delivered'
AND o.created_at > NOW() - INTERVAL '12 months'
GROUP BY u.id, u.username
HAVING SUM(o.total_amount) > 500
ORDER BY total_spent DESC
LIMIT 20;
This query finds the top 20 highest-spending customers over the last 12 months who have completed deliveries, showing their order history statistics. The JOIN clauses combine rows from multiple tables based on matching keys. The GROUP BY aggregates multiple rows into one row per user. The HAVING clause filters the groups (you cannot use WHERE to filter on aggregate functions). The whole thing runs in the database engine, which can optimize it far better than iterating through records in application code.
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count,
COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status = 'delivered'
AND created_at >= DATE_TRUNC('year', NOW())
GROUP BY DATE_TRUNC('month', created_at)
),
monthly_with_growth AS (
SELECT
month,
revenue,
order_count,
unique_customers,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_revenue
)
SELECT
TO_CHAR(month, 'YYYY-MM') AS period,
ROUND(revenue, 2) AS revenue,
order_count,
unique_customers,
ROUND(
100.0 * (revenue - prev_month_revenue) / NULLIF(prev_month_revenue, 0),
1
) AS growth_pct
FROM monthly_with_growth
ORDER BY month;
CTEs (Common Table Expressions, the WITH clause) break complex queries into named intermediate steps. The first CTE computes monthly revenue. The second adds month-over-month growth using LAG(), a window function that accesses the previous row's value. Window functions operate across a set of rows related to the current row without collapsing them into a single group the way GROUP BY does. NULLIF(prev_month_revenue, 0) prevents division by zero, returning NULL instead of crashing.
ACID: The Guarantee That Makes Databases Trustworthy
ACID is the set of properties that define a reliable database transaction. If your database is ACID-compliant, certain categories of data corruption are impossible by design.
Atomicity: A transaction either completes entirely or not at all. If you transfer $100 from account A to account B, both the deduction from A and the addition to B happen, or neither happens. A crash between the two operations cannot leave one done and the other not.
Consistency: A transaction moves the database from one valid state to another valid state. All constraints, rules, and triggers must hold after every transaction. If a transaction would violate a constraint, the database rolls it back.
Isolation: Concurrent transactions do not interfere with each other. The exact behavior depends on the isolation level, but at the strongest level (serializable), transactions produce results identical to running them one at a time in some order.
Durability: Once a transaction commits, it stays committed. The database writes committed transactions to durable storage (the write-ahead log) before acknowledging the commit. A crash after the acknowledgment does not lose the data.
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
UPDATE products
SET stock = stock - 3,
updated_at = NOW()
WHERE id = 42
AND stock >= 3;
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 1001, 42, 3, price
FROM products
WHERE id = 42;
UPDATE orders
SET total_amount = (
SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = 1001
)
WHERE id = 1001;
COMMIT;
FOR UPDATE locks the selected row, preventing other transactions from modifying it until this transaction commits. Without the lock, two concurrent orders for the last 3 units of the same product could both succeed, resulting in stock going negative.
If any statement in the transaction fails, ROLLBACK (explicit or automatic on error) undoes every change since BEGIN. The order never gets created. The stock never gets decremented. The database returns to the state before the transaction started.
This is the atomicity guarantee. Your application code does not need to implement compensating logic to undo partial operations. The database handles it.
Indexes: The Single Biggest Performance Lever
An index is a separate data structure the database maintains to speed up queries. Without indexes, finding a row requires scanning every row in the table. With the right index, the database jumps directly to matching rows in logarithmic time.
CREATE INDEX idx_products_status_created ON products(status, created_at DESC);
CREATE INDEX idx_products_category_status ON products(category_id, status);
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_products_name_fts ON products
USING GIN(to_tsvector('english', name || ' ' || description));
CREATE INDEX idx_products_price_range ON products(price)
WHERE status = 'active';
The compound index (status, created_at DESC) covers the query pattern "give me active products ordered by newest first" without a full table scan. The column order in compound indexes matters: the leftmost columns filter first. (status, created_at) is efficient for queries filtering on status. It is also useful for queries filtering on both status and created_at. It does not help queries filtering only on created_at.
The GIN (Generalized Inverted Index) index on the full-text search vector enables fast full-text search. to_tsvector('english', name || ' ' || description) tokenizes the text, applies stemming (so "running" matches "run"), and removes stop words. Queries using to_tsquery against this index skip the full table scan.
The partial index WHERE status = 'active' only indexes rows where status is active. If 90% of your products are archived drafts and only 10% are active, a partial index is 10% the size of a full index and scans faster.
But indexes are not free. Every index adds overhead to writes. Inserting a row means updating every index on that table. Updating a column means updating every index that covers that column. A table with fifteen indexes on it writes slower than a table with three. Index strategically, guided by the queries your application actually runs, measured with EXPLAIN ANALYZE.
EXPLAIN ANALYZE
SELECT p.id, p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'active'
AND p.category_id = 5
ORDER BY p.created_at DESC
LIMIT 20;
EXPLAIN ANALYZE executes the query and shows the query plan with actual timing. You see whether the database used your index or fell back to a sequential scan, how many rows each step processed, and where the time went. A sequential scan on a table with 10 million rows when you expected an index scan means your index does not cover this query's access pattern.
NoSQL: When the Relational Model Does Not Fit
"NoSQL" is a terrible name. It originally meant "non-relational" but got reinterpreted as "not only SQL" after the backlash against discarding SQL entirely. The useful definition is: databases designed around data models other than the relational table model. Four major categories:
Document Databases
Document databases (MongoDB, CouchDB, Firestore) store data as JSON-like documents. Documents in the same collection do not need the same fields. A product document can have five variant fields; another can have none. This flexibility makes document databases natural for data with variable structure.
The tradeoff: joins across documents are expensive or unavailable. MongoDB's $lookup aggregation performs something like a join, but it cannot be indexed the same way relational joins can. Applications using document databases often denormalize data, embedding related documents inside parent documents instead of referencing them by ID.
Document databases suit product catalogs with varying attributes, user profiles with optional fields, and content management systems where articles have different metadata depending on their type.
Key-Value Stores
Key-value stores (Redis, DynamoDB in key-value mode, Memcached) store data as a mapping from keys to values. The access pattern is simple: get by key, set by key, delete by key. No querying by field values. No joins.
The benefit is speed. Redis keeps its entire dataset in memory. A Redis GET completes in under a millisecond. For caching database query results, session storage, rate limiting counters, and pub/sub messaging: Redis is the right tool.
Redis also supports richer data structures beyond simple strings: lists, sets, sorted sets, hashes, streams, and HyperLogLog sketches. This makes it practical for leaderboards (sorted sets), task queues (lists), and approximate unique counting (HyperLogLog) without a separate service for each.
Wide-Column Stores
Wide-column stores (Apache Cassandra, Google Bigtable, Amazon DynamoDB) organize data into rows with dynamic columns. They are designed for write-heavy workloads at massive scale, distributed across many nodes with no single point of failure. Cassandra has no master node. Every node accepts writes. Data is distributed across nodes by a partition key.
The access model is severely constrained compared to SQL. Cassandra queries must specify the partition key. You cannot query by a non-key column without a secondary index, and secondary indexes in Cassandra have limitations that matter in production. Schema design in Cassandra is query-driven: you design your tables around the specific queries you need to run, not around the logical relationships between entities.
Cassandra suits time-series data (IoT sensor readings, metrics), event logs, and applications that need to write millions of rows per second across geographically distributed nodes.
Graph Databases
Graph databases (Neo4j, Amazon Neptune) store data as nodes and edges in a graph structure. Traversing relationships is a first-class operation, not a join across tables. For data where the relationships between entities are as important as the entities themselves, graph databases are orders of magnitude faster than relational joins.
Fraud detection (find all accounts within three hops of a known fraud account), social networks (find all users within two degrees of connection to a given user), recommendation engines (find products bought by users similar to this user): these query patterns are expensive in SQL and natural in graph databases.
The CAP Theorem and Distributed Databases
When databases run across multiple nodes, the CAP theorem applies. You can guarantee two of three properties:
Consistency: Every read returns the most recent write or an error.
Availability: Every request receives a response (not necessarily the most recent data).
Partition Tolerance: The system continues operating even if network partitions prevent nodes from communicating.
Network partitions happen in real distributed systems. A data center loses connectivity to another for 30 seconds. A network switch fails. When that happens, you must choose: stay consistent and refuse reads that might be stale (sacrifice availability), or stay available and return potentially stale data (sacrifice consistency).
Traditional relational databases running on a single node sidestep CAP because there are no network partitions between nodes. They give you full ACID guarantees.
Distributed databases make explicit CAP tradeoffs. Cassandra prioritizes availability and partition tolerance (AP). During a network partition, every node keeps accepting writes. After the partition heals, the nodes reconcile conflicting writes using a "last write wins" strategy based on timestamps. You might read stale data during a partition. The data eventually becomes consistent.
PostgreSQL with streaming replication and synchronous commit prioritizes consistency (CP). The primary waits for at least one replica to confirm the write before acknowledging it to the client. If the replica is unreachable, writes fail. You never read stale data, but availability decreases during network issues.
Understanding where your database sits in the CAP tradeoff is not academic. It determines whether your application can ever read stale data and what happens to writes during a network partition.
Schema Design: The Decisions That Compound
Database schema design decisions made early in a project are extraordinarily expensive to reverse later. Adding a column to a table with 10 million rows is a table rewrite in some databases. Removing a column that application code still references breaks everything. Changing a column type on a busy table can lock writes for hours.
The normalized vs denormalized tradeoff shapes everything else. Normalization (splitting data into separate tables with foreign keys, no repeated data) keeps data consistent and makes writes fast. Denormalization (duplicating data across tables to avoid joins) makes reads fast at the cost of keeping duplicated data in sync.
First, Second, and Third Normal Form
First Normal Form (1NF): Every column contains atomic values (no lists or nested structures). Every row is uniquely identifiable.
Violation: tags VARCHAR(255) containing "javascript,typescript,react" in one row. You cannot query for all products tagged "javascript" without a LIKE '%javascript%' which is slow and imprecise. Correct: a separate product_tags table with one tag per row.
Second Normal Form (2NF): Every non-key column depends on the entire primary key, not just part of it.
Violation: a compound primary key of (order_id, product_id) in order_items, and a product_name column that depends only on product_id. Product name changes would require updating every order item, not just the product record. Correct: store product_name in the products table, join to get it.
Third Normal Form (3NF): Every non-key column depends on the primary key, not on other non-key columns.
Violation: city, state, and zip_code in the same table, where city and state depend on zip_code rather than on the record's primary key. Correct: a separate zip_codes table, or accept some denormalization if zip code lookups are rare.
CREATE TABLE product_tags (
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
tag VARCHAR(50) NOT NULL,
PRIMARY KEY (product_id, tag)
);
CREATE INDEX idx_product_tags_tag ON product_tags(tag);
SELECT p.id, p.name, p.price
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
WHERE pt.tag = 'javascript'
AND p.status = 'active'
ORDER BY p.created_at DESC;
The normalized version lets you query by tag with an index scan. It lets you count products per tag, find all tags on a product, and add or remove individual tags without rewriting the entire product row.
Choosing Between SQL and NoSQL
The framework: start with a relational database unless you have a specific reason not to. PostgreSQL handles JSON documents through its JSONB type, which covers most "variable structure" use cases without a separate database. PostgreSQL handles full-text search through its built-in tsvector support. PostgreSQL handles time-series data reasonably with the right indexes and partitioning. PostgreSQL is the sensible default.
Reach for a specialized database when:
- You need horizontal write scaling beyond what a single PostgreSQL primary can handle and sharding is too complex. Cassandra or DynamoDB.
- Your primary access pattern is key-based lookup at microsecond latency. Redis.
- You need to traverse deep relationship graphs as a first-class operation. Neo4j.
- Your data truly has highly variable structure that JSONB makes awkward. MongoDB.
- You are building a search feature that needs full-text relevance ranking, facets, and autocomplete. Elasticsearch.
Using multiple databases for different problems in the same system (polyglot persistence) is legitimate and common. Redis for caching and sessions. PostgreSQL for transactional data. Elasticsearch for search. Each does one thing well.
Query Patterns That Break at Scale
The N+1 query problem is the most common performance failure in applications that use ORMs and relational databases. You load a list of orders, then loop through them loading each order's items separately. Ten orders produce eleven queries. Ten thousand orders produce ten thousand and one.
SELECT id, user_id, total_amount, status, created_at
FROM orders
WHERE status = 'delivered'
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;
SELECT id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 1;
SELECT id, product_id, quantity, unit_price
FROM order_items
WHERE order_id = 2;
The fix is to load related data in bulk:
SELECT
o.id AS order_id,
o.total_amount,
o.status,
o.created_at,
oi.id AS item_id,
oi.product_id,
oi.quantity,
oi.unit_price,
p.name AS product_name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC, o.id;
One query. The JOIN retrieves orders and their items together. You assemble the nested structure in application code after receiving the flat rows. This scales from 100 orders to 100,000 orders without multiplying query count.
Pagination with large offsets is another common scaling failure. LIMIT 20 OFFSET 10000 requires the database to read and discard 10,000 rows before returning 20. As users page deeper, queries get slower.
Cursor-based pagination replaces offset-based:
SELECT id, name, price, created_at
FROM products
WHERE status = 'active'
AND (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
The (created_at, id) < ($1, $2) clause uses the last row's values from the previous page as the cursor. The database starts from that point in the index rather than scanning from the beginning. Page 500 is as fast as page 1. The tradeoff: you cannot jump to a specific page number. You can only go forward (or backward). For most UI patterns (infinite scroll, "load more"), cursor pagination is superior.
Transactions and Isolation Levels in Practice
The four SQL transaction isolation levels define how concurrent transactions see each other's changes. Most developers never touch the isolation level and accept the database default. Understanding what that default means prevents a class of concurrency bugs.
READ UNCOMMITTED: A transaction can read uncommitted changes from other transactions. Almost never used. Dirty reads (reading data that is later rolled back) are possible. PostgreSQL does not implement dirty reads even when READ UNCOMMITTED is specified.
READ COMMITTED: A transaction sees only committed data. A query started within the transaction sees the committed state at the moment each query runs, not the moment the transaction started. PostgreSQL default. Two reads of the same row within the same transaction can return different results if another transaction commits between them.
REPEATABLE READ: All reads within a transaction see the committed state as of when the transaction started. Re-reading the same row always returns the same result. MySQL default. Prevents non-repeatable reads but not phantom reads (new rows appearing in range queries) in some databases. PostgreSQL's REPEATABLE READ prevents phantom reads too.
SERIALIZABLE: The strongest isolation level. Transactions execute as if they ran one at a time in some serial order. Prevents all anomalies. Highest cost in terms of lock contention and performance.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE owner_id = 42;
UPDATE accounts SET balance = balance - 100 WHERE id = 101 AND owner_id = 42;
UPDATE accounts SET balance = balance + 100 WHERE id = 102 AND owner_id = 42;
COMMIT;
The REPEATABLE READ isolation here ensures that if another transaction modifies account balances between the SELECT and the UPDATEs, this transaction sees a consistent snapshot. The sum we computed reflects the same state the updates operate on. Without this guarantee, the sum could reflect different account states than the balances being modified.
Choosing the right isolation level requires understanding your specific consistency requirements. Defaulting to READ COMMITTED is correct for most operations. Reach for REPEATABLE READ or SERIALIZABLE when correctness requires that multiple reads within the same transaction see the same data.
Database Design Anti-Patterns
EAV (Entity-Attribute-Value) is the pattern of storing arbitrary attributes as rows in a key-value table instead of columns. It appears when developers want schema flexibility.
CREATE TABLE product_attributes (
product_id BIGINT NOT NULL,
attr_name VARCHAR(50) NOT NULL,
attr_value TEXT,
PRIMARY KEY (product_id, attr_name)
);
EAV defeats the relational model. You lose type safety (everything is TEXT). You cannot add constraints (a price attribute cannot have a CHECK constraint). Queries join and filter on attribute names as string values. Aggregating across attributes requires dynamic SQL or application-level assembly. The PostgreSQL JSONB type solves the flexibility problem properly: structured JSON with index support, stored in a typed column with full query capability.
Storing IDs as comma-separated strings is a normalized schema violation that appears when developers want to avoid a join table.
CREATE TABLE products (
tag_ids VARCHAR(255)
);
WHERE '5' = ANY(string_to_array(tag_ids, ',')) is the kind of query this forces. No index. Full table scan every time. Referential integrity impossible. Counting tags requires string parsing. This is the kind of technical debt that requires a table rewrite to fix.
Missing NOT NULL constraints let null values accumulate in columns that should never be null. Once nulls are in the data, every query that operates on those columns must handle the null case. WHERE column = value does not match null rows in SQL. WHERE column IS NULL OR column = value is required. This adds complexity throughout the codebase and is nearly impossible to fix retroactively at scale.
Add NOT NULL constraints to every column that should never be null. Use DEFAULT values to provide safe defaults. Let the database enforce data integrity instead of relying on application code that might have bugs.
Connection Pooling and Production Considerations
A database connection is expensive. Establishing a TCP connection, authenticating, and negotiating protocol settings takes 10-50ms. Opening a new connection for every query in a high-traffic application adds that overhead to every request.
Connection pooling maintains a pool of open connections that requests borrow and return. Your application starts with 10 connections open. Request A borrows connection 3, runs its queries, returns it. Request B borrows connection 3 (or another), runs its queries, returns it. The 10ms connection establishment happens once at startup for each pooled connection, not on every request.
PgBouncer is the standard connection pooler for PostgreSQL in production:
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
server_lifetime = 3600
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction means a server connection is held only for the duration of a transaction. Between transactions, the connection returns to the pool. This allows far more application connections than server connections: 1,000 client connections sharing 20 server connections, with most clients idle or between transactions at any given moment.
The right pool size is not "as large as possible." More connections mean more memory consumed on the database server, more lock contention, and more context switching. default_pool_size = 20 on a modern server (8-16 CPU cores) is a reasonable starting point. Measure query latency and wait times. Adjust from evidence.
Backup, Recovery, and the Things No One Talks About Until It Is Too Late
Databases fail. Servers crash. Storage fails. People run DELETE FROM users WHERE and forget the WHERE clause. The only thing standing between those events and catastrophe is backups and the practice of restoring them.
A backup you have never tested is not a backup. It is a file that might be a backup. The only way to know your backup works is to restore it to a separate server and verify the data. Do this regularly. Automate it.
Write-ahead logging (WAL) in PostgreSQL records every change before it is applied to data files. WAL archiving ships those logs to a remote location continuously. Point-in-time recovery (PITR) lets you restore the database to any point within the WAL archive window, not just the most recent backup. If you run a base backup nightly and archive WAL continuously, you can recover to any second within the retention window.
pg_basebackup -h localhost -U replication -D /backup/base \
--wal-method=stream --checkpoint=fast --progress
pg_dump myapp -Fc -f /backup/myapp_$(date +%Y%m%d_%H%M%S).dump
pg_restore -d myapp_restored /backup/myapp_20240115_020000.dump
pg_basebackup takes a physical backup of the entire database cluster. pg_dump takes a logical backup that is portable across PostgreSQL versions and can restore individual tables. Production deployments use both: physical backups for disaster recovery, logical backups for table-level restores and migrating data between environments.
The backup strategy that fails is the one where someone sets it up, it runs for six months without errors, and then when a restore is needed, the backup files are corrupt, the restore procedure is undocumented, and nobody on the current team has ever done a restore in production.
Write the runbook before you need it. Test the restore. Keep the backups off the same system as the database. These are the things that separate teams that recover from incidents from teams that lose data.