Re: reclaiming space from heavily used tables?

On Mon, Dec 18, 2017 at 1:03 PM, Rob Nikander <rob(dot)nikander(at)gmail(dot)com>
wrote:

> Hi,
>
> I've got a large table from which I'd like to completely reclaim space. I
> read the docs and it sounds like I can’t run `vacuum full`, because this
> table is accessed constantly and can’t have downtime. Assuming that’s true,
> what do you think of the following idea? Is there a better alternative?
>
> 1. Replace the table T with two tables T1 and T2 and a view T that is
> `select * from T1 union T2`.
> 2. Write only to T1, and slowly move records from T2 to T1.
> 3. When T2 is empty, redefine the view T to be simply `select * from T1`.
> 4. Recreate or vacuum full T2, so space is fully reclaimed.
> 5. Redefine view as the union select and repeat process going other
> direction from T1 to T2, as needed.
>
> I guess one question is whether I can do 1 and 3 (renaming tables,
> redefining views) atomically without disturbing concurrent select queries.
>
>
This is what you want: https://github.com/reorg/pg_repack

This has been around for many years and is a very trusted extension (when
will it be in core????).

> Rob
>
>
>