Postgres nodestore_node is huge

[lucas-zimerman]

Self-Hosted Version

24.8.01 d52e8e

CPU Architecture

x86_x64

Docker Version

24.0.2, build cb74dfc

Docker Compose Version

2.9.0

Machine Specification

• My system meets the minimum system requirements of Sentry

Steps to Reproduce

no clue how it got to this situation

Expected Result

healthy postgres

Actual Result

from Postgres table

table_name                   | total_size | data_size  | index_size 
------------------------------------------------+------------+------------+------------
 nodestore_node                                 | 301 GB     | 10 GB      | 291 GB

SELECT 
  schemaname, relname AS table_name, 
  round(100 * (pg_total_relation_size(relid) - pg_relation_size(relid)) / pg_total_relation_size(relid)::numeric, 2) AS index_bloat_percent
FROM pg_statio_user_tables
WHERE relname = 'nodestore_node';

schemaname |   table_name   | index_bloat_percent 
------------+----------------+---------------------
 public     | nodestore_node |               96.65

I tried running pg-repack but it doesn't run because it says nodestore_node is corrupted.

Could this be the reason why this table is so big? what are the solutions for this case? At the moment I am doing partial selects to see where it breaks on this table but it has loads of data and may take days to find it

Event ID

No response

[hostalp]

Have you tried the good old VACUUM?
VACUUM(VERBOSE, ANALYZE, FULL) nodestore_node;

• ensure that you have enough of free disk space as the FULL mode is going to create a new data file(s), rebuild the table into it, switch to it and then delete the old file(s)
• also the FULL mode will lock the table and will llikely take some significant time to complete, so it's often better to stop applications (that would use the database) to avoid application issues
• you can also run just VACUUM(VERBOSE, ANALYZE, FULL); to fully vacuum all tables, though this way you won't have a control over the table processing order (if you don't have a lot of disk space, it's a good idea to start with the smallest tables)

https://www.postgresql.org/docs/current/routine-vacuuming.html
https://www.postgresql.org/docs/current/sql-vacuum.html

You may also have a look at REINDEX that just rebuilds indexes (just the specified ones, or all for the table, schema, or the whole database).
https://www.postgresql.org/docs/current/routine-reindex.html
https://www.postgresql.org/docs/current/sql-reindex.html

[lucas-zimerman]

vacuum full helped with 30GB.
REINDEX didn't really change anything sadly.

[lucas-zimerman]

I tried deleting everything from the table but still there was corruption.
the only way i managed to fix it was truncating the table (It would take weeks for us to manually fix the table)

[aldy505]

I will fix this for the next release. The PR is here: #3498