homerjones1941 wrote:
Using a Log Manager, I created 23 triggers that record changes to selected tables and fields. The tables that store those log entries use an auto-increment integer as primary key. A few of the tables that are being logged are very active. I worry that, over time, the integer field might reach its limit. I am considering replacing the integer PK with a GUID/UUID. Can anyone tell me if there is much of a performance hit when using GEN_UUID? Also, any tips/precautions would be appreciated.
Thomas Steinmaurer answers:
If you aren't in need for having a unique identifier across an entire system, e.g. for replication, I wouldn't care about GUIDs. IMHO integers are more easier to read and query in case you want to lookup a record.
Quoting the generator guide: You can insert 1000 records per second for 300 million years until you reach the limit of a 64-bit integer.
Although I have no real case performance evidence, I think integers are more efficient index key storage and query performance wise.
Ann W. Harrison adds:
In theory integers make better index keys because they are more susceptible to prefix compression. An index key in Firebird throws away all the bytes at the beginning that match the first bytes of the previous key, but preserves everything else. So if you have a key with immutable parts, you'll get a better index with the immutable parts first, the slowly changes parts next, and the rapidly changing bits last.