0

We are using PostgreSQL version 11 in our production environment. And we are running out of disk space because of Postgres. When I check the table sizes in my DB with this query I saw that the file_table table is the biggest table. But the actual table size is only 1.9GB. On the other hand table's toast size is 739 GB. I try the vacuum file_table command but nothing happen. I'm afraid of trying the execute vacuum full file_table command because there is only 25% free disk space left on the device.

My query:

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes 
FROM (
  SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
          , c.reltuples AS row_estimate
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a ORDER BY total_bytes DESC;

The output of the query:

   oid   |    table_schema    |              table_name              | row_estimate | total_bytes  | index_bytes | toast_bytes  | table_bytes |   total    |   index    |   toast    |   table    
---------+--------------------+--------------------------------------+--------------+--------------+-------------+--------------+-------------+------------+------------+------------+------------
 6036951 | public             | file_table                           |   2.1147e+07 | 796532998144 |   950206464 | 793494487040 |  2088304640 | 742 GB     | 906 MB     | 739 GB     | 1992 MB

df -h output:

/dev/sdb2       1,8T  1,2T  465G  73% /

So what can I do the reclaim this disk space? And what could have caused the problem?

EDIT 1:

This is my table definition:

      Column       |              Type              | Collation | Nullable |                Default                 | Storage  | Stats target | Description 
-------------------+--------------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id                | bigint                         |           | not null | nextval('file_table_id_seq'::regclass) | plain    |              | 
 file_id           | integer                        |           | not null |                                        | plain    |              | 
 phone_number_to   | character varying(255)         |           | not null |                                        | extended |              | 
 phone_number_from | character varying(255)         |           | not null |                                        | extended |              | 
 created_at        | timestamp(0) without time zone |           |          |                                        | plain    |              | 
 updated_at        | timestamp(0) without time zone |           |          |                                        | plain    |              | 
 mail_raw          | text                           |           |          |                                        | extended |              | 
Indexes:
    "file_table_pkey" PRIMARY KEY, btree (id)
    "file_table_file_id_index" btree (file_id)

This is the VACUUM VERBOSE file_table output:

INFO:  vacuuming "public.file_table"
INFO:  "file_table": found 0 removable, 74725 nonremovable row versions in 935 out of 254847 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 2091352576
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 238334 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.02 s.
INFO:  vacuuming "pg_toast.pg_toast_6036951"
INFO:  "pg_toast_6036951": found 0 removable, 3258092 nonremovable row versions in 813495 out of 96604072 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 2091352576
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 36229742 frozen pages.
0 pages are entirely empty.
CPU: user: 4.18 s, system: 0.76 s, elapsed: 4.97 s.
VACUUM

Row count of the table: 21167178

Update:

We deleted the unnecessary data from the table. The row count of the table is 519340 now. But nothing happens. The TOAST usage is still the same. Does running vacuum on the table makes difference or do we need to do something else?

6
  • Can you post the table definition? That would give us a hint as to what columns are eligible for TOASTing.
    – dwhitemv
    Oct 30, 2022 at 0:48
  • Can you also post the output of VACUUM VERBOSE file_table? It should show that the TOAST table is being processed and provide some statistics.
    – dwhitemv
    Oct 30, 2022 at 1:00
  • 1
    The simplest explanation is that you have a lot of data stored in that table. Is there a reason to believe that this is not the case?
    – jjanes
    Oct 30, 2022 at 2:25
  • @jjanes Actually when I talked to the dev team about this they said they were keeping raw mail data in the raw_mail column. So I think it might be the case. Oct 30, 2022 at 7:04
  • @dwhitemv I edited my question and add the outputs of your wants. Oct 30, 2022 at 7:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.