pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNo vacuum for insert-only database?
6+ messages / 4 participants
[nested] [flat]
* No vacuum for insert-only database?
@ 2006-03-13 15:02 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 6+ messages in thread
From: Craig A. James @ 2006-03-13 15:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: No vacuum for insert-only database?
@ 2006-03-13 15:09 Alvaro Herrera <alvherre@commandprompt.com>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2006-03-13 15:09 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: No vacuum for insert-only database?
@ 2006-03-13 17:19 Craig A. James <cjames@modgraph-usa.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 6+ messages in thread
From: Craig A. James @ 2006-03-13 17:19 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-performance
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?
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: No vacuum for insert-only database?
@ 2006-03-13 17:41 Alvaro Herrera <alvherre@commandprompt.com>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2006-03-13 17:41 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: No vacuum for insert-only database?
@ 2006-03-13 18:52 Bruno Wolff III <bruno@wolff.to>
parent: Craig A. James <cjames@modgraph-usa.com>
1 sibling, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2006-03-13 18:52 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-performance
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).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: No vacuum for insert-only database?
@ 2006-03-13 19:16 Josh Berkus <josh@agliodbs.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2006-03-13 19:16 UTC (permalink / raw)
To: pgsql-performance; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Craig A. James <cjames@modgraph-usa.com>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-13 19:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-13 15:02 No vacuum for insert-only database? Craig A. James <cjames@modgraph-usa.com>
2006-03-13 15:09 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-13 17:19 ` Craig A. James <cjames@modgraph-usa.com>
2006-03-13 17:41 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-03-13 19:16 ` Josh Berkus <josh@agliodbs.com>
2006-03-13 18:52 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox