Title: fix(db): throttle autovacuum on the largest tables so vacuum WAL bursts stop stalling commits by waleedlatif1 · Pull Request #9087 · simstudioai/sim · GitHub
Open Graph Title: fix(db): throttle autovacuum on the largest tables so vacuum WAL bursts stop stalling commits by waleedlatif1 · Pull Request #9087 · simstudioai/sim
X Title: fix(db): throttle autovacuum on the largest tables so vacuum WAL bursts stop stalling commits by waleedlatif1 · Pull Request #9087 · simstudioai/sim
Description: Summary Vacuuming the largest tables (and their TOAST relations) writes WAL, mostly full-page images, faster than the synchronous standbys can flush it. Until the vacuum ends, every write commit on the primary waits for a standby, so the whole app stalls. Adds migration 0431, which sets a per-table autovacuum_vacuum_cost_delay = 10 / autovacuum_vacuum_cost_limit = 200 on embedding, embedding_search and workflow_execution_logs, for each table and its TOAST relation. That caps how fast those vacuums dirty pages, and so their WAL rate. Trade-off: these three tables leave cross-worker I/O balancing and vacuum more slowly. This reverses 0362's choice for these tables only. Lock analysis: ALTER TABLE ... SET (autovacuum params) only updates the catalog and takes SHARE UPDATE EXCLUSIVE. There is no table rewrite. Reads and writes never wait on it, even while it is queued behind a running vacuum or another holder. A running non-wraparound autovacuum is cancelled by the waiting ALTER and restarts later with the new settings. The migrator's existing 5s lock_timeout plus retry covers the case where the ALTER queues behind a long-running conflicting holder. Type of Change Bug fix Testing New packages/db/large-table-vacuum-throttle-migration.integration.ts runs against real Postgres 17 and checks that: the statement holds only ShareUpdateExclusiveLock, and relfilenode is unchanged (no rewrite); another session can read and write the table while the migration transaction is open; while the ALTER is queued behind a conflicting holder, a writer with a 300ms lock_timeout still inserts; every table and its TOAST relation carry both settings. Fails before this change (the migration file is missing). Also fails if the migration is swapped for an exclusive-lock DDL. check:migrations passes, and drizzle-kit generate reports no schema changes. Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing No new warnings introduced I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary Vacuuming the largest tables (and their TOAST relations) writes WAL, mostly full-page images, faster than the synchronous standbys can flush it. Until the vacuum ends, every write commit o...
X Description: Summary Vacuuming the largest tables (and their TOAST relations) writes WAL, mostly full-page images, faster than the synchronous standbys can flush it. Until the vacuum ends, every write commit o...
Opengraph URL: https://github.com/simstudioai/sim/pull/9087
X: @github
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf |
| route-pattern | /:user_id/:repository/pull/:id/files(.:format) |
| route-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:c47e1f7a-12f1-771f-19c3-8d269984f0ea |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | D316:25245F:DF67B7:1320B2B:6AFB5D24 |
| html-safe-nonce | 7bb692b6354fc534c9d17a5facf4d0d63d68bca2fa47308cb3a0202f89430b23 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJEMzE2OjI1MjQ1RjpERjY3Qjc6MTMyMEIyQjo2QUZCNUQyNCIsInZpc2l0b3JfaWQiOiI4OTQxNjA4NzQwNzg0Mzk3NjA0IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | 6e45bdeb8c91eba3eb9cbf8b74e14120aabab6616586b50da0dec8a923b158fe |
| hovercard-subject-tag | pull_request:4879051772 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/9087/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary Vacuuming the largest tables (and their TOAST relations) writes WAL, mostly full-page images, faster than the synchronous standbys can flush it. Until the vacuum ends, every write commit o... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | ea33d13c96d0ff66bd8fa906b193d2213536b78cb06b0c50a4632c58b1418f94 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 59ef97164c52cf59bd10558ddec9c4f482763988 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width