0

We have a PGSQL server (v13) with alot of data in it. The database contains documents.

The total database is around 1.5 TB. Today, someone called me telling me the disk space was almost full. They put in 1 TB extra storage some time ago but that extra storange ran full extremely quickly, which is very abnormal. Disk was 2 TB, now 3 TB with the extra storage.

If I look at the table containing the documents, it only added around 10 GB since 20/07/2022, so I really don't understand why the disk is running full this fast. If I do this query on the database: SELECT pg_size_pretty( pg_total_relation_size('documents') );

It returns '2.7 TB' which is impossible, since there aren't that much documents added recently.

I did a Vacuum as a test on a certain table (total: 20 gb). The vacuum failed with Error:

ERROR: wrong tuple length

What does it mean? I have the same errors in the PGSQL logfiles. They recently installed a new antivirus system on the server. I already asked for exclusions but it didn't seem to solve the problem.

I now only have +/- 130 gb free disk space and it keeps getting full. Is it possible the vacuum takes the disk space and does not return it to Windows because of the error?

Any help is appreciated. I'm not a database expert but i really need to solve this.

Morph
  • 33
  • 3
  • 1
    [this is on the mailing list](https://www.postgresql.org/message-id/flat/DM5PR0501MB38800D9E4605BCA72DD35557CCE10%40DM5PR0501MB3880.namprd05.prod.outlook.com) This may help you. – VynlJunkie Aug 05 '22 at 14:37
  • 1
    Without details like your usage patterns, insert/update/delete, table and index bloat, etc etc is hard to help. Start measuring the size per table and index, to pinpoint the problem. Use vacuum full to reclaim space, just vacuum might not be enough – Frank Heikens Aug 05 '22 at 14:43
  • Hi Frank Thanks for your answer. I know it's hard to pinpoint the issue without too many details. Vacuum full requires alot of disk space, no? Disk space I don't have at this moment... The 2.7 TB documents table is split in 1 GB files in de database folder. Will i need 2.7 tb for the full vacuum or only 1 gb of disk space? Does 'normal' vacuum consume disk space or not? Maybe I can start with the vacuum on the documents table. I was hoping someone could put me in the right direction conserning that 'wrong tuple length error', i can't find anything about it online. – Morph Aug 05 '22 at 14:50
  • @VynlJunkie thanks for that! we do have alot of grants on the table. I will look into it and remove them if I can. – Morph Aug 05 '22 at 14:54
  • 1
    A normal VACUUM won't release disk space, won't shrink the tables. And yes, you need a lot of free disk space to run VACUUM FULL (or pg_repack). Maybe you could drop some/all indexes? But first I would check why the tables grow so fast, what is going wrong? Do you have many failed transactions? Is auto vacuum running? – Frank Heikens Aug 05 '22 at 15:11
  • Depending on the value of this database, you might need some profession help with this, to keep the database alive. – Frank Heikens Aug 05 '22 at 15:12
  • 2
    I would start with [pg_stat_activity](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW) and see if there are any "idle in transaction" queries. Then [pg_stat_all_tables](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW) to see what vacuum activity has or has not happened. Then [pg_locks](https://www.postgresql.org/docs/current/view-pg-locks.html) to see if there is a locking issue. – Adrian Klaver Aug 05 '22 at 15:22
  • @AdrianKlaver thanks! Sorry for my 'stupid' question, but how do i know there is a locking issue? I see 5 records in pg_locks but i don't really know how to know something is wrong there. I'm also wondering: - howmuch disk space do i need for a full vacuum on the 2.7 TB table. The full 2.7 TB? How on earth will I ever be able to do a full vacuum on this table? The physical files of the table are split in files 1 gb each. – Morph Aug 05 '22 at 15:56
  • 1) Did you see any "idle in transaction" queries in `pg_stat_activity`? 2) Read [VACUUM](https://www.postgresql.org/docs/current/sql-vacuum.html) . `FULL` makes a copy of the table so yes you need a lot of space. But there is no point in doing `VACUUM FULL` if something is preventing tuples from being deleted e.g. "idle in transaction" queries. 3) This is really not the appropriate place for this. At the very least you should be on [dba stackexchange](https://dba.stackexchange.com/) or contacting one of the Postgres support companies. – Adrian Klaver Aug 05 '22 at 16:37
  • @AdrianKlaver There are no 'idle in transactions'. I think the disk space increase if because of an autovacuum on the 2.7 TB file. Is that possible? Any idea if I need 1 gb or 2.7TB of free space for the vacuum full on that file? – Morph Aug 06 '22 at 10:34

0 Answers0