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

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 <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <http://www.postgresql.org/docs/current/interactive/sql-cluster.html>,
> 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!).

Regards,
Gavin