Hi all.
I have a database which is 4TB big. We currently store binary data in a bytea data type column (seg_data BYTEA). The column is behind binary_schema and the files types stored are: pdf, jpg, png.
Getting the schema binary_schema size:
SELECT pg_size_pretty(pg_database_size('live_database')) As fullprod,
pg_size_pretty(CAST(pg_database_size('live_database') - (SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables WHERE table_schema = 'binary_schema') As bigint)) As tobebackedup_size,
pg_size_pretty(CAST((SELECT SUM(pg_total_relation_size(table_schema || '.' || table_name) )
FROM information_schema.tables
WHERE table_schema = 'binary_schema') As bigint) ) As junk_size;
fullprod tobebackedup_size junk_size
-------- ----------------- ---------
On my database, using pgadmin, I can see a lot of pg_tast_temp_* and pg_temp_* table.
I understand the TOAST code is triggered when a row is wider than the TOAST_TUPLE_THRESHOLD [1]. I also understand the only way to shrink toast table is by using a vacuum full or even pg_dump.
Questions:
1 - If I take out 500GB of bytea data ( by updating the column seg_data and setting it to null ), will I get those 500GB of free disk space? or do I need to run vacuum full or either pg_dump?
2 - If I choose going ahead with VACUUM FULL, I have 3 streaming replication slaves, Will I need to run the vacuum full on them too?
3 - [2] vacuum full needs some free disk space as same size as the target table. It locks the table (cannot be used while running vacuum full) and a REINDEX might be needed after. AM I right?
Thanks in advanced for your help.
Patrick