> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqd.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Tuning update-heavy tables

> Keep frequently-rewritten Postgres tables (prices, TVL, balances, positions) fast by enabling HOT updates — skip no-op writes, set fillfactor, and tune autovacuum.

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`](/en/sdk/squid-sdk/reference/store/typeorm#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](/en/sdk/squid-sdk/resources/persisting-data/typeorm#database-migrations).

## 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](/en/sdk/squid-sdk/resources/batch-processing#patterns), keep only the entities that differ, e.g.

```ts theme={"system"}
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:

```ts theme={"system"}
function isDifferent(a: MyEntity, b: MyEntity): boolean {
  return a.amount0 !== b.amount0
    || a.amount1 !== b.amount1
    || a.ratio !== b.ratio
}
```

<Tip>
  In most cases, this is the single highest-impact change for periodic "refresh everything" routines.
</Tip>

## 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.

```sql theme={"system"}
ALTER TABLE "my_table" SET (fillfactor = 90);
```

<Warning>
  `SET (fillfactor = ...)` only affects pages written **after** it runs — it does not repack existing rows. See [Reclaiming existing bloat](#reclaiming-existing-bloat) to fix a table that is already bloated.
</Warning>

## 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.

```sql theme={"system"}
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:

```sql theme={"system"}
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`](/en/sdk/squid-sdk/resources/persisting-data/typeorm#database-migrations) 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:

```js theme={"system"}
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`:

<Accordion title="db/migrations/NNNN-TuneUpdateHeavyStorage.js">
  ```js theme={"system"}
  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)`)
      }
  }
  ```
</Accordion>

<Warning>
  The drop-and-regenerate flow in [Updating after schema changes](/en/sdk/squid-sdk/resources/persisting-data/typeorm#updating-after-schema-changes) as well as the `sqd migration:generate` [local command](/en/cloud/reference/cli/commands-json) 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.
</Warning>

## 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:

```sql theme={"system"}
VACUUM FULL "table_name";
```

<Note>
  If downtime is unacceptable, use a [zero-downtime update](/en/cloud/resources/slots-and-tags/#zero-downtime-updates): 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.
</Note>

## Verifying the effect

Check the HOT rate and bloat for your tables in `psql` ([DB access](/en/cloud/reference/pg#direct-access)):

```sql theme={"system"}
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](#1-skip-no-op-writes-in-your-handler) and your `@index` choices.

## Related

* [Batch processing](/en/sdk/squid-sdk/resources/batch-processing) — the in-memory batching that no-op skipping builds on.
* [Saving to PostgreSQL](/en/sdk/squid-sdk/resources/persisting-data/typeorm) — `Store.upsert` and the migration workflow.
* [Query optimization](/en/cloud/resources/query-optimization) — the read-side counterpart: indexing slow GraphQL queries.
* [Indexes and constraints](/en/sdk/squid-sdk/reference/schema-file/indexes-and-constraints) — `@index` decorators; fewer indexes also means fewer to keep HOT.