Moving to a smaller database disk

A squid’s managed Postgres volume can grow but never shrink. Deploying a smaller scale.addons.postgres.storage to an existing slot is rejected:

Database storage size can not be decreased. Please reserve at least <current_db_size> for the squid.

To reduce the disk you have to build a new deployment sized correctly and move your traffic to it. This page walks through copying the database into that new slot with pg_dump and pg_restore, so the new deployment starts from your current data instead of replaying the chain from block zero.

First, find out what is actually using the space

The right fix depends on whether the disk holds data or dead weight.

SELECT pg_size_pretty(pg_database_size(current_database())) AS total;

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Connect with the string from the deployment’s DB access tab (direct access).

A high n_dead_tup relative to n_live_tup means bloat, not data. Follow Update-heavy tables instead — tuning fillfactor and autovacuum fixes the cause, and this page only moves the problem to a new disk.
The database is genuinely much smaller than the disk — for example 100 Gb of data on a 700 Gb volume, usually the result of an earlier over-provision or a backfill that has since been cleaned up. That is what this page is for.

How the switch works

You never shrink anything in place. You stand up a second deployment, seed its database from the first, and then move your production tag across:

sqd deploy . -m squid.restore.yaml      # 1. new slot, right-sized disk, no processor
pg_dump ... | pg_restore ...            # 2. copy the database in
sqd deploy . -m squid.live.yaml         # 3. attach the real processor and API
sqd tags add production -n my-squid -s <new-slot>   # 4. switch traffic

The database copy is a snapshot taken at one moment. While you restore it the old deployment keeps indexing, so the new one starts slightly behind and catches up on its own before you switch. The API stays on the old slot the whole time, which is what makes the switch a zero-downtime update.

Both slots run and bill independently until you remove the old one. Size the overlap into your plan.

1. Deploy the new slot without a processor

The new deployment must come up with an empty database and leave it alone. If migrations run first, they create tables that collide with the ones in your dump; if the processor starts first, it begins indexing from block zero and writes state you are about to overwrite. Suppress both in a temporary manifest:

  init: false
  processor:
    cmd: ["sleep", "infinity"]

init: false skips migrations, because the dump already carries the full schema. The placeholder processor is required — every manifest needs one — but it must not touch the database. Pin the Postgres version and set the disk you actually want:

scale:
  addons:
    postgres:
      storage: 1500Gi
      profile: small

Pin deploy.addons.postgres.version to the same major version the source deployment runs. Changing the version on a slot later requires a hard reset, and the two databases must match for a clean restore.

squid.restore.yaml — complete temporary manifest

manifest_version: subsquid.io/v0.1
name: my-squid
description: 'My squid'
build:
deploy:
  addons:
    postgres:
      version: "18"
  # No migrations: the dump carries the full schema, including the
  # `migrations` bookkeeping table.
  init: false
  # Placeholder so that nothing writes to the database while it is restored.
  processor:
    cmd:
      - sleep
      - infinity
scale:
  addons:
    postgres:
      storage: 1500Gi
      profile: small

Deploy it without a slot: or tag: field so Cloud assigns a fresh slot:

sqd deploy . -m squid.restore.yaml

Take the new slot name from the output, then read its connection string from its DB access tab.

Give the new disk real headroom. Postgres needs room for write-ahead logs, autovacuum churn, and index growth, and a disk that fills up stops the processor. Sizing to roughly 1.5× the current database size is a reasonable starting point, and you can always grow it later.

2. Dump the source database

Use the directory format. Unlike a plain SQL file it supports parallel dump and restore, which is what keeps this practical as the database grows.

pg_dump "$SOURCE_URL" \
  --format=directory --jobs=4 --compress=zstd \
  --no-owner --no-privileges \
  --file=./dump

Two flags matter more than they look:

--no-owner --no-privileges — the dump otherwise records the source deployment’s database role, which does not exist in the new deployment, and every ALTER ... OWNER TO fails on restore.
--jobs — opens one connection per worker against a consistent snapshot. Keep it at or below the deployment’s external_access.max_connections.

Dump all schemas. Alongside your entity tables the database holds the processor’s own bookkeeping schema, which records the block height reached so far. Copying it is exactly what lets the new processor resume instead of replaying the chain — so do not narrow the dump with --schema.

