Database — Where the Truth Lives

What is it? #

A database stores data in a structured way and answers questions about it quickly, safely and concurrently.

Files can store data too. What they cannot do well is let twenty processes write at once without corrupting each other, find one record among ten million instantly, or guarantee that a half-finished update leaves nothing behind.

Relational databases organise data into tables with defined columns and relationships between them. Constraints — not null, unique, foreign keys — push correctness into the database itself, so no application bug can create impossible data.

Transactions are the other core feature: a group of changes that either all apply or none do.

Think of it like this #

A bank ledger with strict rules, compared with a shared notebook.

The notebook works until two people write at once, or someone tears out a page, or a transfer is written on one line and the matching line is forgotten. The ledger enforces that both sides of a transfer are recorded together or neither is.

Simple example #

Transferring money between two accounts means two updates: one decrease, one increase. If the second fails, the first must be undone. A transaction is what makes that guaranteed.

Code #

-- Structure with constraints: the database enforces correctness
CREATE TABLE accounts (
    id         INTEGER PRIMARY KEY,
    owner      TEXT    NOT NULL,
    balance    NUMERIC NOT NULL CHECK (balance >= 0),
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE transfers (
    id        INTEGER PRIMARY KEY,
    from_id   INTEGER NOT NULL REFERENCES accounts(id),
    to_id     INTEGER NOT NULL REFERENCES accounts(id),
    amount    NUMERIC NOT NULL CHECK (amount > 0),
    made_at   TIMESTAMP NOT NULL DEFAULT now()
);

-- A transaction: both updates apply, or neither does
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
  INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 500);
COMMIT;
-- If any statement fails, ROLLBACK undoes everything in the block

# Connection pooling: reuse connections instead of opening one per request
from psycopg_pool import ConnectionPool

pool = ConnectionPool("postgresql://app:secret@localhost/shop", min_size=2, max_size=10)


def transfer(from_id: int, to_id: int, amount: float) -> None:
    with pool.connection() as conn:          # borrowed, returned automatically
        with conn.transaction():             # commits, or rolls back on error
            conn.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                (amount, from_id),
            )
            conn.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, to_id),
            )

ACID, in plain words

Atomic       all the changes in a transaction happen, or none do
Consistent   constraints are never violated, before or after
Isolated     concurrent transactions do not see each other's half-done work
Durable      once committed, it survives a crash or power loss

How it works #

The CHECK (balance >= 0) constraint means an overdraft cannot exist in the table, even if an application bug tries. Constraints are the last line of defence and the only one that applies to every client, including manual queries.

REFERENCES accounts(id) is a foreign key. It prevents a transfer row pointing at an account that does not exist, and it stops that account being deleted while transfers reference it.

BEGIN and COMMIT bracket the transaction. Between them, other transactions do not see the partial state. If anything fails, ROLLBACK returns the database to exactly how it was.

The Python example uses a connection pool. Opening a database connection is expensive — often tens of milliseconds — so a pool keeps a few open and lends them out. Without one, a busy application spends most of its time connecting.

max_size=10 matters more than it looks. Databases have a connection limit, and each connection consumes memory. Ten application servers with a pool of 100 each will exhaust a typical PostgreSQL instance.

with conn.transaction(): commits on a clean exit and rolls back on an exception, which is the safe default and removes a whole category of "forgot to commit" bugs.

Real-world use #

Almost every application has a database at its centre, and it is usually the hardest component to scale, because it is the one place where state lives.

That is why caches, read replicas and queues exist: each removes load from the database. It is also why the database is the most common cause of slow endpoints, and where performance work starts.

Transactions are used far beyond money. Creating an order with its line items, updating stock while recording a reservation, or registering a user while creating their default settings all need all-or-nothing behaviour.

Connection limits cause real outages. An autoscaling application that opens a new pool per instance can exhaust the database during a traffic spike — exactly when you need it most. Connection poolers such as PgBouncer exist for this reason.

Common mistakes #

• Skipping constraints and relying on application code to keep data valid.
• Opening a new connection per request instead of using a pool.
• Pool sizes that multiply across instances and exhaust the database limit.
• Holding a transaction open while calling an external API, blocking other writers.
• Running many small queries in a loop instead of one query.

Practice #

Create two tables with a foreign key and a check constraint. Write a transaction that inserts into both, then deliberately violate the constraint in the second statement and confirm the first is rolled back. Finally, time 100 operations with a fresh connection each versus a pooled connection.