Database Table Indexes
An important aspect of database design is the creation of indexes, specially those ones that are unique. Indexes are use to improve the speed of data retrieval operations. It affects the performance of write operations, such as insert, update, and delete, because the indexes need to be updated whenever the data in the table changes.
Types
There are several types of indexes that can be used in a database table:
UUIDs
UUIDS are a 128-bit number used to uniquely identify objects in a computer.
In a database, UUIDs can be used as primary keys for tables. But the are some issues to take a look at:
- Performance: to organize indexes, the database needs to sort the data. This is a problem when using UUIDs because they are not sequential.
- Storage: UUIDs are 128 bits long, which is more than the 32 bits used by by auto-incrementing IDs. In a large database, this can lead to a significant amount of storage.
On the other hand, UUIDs are unique across different databases and can be generated in different machines without the need to coordinate with each other.
Auto-incrementing IDs
Auto-incrementing IDs are a good choice for primary keys in a database. They are sequential and can be used to organize indexes quickly and efficiently. They don't take up much storage space.
It is a natural sequence which is easy to predict and identify. Ideally, when those ids are not exposed to the user.
UUIDsv7
UUIDv7 is a new version of UUIDs that are sequential, solving performance issue on insert new records. It offers the best of both worlds: the uniqueness of UUIDs and the performance of auto-incrementing IDs.
ULID
ULID offers a timestamp prefix followed by random characters. It allows efficient sorting and is more compact than UUIDs.