Re: XIDs and big boxes again ...

"Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:

> my DB is facing around 600mio transaction a month. 85% of those contain at
> least some small modification so I cannot save on XIDs.

What's a "mio"? Assuming it's short for "million" I don't see the problem. The
transaction horizon is 2 *billion*. So as long as you can complete a vacuum of
every table once every 3 months you should be fine.

> my problem is that I cannot VACUUM FREEZE my 150k tables where most of the data
> is as I have a couple of thousand transactions a day modifying this data.

vacuum freeze doesn't take any locks. But in any case vacuum freeze would only
extend the vacuum horizon by 100k so it would hardly make any difference.

> but, i also have troubles to prevent myself from transaction wraparound as it
> is pretty boring to vacuum that much data under heavy load - with some useful
> vacuum delay it just takes too long.

How long is too long?

> i basically have to vacuum the entire database too often to get spare XIDs.

How often is too often?

> i suggest to introduce a --with-long-xids flag which would give me 62 / 64 bit
> XIDs per vacuum on the entire database.
> this should be fairly easy to implement.
> i am not too concerned about the size of the tuple header here - if we waste
> 500 gb of storage here i am totally fine.

Keep in mind you're proposing to make everything run 3% slower instead of
using that 3% i/o bandwidth headroom to run vacuum outside the critical path.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!