Performance

The column is indexed and the query still scans

Difficulty 4/5Subscribers

An app looks people up by email at every sign-in.

Email is case insensitive as far as users are concerned: someone who registered as Ana@Example.com signs in as ana@example.com and expects to get in. The application therefore searches with WHERE lower(email) = lower($1).

What has to hold:

That lookup is answered using an index, not by reading the table.
Two accounts cannot exist whose emails differ only in case. Ana@Example.com and ana@example.com are the same person.
The address is still stored the way the user typed it. Support reads it back to them, and shouting it in lower case is not the same string.

The table is seeded with twenty thousand accounts after your schema is created.

The one thing that is fixed

1 relation

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

accounts
idemail

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…