Thread: No vacuum for insert-only database?

If I only insert data into a table, never update or delete, then I should never have to vacuum it.  Is that correct?

Thanks,
Craig

Craig A. James wrote:
> If I only insert data into a table, never update or delete, then I should
> never have to vacuum it.  Is that correct?

You still need to vacuum eventually, to avoid transaction Id wraparound
issues.  But not as often.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
>>If I only insert data into a table, never update or delete, then I should
>>never have to vacuum it.  Is that correct?
>
> You still need to vacuum eventually, to avoid transaction Id wraparound
> issues.  But not as often.

Thanks.  Any suggestions for what "not as often" means?  For example, if my database will never contain more than 10
millionrows, is that a problem?  100 million rows?  When does transaction ID wraparound become a problem? 

Craig

Craig A. James wrote:
> Alvaro Herrera wrote:
> >>If I only insert data into a table, never update or delete, then I should
> >>never have to vacuum it.  Is that correct?
> >
> >You still need to vacuum eventually, to avoid transaction Id wraparound
> >issues.  But not as often.
>
> Thanks.  Any suggestions for what "not as often" means?  For example, if my
> database will never contain more than 10 million rows, is that a problem?
> 100 million rows?  When does transaction ID wraparound become a problem?

Transaction ID wraparound will be a problem at a bit less than 2 billion
transactions.  So if you vacuum the table every 1 billion transactions
you are safe.  I suggest you read the "routine maintenance" section in
the docs; the wraparound issue is explained there.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Mar 13, 2006 at 09:19:32 -0800,
  "Craig A. James" <cjames@modgraph-usa.com> wrote:
> Alvaro Herrera wrote:
> >>If I only insert data into a table, never update or delete, then I should
> >>never have to vacuum it.  Is that correct?
> >
> >You still need to vacuum eventually, to avoid transaction Id wraparound
> >issues.  But not as often.
>
> Thanks.  Any suggestions for what "not as often" means?  For example, if my
> database will never contain more than 10 million rows, is that a problem?
> 100 million rows?  When does transaction ID wraparound become a problem?

I believe it is at billion (10^9).

Craig,

> Transaction ID wraparound will be a problem at a bit less than 2 billion
> transactions.  So if you vacuum the table every 1 billion transactions
> you are safe.  I suggest you read the "routine maintenance" section in
> the docs; the wraparound issue is explained there.

For reference, we calculated on a data warehouse with about 700 million
rows in the main fact table that we had 6 years until XID wraparound.
Mind you, that's partly because all of our rows were inserted in large
batches of 100,000 rows each.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco