From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "'Patrick B *EXTERN*'" <patrickbakerbr(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question about TOAST table - PostgreSQL 9.2 |
Date: | 2017-02-28 14:33:24 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B53A0078E@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-02-28 14:40:14 | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Previous Message | Tom Lane | 2017-02-28 14:07:19 | Re: Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling |