Re: PostgreSQL 64 Bit XIDs - Transaction IDs

On Fri, Mar 23, 2012 at 11:35 AM, Gerhard Wiesinger <lists(at)wiesinger(dot)com> wrote:
> On 23.03.2012 11:16, Jan Kesten wrote:
>>
>> On 23.03.2012 06:45, Gerhard Wiesinger wrote:
>>
>>> With a database admin of a commercial database system I've discussed
>>> that they have to provide and they also achieve 2^31 transactions per
>>> SECOND!
>>> As PostgreSQL uses transaction IDs (XIDs) in the range of 2^31 they
>>> would turn around in about one second.
>>
>> Wow. What application issues that much transactions? And what is the
>> database system that can handle that? I can't think of a single machine
>> capable of this - and hardy believe postgresql can came close. 2^31
>> transactions mean that a single one lasts 0.5ns. Even the fastest
>> DDR3-2133 has cycle times of 4ns.
>>
>> I have seen a database monster in action - 43 trillion (academic)
>> transactions per day, but that's only 5*10^8 transactions per second,
>> under a quarter of 2^31 per second.
>>
>> So, I can't answer your question - but you triggered my curiosity :-)
>
>
> I'm just answering in one of the posts ...
>
> Ok, talked again to the admin and he was wrong with 3 zeros and per minute
> .... :-)
> So corrected data are: 1 Mio transaction per minute. 1Mio/60s=16666
> transactions/s
>
> 2^32*60/1E6=257698s to get to the XID overflow which are 2.98 days (~1.5
> days when 2^31 with signed int is the border)
>
> So in that time autovacuum is triggered. Nevertheless we are getting into
> the area where XID overflow is an issue in the near future.
>
> In your example with 5E8 transactions per second overflow will be in 4s
> (2^31) or 8s (2^32) ...
>
> So I think XID overflow should be planned for one of the next PostgreSQL
> releases.

two mitigating factors:
1. read only transactions do not increment xid counter
2. xid wraparound counter is per table.

merlin