10. Schema and indexes

In a single-node database the schema is a logical statement and the physical consequences are contained. Here the schema is the physical layout: it decides which machine takes each write, how many machines a query touches, and how much network a join costs. This chapter is the practical version of chapter 3.

The primary key is a placement decision

-- three tables, three completely different clusters
CREATE TABLE a (id INT PRIMARY KEY DEFAULT unique_rowid(), …);
CREATE TABLE b (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), …);
CREATE TABLE c (tenant UUID, id UUID, PRIMARY KEY (tenant, id), …);

   a   roughly ordered ints spread by node id
   b   uniformly scattered — writes hit every range
   c   COLOCATED BY TENANT: one tenant's rows are
       contiguous, so a per-tenant query touches one
       or two ranges, and a per-tenant transaction is
       usually single-range (the fast path of ch 5)

Table c is the pattern worth internalising. Putting the access dimension first in the primary key — tenant, customer, device, account — buys you range locality for reads and the one-phase commit for writes. The cost is that one enormous tenant becomes a hotspot, which is the multi-tenant version of the problem chapter 3 described.

   CHOOSE A PRIMARY KEY BY ASKING

   1. what does 90% of my traffic filter on?
      → that column goes first
   2. is the leading column monotonic?
      → if yes, you have built a hotspot. UUID,
        unique_rowid(), or hash-shard it.
   3. do I query ranges of it (time windows)?
      → ordered storage helps you; hash sharding
        costs you. decide which you need more.

Secondary indexes

Every secondary index is a second copy of part of your table, written transactionally with every insert, update and delete — in a different range, usually on a different node.

   INSERT one row, three indexes
   ──► 4 keys written, in up to 4 ranges,
       each one a Raft proposal

So the usual advice (“index what you filter on”) comes with a sharper warning than in PostgreSQL: indexes here cost write latency and cross-range transactions, not just disk.

Covering indexes

CREATE INDEX ON orders (customer_id) STORING (status, total);

STORING puts extra columns in the index so the query never returns to the primary index. On a single node this saves a lookup; here it can save a network hop per row. It is the highest-value index feature in the product.

Partial indexes

CREATE INDEX ON orders (created_at) WHERE status = 'pending';

Index only the rows you actually query. A queue table where 0.1% of rows are pending gets an index a thousand times smaller, and — more importantly — a thousand times fewer index writes.

Expression and inverted indexes

CREATE INDEX ON users ((lower(email)));         -- expression
CREATE INVERTED INDEX ON events (payload);      -- JSONB
CREATE INDEX ON places USING GIST (geom);       -- spatial

Hash-sharded indexes, in practice

Chapter 3 introduced them; here is when to reach for one.

CREATE TABLE events (
  id   UUID DEFAULT gen_random_uuid(),
  ts   TIMESTAMPTZ NOT NULL,
  data JSONB,
  PRIMARY KEY (id)
);

-- the query you actually run is "recent events", so the
-- index you need is on ts — which is monotonic, and hot
CREATE INDEX ON events (ts) USING HASH WITH (bucket_count = 8);

   WITHOUT HASH                 WITH 8 BUCKETS

   writes ──► [ last range ]    writes ──► 8 ranges
              one node                     up to 8 nodes

   read "last hour"             read "last hour"
     one ordered scan             8 scans, merged

Pick bucket_count close to the number of nodes. More buckets spread writes no better and make every range scan wider.

Foreign keys

They work, they are enforced, and they are distributed:

CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  …
);

Every insert into orders reads customers — potentially on another node, inside your transaction. Two consequences:

   • FK checks add latency and cross-range work
   • ON DELETE CASCADE over many children can turn one
     small DELETE into an enormous transaction

The fix for the second is the same as everywhere else: delete in batches, in a loop, rather than in one statement.

Deleting data at scale

-- NOT this on a big table
DELETE FROM events WHERE ts < now() - INTERVAL '90 days';

-- this
DELETE FROM events WHERE ts < now() - INTERVAL '90 days' LIMIT 5000;
-- …repeated until no rows are affected

Large single-statement deletes build enormous transactions, hold intents for a long time, and leave a tombstone field behind (chapter 6). Better still, let the database do it:

ALTER TABLE events SET (ttl_expiration_expression = "ts + INTERVAL '90 days'");

Row-level TTL runs as a background job, in batches, without your application writing a deletion loop at all.

Online schema changes

This is one of the genuinely strong parts of the product. Schema changes run as background jobs, in stages, and never take the table offline:

ALTER TABLE users ADD COLUMN last_seen TIMESTAMPTZ;
CREATE INDEX ON users (last_seen);              -- backfills in the background

SHOW JOBS;                                      -- watch it
PAUSE JOB 89123…;  RESUME JOB 89123…;  CANCEL JOB 89123…;

