Skip to main content
Some squids repeatedly rewrite the same rows: per-block price and TVL refreshes, running balances, position amounts and ratios, swap state, and similar “live” data. Every one of those writes goes through Store.upsert as a Postgres INSERT ... ON CONFLICT ... DO UPDATE, and each update leaves behind a dead row version that has to be cleaned up later. On the default table settings this churn bloats the table and its indexes, and query latency drifts upward until autovacuum catches up. This page explains why that happens and the three changes that keep these tables lean. The biggest lever is in your handler code; the other two are one-line Postgres settings applied through a migration.

​
Why update-heavy tables slow down

Postgres never overwrites a row in place. An UPDATE (and an upsert that hits an existing row) writes a new version of the row and marks the old one dead. Autovacuum reclaims dead versions later. Until it does, the table — and every index on it — carries the extra dead entries, so scans read more pages and queries slow down. There is a fast path that avoids most of this cost: a HOT (Heap-Only Tuple) update. When the new row version fits on the same 8kB page as the old one and no indexed column’s value changes, Postgres links the versions on the page and skips updating the indexes entirely. HOT updates produce far less bloat and are cleaned up cheaply. Two conditions must hold for an update to stay HOT:
  1. No indexed column’s value actually changes. Postgres compares stored values, not the SET list — rewriting a column to the value it already holds does not break HOT.
  2. The new version fits on the same page. This requires free space on the page, which the default table layout does not reserve.
The three sections below target exactly these conditions.

​
1. Skip no-op writes in your handler

Before adding an entity to your save batch, compare it against the loaded copy and drop it if nothing changed - this will prevent invalidating rows for no reason. Starting from the batch-processing pattern, keep only the entities that differ, e.g. 
const changed = [...myEntities.values()].filter(e => {
  const before = original.get(e.id)        // the row as loaded from the DB
  return before == null || isDifferent(before, e)
})
await ctx.store.upsert(changed)
where original is a snapshot of the entities as they were loaded, and isDifferent compares the fields your handler may have touched: 
function isDifferent(a: MyEntity, b: MyEntity): boolean {
  return a.amount0 !== b.amount0
    || a.amount1 !== b.amount1
    || a.ratio !== b.ratio
}
In most cases, this is the single highest-impact change for periodic “refresh everything” routines.

​
2. Reserve page space with fillfactor

fillfactor is the percentage of each heap page Postgres fills when inserting rows; the rest is reserved for future updates. At the default fillfactor = 100 there is no headroom, so an updated row almost never fits on its original page and the HOT fast path is lost — every update bloats the heap and all indexes. Lowering fillfactor to 90 leaves 10% of each page free, dramatically raising the share of updates that stay HOT. On an update-heavy table this typically moves the HOT rate from a few percent to a clear majority and roughly halves heap bloat. The cost is modest: rows are packed slightly less densely, so a cold table takes marginally more disk. 
ALTER TABLE "my_table" SET (fillfactor = 90);
SET (fillfactor = ...) only affects pages written after it runs — it does not repack existing rows. See Reclaiming existing bloat to fix a table that is already bloated.

​
3. Vacuum more often with autovacuum_vacuum_scale_factor

Autovacuum fires for a table once its dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * row_count. The default scale_factor of 0.2 means a table must be roughly 20% dead before it is cleaned. On a large, update-heavy table that lets bloat — and query latency — swing badly between cycles. Setting scale_factor = 0.05 triggers a vacuum at about 5% dead, giving smaller, more frequent cleanups and steadier query performance, at the cost of a little more background autovacuum I/O. 
ALTER TABLE "my_table" SET (autovacuum_vacuum_scale_factor = 0.05);
For a table that holds a single, constantly-rewritten row (a global counter or summary), scale_factor never trips. Drive its autovacuum off the absolute threshold instead: 
ALTER TABLE "singleton" SET (autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 25);

​
Applying the table tuning in a migration

fillfactor and the autovacuum settings live in Postgres, not in schema.graphql, so squid-typeorm-migration generate will never produce them. Add a small hand-written migration alongside the generated ones. The core of the migration is one ALTER TABLE per update-heavy table: 
async up(db) {
  await db.query(`ALTER TABLE "position" SET (fillfactor = 90, autovacuum_vacuum_scale_factor = 0.05)`)
}
A complete migration, tuning several tables and providing a reversible down: 
module.exports = class TuneUpdateHeavyStorage1780000000000 {
    name = 'TuneUpdateHeavyStorage1780000000000'

    // Tables that receive repeated upserts. Insert-mostly tables are left at defaults.
    tables = ['position', 'pool', 'token']

    async up(db) {
        for (const t of this.tables) {
            await db.query(`ALTER TABLE "${t}" SET (fillfactor = 90, autovacuum_vacuum_scale_factor = 0.05)`)
        }
        // A single constantly-rewritten row: drive autovacuum off an absolute threshold.
        await db.query(`ALTER TABLE "bundle" SET (fillfactor = 80, autovacuum_vacuum_scale_factor = 0.0, autovacuum_vacuum_threshold = 25)`)
    }

    async down(db) {
        for (const t of this.tables) {
            await db.query(`ALTER TABLE "${t}" RESET (fillfactor, autovacuum_vacuum_scale_factor)`)
        }
        await db.query(`ALTER TABLE "bundle" RESET (fillfactor, autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold)`)
    }
}
The drop-and-regenerate flow in Updating after schema changes as well as the sqd migration:generate local command that ships with most templates remove the db folder, which deletes this file along with the generated ones. After regenerating, re-add the tuning migration as a new, later file so it applies on top of the fresh schema. If you’re running sqd migration:generate in any of your workflows, update its definition in commands.json to skip the removal of the db folder (usually done via the dependency on migration:clean) and adjust the way you’re using the command accordingly.

​
Reclaiming existing bloat

The settings above keep new churn cheap but do not repack data that is already bloated, and lowering fillfactor only changes pages written from then on. To rewrite an existing table compactly under its new fillfactor, run a one-time repack. VACUUM FULL is built in but takes an ACCESS EXCLUSIVE lock for the whole rewrite, blocking all reads and writes — only suitable when the squid is paused: 
VACUUM FULL "table_name";
If downtime is unacceptable, use a zero-downtime update: deploy to a fresh slot whose database is built with the tuning migration from block zero, then move your production tag to it. Alternatively, contact us to arrange an online repack if you are an enterprise customer.

​
Verifying the effect

Check the HOT rate and bloat for your tables in psql (DB access): 
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;
A healthy update-heavy table shows a high hot_pct and a n_dead_tup count that stays low relative to its size. A low hot_pct after tuning usually means an indexed column’s value really is changing on every update — revisit step 1 and your @index choices.