Re: R: Vacuum full: alternatives?

El 20/06/16 a las 11:52, Jeff Janes escribió:
> On Mon, Jun 20, 2016 at 3:13 AM, Andreas Kretschmer
> <andreas(at)a-kretschmer(dot)de> wrote:
>>
>>
>> Am 20.06.2016 um 11:43 schrieb Job:
>>>
>>> Hi Andreas,
>>>
>>>> I would suggest run only autovacuum, and with time you will see a not
>>>> more growing table. There is no need for vacuum full.
>>>
>>> So new record, when will be pg_bulkloaded, will replace "marked-free"
>>> location?
>>
>> exactly, that's the task for vacuum
>
> Are you sure that that is the case with pg_bulkload specifically? It
> bypasses the shared buffers, so it would not surprise me if it
> bypasses the free space map as well, and thus always appends its data
> to the end of the table.

I didn't do a super intensive check of pg_bulkload, but AFAICS it does
batches of COPY with PQputCopyData.

If the relation has free space which was reclaimed by vacuum/autovacuum
it will try to use that space and not extend the relation (which is more
expensive). This happens if used space on those pages is lower than the
fillfactor set for that table.

IMO, he should start setting autovacuum more aggressively, or running
aggressive vacuum, and see how that works.

Also, install pgstattuple and check free space on the relation to see
how much dead tuples and free space there is.

Those are my 2 cents.

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services