R: R: Vacuum full: alternatives?

Hello,
very interesting comments and contributions, thank you.

>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.

So with WRITER = BUFFERED it should be slower but free-marked space should be reused again?

Thank you!
Francesco

________________________________________
Da: Jeff Janes [jeff(dot)janes(at)gmail(dot)com]
Inviato: lunedì 20 giugno 2016 17.51
A: Martín Marqués
Cc: Melvin Davidson; Rakesh Kumar; Job; pgsql-general(at)postgresql(dot)org
Oggetto: Re: R: [GENERAL] Vacuum full: alternatives?

On Mon, Jun 20, 2016 at 7:23 AM, Martín Marqués <martin(at)2ndquadrant(dot)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.

Cheers,

Jeff