1

I have a few tables in Postgres (12.7) database hosted with an Heroku hobby-basic plan (max size = 10GB).

Doing heroku pg:info reports 11GB of used disk, so I'd like to do something about it to avoid having problems soon.

Most of the tables are small but a big one I have to store job openings. It has about 500MB in size, with the description column being the biggest as well. There are a few indices as well that take about 3GB.

Checking table and index sizes (adapted the query from this SO answer) I see that there is 7.3GB of data that's not either data or indices.

df5icl8qkl5bs4=> SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_relation_size(relid)) as "Data Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)) as "Remaining Size"
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;


   table_name    | Total Size | Data Size  | Index size | Remaining Size 
-----------------+------------+------------+------------+----------------
 job             | 11 GB      | 453 MB     | 3373 MB    | 7385 MB
 location        | 15 MB      | 7144 kB    | 8392 kB    | 40 kB
 keyword         | 5480 kB    | 4648 kB    | 792 kB     | 40 kB
 company         | 5384 kB    | 3776 kB    | 1568 kB    | 40 kB
 biggest_cities  | 3344 kB    | 3336 kB    | 0 bytes    | 8192 bytes
 topic           | 80 kB      | 24 kB      | 16 kB      | 40 kB
 alembic_version | 56 kB      | 8192 bytes | 16 kB      | 32 kB
(7 rows)

What's up with those 7.3 GB? How can I free up that space?

1

That must be the TOAST table, which stores overlength attributes out of line.

0

You need pg_table_size instead of pg_relation_size. The second one is not a size of the table. To use pg_relation_size, you need to understand how data is stored in postgresql at a low level: relation forks, toast.

pg_total_relation_size provides correct size calculation including indexes. So the main difference is a separate TOAST table, which stores long columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.