Review an index set for a multi-tenant table

DecisionAdvanced3 min read15 min hands-on#indexing#data-modeling

The idea in 30 seconds

Review indexes as a set, not one at a time. Look for indexes that are prefixes of others, columns in the wrong order, uniqueness that ignores the tenant, and foreign keys with no index on the referencing side. Each mistake either wastes writes on every insert or breaks something the application assumed.

Helps to know first: B-tree indexes, PostgreSQL index types

The situation

The billing service is multi-tenant: every row belongs to a tenant, and every query is scoped to one. An AI assistant proposed the indexes for invoices.

Review taskInvoice indexes

Pull request: “Indexes for the invoices feature.”

CREATE TABLE invoices (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  integer NOT NULL REFERENCES tenants,
  number     text NOT NULL,              -- e.g. 'INV-0001', numbered per tenant
  status     text NOT NULL,
  issued_on  date NOT NULL
);
CREATE TABLE invoice_lines (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id  bigint NOT NULL REFERENCES invoices ON DELETE CASCADE,
  amount      numeric(12,2) NOT NULL
);

CREATE UNIQUE INDEX ON invoices (number);
CREATE INDEX ON invoices (tenant_id);
CREATE INDEX ON invoices (issued_on, tenant_id);       -- "invoices for a tenant this month"
CREATE INDEX ON invoices (tenant_id, issued_on);       -- same, other order, just in case

Would you approve it? Write the review you would leave.

Your call

Model review

Request changes. One index breaks the product, one is missing, and two are wasted.

1. UNIQUE (number) blocks other tenants. Invoice numbers are per tenant, so the second tenant to issue INV-0001 gets a unique violation. Make uniqueness per tenant: UNIQUE (tenant_id, number). That index also serves “find invoice INV-0042 for this tenant”.

2. invoice_lines.invoice_id needs an index. Declaring a foreign key does not create one. Deleting an invoice cascades to its lines, and without an index each delete scans all of invoice_lines. In our test, deleting 50 customers whose orders referenced them took 3.6 seconds without the index and about 11 ms with it. Loading an invoice’s lines needs the same index.

3. (issued_on, tenant_id) is in the wrong order. For “this tenant, this month” the tenant is an equality and the date a range: tenant first. In our test, the date-first version of such an index read 962 pages where the right order read 14.

4. (tenant_id) is redundant. (tenant_id, issued_on) serves queries on tenant_id alone just as well: in our test 92 pages against 91. Every extra index costs a write on every insert.

What I would ask for:

CREATE UNIQUE INDEX ON invoices (tenant_id, number);
CREATE INDEX ON invoices (tenant_id, issued_on);
CREATE INDEX ON invoice_lines (invoice_id);

Compare it with yours. Did your review catch the main problems?

How to review a set of indexes

Start from the queries. List each query the table serves and the columns it filters and sorts on. Every index should map to at least one of them.

Put the tenant first in multi-tenant tables, both in indexes and in unique constraints. Global uniqueness is rarely what a multi-tenant product means.

Index every foreign key’s referencing columns unless the referenced rows are never deleted or updated and nothing queries by that column.

Remove prefixes. An index on (a) next to one on (a, b) is usually waste.

Prefer fewer, well-ordered multicolumn indexes over many overlapping ones.

Check yourself

Why is UNIQUE (number) a bug in a multi-tenant table where numbers restart for each tenant?
Show answer
It requires numbers to be unique across all tenants, so a second tenant cannot create an invoice with a number another tenant already used. UNIQUE (tenant_id, number) makes only the combination unique.
I could explain this without looking
Which index from the pull request would have made deleting invoices slow, and why?
Show answer
None of them: the slow part is the missing index on invoice_lines.invoice_id. Cascading a delete must find each invoice’s lines, which is a full scan of invoice_lines without that index.
I could explain this without looking
Why is (tenant_id, issued_on) better than (issued_on, tenant_id) for “a tenant’s invoices this month”?
Show answer
The tenant is an equality condition and the month a range. With the tenant first, that tenant’s invoices are together in the index in date order, so one short stretch is read.
I could explain this without looking

Sources

Where each claim on this page comes from.

PostgreSQL documentation: 11.3 Multicolumn Indexes

Primary· §11.3 Multicolumn Indexes

Equality columns first, then one range column, limit the part of the index scanned; multicolumn indexes should be used sparingly.

PostgreSQL documentation: 5.5 Constraints

Primary· §5.5.3 Unique Constraints, §5.5.5 Foreign Keys

A unique constraint over several columns requires only the combination to be unique; a foreign key does not create an index on the referencing columns.

The situation

How to review a set of indexes

Check yourself

Sources

Go further