- Is it good practice to always have an auto-increment integer primary key? Reference
- German tank problem: Get the estimated number of so-and-so based on the IDs or the serial numbers of whatever that was.
- This isn’t just a URL thing, it’s also an ID thing, like you can see how many posts were added in so-and-so timeslot.
- Usually better to have a guaranteed row identifier.
- SQLite always makes a comment under the hood.
- UUIDs: Okay, but they take up too much space for the majority of tables. Also, indexing?
- If you have a business key, you have to add a unique index on that column to enforce rules.
- The sync is not as simple because you need to create an equivalence key?
- If the system grows so large that you need to shard, you can no longer use AI to produce a globally unique key.
- There are very few things that don’t change: even usernames and home addresses (given street naming changes) change.
- For gigantic ERPs, your integer autoincrement does:
- Easier to remember convention.
- You can JOIN tables without needing to check what the keys are.
- Just refer to IDs of existing tables.
- Examples of no identity column:
- In many-many cross reference tables. The foreign key columns can reference each table’s PK.
- When you plan on inserting and deleting in bulk on this table a lot.
- Distributed tables.
- Uniqueness across multiple rows in the DB.
- Primary Keys: IDs versus GUIDs: Reference
- GUID Pros:
- Unique across every table, every database, every server.
- Allows easy merging of records from different databases.
- Allows easy distribution of databases across multiple servers.
- You can generate IDs anywhere, instead of having a roundtrip to the database.
- Most replication scenarios require GUID columns anyway.
- GUID Cons:
- Takes up more memory.
- Cumbersome to debug.
- Should be partially sequential for best performance.
- GUID Pros:
Cloud Computing Trends: 2018 State of the Cloud Survey
- Public cloud increasing as top priority.
- Serverless is increasing: (12-21% adoption).
- Docker and Kubernetes is up.
- Ansible and Chef for configuration tools.
- Enterprises: Azure is up.
- Private cloud: VMware Sphere, OpenStack.
TIL, 2018-04-10, Browsing Systems Design on Youtube.
When I can’t connect to the router upstairs, I should just shut down (not restart) the machine. Sharding Connection Pool...