Question about TOAST table - PostgreSQL 9.2

Started by Patrick Babout 9 years ago2 messagesgeneral
Jump to latest
#1Patrick B
patrickbakerbr@gmail.com

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

-------- ----------------- ---------

4302 GB 489 GB 2813 GB

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]https://www.postgresql.org/docs/9.2/static/storage-toast.html. 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]https://wiki.postgresql.org/wiki/VACUUM_FULL 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

[1]: https://www.postgresql.org/docs/9.2/static/storage-toast.html
[2]: https://wiki.postgresql.org/wiki/VACUUM_FULL

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Patrick B (#1)
Re: Question about TOAST table - PostgreSQL 9.2

Patrick B wrote:

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.

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?

You'll need VACUUM (FULL) or dump/restore.

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?

No, and indeed you cannot.
The changes made by VACUUM on the primary will be replicated.

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?

It locks the table for all concurrent access, but a REINDEX is not necessary, as the
indexes are rewritten as well.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general