Re: Is it ever necessary to vacuum a table that only gets inserts/updates?

On Sat, Nov 19, 2011 at 12:53 PM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
> On 19/11/11 11:32, Adam Cornett wrote:
>
> On Fri, Nov 18, 2011 at 2:56 PM, Gavin Flower
> <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>>
>> On 18/11/11 04:59, Tom Lane wrote:
>>>
>>> Craig Ringer<ringerc(at)ringerc(dot)id(dot)au>  writes:
>>>>
>>>> On Nov 17, 2011 1:32 PM, "Tom Lane"<tgl(at)sss(dot)pgh(dot)pa(dot)us>  wrote:
>>>>>
>>>>> If it's purely an insert-only table, such as a logging table, then in
>>>>> principle you only need periodic ANALYZEs and not any VACUUMs.
>>>>
>>>> Won't a VACUUM FREEZE (or autovac equivalent) be necessary eventually,
>>>> to
>>>> handle xid wraparound?
>>>
>>> Sure, but if he's continually adding new rows, I don't see much point in
>>> launching extra freeze operations.
>>>
>>>                        regards, tom lane
>>>
>> Just curious...
>>
>> Will the pattern of inserts be at all relevant?
>>
>> For example random inserts compared to apending records.  I thought that
>> random inserts would lead to bloat, as there would be lots of blocks far
>> from the optimum fill factor.
>>
>>
>> Regards,
>> Gavin
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> I might be wrong (I'm sure Tom will correct me if so), but Postgres does not
> store tuples in an ordered format on disk, they are on disk in the order
> they are inserted, unless the table is re-ordered by cluster, which only
> does a one time sort.
> Table bloat (and the table fill factor) are usually associated with deletes
> and updates.  If you delete a row, or update it so that it takes up less
> room (by say removing a large text value) then postgres could use the now
> free space on that page to store a new tuple.
> -Adam
>
> HI Adam,
>
> I suspect that you are right - noiw I come to think of it- I think I got
> caught out by the ghost of VSAM creeping up on me )You seriously do NOT want
> to know about IBM's VSAM!).

Careful, on a list with as many old timers as this one, you may be
sending that message to the guy who wrote the original implementation.
:) I only go as far back as Rexx and JCL and RBase 5000, but never
used VSAM. ISAM yes.