Why you need a database and not a file

Two requests arrive at the same moment. One wants to add 20 to an account balance, the other wants to subtract 20. Both read the current value, 40. Both compute. Both write. The final value is either 60 or 20, depending on which CPU cycle won, and neither is correct.

If your data lives in a text file you own that race. You can't fix it in application code because your application is the thing with two copies of it running. This is the single best argument for a database: not that it stores data — a file stores data — but that it is a shared arbiter every process defers to, one that can serialize conflicting writes and refuse invalid ones.

What you're actually buying

Strip a database management system down and it has two jobs: keep bytes on disk, and expose create/read/update/delete over them efficiently. Everything else — constraints, transactions, roles, indexes — exists to make those two jobs survivable when more than one client is talking to it.

The bytes go on disk, not in RAM, and that's a deliberate trade. A laptop has 16 or 32 GB of RAM and 512 GB to 2 TB of disk. RAM is an order of magnitude faster and an order of magnitude scarcer. Databases pick capacity; caches like Redis pick speed. That's the whole distinction — Redis isn't a "faster database", it's the same idea sitting on the other side of the memory hierarchy, and it forgets things.

Text files fail on three fronts, and the failures are worth naming because each one maps to a database feature you'll use later:

• Parsing. Finding one customer means reading every line, splitting it, comparing fields — in your application language, at your application's speed.
• Structure. A text file will happily accept "banana" in the payment amount column. There's nothing to enforce a rule against.
• Concurrency. The race above.

Constraints are the point

The most common mistake I see is treating the schema as a storage declaration. It's not. It's a set of invariants the database will enforce for every writer, forever, including the intern's script and the migration you run at 2am.

CREATE TABLE tasks (
  id          uuid PRIMARY KEY,
  project_id  uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  priority    integer NOT NULL DEFAULT 1 CHECK (priority BETWEEN 1 AND 5),
  assigned_to uuid REFERENCES users(id) ON DELETE SET NULL
);

Read what that buys. NOT NULL REFERENCES projects(id) means an orphan task cannot exist — not "shouldn't", cannot. Pass a syntactically valid UUID that isn't a real project and the insert fails. CHECK (priority BETWEEN 1 AND 5) means priority 55 is rejected at the database, not at whichever of your seven code paths remembered to validate.

ON DELETE is where you declare what a deletion means. CASCADE deletes the children. SET NULL orphans them deliberately — right for assigned_to, since removing a user shouldn't delete their tasks. RESTRICT blocks the delete entirely, which is what you want for a project owner: you cannot delete a user who still owns projects.

Every constraint you don't write is a constraint you've decided to enforce in code you haven't finished reviewing yet.

One anti-pattern: VARCHAR(255). That number is a MySQL convention with no meaning in Postgres, and the Postgres docs recommend text. There's no performance difference. What VARCHAR(255) does buy you is a future migration when someone needs 300 characters, plus a number in your schema that looks intentional and isn't. Use text and enforce length in application code, where changing it costs nothing.

Migrations, up and down

Never change a schema by hand in a GUI. Every change is a numbered SQL file in version control with two halves: up applies it, down reverts it exactly. The tool keeps a table tracking which version the database is on, applies files in order, and stops.

The down half is the one people skip, and it's the one that matters at 3am when a deploy went bad and you need the schema back where it was. Reverse order, exactly: drop the indexes, then the tables, then the types.

One query, not N

Given users and a separate profiles table, the naive backend fetches all users, then loops and fetches each profile. That's 1 + N round trips for one endpoint. The database can do it in one:

SELECT u.*, to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
ORDER BY u.created_at DESC;

LEFT JOIN, not INNER JOIN, because a user who never filled in a profile should still appear. to_jsonb folds the joined row into a single JSON column, so the driver hands your serializer something already shaped like the response. And ORDER BY is not optional — a SELECT without one returns rows in whatever order the storage engine felt like, which is not the same order twice.

Any dynamic value in that query goes in a parameter slot, never string concatenation:

SELECT * FROM users WHERE id = $1;

The database treats $1 as a value, always, even if the value is the literal text DROP TABLE users. That's the entire mechanism behind SQL injection prevention: parameters are data, and data never gets promoted to code.

Indexes and their bill

Without an index, finding one row means a sequential scan — the engine visits every row on disk and compares. Fine at six rows, ruinous at six million. An index is a lookup structure holding the indexed value and the row's location, kept in order, so the engine jumps straight there. It can also be ordered ascending or descending, which is why an index on created_at DESC makes that ORDER BY above nearly free.

The bill: every insert and update must also update every index on that table. So the heuristic is narrow — index a column when it appears in a WHERE, a JOIN condition, or an ORDER BY, and that query runs often. Add it, measure, and drop it when the query stops mattering.

The same instinct applies to updated_at. You can set it by hand in every write path and eventually forget one, or you can write a trigger once and have the database do it on every UPDATE of every row, including the ones your code didn't make.