Re: Interesting Slashdot article that attracted a lot of comments about pg

On Sun, Jul 10, 2011 at 5:35 AM, Gavin Flower
<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:

> I would be interested in the developers' comments about the XID problem (not
> that it is likely to affect my usage of pg!).
>
> http://developers.slashdot.org/story/11/07/09/1256241/Facebook-Trapped-In-MySQL-a-Fate-Worse-Than-Death
> [...]
> Re:Oracle vs Facebook?
> by evilviper (135110) Alter Relationship on 2011-07-10 14:06 (#36708666)
> Journal
>
> I agree that Postgres is vastly superior to MySQL and competitive with
> enterprise-class databases. We're using Postgres for databases in excess of
> a terabyte ourselves, for large-scale production purposes, so I know it
> works... HOWEVER, I think the XID issue is significant, not getting any
> developer attention, and is something you're just about guaranteed to
> run-into with terabyte databases.
>
> You see, even if you run Postgres on a 64-bit platform, you're limited to
> XIDs of 2^31, or 2 billion rows. Now, to prevent this causing a problem,
> Postgres' VACUUM process will start running when you get close to 1 billion,
> killing your performance. Of course there are a dozen possible ways to
> workaround this, but none are trivial or work perfectly and consistently.
> This is postgres' biggest scalability limitation, ahead of even the
> imperfect replication options available.
> [...]
>

I've posted the following response. Thanks for bringing this to the
attention of the list.

Thanks for your positive comments about PostgreSQL. I wanted to
respond in detail to the points made about limitations, which aren't
correct.

You are correct that PostgreSQL uses only 2^31 XIDs or transaction
ids. That means every billion write transactions PostgreSQL needs to
perform a VACUUM operation. You mention that kills performance.
PostgreSQL allows you to run this as a low priority background job,
which continues to allow reads and writes, yet runs at a deliberately
slower pace to avoid impact on system resources. All of that is
automatic, with the VACUUM task even cancelling itself if it really
does get in the way of any user request. The way this works has
received significant improvements in performance and operability in
every annual release for many, many years. The benefit of all of this
is that read queries aren't blocked by write queries - an incredibly
important feature for general purpose computing.

The number of XIDs has no effect whatsoever on the number of rows in a
database. There is no limit of 2 billion rows - in fact there is no
limit to the number of rows in a table at all. Currently, tables are
limited to 32TB, but we can increase that if people find that a
limiting factor. There *is* an object PostgreSQL called an OID, that
acted somewhat similar to that described by the poster but any
limitation there was removed more than 7 years ago. Many PostgreSQL
users have installations above a Terabyte in size.

Replication has been one of the areas that has received significant
and consistent developer attention across many years. Starting in 9.0
we have native binary replication, with options that make it the full
equivalent of the best options in Oracle Enterprise Edition, including
pay for options of Active Data Guard. Synchronous replication will be
available in PostgreSQL 9.1, out real soon now. PostgreSQL 9.2 will
have cascaded replication, multiple performance options and other
features, many of which are in advance of what is available in pay-for
commercial systems. We think this area so important that there is a
dedicated conference on this aspect of PostgreSQL.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services