Обсуждение: 64 bit XID / Wraparound / Streaming Replication

Поиск
Список
Период
Сортировка

64 bit XID / Wraparound / Streaming Replication

От
Frank Foerster
Дата:

Hi all,

i am not really sure that this is the right forum, but anyway. We are running a 200 GB Postgres 9.1 64bit database. We do lots of writing and even more reading. The main database in office A is replicated on-site via streaming as a pure failover and also offsite via streaming to another office B over an asynchronous 100Mbit-Down/10-Mbit Up-Internetconnection. The other office is doing all reads from the read-only-streamed-to server and writes to the main-production-server in office A. During regular operations the delay is usually sub-second, which is perfectly fine for us.

But we have been bitten multiple times by the 32bit XID wraparound and the necessary vacuums that create a high amount of data-turnover that needs to go over the thin upload. We need to keep a very high count of wal-archives ( 8000+ ) to make sure all files are transferred before reuse and the transfers can take multiple days until both machines are in sync again.

The streaming replication is a very very cool feature and we are very enthusiastic postgres-users. I believe this problem was not really that severe before streaming replication was introduced, but is now becoming quite a problem.

My question is: do other users experience the same problem and what is their preferred way of dealing with it ?  Also, i am aware of a discussion a few years ago that dealt with the usage of 64bit XIDs, but led nowhere. Are there any more recent plans or discussions concerning 64bit XIDs ? ( I am not a postgres-developer and have no idea about the complexity or performance/space side-effects of such a change, i am purely argumenting from a users point of view).

Thanks,

Frank

Re: 64 bit XID / Wraparound / Streaming Replication

От
Tom Lane
Дата:
Frank Foerster <frankwordsten@googlemail.com> writes:
> My question is: do other users experience the same problem and what is
> their preferred way of dealing with it ?  Also, i am aware of a discussion
> a few years ago that dealt with the usage of 64bit XIDs, but led nowhere.
> Are there any more recent plans or discussions concerning 64bit XIDs ?

I don't believe anybody is actively thinking about 64-bit XIDs at
present; the additional row storage overhead is unpleasant to
contemplate.  I'd suggest looking at whether you couldn't reduce your
rate of consumption of XIDs, for instance by merging updates into a
single transaction when possible.

            regards, tom lane