Partitioning

It is not. PARTITION BY toDate(observed_at) looks identical but does a different job: in ClickHouse the ORDER BY key is the primary index, and partitions are mostly a data-management and TTL unit. Over-partitioning there is a known foot-gun — each partition multiplies parts and merge pressure, where in Postgres it multiplies planner work. Daily is right in both, for different reasons.

No. ClickHouse creates partitions implicitly on insert, so there is nothing to pre-create and nothing to fall through to. The DEFAULT partition, the pre-creation job, and the alert on its row count all exist purely because Postgres requires the target partition to exist before the insert.

A declarative TTL observed_at + INTERVAL 30 DAY DELETE on the table. The entire partitions.ts file disappears, along with the advisory lock, the DETACH/ATTACH dance, and the ACCESS EXCLUSIVE stall that comes with it.

No, and this is the one to volunteer before being asked. A monotonic global sequence is a single-writer construct; there is no IDENTITY across ClickHouse shards. Paging would move to (observed_at, tiebreaker), which changes the public API contract. It is the single design decision in the submission that does not port.

It returns 400, Unrecognized key q. The tail schema is a strictObject with only service and level, so it rejects a filter the search endpoint accepts. The prompt says the tail should honour the same filters, so this is a genuine gap — matchesFilters is a two-line predicate and adding a substring test on message is small. Time bounds are a fairer omission: from/to on a live tail are close to meaningless, since every row is by definition new.

Nothing, today. The limiter keys on the raw x-api-key header and runs before requireApiKey validates it, so rotating the header mints a fresh 1,000-per-minute bucket on every request. I verified it: RateLimit stays at r=999 with a rotating key and counts down correctly with a fixed one. The per-IP fallback was deliberate — it stops an anonymous flood from draining a real key's quota — but it protects the wrong direction. The fix is a composite ip+key, or throttling by IP until the key validates.

The range is optional — omit both and you get the last 24 hours. The cap is on the span, and it is a deliberate bound on worst-case cost: Postgres is serving ingest and recent reads here, and without a cap one request could scan the entire 30-day retention. Any historical window still works as long as it is 24 hours or narrower. If a product needed month-wide aggregates, the answer is rollup tables or a column store, not lifting the cap.

One scan and one snapshot, so the level counts and service counts cannot disagree with each other — that argument needs no benchmark. On I/O I would correct my own decision log: it claims two queries read the pages twice for half the I/O, but measured on this schema it is 438 buffers versus 517, roughly 15%, because logs_observed_at_level_idx covers the level aggregate with an index-only scan. The 2× claim holds only without that covering index.

Deliberate, and it is the one place the difference pays off. observed_at is the partition key, so a stats range prunes to a single child — one partition instead of twelve. It also makes the endpoint's time semantics match retention. Search filters on event_time because operators care when the producer says something happened; the cost is that a search time filter can never prune.

Disconnect is a policy choice: a client that cannot drain 1 MB is not going to catch up on a firehose, and dropping silently gives it a false view of the stream. Reconnecting gets it back to live immediately. The honest caveat is that the decision log lists backpressure as a to-do, so treat this as the shape of the answer rather than a claim that it was fully designed — and any real version needs a metric on how often it fires.

id comes from a sequence, and sequences allocate outside transaction control so inserts do not serialise on them. That makes id allocation order, while visibility is commit order. Txn A takes id 5, txn B takes 6, B commits first, the poller runs WHERE id > cursor, sees only 6, and advances past 5. When A commits, 5 is behind the cursor and no client ever receives it. It is reproducible on demand — npm run demo:race forces the interleaving.

In the tail, yes. It is a design limitation rather than a coding error — every line does what it intends; the flawed premise is that a sequence cursor tracks visibility. It is latent (needs concurrent ingest) and silent (no error, no metric). I shipped it documented because the correct fix is a substantial build and the endpoint is explicitly best-effort. What I would tighten is the wording: I called it acceptable under a single-writer assumption, but the pool is ten connections, so that assumption was already false. It held because my test traffic was serial.

You largely cannot from the data, which is the uncomfortable part. Sequence gaps are normal — a rollback burns its id permanently — so a missing id proves nothing. Detection has to come from the delivery side: count rows the poller emitted versus rows committed in the same window and alert on divergence, or run a reconciliation query that replays a trailing window and compares against what was sent.

A trailing window, because it is one predicate: AND observed_at < now() - L. It is correct provided L exceeds the longest gap between a row's INSERT and its COMMIT, which idle_in_transaction_session_timeout already bounds, and it costs L of tail latency. I tested it against the forced interleaving and the skipped row is delivered. The tempting alternative — stop the cursor at the first id gap — I also tested, and it is wrong: deletes, rollbacks and dropped partitions all leave permanent holes, so it stalls forever. If the latency mattered I would move to LISTEN/NOTIFY as a wake signal for a catch-up query, since notifications fire on commit.

It has the same defect and adds new ones. observed_at is clock_timestamp(), assigned before commit, so a late-committing row can carry an earlier timestamp than one already delivered — identical race. On top of that, timestamps collide, so you need a tiebreaker anyway, and any clock adjustment reorders history. Swapping id for a timestamp changes which column is wrong, not whether the cursor is correct.

Yes, but far less severely. A page walks toward lower ids, so a row that commits late above the cursor is skipped for that scroll session and shows up on a fresh page one. That is a consistency artifact rather than data loss. If it mattered, the fix is a repeatable-read snapshot or paging on a snapshot id captured at page one.

Not this one — there is no cross-shard sequence to allocate out of order. But the underlying problem does not vanish: inserted parts become queryable after they are committed and merges reorder data, so a tail still needs a watermark rather than an assumption that a monotonic key equals visibility order. Kafka offsets or a CDC stream give commit order directly, which is why real log pipelines put a log in front of the store.

The stats endpoint and free-text search, in that order — both scan rather than seek. At roughly 1M logs/day over 30 days retention you are at 30B rows, well past where a row store on one box is the right call. Ingest and keyset paging would still be fine; aggregation is what forces the move.

A leading wildcard cannot use a btree, so it is a sequential scan across every partition in range. The Postgres fix is a pg_trgm GIN index per partition. In ClickHouse it is a token bloom-filter skip index or the full-text index. This query is the strongest single argument for moving the workload.

At the prompt's scale Postgres was correct and every query is bounded. The honest version names the two triggers that would change the answer: p95 latency on the stats endpoint, and search latency once messages stop fitting in cache. Without traffic data, a second datastore is speculative complexity.

Likely (service, observed_at) — low-cardinality prefix first so the sparse primary index prunes granules effectively, then time within a service. service and level would both be LowCardinality(String), which cuts storage and speeds up the group-bys the stats endpoint runs.

Much less than the raw number suggests. It is paid once per backend connection, so a pool of ten pays it ten times and then it amortises to about 0.11 ms. The pruned query still wins warm, just by 2× rather than 30×. Measuring it is what turned a scary number into a bounded one.

A buffer count is a deterministic page count; a sub-millisecond timing on a laptop has an error bar wider than its own value — the warm planning spread at 128 partitions was ±1.5 ms on a 0.76 ms median. Buffers do not move with machine load, so they are the honest unit for a claim about complexity.

Not at first — the initial sweep held rows-per-partition constant, so total rows grew with partition count and the curve measured both variables at once. Rerun with total rows pinned at 64,000, the per-partition cost converges on ~56 buffers and stays flat across a 128× range. The separate row sweep moved planning by one page across 1,000× more data.

Yes. now() freezes at transaction start; clock_timestamp() advances per row, so rows in one batch get distinct observed_at values and a batch landing at UTC midnight can straddle two children. Harmless because reads order by id, not observed_at. now() would also be defensible — one timestamp per batch, cleaner routing — and naming that trade is the actual test.

It stalls for the whole detach → create → move → re-attach transaction. This is the sharpest self-identified weakness in the design. The window is short and only fires when a new daily partition is created, and an advisory lock serialises replicas. Production answer: pre-create several days of runway so the pause never coincides with load. DETACH CONCURRENTLY is restricted when a DEFAULT partition exists — which is exactly why pg_partman exists.

Each one has to name a query. logs_id_idx serves the keyset cursor; logs_service_id_idx and logs_level_id_idx serve filtered paging; the observed_at composites serve stats and the SSE tail. Unused indexes are pure write-amplification, which matters more to a logs product than almost anything else — an index nobody queries is a tax on every insert.

There is a real race: startStreamTimers guards on pollTimer !== undefined before an await, so two concurrent first-subscribers both pass the guard, both query the latest id, and both call setInterval — the second assignment leaks the first interval. The fix is a promise latch storing the in-flight startup promise rather than the timer. Same check-then-act family as the id-versus-commit-order race already in the decision log.