Search Postgresql Archives

Re: vacuum - reclaiming disk space.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Just to throw some extreme ideas out there, you could stand up a postgres on some other server, pg_dump your current database and use that dump to build up your second postgres. Use that new postgres when your system goes live again after downtime. Restoring from a dump means your database would not take up as much space since I assume your issue is that all that space was allocated to postgres for the purposes of your large number of table updates.  

On Thu, Mar 17, 2016 at 11:34 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:


On Thu, Mar 17, 2016 at 10:57 AM, bricklen <bricklen@xxxxxxxxx> wrote:
On Thu, Mar 17, 2016 at 7:27 AM, Mike Blackwell <mike.blackwell@xxxxxxx> wrote:
I have a large table with numerous indexes which has approximately doubled in size after adding a column - every row was rewritten and 50% of the tuples are dead.  I'd like to reclaim this space, but VACUUM FULL cannot seem to finish within the scheduled downtime.

Any suggestions for reclaiming the space without excessive downtime?

pg_repack is a good tool for removing bloat. https://github.com/reorg/pg_repack


"I have a large table with numerous indexes :
My first thought is, "DEFINE NUMEROUS". How many indexes do you actually have? How many of those indexes are actually used? In addition to VACUUMing the table, it also needs to go through every index you have.
So find out if you have any unneeded indexes with:

SELECT n.nspname as schema,
       i.relname as table,
       i.indexrelname as index,
       i.idx_scan,
       i.idx_tup_read,
       i.idx_tup_fetch,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(i.indexrelname))) AS index_size,
       pg_get_indexdef(idx.indexrelid) as idx_definition
  FROM pg_stat_all_indexes i
  JOIN pg_class c ON (c.oid = i.relid)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
 WHERE i.idx_scan = 0
   AND NOT idx.indisprimary
   AND NOT idx.indisunique
 ORDER BY 1, 2, 3;

Then drop any index that shows up!

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.




--
Robert McAlpine
DevOps Engineer
6506 Loisdale Road
Springfield, VA 22150

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux