Why Files and Spreadsheets Break

The Friday-meeting story

Every team that has ever shared a Google Sheet of "real" data has lived this: Monday, two people paste rows simultaneously and one set vanishes. Tuesday, a column gets renamed and a downstream script breaks silently. Thursday, somebody types "N/A" where a number should live, and now SUM returns a string error. Friday, an emergency meeting decides to "clean up" the sheet — which is to say, hand-edit hundreds of rows.

Spreadsheets and CSVs fail at scale not because they are stupid but because they were never designed to enforce three things production data needs every second: concurrency control (who wins when two writers collide), data integrity (refuse rows that violate the schema), and efficient querying (find a row in 50ms among 50 million).

What the database does instead

PostgreSQL refuses bad rows at insert time. Two transactions cannot both win. Indexes turn linear scans into logarithmic lookups. The ACID guarantees that the rest of this quest unpacks are not academic — they are the difference between Friday meetings and "it just works."

When CSVs are still right

Files are great for data interchange (export, sharing, archival) and for config. They stop being great the moment more than one process writes to them or you need to ask non-trivial questions about them. The instinct to reach for a database is exactly: "more than one writer, or more than trivial queries."

Code

Schema rejects garbage at the door·sql

CREATE TABLE orders (
    id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer TEXT    NOT NULL,
    product  TEXT    NOT NULL,
    price    NUMERIC(10,2) NOT NULL CHECK (price > 0),
    placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Each of these fails at the boundary. None of them lands.
INSERT INTO orders (customer, product, price) VALUES ('Alice', 'Widget', NULL);
-- ERROR: null value in column "price" violates not-null constraint

INSERT INTO orders (customer, product, price) VALUES ('Bob', 'Gadget', -5);
-- ERROR: new row for relation "orders" violates check constraint

INSERT INTO orders (customer, product, price) VALUES ('Carol', 'Bolt', 'free');
-- ERROR: invalid input syntax for type numeric: "free"

Two writers, one truth·sql

-- Session A                         | -- Session B (concurrent)
BEGIN;                               | BEGIN;
UPDATE products SET stock = stock-1  | UPDATE products SET stock = stock-1
  WHERE id = 7;                      |   WHERE id = 7;
COMMIT;                              | COMMIT;
-- PostgreSQL serialises these so stock decrements correctly twice.
-- A spreadsheet would lose one of the writes silently.

Why Files and Spreadsheets Break

The Friday-meeting story

What the database does instead

When CSVs are still right

Code

External links

Exercise

Progress

Comments 0