Re: Deleting schema - saving up space - PostgreSQL 9.2

On Wed, Mar 16, 2016 at 2:27 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

>
>
> On 17 March 2016 at 10:21, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Wed, Mar 16, 2016 at 1:59 PM, drum(dot)lucas(at)gmail(dot)com <
>> drum(dot)lucas(at)gmail(dot)com> wrote:
>>
>>>
>>> 1 - The problem here is that a VACUUM FULL will lock all the DB to
>>> wirte, am I right? My DB is 1.7 TB, so it will take a while and the System
>>> can't be offline
>>>
>>> 1. Migrate the files to the NFS server
>>> 2. Delete the schema from the MASTER DB
>>> 3. Put the slaves into read-only servers
>>> 4. Run Vacuum FULL into the MASTER DB
>>> 5. Once the vacuum is done, do a DUMP from the MASTER to the slaves
>>> (excluding the GORFS schema of course)
>>>
>>>
>> ​If you are removing the entire object there should be no cause to VACUUM
>> FULL. A vacuum full reclaims unused space ​*within a given relation.*
>>
>> ​Both DROP TABLE and TRUNCATE have the effect of (near) immediately
>> ​freeing up the disk spaced used by the named table and returning it to the
>> operating system.
>>
>> ​You want to use VACUUM FULL tablename; if you remove a significant chuck
>> of a table using DELETE or UPDATE and want to reclaim the spaced that was
>> occupied by the older version of the ​row within "tablename".
>>
>> VACUUM FULL; simply does this for all tables - I'm not sure when locks
>> are taken and removed. likely only the actively worked on tables are
>> locked - but the I/O hit is global so targeted locking only buys you so
>> much.
>>
>> David J.
>>
>>
>>
>
> I see..
>
> so in your opinion a DROP SCHEMA and maybe a VACUUM (not full) would be
> enough?
>
>
​I don't deal with Hot Standby's in my day-to-day but if you DROP SCHEMA
all of the spaced consumed by indexes and tables in that schema will be
freed. The vacuum might make a small difference in performance on the
system catalogs (pg_class, stats, etc) that were updated but with respect
to the dropped schema there won't be anything present there for vacuum to
touch.

Create and populate a dummy table in a test setup, measure the HD space
taken in PGDATA, then drop it and measure again to see it in action.

I've only done this using "TRUNCATE" - I've got a system with space
constraints a the same kind of "file data" table and freed up around 20GB
with a single fast truncate (though ensuring FKs wouldn't be a problem was
fun...).

David J.