Performance Benchmarking Cloudflare D1 (SQLite): Integer vs UUID v4 vs ULID

The main goal is comparing execution time for the users (1M records) and posts (10M records) tables with the id column as integer, UUID v4, and ULID.

Prehistory

As a frontend developer, I mostly worked with MongoDB in my pet projects. Currently, I'm working on a bigger product. That's a reason why I decided to rethink my decisions regarding the chosen infrastructure, including the database. I'm migrating to Cloudflare infra. Shortly, because it has friendly DX, transparent pricing, wider free packages, services I need, etc. So, the first step is migrating from usual MongoDB to the Cloudflare D1 database based on SQLite. As a frontend developer, I have no idea how to write good SQL queries. During the investigation, I found posts about Prisma and how it's slow. That's funny. My search brought me to Drizzle. It has pretty nice documentation, it's free and open-sourced, it's faster than Prisma regarding the provided benchmarks, I found a lot of good feedback about it. When I started writing my first schemas, I started thinking about the architecture, security, and performance aspects from the beginning, just because I usually do that. And the first point is that I'm using a Nuxt application. I have a TypeScript interface provided by Drizzle from a table schema. This interface and data object type are shared across the server, API, and storefront components. It means, all the fields will be visible in the Network tab on request once it's fetched. I want to avoid cases when I get /api/users/1 with an explicit id field due to aesthetic and security reasons (yes, I know about session tokens). From the past while I was working with MongoDB, I remember that typically the _id field auto-generated by MongoDB uses UUID format. Not sure about the exact UUID version, but I'm sure it's not just an auto-incremented integer. I was just interested in whether it can negatively affect the performance of the SQLite database.

Preparation for performance benchmarking

I'm using a Nuxt 3 application (with Nitro included), nitro-dev-cloudflare, wrangler, Miniflare for D1 local development, Drizzle ORM, Drizzle Kit, Drizzle Studio, and Drizzle Seed functionality. I decided to seed the database with 1 million users and 10 million posts. But found out that D1 has a pretty limited request row. In my case, I could see only around 10-15 users and posts, then I just got the error:

too many SQL variables at offset 425: SQLITE_ERROR

Terminal error stack showing "too many SQL variables at offset 443: SQLITE_ERROR"

So, instead of using the drizzle-seed package, I just manually seeded the tables in a for loop. Also, I had to increase RAM memory to 10 GB (default is 2 GB) for Node in my ~/.zshrc:

export NODE_OPTIONS="--max-old-space-size=10000"

Node.js garbage collection log ending with "FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory"

Request example

It's pretty similar for all cases:

Drizzle findFirst code that fetches a user by id together with their posts

Performance benchmarking — Integer

Response time is ~480ms:

Nuxt DevTools server routes panel: GET /api/users/99999 with an integer id, request finished in 480 ms

Performance benchmarking — UUID v4

Response time is ~1789ms:

Nuxt DevTools server routes panel: GET /api/users/uuid with a UUID v4 id, request finished in 1769 ms

Performance benchmarking — ULID

Response time is ~878ms.

In comparison with UUID v4 (36 chars), ULID has fewer chars (26 chars), but it's lexicographically sortable, time-based. I guessed it has to be better for performance and database size if I want to keep UUID flow for the id column.

Nuxt DevTools server routes panel: GET /api/users/uuid with a ULID id, request finished in 878 ms

Conclusions

If you really care about performance and expect 1M+ records in tables on your product, I guess it'd be better to stay around an auto-incremented integer. If not, it doesn't matter what you choose.
If you want to have the UUID flow, pick ULID over UUID v4. Also, take a look at UUID v7. Feel free to use the repo below, to test other approaches, including uuidv7.
drizzle-seed needs adjustments for D1 for seeding more than 20 records.
Drizzle Studio is pretty fast, clear, and it has nice UX. Not a phpMyAdmin if you are as old as me :D (I'm 29, LOL)

The best option

Andrii Sherman suggested to me the best approach. I can create a custom type via Drizzle ORM that returns an integer in the same way for the id column. So, just go forward with an auto-incremented integer primary key. But! It can be encoded only on select (no need for insert) to hide the exact value when returned from the API.

Screenshot explanation:

Code snippets in an editor: integer primary keys in the Drizzle schema, the generated SQL migration, Hashids encode and decode helpers, a custom publicId type, and a findFirst query using decodeId

Database example:

Drizzle Studio with the users table of auto-incremented integer ids (1.00K users, 5.00K posts) and one user's posts expanded

API response:

Get all users with their posts (limited): Nuxt DevTools response of GET /api/users/int where the user and post ids are encoded strings

Get a user by ID (already encoded) with their posts (limited): Nuxt DevTools response of GET /api/users/int/pxe67gJd8J9YQobj

So, it's equally as fast as just using an integer. In addition, I encoded it via the Hashids NPM package. It means, it could be decoded only when you know the exact value of a decoding secret key. It solves what I meant. Furthermore, it seems much better in the API response, but still being the fastest option as needed due to the performance requirements.

There's a related commit .

There's a related GitHub repository :

GitHub card of serhii-chernenko/sqlite-id-uuid-performance-test: load testing for Cloudflare D1 SQLite with the id column as a text primary key instead of auto-incremented integer

Subscribe to my GitHub and LinkedIn profiles here to stay tuned for future investigations and insights about a product I'm working on.

Credits

Huge thanks to Andrii Sherman for the time, assistance, useful tips, and for the detailed information that was pretty handy for this investigation.