An index serves a question, not a column
An activity feed stores events. There are a lot of them, and exactly two queries matter:
- A user's most recent events.
WHERE user_id = ? ORDER BY at DESC LIMIT 20. This runs on every page load. - Deduplication on write. Clients retry, so each event carries an idempotency key, and the same user must never end up with two events carrying the same key. A retry has to be rejected by the database, not by the application checking first.
The rules:
- Both queries must be answered using an index, with no sort and no scan of the whole table.
- The duplicate key for the same user is refused. Two different users may of course use the same key.
- An event belongs to a user that exists.
users already exists, and the table is seeded with twenty thousand events
after your schema is created.
The contract
2 relationsThe tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.
users
id
events
iduser_idatidempotency_key
The schema that already exists
This runs before your submission. Do not repeat it, extend it.
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
INSERT INTO users SELECT FROM generate_series(1, 500);Nothing here yet. Run the tests while signed in and every distinct schema you submit is kept, newest first, so an earlier design can be read next to the one you would write now.
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.
Checking your subscription…