Thread: Wrap-around XID's

Hello,

Two years ago, I lead a smallish project which implemented a PostgreSQL v7.3 database on the Linux platform. The size of the database is only about 50MB, but all has been well. So, I have become a PostgreSQL advocate, preaching its value wherever I go.

However, my company is now planning a large scale enterprise database system with accompanying API's and web UI's. Of course, I proposed we use PostgreSQL again, but one of our technology partners objected.

The objection is the wrap-around problem with XID's. We read together the v8.0 docs and saw the work-around solution (section 21.1.3), using the VACUUM command. However, our technology partner still warned against using PostgreSQL.

What I need:

1) More detail on the use of XID's by PostgreSQL, so I can better understand the problem.

2) An explanation that the work-around is sufficient.

3) I see that PostgreSQL has an 8-byte data type (bigint) which would all but the solve the problem. Are there any plans to offer 8-byte XID's in future versions of PostgreSQL?

Thank you very much for your help,

-------
Michael Tamburro
Director, Information Technology Department
Technologue, Inc.
www.technologue.com
610-430-0104 x29

> What I need:
>
> 1) More detail on the use of XID's by PostgreSQL, so I can better
> understand the problem.

The problem is a phantom as long as you perform routine vacuuming. This
is something you have to do anyway so it isn't that big of a deal.

> 2) An explanation that the work-around is sufficient.

Just vacuum :)

>
> 3) I see that PostgreSQL has an 8-byte data type (bigint) which would
> all but the solve the problem. Are there any plans to offer 8-byte XID's
> in future versions of PostgreSQL?

I don't know about the 8-byte xid but again, as long as you vacuum it
isn't an issue.

Sincerely,

Joshua D. Drake


>
> Thank you very much for your help,
>
>
>
> -------
> Michael Tamburro
> Director, Information Technology Department
> Technologue, Inc.
> www.technologue.com
> 610-430-0104 x29
>


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage,  and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com

On Tue, 8 Feb 2005, Mike Tamburro wrote:

> Hello,
>
> However, my company is now planning a large scale enterprise database
> system with accompanying API's and web UI's. Of course, I proposed we use
> PostgreSQL again, but one of our technology partners objected.
>
> The objection is the wrap-around problem with XID's. We read together the
> v8.0 docs and saw the work-around solution (section 21.1.3), using the
> VACUUM command. However, our technology partner still warned against using
> PostgreSQL.

Then they have no experience with PostgreSQL, IMHO.

>
> What I need:
>
> 1) More detail on the use of XID's by PostgreSQL, so I can better
> understand the problem.

XIDs provide two mechanisms: they are used to determine data visibility
for concurrent transactions and they allow us to atomically mark data as
commited or aborted.

> 2) An explanation that the work-around is sufficient.

VACUUMing every 500 million transactions is sufficient. Most systems I've
seen would not top 200 transactions per second. This gives you, say, 30
days before you need to vacuum.

I have only come across one installation which had issues with XID
wraparound and that was because they were attempting to load 1 TB a day
and had no time to vacuum.

>
> 3) I see that PostgreSQL has an 8-byte data type (bigint) which would all
> but the solve the problem. Are there any plans to offer 8-byte XID's in
> future versions of PostgreSQL?

It is not as trivial as that. As mentioned abov, we must store a COMMIT or
ABORT bit for transactions. These are stored in a sort of bitmap. If we
had 64 bit XIDs and told people they no longer needed to vacuum (for the
purpose of XID wraparound) we have to accept that the bitmap will grow
toward 2^61 bytes in size -- a very large number. This means, I believe,
that we need a more novel approach to implementing 64 bit XIDs.

Putting that to one side, the fact is that 32 bit XIDs only present a
problem to people who cannot vacuum their systems.

Gavin

On Wed, Feb 09, 2005 at 10:34:01AM +1100, Gavin Sherry wrote:

Gavin,

> Putting that to one side, the fact is that 32 bit XIDs only present a
> problem to people who cannot vacuum their systems.

This reminds me: how does Fujitsu-supported Postgres not need vacuum?
Can you make any comment or description, beyond what's on the website?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"All rings of power are equal,
But some rings of power are more equal than others."
                                 (George Orwell's The Lord of the Rings)

On Tue, 8 Feb 2005, Alvaro Herrera wrote:

> On Wed, Feb 09, 2005 at 10:34:01AM +1100, Gavin Sherry wrote:
>
> Gavin,
>
> > Putting that to one side, the fact is that 32 bit XIDs only present a
> > problem to people who cannot vacuum their systems.
>
> This reminds me: how does Fujitsu-supported Postgres not need vacuum?
> Can you make any comment or description, beyond what's on the website?

I don't have any information on how Fujitsu's storage manager does this. I
presume they have some system to say that all current transactions see
this tuple as dead and then mark the space as reusable. It might be done
at the block level too. I do not have access to the source code so I can
only guess.

Another point worth noting is that they do have 64 bit XIDs.

Gavin