The protocol underneath comes from Google’s F1 paper: a new index or column moves through intermediate states (DELETE_ONLY, WRITE_ONLY, then public), and because no two nodes can be more than one state apart, every node’s behaviour stays mutually compatible while the change rolls out.

   DELETE_ONLY   writers maintain deletes only
   WRITE_ONLY    writers maintain the index; readers
                 still ignore it; the BACKFILL runs
   PUBLIC        readers may use it

Compare MySQL’s chapter 12, where the question “will this lock my table?” has a long and unhappy answer. Here the honest caveats are different ones:

   • a backfill on a huge table is a LONG, heavy job.
     it is online, not free.
   • schema changes are transactional per statement,
     but you cannot mix arbitrary DDL and DML in one
     transaction the way you might expect
   • a paused or failed schema-change job must be dealt
     with; it does not simply vanish

Column families

By default all non-key columns live in one value. If a table has one column that changes constantly and others that never change, splitting them means a write touches less data:

CREATE TABLE devices (
  id         UUID PRIMARY KEY,
  name       STRING,
  owner      STRING,
  last_ping  TIMESTAMPTZ,
  FAMILY meta (name, owner),
  FAMILY hot (last_ping)
);

This is a real optimisation for the high-churn-column case and a needless complication otherwise. Reach for it when you have measured the write amplification, not before.

Unique constraints, and what they cost

A unique constraint is an index plus a check, and the check is a distributed read inside your transaction:

CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email STRING NOT NULL UNIQUE          -- an index, and a read per insert
);

   INSERT ──► write the row key
          ──► read the unique index to check for a
              conflict — in another range, maybe on
              another node, maybe in another region
          ──► write the index key

That is usually fine and occasionally decisive: in a REGIONAL BY ROW table (chapter 11) a globally unique constraint must be checked across every region, which turns a local insert into a global one. When a constraint only has to hold within a tenant or a region, say so:

-- unique per tenant, checked locally
ALTER TABLE users ADD CONSTRAINT users_tenant_email_key
  UNIQUE (tenant_id, email);

Sequences, and why to avoid them

CREATE SEQUENCE order_no;                 -- works, and is a hotspot

A sequence is a single row in a single range that every insert updates. It is the purest form of chapter 3’s problem: one key, one leaseholder, one machine, plus contention on top. CACHE reduces the round trips but not the fundamental shape.

   need                          use
   ─────                         ───
   a unique identifier           gen_random_uuid()
   a roughly-ordered id          unique_rowid()
   a human-facing invoice        generate it in a batch
   number with no gaps           job, not per insert

If a gapless, strictly increasing business number is a hard requirement, it is a business process with a queue behind it, not a column default. Treat it that way and the database stops being the bottleneck.

Changing a schema safely

Online schema changes make the mechanics easy; the discipline is still the same three-step dance that any zero-downtime deploy needs.

   ① ADD the new thing, nullable / unused
        ALTER TABLE … ADD COLUMN status STRING;
   ② DEPLOY code that writes BOTH old and new, and
      reads the old. backfill at your own pace.
        UPDATE … SET status = … WHERE status IS NULL
        LIMIT 5000;   -- in a loop
   ③ DEPLOY code that reads the new, then DROP the old
        ALTER TABLE … DROP COLUMN legacy_state;

-- constraints: add first, validate second, so the long
-- scan does not block the schema change
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

The reason to keep the dance even though the database does not force you to: your application is still deployed gradually, so two versions of it run at once. The database being fine with the change is not the same as every running instance of your code being fine with it.

A schema checklist

   □ primary key leads with the column 90% of queries
     filter on, and is NOT monotonic
   □ every hot query is covered by an index (STORING),
     or knowingly pays an index join
   □ no index exists that no query uses — each one
     costs every write
   □ time-ordered writes are hash-sharded, or proven
     not to be hot
   □ large deletes are batched, or replaced by row TTL
   □ FK cascades cannot produce a million-row txn
   □ disallow_full_table_scans is on in the app's
     session defaults

What to take from this chapter

The primary key is a physical placement decision. Leading it with the access dimension (tenant, customer, device) buys range locality and the single-range commit fast path.
A monotonic leading column is a hotspot, every time. UUID, unique_rowid(), or hash-shard it.
Every secondary index is written transactionally, in another range — indexes cost write latency here, not just disk.
STORING covering indexes are the highest-value index feature; partial indexes cut both size and write cost dramatically.
Hash-sharded indexes turn one hot ordered key into N write points, at the cost of wider range scans. Bucket count ≈ node count.
Foreign keys are enforced and distributed: checks add cross-range work, and cascades can build enormous transactions.
Batch large deletes or use row-level TTL instead of one giant statement.
Schema changes are online background jobs using the F1 multi-state protocol — visible in SHOW JOBS, pausable and cancellable. Online is not the same as free.
Column families help when one column churns and the rest do not, and complicate everything otherwise.

Next: multi-region — survival goals, table localities, and the latency arithmetic of a quorum that spans oceans.

Written by Sepuri Sai Krishna