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

On 20/11/11 11:57, Scott Marlowe wrote:
> 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.
Brings back memories... AAAARRRGGGGHHHHHHH!!!!!!!!!!!!!!!!!

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!