WAL sequence number infinite?

Question

I am wondering if database WAL sequences are infinite? I guess most WAL records have a fix size for the WAL number? Is this a really big number that is so big that it just won't reach an end? This might be quite a waste of space? Or have the big DB-player invented a better method?

Or do they implement some logic to let the WAl start at 0 again? That might have heavy impact on many spots in the code...?

EDIT:

Impact: E.g. the recovery after a crash relies on the sequence number getting bigger along the timeline. If the sequence could start over the recovery could get confused.

Term WAL sequence number: WAL (Write Ahead Log a.k.a the transactional log that is guranteed to be on your disk before the application layer received that a transaction was successful). This log has a growing number to keep the database consitent e.g. in case of recovery by checking the WAL sequence number from the pages against the sequence number from the WAL.

Answer 1

I would not assume that every database implements the same strategy.

Speaking only for Oracle, the SCN (system change number) is a 48-bit number so an Oracle database can handle nearly 300 trillion transactions before hitting the limit. Realistically, that will take eons. Even if you could do 1 thousand transactions per second, the SCN wouldn't hit the limit for 300 billion seconds or roughly 9500 years. Now, there are various things that can cause the SCN to increment in addition to just doing transactions (famously the recent issue with hot backups and database links that caused a few users to exceed the database's checks for the reasonability of the SCN) so it won't really take 9500 years to hit the limit. But, realistically, it gives Oracle plenty of time to move to a 64-bit SCN some years down the line, buying everyone a few more centuries of functionality.

Answer 2

Like SQL Server, DB2 calls that counter a Log Sequence Number (LSN). IBM recently expanded the size of their LSN from six bytes to eight bytes, unsigned. The LSN is an ever-growing pointer that shows where in the log files that specific log record can be found. An eight byte LSN means that a DB2 database can write nearly 16 exbibytes of log records before running out of address space, at which point the contents of the database must be unloaded and copied into a new database.