Your pg_dump must be at least as new as the server it reads. An older client refuses to dump a newer server. Check with pg_dump --version against the version reported by SELECT version(), and if your system client is older, run the tools from a container image matching the server version.

3. Restore into the new slot

pg_restore -d "$TARGET_URL" \
  --format=directory --jobs=4 \
  --no-owner --no-privileges \
  --exit-on-error ./dump

--exit-on-error stops at the first failure rather than leaving a half-populated database that looks healthy. Without it pg_restore reports errors and carries on, and you find out only once the processor is running. Confirm that both the entity tables and the processor’s recorded height arrived:

\dt
SELECT height FROM squid_processor.status;

The height should match the source deployment at roughly the time the dump started.

4. Attach the real processor

Redeploy the same slot with your normal manifest — real init, processor and api — pinning slot: to the new slot and keeping the same storage: and Postgres version::

sqd deploy . -m squid.live.yaml --allow-update

The migration step is a no-op: the migrations table came across in the dump, so there is nothing left to apply. The processor reads the restored height and resumes from there, indexing only the blocks produced since the dump. Watch it close the gap:

sqd logs -n my-squid -s <new-slot> -c processor -f

5. Verify before switching

Wait for the new deployment to report SYNCED in sqd view, then compare the two slots rather than trusting the status alone:

entity counts on the tables that matter to you;
a checksum over a representative table on both slots, which should agree once both are at the chain head:
SELECT md5(string_agg(t::text, '|' ORDER BY t.id)) FROM my_table t;
the same query against both canonical API URLs;
the processor logs, for restarts or migration errors.

6. Move the tag, then remove the old slot

sqd tags add production -n my-squid -s <new-slot>

Traffic follows the tag within seconds. Keep the old slot through an observation window so you can move the tag back if anything looks wrong, then remove it — and with it the oversized disk:

sqd remove -n my-squid -s <old-slot>

How large a database can this move?

The dump is usually the cheap half. The restore has to rebuild every index from scratch, and that is what dominates the wall clock — expect hours for tens of gigabytes and considerably longer beyond a few hundred. Both halves run over the public database endpoint, so your own bandwidth counts too. Before committing to a maintenance plan, measure it: restore your dump into a scratch slot and time it. That number, not an estimate, tells you how long the two slots overlap and how far the new processor will have to catch up.

For databases where a logical copy is no longer practical, contact SQD. Copies at the storage layer avoid the rebuild entirely and are much faster at that scale, but they have to be performed on our side.

Troubleshooting

Symptom	Cause
`role "..." does not exist` during restore	`--no-owner --no-privileges` missing from the dump or the restore
`relation "..." already exists`	Migrations ran before the restore. Redeploy the slot with `init: false` and an empty database, then restore again
Processor starts from block zero	The processor’s bookkeeping schema was not included in the dump, or the processor ran before the restore finished
Deployment rejected with `Database storage size can not be decreased`	You are deploying the smaller disk to the existing slot instead of a new one
`server version mismatch` from `pg_dump`	The client is older than the server. Use a client at least as new

Postgres addon — storage, profiles, and connection limits.
Slots and tags — the tag switch this procedure ends with.
Update-heavy tables — the fix when the disk holds bloat rather than data.
Schema changes and backfills — when the new deployment also needs different data.

Managed

Self-Hosting

Moving to a smaller database disk

First, find out what is actually using the space

How the switch works

1. Deploy the new slot without a processor

2. Dump the source database

3. Restore into the new slot

4. Attach the real processor

5. Verify before switching

6. Move the tag, then remove the old slot

How large a database can this move?

Troubleshooting

​First, find out what is actually using the space

​How the switch works

​1. Deploy the new slot without a processor

​2. Dump the source database

​3. Restore into the new slot

​4. Attach the real processor

​5. Verify before switching

​6. Move the tag, then remove the old slot

​How large a database can this move?

​Troubleshooting

​Related

First, find out what is actually using the space

How the switch works

1. Deploy the new slot without a processor

2. Dump the source database

3. Restore into the new slot

4. Attach the real processor

5. Verify before switching

6. Move the tag, then remove the old slot

How large a database can this move?

Troubleshooting

Related