Re: Is it ever necessary to vacuum a table that only gets inserts/updates? - Mailing list pgsql-general

On Sat, Nov 19, 2011 at 12:53 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

On 19/11/11 11:32, Adam Cornett wrote:

On Fri, Nov 18, 2011 at 2:56 PM, Gavin Flower
<GavinFlower@archidevsys.co.nz> wrote:

On 18/11/11 04:59, Tom Lane wrote:

Craig Ringer<ringerc@ringerc.id.au>  writes:

On Nov 17, 2011 1:32 PM, "Tom Lane"<tgl@sss.pgh.pa.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@postgresql.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.

Many years ago (when I was not so old as I am now) I had a junior analyst/programmer, who I Had asked to insert about 20k ordered records from a tape file into a VSAM file where its primary was the sort key of the records on tape.  He wrote a COBOL program that took 7 hours to do so.  Now, he and a Systems Programmer had each been on a 5 day VSAM course, and they managed to optimise the download to take only half the time.  I went and looked at a COBOL manual for the first time in a few yeas (I was a Systems Analyst, and above 'mere' programming), and suggested they change the file type from 'RANDOM' to 'INDEX-SEQUENTIAL' - the download now took a mere 70 seconds!

At that I time I was adept at designing index sequential files on ICL mainframes, then VSAM on FACOM mainframes. So I avoided databases, especially as it involved another (rather snooty) team to do anything with a database, and program code had to be changed when migrating form development to UAT and then to production. Once they insisted I create a data model of a system I had designed with 5 files – after 4 hours overtime 2 members of that team and myself came up with a data model; that exactly matched the 5 files and fields I had used...

I left the mainframe world many years ago, and did not fall into the temptation to get back into COBOL programming for Y2K.

Now my favourite software stack is Linux/PostgreSQL/JBoss – and now I not only design systems, I am expected to code them too!