The Problem with Auto-Incrementing IDs
When building a B2B SaaS platform at Smart Tech Devs, using standard auto-incrementing integers (1, 2, 3) for your primary keys is an enormous security liability. If a user sees /invoices/405 in their URL, they immediately know you only have 405 invoices in your system. Worse, malicious actors can easily write a script to scrape /invoices/406, 407, and 408 (an Insecure Direct Object Reference attack).
The industry standard solution for the last decade has been UUIDs (Universally Unique Identifiers). A UUIDv4 looks like 9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d. It is completely random and impossible to guess. But at massive scale, UUIDs introduce a catastrophic performance flaw at the database level.
The B-Tree Fragmentation Catastrophe
PostgreSQL and MySQL use B-Tree structures for their primary key indexes. B-Trees are designed for sequential data. When you insert row #1, then row #2, the database simply appends the new data to the end of the tree. It is blazingly fast.
