Re: R: Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 7:23 AM, Martín Marqués <martin@2ndquadrant.com> wrote:
> El 20/06/16 a las 09:50, Melvin Davidson escribió:
>>
>>
>>>but it won't let it grow too (or am I missing something).
>>
>> Yes, you are missing something. By partioning and {Vacuum Full only the
>> table with data no longer needed}, the rest of the data remains
>> available to the users
>> AND space is reclaimed by the O/S, so it's the best of both worlds.
>
> That's not entirely true. Think about a SELECT which has to scan all
> child tables.
>

Yes, for the partitioning to be a good option, you would probably have
to arrange it such that you can prove that all tuples in a given
partition are eligible for deletion (or have already been deleted),
and then either truncate or dis-inherit the partition.  That still
requires a stringent lock, but it is only held for a very short time.

> Your are also adding another layer of complexity to the system.

I think that using pg_bulkload adds more complexity to the system than
partitioning would.  I wonder if they really need to use that, or if
they just picked it over COPY because it sounded like a free lunch.

I've just tested pg_bulkload with the default settings, and it
definitely isn't using the fsm to re-use freed space in the table.  If
they use WRITER = BUFFERED it would, though.

Cheers,

Jeff