Repository navigation
docs: adopt partitioning with pg-partsmith where an extension can't be installed - #1110
AlexeyShalaev wants to merge 2 commits into
Conversation
alexluong
left a comment
alexluong
left a comment
There was a problem hiding this comment.
Thanks @AlexeyShalaev. I followed the recipe, worked as expected! Two small points inline.
Separately, reading the whole page again, I think it's getting hard to follow, and that's on the structure more than on this PR. Here's where my thinking is:
- This page is an overview of the event log, and with this PR about half of it is a how-to for adopting partitioning. I'd move that how-to into its own guide and keep a short pointer here next to the batched delete.
- pg_partman and pg-partsmith go through the same four steps: create the partitions, move the current month, move the earlier months, schedule retention. Both could be written as numbered steps with the same names, using the docs'
{% tabs %}component with one tab per tool, so a reader picks one and skips the other. Right now the pg-partsmith part leans on the pg_partman prose above it. - The lock waits and the brief gap in visible events come from PostgreSQL and Outpost's schema, so they could be explained once before the steps. Each step would then only say what to run and what you'll notice.
I know that's a bigger restructure, so we can merge this without it and our team can help edit afterwards. Up to you if you'd like to do it here.
@leggetter, could you weigh in on the docs structure side?
|
|
||
| `apply` creates partitions for the current month and the ones ahead, and moves the rows the current month already has out of `events_default`. Writes wait while they move, and new rows land in the new partition from then on. Then run the same command with `backfill` in place of `apply`. It moves the earlier months out of the DEFAULT partitions, oldest first, in batches of 10,000 rows (`--batch-rows`). Each batch commits on its own, so a month's rows don't show through `events` from its first batch until its partition is attached, which means events briefly disappear from the API and the tenant portal. Larger batches shorten that, at the cost of longer waits for writes. Nothing writes to the earlier months, so with the default batches `backfill` holds up writes only briefly, while each month's partition is attached. | ||
|
|
||
| From then on, run `apply --allow-destructive` on a schedule, daily is plenty, on the same kind of scheduler as above. It creates partitions ahead and drops the ones older than `retention_count`; the first run drops every month `backfill` moved that is older than that. `plan --locks` in place of `apply` prints what a run would do, and the lock each step takes, without changing anything. `plan --check` exits non-zero when partitions are missing or something needs a look, which makes it a monitoring check. |
There was a problem hiding this comment.
Publish accepts a client-supplied time, so after adoption an event dated in a month with no partition lands in events_default. I inserted one dated 2025-06 and one dated 2026-03 (already dropped): plan --check said "nothing to do" and exited 0, and apply --allow-destructive left both there, so retention never removes them. backfill followed by apply --allow-destructive cleared them.
Could the page schedule backfill before apply --allow-destructive, or say to run it when events_default isn't empty?
There was a problem hiding this comment.
Good catch, and it was a gap in pg-partsmith as much as in the page: nothing reported rows sitting in DEFAULT behind the cursor, so plan --check had no reason to fail. 1.8.0 reports them as rows_in_default, a warning, so plan --check exits 3. The page now schedules backfill before apply --allow-destructive and pins 1.8.0.
I reran it with your two cases, events dated 2025-06 and 2026-03 published after adoption. plan --check exited 3 and named 2025_06, backfill moved both, apply --allow-destructive dropped both months since they're past retention_count, and plan --check was back to 0 with events_default empty. A backfill with nothing in DEFAULT moves nothing and exits 0, so it costs next to nothing to run daily.
One side effect: the first apply during adoption now exits 3 as well, since the earlier months are still in DEFAULT at that point. The page says so.
| apply -c /etc/pg-partsmith/partitions.yaml | ||
| ``` | ||
|
|
||
| `apply` creates partitions for the current month and the ones ahead, and moves the rows the current month already has out of `events_default`. Writes wait while they move, and new rows land in the new partition from then on. Then run the same command with `backfill` in place of `apply`. It moves the earlier months out of the DEFAULT partitions, oldest first, in batches of 10,000 rows (`--batch-rows`). Each batch commits on its own, so a month's rows don't show through `events` from its first batch until its partition is attached, which means events briefly disappear from the API and the tenant portal. Larger batches shorten that, at the cost of longer waits for writes. Nothing writes to the earlier months, so with the default batches `backfill` holds up writes only briefly, while each month's partition is attached. |
There was a problem hiding this comment.
nit: apply moves the current month out of attempts_default too; the sentence names only events_default.
There was a problem hiding this comment.
Fixed, thanks.
54fc7a7 to
08852a9
Compare
|
Thanks for running it. Both inline points are in 08852a9, details on the threads. On the structure I agree, and I'm happy to do it here rather than leave it to your team: a separate guide for adopting partitioning with a short pointer from this page next to the batched delete, the PostgreSQL side (rows in DEFAULT, the lock on drops, the gap while a month moves) explained once up front, then the same four steps under |
|
@leggetter a gentle nudge on the structure question. To make it an easy yes or no: I'd put the guide at |
|
@alexluong no word on the structure question yet, and I don't want the PR to sit on it. Either works for me: you merge as is and your team edits later, as you offered, or I move it to |
Follow-up to #1058, as discussed there: the pg-partsmith recipe as its own PR, next to the pg_partman one rather than in place of it, for installs where the extension isn't available.
The four issues you opened are fixed: bedrock-python/pg-partsmith#74 (retention on the database's clock) in 1.6.0, bedrock-python/pg-partsmith#75 (lock timeout) in 1.6.1, bedrock-python/pg-partsmith#73 (
sslmodein the DSN) in 1.6.2, and bedrock-python/pg-partsmith#76 (an interrupted backfill) in 1.6.3 and 1.7.0. Measuring this recipe turned up two more, both fixed before writing it: retention could deadlock against a reader of the table (1.7.1), andbackfill's last attach could reject a live insert into the current month (1.7.2). The page pins 1.7.2.Measured on PostgreSQL 17 against Outpost's migrations at a5ae602, with 300,091 rows per table in the DEFAULT partitions, a writer inserting 20 events and 20 attempts a second, a reader counting the history in a loop, and
sslmode=requirein the DSN:apply, current month holding 33,600 rowsbackfill, nine earlier monthsapply --allow-destructive, four months droppedNo insert failed, both DEFAULT partitions ended empty, and
plan --checkexited 0 afterwards. With batches larger than a month, a month was out of sight for about 30 ms and inserts waited up to 247 ms, which is the trade-off the page describes.Same disclosure as before: I maintain pg-partsmith.