Performance

A number that is right and cheap at the same time

Difficulty 5/5Subscribers

A forum lists threads. Every row in the list shows how many comments the thread has, and the list is the busiest page in the product.

Counting the comments for each thread is correct and too slow: the comments table is large and the list is read constantly.

What has to hold:

Reading a thread's comment count must not read the comments table. The tests check the query plan, not just the number.
The count is always right. Adding a comment raises it, deleting one lowers it, and moving a comment from one thread to another moves the count too.
It never goes negative, and a thread with no comments reads zero rather than nothing.

The tests read the count through a view you provide, thread_counts, with thread_id and comment_count.

threads and comments already exist, and comments already has ten thousand rows in it. Whatever you add has to be right about those too.

The one thing that is fixed

3 relations

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

threads
idtitle
comments
idthread_idbody
thread_counts
thread_idcomment_count

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE threads (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL
);

INSERT INTO threads (title) VALUES ('Indexes'), ('Deployments'), ('Quiet thread');

CREATE TABLE comments (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  thread_id BIGINT NOT NULL REFERENCES threads (id) ON DELETE CASCADE,
  body      TEXT   NOT NULL
);

INSERT INTO comments (thread_id, body)
SELECT (g % 2) + 1, 'seeded ' || g FROM generate_series(1, 10000) g;

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…