Обсуждение: Vacuuming DVs with few/no updates?

Hi all,

Just wondering about how useful the vacuumdb functionality is for a database
that pretty much only ever gets inserts/selects.

I've got a database that is intended for next-to-no deletion of records, which
is where I see the vacuum facility mostly intended for. I.e., while there'll be
the occasional record edits, these edits will primarily be of the form of
adjusting integer records (and therefore shouldn't adjust any space requirements
for a row).

I'm thinking that with this database I'll configure vacuumdb to only run once a
month or so. But before I do that I want to make sure I'm not missing some other
important functionality that it does...

Cheers,

-Preston de Guise.

--
Oops.

Preston <unsane@idl.com.au> writes:
> Just wondering about how useful the vacuumdb functionality is for a database
> that pretty much only ever gets inserts/selects.

> I've got a database that is intended for next-to-no deletion of
> records, which is where I see the vacuum facility mostly intended
> for. I.e., while there'll be the occasional record edits, these edits
> will primarily be of the form of adjusting integer records (and
> therefore shouldn't adjust any space requirements for a row).

You have an important misconception lurking in there.  In Postgres,
an UPDATE is equivalent to INSERT (of the new row version) followed by
DELETE (of the old row version).  Therefore, it creates dead rows that
need to be reclaimed by VACUUM, just as much as DELETE would do.

But yeah, if you have very very few updates or deletes then you don't
need to vacuum very often.  (You might possibly need to ANALYZE more
often than you VACUUM, if statistics like column min/max values are
changing significantly due to the insertion traffic.)

> I'm thinking that with this database I'll configure vacuumdb to only
> run once a month or so. But before I do that I want to make sure I'm
> not missing some other important functionality that it does...

You should read the discussion of transaction wraparound in the Admin
Guide's chapter about routine maintenance (specifically VACUUM ;-)).
Once-a-month vacuum is fine if your total transaction load doesn't
exceed 1 billion per month ...

            regards, tom lane

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> Preston <unsane@idl.com.au> writes:
> > Just wondering about how useful the vacuumdb functionality is for a
> > database
> > that pretty much only ever gets inserts/selects.
>
> > I've got a database that is intended for next-to-no deletion of
> > records, which is where I see the vacuum facility mostly intended
> > for. I.e., while there'll be the occasional record edits, these edits
> > will primarily be of the form of adjusting integer records (and
> > therefore shouldn't adjust any space requirements for a row).
>
> You have an important misconception lurking in there.  In Postgres,
> an UPDATE is equivalent to INSERT (of the new row version) followed by
> DELETE (of the old row version).  Therefore, it creates dead rows that
> need to be reclaimed by VACUUM, just as much as DELETE would do.

Ah OK - thanks for explaining that to me. It changes my outlook quite a bit.

However, the inserts will still outnumber the updates on a scale of about 30:1.

> But yeah, if you have very very few updates or deletes then you don't
> need to vacuum very often.  (You might possibly need to ANALYZE more
> often than you VACUUM, if statistics like column min/max values are
> changing significantly due to the insertion traffic.)

OK, will look into that side of it.

> > I'm thinking that with this database I'll configure vacuumdb to only
> > run once a month or so. But before I do that I want to make sure I'm
> > not missing some other important functionality that it does...
>
> You should read the discussion of transaction wraparound in the Admin
> Guide's chapter about routine maintenance (specifically VACUUM ;-)).
> Once-a-month vacuum is fine if your total transaction load doesn't
> exceed 1 billion per month ...

Thanks - will find and read that... Once again caught by the "too much to do not
enough time" bug.

Does that transaction load include selects?

Due to automated web-based refreshing of views of tables I'd expect the selects
to number around 2,000 to 10,000 per day... I know that's still not anywhere
near a billion but it's to the point of a more interesting amount if transaction
load includes selects...

Cheers,

-Preston.

--
Oops.

Preston <unsane@idl.com.au> writes:
> Does that transaction load include selects?

Yes.

            regards, tom lane