khalil Drissi Cms

Tag: SQL

  • Database schema best practices

    Database schema best practices

    The schema is the part of a system that is hardest to change once it is full of data. You can rewrite a service in a weekend. Migrating a billion-row table without downtime is a project. So I spend real time on the schema up front, because the cost of getting it wrong only grows. These are the decisions I do not regret.

    Let the database enforce the rules

    Application code is not the place to guarantee data integrity, because there is always another path in: a migration script, a manual fix, a second service, a developer in a console. The database is the one chokepoint everything passes through, so that is where the rules belong. I use NOT NULL aggressively, foreign keys to enforce relationships, unique constraints to prevent duplicates, and check constraints for value ranges.

    CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  status      text   NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);

    Every constraint here is a bug that can never reach production. A status of “shippd” gets rejected at write time instead of breaking a report three weeks later.

    Normalize first, denormalize on evidence

    I start normalized: each fact lives in exactly one place. Duplicated data is duplicated truth, and the copies drift apart the moment someone updates one and forgets the other. Normalization keeps writes simple and correctness cheap.

    Denormalization is a performance optimization, and like any optimization I want a measurement before I do it. When a specific query is genuinely too slow and the profile points at joins, then I will cache a computed value or duplicate a column, knowing I am taking on the job of keeping the copies in sync. Doing it preemptively is how you get a schema full of fields nobody trusts.

    Choose keys and types deliberately

    Every table gets a synthetic primary key, usually a bigint identity or a UUID. I avoid natural keys like email addresses as primary keys, because the one thing you can promise about a natural key is that it will change, and changing a primary key that is referenced everywhere is misery. Use types that mean something:

    • Store money as integer cents, never floats. Floating point and currency are old enemies.
    • Always use timestamp-with-time-zone for time, and store UTC.
    • Reach for a native enum or a check constraint instead of free-text status fields.
    • Use the database’s real JSON type for genuinely unstructured data, not a text blob.

    Index for your reads, but know the cost

    An index makes reads fast and writes slightly slower, and it takes space. I index foreign keys, columns I filter on regularly, and columns I sort by. I do not index everything, because an unused index is pure overhead on every insert. The way to know is to look: most databases will tell you which indexes go untouched, and those are candidates for removal.

    Composite indexes are worth understanding because column order matters. An index on (customer_id, created_at) helps a query filtering by customer and sorting by date, but it does nothing for a query that only filters by date. The same observability mindset I described in logging and observability best practices applies here: measure the real query patterns before you guess.

    Treat migrations as code

    Every schema change goes through a migration file, checked into version control, reviewed like any other change. No manual ALTER statements run by hand in production, ever, because the next environment will not have them and you will spend a day chasing the difference. The same review discipline from code review best practices applies, with extra care, since a bad migration can lock a table or drop data.

    • Make migrations forward-only and additive where you can. Add a column, backfill, then later remove the old one in a separate step.
    • Avoid changes that take a long exclusive lock on a large, live table.
    • Test the migration against a copy of production-sized data before you run it for real.

    A schema you can evolve safely is worth more than a perfect schema you are afraid to touch. Build for change, because change is the only certainty.

  • Database design and normalization (and when to denormalize)

    Database design and normalization (and when to denormalize)

    I have inherited enough broken schemas to have strong opinions. The worst outages I have dealt with were almost never about a missing index or a slow disk. They were about a data model that lied. A column that meant three different things depending on the row. A “status” field that was secretly a free-text dumping ground. A foreign key that existed in someone’s head but never in the database. Good design is the cheapest insurance you will ever buy, and you buy it before you write a single query.

    This post is about how I actually approach normalization, what the normal forms buy you in practice, and the handful of situations where I deliberately denormalize. If you want the requirements-gathering side of this, I wrote that up separately in my data modeling methodology guide. This one is about the schema itself.

    What normalization actually protects you from

    People talk about normalization like it is an academic exercise. It is not. Every normal form exists to prevent a specific class of bug that will eventually wake you up at 3am. Strip away the formal language and the goal is simple. Store each fact exactly once, in the place where it belongs, so that there is no way for two copies of the same fact to disagree.

    When the same piece of data lives in two places, those two places will drift apart. Not maybe. They will. Someone updates the customer’s email in one table and forgets the other. A batch job touches half the rows. Now you have two truths and no way to know which one is correct. Normalization removes the second copy so the contradiction becomes impossible rather than merely unlikely.

    The normal forms, the way I think about them

    I do not walk around quoting the formal definitions, but I do keep their intent in my head when I sketch tables.

    • First normal form means no repeating groups and no multi-valued columns. If you find yourself naming columns phone1, phone2, phone3, you have a separate table waiting to be born. A comma-separated list in a varchar is the same crime wearing a disguise.
    • Second normal form means every non-key column depends on the whole primary key, not just part of it. This only bites you with composite keys, but when it bites it leaves a mark.
    • Third normal form means non-key columns depend on the key and nothing but the key. If a column depends on another non-key column, it belongs in its own table. The classic example is storing a city and its postal code together when one determines the other.

    In day to day work, if I hit third normal form I am usually in good shape. Boyce-Codd and the higher forms matter for specific overlapping-key situations, but third normal form catches the vast majority of real modeling mistakes I see in code review.

    A concrete example

    Say we are storing orders. The naive version crams everything into one wide table, repeating the customer name and email on every single order row. Here is the normalized version I would actually ship.

    -- Customers own their own facts, once
CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       CITEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Orders reference the customer, they do not copy it
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    status       TEXT NOT NULL
                 CHECK (status IN ('pending','paid','shipped','cancelled')),
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Line items are their own grain: one row per product per order
CREATE TABLE order_items (
    order_id     BIGINT NOT NULL REFERENCES orders(id),
    product_id   BIGINT NOT NULL REFERENCES products(id),
    quantity     INT NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

    Notice a few choices that are not strictly about normal forms but travel with good design. The status column has a CHECK constraint so the database itself enforces the allowed values. The unit_price lives on the line item, not on the product, because the price at the moment of sale is a different fact from the current price. That distinction is the kind of thing normalization forces you to think about. Is this the current value or the value as it was? They are not the same fact and they do not belong in the same column.

    Constraints are part of the design, not decoration

    A schema without constraints is a suggestion. I push as much invariant enforcement into the database as I reasonably can, because application code is the wrong place to guarantee data integrity. There will always be a second writer eventually. A migration script, an admin tool, a coworker poking around in a psql session. The database is the only layer all of them share.

    So I use NOT NULL aggressively, foreign keys without apology, UNIQUE constraints on anything that should be unique, and CHECK constraints for value ranges and enumerations. If you only take one habit from this post, make it this one. Most of the “mysterious bad data” I have debugged would have been impossible with a constraint that took thirty seconds to write. I go deeper on this in my notes on schema best practices.

    When I denormalize on purpose

    Now the heresy. I denormalize regularly, and I do not feel bad about it, because denormalization done with intent is an optimization, not a mistake. The trick is that you only denormalize after you understand the access pattern, never before. Premature denormalization is just a data model with extra bugs.

    Here are the cases where I reach for it.

    • Read-heavy aggregates. If a dashboard reads an order total a thousand times for every one time the order changes, computing that total on every read is wasteful. I will store a cached total column and keep it current with a trigger or in the same transaction as the write.
    • Reporting and analytics tables. Transactional normalization and analytical query patterns pull in opposite directions. A wide, denormalized table or a star schema can turn a brutal eight-way join into a single scan. I keep these separate from the source of truth and rebuild them from it.
    • Expensive joins on the hot path. Sometimes a join is genuinely the bottleneck even after indexing. Copying one frequently-read column to avoid a join can be worth it, as long as you own the update path.

    The non-negotiable rule with every one of these is that the normalized version remains the source of truth. The denormalized copy is derived, disposable, and rebuildable. The moment you have two independent sources of truth you are back to the original sin that normalization existed to prevent.

    How I keep denormalization safe

    If I store a derived value, I make the derivation explicit and I make it automatic. A cached column gets updated in the same transaction as its source, or by a trigger, never by a hopeful comment that says “remember to update this.” A materialized view gets a documented refresh schedule. A reporting table gets rebuilt by a job I can run on demand and verify against the source.

    I also write a check, even a slow one I run nightly, that compares the derived value against a fresh computation and screams if they disagree. Drift is the failure mode of denormalization, and the only defense is to detect it early. Once you can prove the copy matches the source, the performance win comes with a clear conscience. When the copies do start to disagree, it is almost always because a query plan changed or an index disappeared, which is exactly the territory I cover in my indexing deep dive.

    The order I do things in

    My default sequence has not changed in years. Normalize first, to third normal form, with real constraints. Get it correct and let it be correct. Then measure. Only when a specific, measured access pattern demands it do I introduce a denormalized copy, and only as a derived artifact with an enforced update path. Correctness first, then speed, and never speed bought with a lie in the data.

    That order matters because it is far easier to denormalize a clean model than to clean up a model that was muddy from birth. Start strict. Loosen deliberately. Your future self, paged at 3am, will thank you.

  • Database indexing and query optimization, a deep dive

    Database indexing and query optimization, a deep dive

    If you give me one slow query and an hour, the fix is an index more often than anything else. Indexes are the highest-leverage performance tool in a relational database, and they are also where I see the most confident, wrong intuition. People add an index to every column “just in case,” or they add a multi-column index in the wrong order and wonder why the planner ignores it. This is my mental model for how indexes work and how I decide which ones to build.

    None of this matters if the schema underneath is a mess, so if you have not already, the foundation comes from good design and normalization. Assume here that the model is sound and we are making it fast.

    What an index actually is

    An index is a separate, sorted data structure that lets the database find rows without scanning the whole table. The default in most relational databases is a B-tree, which keeps keys in sorted order and supports both equality lookups and range scans in logarithmic time. That sorted order is the whole point, and it explains almost everything an index can and cannot do.

    Because the keys are sorted, a B-tree index is great at three things. Finding a specific value, finding a range of values, and returning rows already in sorted order so the database can skip a separate sort step. It is useless for the opposite. A query that asks for everything except one value, or that wraps the column in a function the index does not know about, cannot use the sorted structure and falls back to a full scan.

    The cost nobody mentions

    Every index you add makes reads faster and writes slower. That is not a slogan, it is mechanical. When you insert, update, or delete a row, the database has to update every index that covers the affected columns. A table with eight indexes pays for eight little maintenance operations on every write. Indexes also take disk space and memory, and a bloated index that does not fit in cache stops being the speed-up you wanted.

    So I do not index defensively. I index in response to evidence. The right number of indexes is the smallest set that makes your actual queries fast, and finding that set means reading query plans rather than guessing.

    Reading the query plan

    The single most useful skill in database performance is reading EXPLAIN output. It tells you what the planner intends to do, and EXPLAIN ANALYZE tells you what actually happened with real timings. I run it constantly.

    -- See the plan and the real execution numbers
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, placed_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY placed_at DESC
LIMIT 20;

    The things I look for, in order. Is there a sequential scan on a big table where I expected an index scan? That is the loudest alarm. How far off is the planner’s estimated row count from the actual row count? A big gap means stale statistics and I run ANALYZE on the table. Is there an expensive sort that an index could satisfy directly? Is the same table being scanned more than once?

    Composite indexes and column order

    Multi-column indexes are where the most points are won and lost. The rule that took me too long to internalize is that column order is everything, and it follows from the sorted structure. An index on (customer_id, status, placed_at) is sorted by customer_id first, then status, then placed_at. That ordering means it can serve a query filtering on customer_id alone, or customer_id and status, or all three. It cannot efficiently serve a query that filters only on status, because status is not the leading column.

    The guideline I use is equality columns first, then the range or sort column last. For the query above, an index on (customer_id, status, placed_at) is close to ideal. The two equality predicates narrow the search, and because placed_at is the final column and already sorted, the database can satisfy the ORDER BY and the LIMIT without a separate sort. One index, no sort step, twenty rows.

    -- Equality columns first, then the column we sort on
CREATE INDEX idx_orders_customer_status_time
    ON orders (customer_id, status, placed_at DESC);

    Covering indexes and index-only scans

    There is a further trick. If an index contains every column a query needs, the database can answer the query from the index alone and never touch the table. That is an index-only scan, and it can be dramatically faster because it avoids the random reads back into the heap. I get there by including the extra columns the query returns.

    -- INCLUDE adds payload columns without changing the sort key
CREATE INDEX idx_orders_cover
    ON orders (customer_id, status)
    INCLUDE (placed_at, id);

    I do not do this everywhere, because wider indexes cost more to maintain and store. But for a hot, well-understood query that runs constantly, turning it into an index-only scan is one of the best returns on effort available.

    Indexes the planner will quietly refuse

    A surprising number of indexes go unused because of how the query is written, not how the index is built. The classic mistakes I look for first.

    • Functions on the indexed column. WHERE lower(email) = ‘x’ cannot use a plain index on email. Either store the normalized value, use a case-insensitive type, or build an expression index on lower(email).
    • Leading wildcards. LIKE ‘foo%’ can use a B-tree, but LIKE ‘%foo’ cannot, because the sorted order is useless when the start of the string is unknown.
    • Type mismatches. Comparing a text column to a number forces a cast that can disable the index. Match your types.
    • Low selectivity. An index on a column with two possible values rarely helps, because reading the index plus the heap is often slower than just scanning. The planner knows this and skips it, correctly.

    Partial indexes for skewed data

    One of my favorite tools for real workloads is the partial index, which only covers rows matching a condition. If 95 percent of your orders are completed and you almost always query the small slice that is still pending, indexing only the pending rows gives you a tiny, fast index that stays hot in memory.

    -- Index only the rows we actually search for
CREATE INDEX idx_orders_pending
    ON orders (placed_at)
    WHERE status = 'pending';

    This keeps the index small, which keeps it fast and cheap to maintain. For tables with heavy skew it is often the difference between an index that fits in cache and one that does not.

    How I actually work

    My loop is boring and it works. Find the slow query from real metrics, not from a hunch. Run EXPLAIN ANALYZE and read it carefully. Identify whether the problem is a missing index, a bad column order, stale statistics, or a query written in a way that defeats indexing. Make one change. Measure again. Repeat until the plan is clean.

    I resist the urge to add five indexes at once, because then I cannot tell which one helped and I have signed up for write overhead I may not need. One change, one measurement. And I revisit the index set periodically, because workloads drift and yesterday’s essential index can become today’s dead weight that only slows down writes. The same care that goes into the schema and the data model goes into keeping indexes honest. Measure, change one thing, measure again. That discipline beats cleverness every time.