Is bigint big enough for an event log table?

Question

Now I know that bigint is 2 ^ 64; that is, more atoms than in the known universe. I do not have to worry, because my simple human brain simply cannot get around the scale of this number.

However, let’s say that I record every change in every category, product and order in my system, from the time it is launched to the end. Do I have to worry about table write performance before I worry about expiration of primary key values? Should I write events of different priorities to different event tables? Do I have enough atoms on my hard drive before I exit the fittings? How large should the event log table be made before I start archiving / clearing it?

Answer 1

Even if each of your records has only 1 byte, 2 ^ 64 records will occupy about 18,000,000 TB on your hard drive, so I think you should not worry about that.

Answer 2

If your application added an entry to the table once in a millionth of a second, it would have worked for more than five hundred thousand years before it ran out of keys.

Answer 3

"How big should the event log table be made before I start archiving / clearing it?"

Never clear event logs - information is significant.

However, when some manager insists on the need for an archive, you can show the cost of storage and the cost of your time to (a) think about it, (b) get a second and third opinion, and then (c) write a procedure for archiving journal entries .

The cost of storage drops sharply. Your time is better spent on POVERTY, other than clearing log entries.

Bottom line: you have permission to stop twisting your arms. Things are good. You are not making a fundamental mistake.

Answer 4

We handle this by providing log archiving functionality that separates the log table into separate databases by year, allowing us to reset the identification seed in our LogEvent table.

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

Answer 5

It is very unlikely that you will ever finish the primary key values. However, you may need to consider how you want to access the log table to retrieve the data. Use this to let you know when you should archive or clear data. If journal data is read frequently, consider adding indexes to improve read performance, but keep in mind that indexes should be maintained for each record added.