The chain blocks table is extremely large, how do I make it smaller?

[gizmotangle]

I'm running a graph node on base with some deployed subgraphs. The blocks table in chain1 has now grown to over 2.5 TB. How can I shrink it? I've already performed a prune to 10 million blocks with graphman, but the blocks table is still extremely large. How do I free up memory?

[codebyankita]

Hi @gizmotangle,

The chain_blocks table can remain very large even after pruning because the graphman prune operation only marks historical blocks as prunable, but it doesn’t automatically reclaim the disk space at the database level. To actually reduce the physical size of the table, you need to perform database-level cleanup.

Here are a few steps you can take:

Run VACUUM (FULL) on the chain_blocks table

This will rewrite the table and release unused disk space back to the OS.

Example:

VACUUM FULL chain_blocks;

Note: VACUUM FULL is blocking, so you’ll want to schedule downtime or run it during low-traffic periods.

Run ANALYZE afterwards

This updates the Postgres statistics so queries continue to perform well.

Example:
ANALYZE chain_blocks;

Consider using pg_repack if downtime is an issue

pg_repack can reclaim disk space without requiring long locks like VACUUM FULL.

Adjust pruning strategy

Make sure your graphman prune is configured properly for your chain. For example:

graphman prune <chain> --blocks-to-keep <N>

On chains like Base, you may be able to prune more aggressively if your subgraphs don’t need deep historical block data.

Long-term solution

If you run multiple chains or many subgraphs, consider moving to firehose-based indexing. Firehose doesn’t require storing such a massive block history locally, which keeps your database much smaller.

[gizmotangle]

Thanks for your answer @codebyankita .
I've already tried running a vacuum job on the blocks table. Unfortunately, it ran for several days and ultimately didn't really achieve anything. The actual subgraph tables are much smaller, so I was hoping there was another way to delete the blocks.

[davidreis97]

@gizmotangle did you find any improvements?