Is bigint large enough for an event log table?

Question

Now I know that bigint is 2^64; that is, more atoms than there are in the known universe. I shouldn't be worried, as my mere human brain simply can't get around the enormity of that number.

However, let's say that I record every change to every category, product and order in my system, from launch until the end of time. Should I be concerned about the performance of table writes before I worry about running out of primary key values? Should I record events of different priorities to different event tables? Will I run out of atoms on a hard drive before I run out of bigints? How big should I let an event log table get before I start archiving / clearing it out?

Answer 1

Even if every of your entries only had 1 byte, 2^64 entries would occupy around 18000000 TB on your hard drive, so I guess you shouldn't worry about this.

Answer 2

The way we handle this is by providing a log archiving functionality, that separates out the log table into separate databases by year, allowing us to reset the identity seed on our LogEvent table.

We also have different log tables, though only two main ones.

Answer 3

It is highly unlikely that you will ever run out of primary key values. However you may need to give consideration to how you want to access the log table to retrieve data. Use this to inform when you should be archiving or cleaning the data. If the log data is read frequently think about addding indexes to improve read performance but keep in mind that indexes need to be maintained for every record added.

Answer 4

"How big should I let an event log table get before I start archiving / clearing it out?"

Never clear the event logs -- the information has significant value.

However, when some manager insists that an archive is necessary, you can show the cost of storage vs. the cost of your time to (a) think about it, (b) get second and third opinions, and then (c) write a procedure to archive log records.

The cost of storage is plummeting. Your time is better spent on ANYTHING other than purging log records.

Bottom line: you have permission to stop wringing your hands. It's all good. You're not making a fundamental mistake.