UUID vs bigint in PostgreSQL: choosing IDs that scale

Why ID choice matters more than it seems

Every row in a PostgreSQL table needs a stable way to be found again. That’s what an ID does: it uniquely identifies a record, it’s usually the primary key, and it becomes the glue for relationships. Other tables store it as a foreign key, queries join on it, and apps pass it around as the handle for “that customer”, “that invoice”, or “that support ticket”.

Because IDs end up everywhere, the choice isn’t just a database detail. It shows up later in index size, write patterns, query speed, cache hit rates, and even product work like analytics, imports, and debugging. It also affects what you expose in URLs and APIs, and how easy it is for a mobile app to store and sync data safely.

Most teams end up comparing UUID vs bigint in PostgreSQL. In plain terms, you’re choosing between:

• bigint: a 64-bit number, often generated by a sequence (1, 2, 3...).
• UUID: a 128-bit identifier, often random-looking, or generated in a time-ordered way.

Neither option wins in every case. Bigint tends to be compact and friendly to indexes and sorting. UUIDs are a strong fit when you need globally unique IDs across systems, want safer public IDs, or expect data to be created in many places (multiple services, offline mobile, or future sharding).

A useful rule of thumb: decide based on how your data will be created and shared, not just how it will be stored today.

Bigint and UUID basics in plain terms

When people compare UUID vs bigint in PostgreSQL, they’re choosing between two ways to name rows: a small counter-like number, or a longer globally unique value.

A bigint ID is a 64-bit integer. In PostgreSQL you usually generate it with an identity column (or the older serial pattern). The database keeps a sequence under the hood and hands out the next number each time you insert a row. That means IDs tend to be 1, 2, 3, 4... and so on. It’s simple, easy to read, and friendly in tools and reports.

A UUID (Universally Unique Identifier) is 128 bits. You’ll often see it written as 36 characters with hyphens, like 550e8400-e29b-41d4-a716-446655440000. Common types include:

• v4: random UUIDs. Easy to generate anywhere, but they don’t sort in creation order.
• v7: time-ordered UUIDs. Still unique, but designed to roughly increase over time.

Storage is one of the first practical differences: bigint uses 8 bytes, while UUID uses 16 bytes. That size gap shows up in indexes and can affect cache hit rates (the database can fit fewer index entries into memory).

Also think about where IDs show up outside the database. Bigint IDs are short in URLs and easy to read from logs or support tickets. UUIDs are longer and annoying to type, but they’re harder to guess and can be generated safely on clients when needed.

Index size and table bloat: what changes

The biggest practical difference between bigint and UUID is size. Bigint is 8 bytes; UUID is 16 bytes. That sounds minor until you remember that indexes repeat your IDs many times.

Your primary key index needs to stay hot in memory to feel fast. A smaller index means more of it fits in shared buffers and CPU cache, so lookups and joins need fewer disk reads. With UUID primary keys, the index is usually noticeably larger for the same row count.

The multiplier is secondary indexes. In PostgreSQL B-tree indexes, every secondary index entry also stores the primary key value (so the database can find the row). So wider IDs inflate not just the primary key index, but every other index you add. If you have three secondary indexes, the extra 8 bytes from UUIDs effectively shows up in four places.

Foreign keys and join tables feel it too. Any table that references your ID stores that value in its own rows and indexes. A many-to-many join table can be mostly two foreign keys plus a little overhead, so doubling key width can change its footprint a lot.

In practice:

• UUIDs usually make primary and secondary indexes larger, and the difference compounds as you add more indexes.
• Larger indexes mean more memory pressure and more page reads under load.
• The more tables that reference the key (events, logs, join tables), the more the size difference matters.

If a user ID appears in users, orders, order_items, and an audit_log, that same value is stored and indexed across all those tables. Choosing a wider ID is a storage decision as much as an ID decision.

Sort order and write patterns: sequential vs random IDs

Most PostgreSQL primary keys sit on a B-tree index. A B-tree works best when new rows land near the end of the index, because the database can keep appending with minimal reshuffling.

Sequential IDs: predictable and friendly to storage

With a bigint identity or sequence, new IDs rise over time. Inserts usually hit the rightmost part of the index, so pages stay packed, cache stays warm, and PostgreSQL does less extra work.

This matters even if you never run ORDER BY id. The write path still has to place each new key into the index in sorted order.

Random UUIDs: more scattering, more churn

A random UUID (common with UUIDv4) spreads inserts across the whole index. That increases the chance of page splits, where PostgreSQL has to allocate new index pages and move entries around to make room. The result is more write amplification: more index bytes written, more WAL generated, and often more background work later (vacuum and bloat management).

Time-ordered UUIDs change the story. UUIDs that mostly increase over time (like UUIDv7-style or other time-based schemes) restore much of the locality, while still being 16 bytes and still looking like UUIDs in your API.

You’ll feel these differences most when you have high insert rates, large tables that don’t fit in memory, and multiple secondary indexes. If you’re sensitive to write latency spikes from page splits, avoid fully random IDs on hot write tables.

Example: a busy events table receiving mobile app logs all day will usually run smoother with sequential keys or time-ordered UUIDs than with fully random UUIDs.

Performance impact you can actually feel

Most real-world slowdown isn’t “UUIDs are slow” or “bigints are fast”. It’s what the database has to touch to answer your query.

Query plans mainly care about whether they can use an index scan for filters, do fast joins on the key, and whether the table is physically ordered (or close enough) to make range reads cheap. With a bigint primary key, new rows land in roughly increasing order, so the primary key index tends to stay compact and locality-friendly. With random UUIDs, inserts scatter across the index, which can create more page splits and a messier on-disk order.

Reads are where many teams notice it first. Larger keys mean larger indexes, and larger indexes mean fewer useful pages fit in RAM. That reduces cache hit rates and increases IO, especially on join-heavy screens like “list orders with customer info”. If your working set doesn’t fit in memory, UUID-heavy schemas can push you over that edge sooner.

Writes can shift too. Random UUID inserts can increase churn in the index, which adds pressure on autovacuum and can show up as latency spikes during busy periods.

If you benchmark UUID vs bigint in PostgreSQL, keep it honest: same schema, same indexes, same fillfactor, and enough rows to exceed RAM (not 10k). Measure p95 latency and IO, and test both warm and cold cache.

If you build apps in AppMaster on PostgreSQL, this often shows up as slower list pages and heavier database load long before it looks like a “CPU problem.”

Security and usability in public-facing systems

If your IDs leave your database and show up in URLs, API responses, support tickets, and mobile screens, the choice affects both safety and day-to-day usability.

Bigint IDs are easy for humans. They’re short, you can read them over the phone, and your support team can quickly spot patterns like “all failing orders are around 9,200,000.” That can speed up debugging, especially when you’re working from logs or customer screenshots.

UUIDs are helpful when you expose identifiers to the public. A UUID is hard to guess, so casual scraping like trying /users/1, /users/2, /users/3 doesn’t work. It also makes it harder for outsiders to infer how many records you have.

The trap is thinking “unguessable” equals “secure.” If authorization checks are weak, predictable bigint IDs can be abused quickly, but UUIDs can still be stolen from a shared link, a leaked log, or a cached API response. Security must come from permission checks, not from hiding the ID.

A practical approach:

• Enforce ownership or role checks on every read and write.
• If you expose IDs in public APIs, use UUIDs or separate public tokens.
• If you want human-friendly references, keep an internal bigint for ops.
• Don’t encode sensitive meaning in the ID itself (like user type).

Example: a customer portal shows invoice IDs. If invoices use bigint and your API only checks “invoice exists,” someone can iterate numbers and download others’ invoices. Fix the check first. Then decide whether UUIDs for public invoice IDs reduce risk and support load.

In platforms like AppMaster, where IDs flow through generated APIs and mobile apps, the safest default is consistent authorization plus an ID format your clients can handle reliably.

How IDs flow through APIs and mobile apps

The database type you pick doesn’t stay in the database. It leaks into every boundary: URLs, JSON payloads, client storage, logs, and analytics.

If you ever change ID type later, the breakage is rarely “just a migration.” Foreign keys must change everywhere, not only in the main table. ORMs and code generators may regenerate models, but integrations still expect the old format. Even a simple GET /users/123 endpoint becomes messy when the ID turns into a 36-character UUID. You also have to update caches, message queues, and any place IDs were stored as integers.

For APIs, the biggest choice is format and validation. Bigints travel as numbers, but some systems (and some languages) risk precision issues at very large values if they parse them as floating point. UUIDs travel as strings, which is safer for parsing, but you need strict validation to avoid “almost UUID” junk ending up in logs and databases.

On mobile, IDs are constantly serialized and stored: JSON responses, local SQLite tables, and offline queues that save actions until the network returns. Numeric IDs are smaller, but string UUIDs are often easier to treat as opaque tokens. What causes real pain is inconsistency: one layer stores it as an integer, another as text, and comparisons or joins become fragile.

A few rules that keep teams out of trouble:

• Pick one canonical representation for APIs (often string) and stick to it.
• Validate IDs at the edge and return clear 400 errors.
• Store the same representation in local caches and offline queues.
• Log IDs using consistent field names and formats across services.

If you build web and mobile clients with a generated stack (for example, AppMaster generating backend and native apps), a stable ID contract matters even more because it becomes part of every generated model and request.

Sharding readiness and distributed systems

“Sharding-ready” mostly means you can create IDs in more than one place without breaking uniqueness, and you can move data across nodes later without rewriting every foreign key.

UUIDs are popular in multi-region or multi-writer setups because any node can generate a unique ID without asking a central sequence. That reduces coordination and makes it easier to accept writes in different regions and merge data later.

Bigint can still work, but you need a plan. Common options include allocating numeric ranges per shard (shard 1 uses 1-1B, shard 2 uses 1B-2B), running separate sequences with a shard prefix, or using Snowflake-like IDs (time-based bits plus machine or shard bits). These can keep indexes smaller than UUIDs and preserve some ordering, but they add operational rules you must enforce.

Trade-offs that matter day to day:

• Coordination: UUID needs almost none; bigint often needs range planning or a generator service.
• Collisions: UUID collisions are extremely unlikely; bigint is safe only if allocation rules never overlap.
• Ordering: many bigint schemes are roughly time-ordered; UUID is often effectively random unless you use a time-ordered variant.
• Complexity: sharded bigint stays simple only if the team is disciplined.

For many teams, “sharding-ready” really means “migration-ready.” If you’re on a single database today, choose the ID that makes current work easier. If you’re already building multiple writers (for example, via generated APIs and mobile apps in AppMaster), decide early how IDs are created and validated across services.

Step-by-step: choosing the right ID strategy

Start by naming the real shape of your app. A single PostgreSQL database in one region has different needs than a multi-tenant system, a setup that may later split by region, or a mobile app that must create records offline and sync later.

Next, be honest about where IDs will show up. If identifiers stay inside your backend (jobs, internal tools, admin panels), simplicity often wins. If IDs appear in URLs, logs shared with customers, support tickets, or mobile deep links, predictability and privacy matter more.

Use ordering as a deciding factor, not an afterthought. If you rely on “newest first” feeds, stable pagination, or audit trails that are easy to scan, sequential IDs (or time-ordered IDs) reduce surprises. If ordering isn’t tied to the primary key, you can keep the PK choice separate and sort by a timestamp instead.

A practical decision flow:

1. Classify your architecture (single DB, multi-tenant, multi-region, offline-first) and whether you might merge data from multiple sources.
2. Decide if IDs are public identifiers or purely internal.
3. Confirm your ordering and pagination needs. If you need natural insertion order, avoid purely random IDs.
4. If you go with UUIDs, pick a version on purpose: random (v4) for unpredictability, or time-ordered for better index locality.
5. Lock conventions early: one canonical text form, case rules, validation, and how every API returns and accepts IDs.

Example: if a mobile app creates “draft orders” offline, UUIDs let the device generate IDs safely before the server ever sees them. In tools like AppMaster, that’s also convenient because the same ID format can flow from database to API to web and native apps without special casing.

Common mistakes and traps to avoid

Most ID debates go wrong because people pick an ID type for one reason, then get surprised by the side effects later.

One common mistake is using fully random UUIDs on a hot write table and then wondering why inserts feel spiky. Random values spread new rows across the index, which can mean more page splits and more work for the database under heavy load. If the table is write-heavy, think about insert locality before you commit.

Another frequent problem is mixing ID types across services and clients. For example, one service uses bigint, another uses UUID, and your API ends up with both numeric and string IDs. That often turns into subtle bugs: JSON parsers that lose precision on large numbers, mobile code that treats IDs as numbers in one screen and strings in another, or caching keys that don’t match.

A third trap is treating “unguessable IDs” as security. Even if you use UUIDs, you still need proper authorization checks.

Finally, teams change the ID type late without a plan. The hardest part isn’t the primary key itself, it’s everything attached to it: foreign keys, join tables, URLs, analytics events, mobile deep links, and stored client state.

To avoid pain:

• Pick one ID type for public APIs and stick to it.
• Treat IDs as opaque strings in clients to avoid numeric edge cases.
• Never use ID randomness as access control.
• If you must migrate, version the API and plan for long-lived clients.

If you build with a code-generating platform like AppMaster, consistency matters even more because the same ID type flows from database schema to generated backend and into web and mobile apps.

Quick checklist before you decide

If you feel stuck, don’t start with theory. Start with what your product will look like in a year, and how many places that ID will travel.

Ask:

• How big will the largest tables get in 12 to 24 months, and will you keep years of history?
• Do you need IDs that roughly sort by creation time for easy paging and debugging?
• Will more than one system create records at the same time, including offline mobile apps or background jobs?
• Will the ID show up in URLs, support tickets, exports, or screenshots shared with customers?
• Can every client treat the ID the same way (web, iOS, Android, scripts), including validation and storage?

After you answer those, sanity-check the plumbing. If you use bigint, make sure you have a clear plan for ID generation in every environment (especially local dev and imports). If you use UUID, make sure your API contracts and client models handle string IDs consistently, and that your team is comfortable reading and comparing them.

A quick reality test: if a mobile app needs to create an order while offline and sync later, UUIDs often reduce coordination work. If your app is mostly online and you want simple, compact indexes, bigint is usually easier.

If you build apps in AppMaster, decide early so your database model, API endpoints, and mobile clients stay consistent as you regenerate and grow the project.

A realistic example scenario

A small company has an internal operations tool, a customer portal, and a mobile app for field staff. All three hit the same PostgreSQL database through one API. New records are created all day: tickets, photos, status updates, and invoices.

With bigint IDs, the API payloads are compact and easy to read:

{ "ticket_id": 4821931, "customer_id": 91244 }

Pagination feels natural: ?after_id=4821931&limit=50. Sorting by id usually matches creation time, so “latest tickets” is fast and predictable. Debugging is also simple: support can ask for “ticket 4821931” and most people can type it without mistakes.

With UUIDs, the payloads get longer:

{ "ticket_id": "3f9b3c0a-7b9c-4bf0-9f9b-2a1b3c5d1d2e" }

If you use random UUID v4, inserts land all over the index. That can mean more index churn and slightly messier day-to-day debugging (copy/paste becomes normal). Pagination often shifts to cursor-style tokens instead of “after id”.

If you use time-ordered UUIDs, you keep most of the “newest first” behavior while still avoiding guessable IDs in public URLs.

In practice, teams usually notice four things:

• How often IDs are typed by humans vs copied
• Whether “sort by id” matches “sort by created”
• How clean and stable cursor pagination feels
• How easy it is to trace one record across logs, API calls, and mobile screens

Next steps: pick a default, test it, and standardize

Most teams get stuck because they want a perfect answer. You don’t need perfect. You need a default that fits your product today, plus a quick way to prove it won’t hurt you later.

Rules you can standardize:

• Use bigint when you want the smallest indexes, predictable ordering, and easy debugging.
• Use UUID when IDs must be hard to guess in URLs, you expect offline creation (mobile), or you want fewer collisions across systems.
• If you might split data by tenant or region later, prefer an ID plan that works across nodes (UUID, or a coordinated bigint scheme).
• Pick one as the default and make exceptions rare. Consistency usually beats micro-optimizing one table.

Before you lock it in, run a small spike. Create a table with a realistic row size, insert 1 to 5 million rows, and compare (1) index size, (2) insert time, and (3) a few common queries with the primary key and a couple of secondary indexes. Do it with your real hardware and your real data shape.

If you’re worried you may change later, plan the migration so it’s boring:

• Add the new ID column and a unique index.
• Dual-write: fill both IDs for new rows.
• Backfill old rows in batches.
• Update APIs and clients to accept the new ID (keep the old one working during the transition).
• Cut over reads, then drop the old key when logs and metrics look clean.

If you’re building on AppMaster (appmaster.io), it’s worth deciding early because the ID convention flows through your PostgreSQL model, generated APIs, and both web and native mobile apps. The specific type matters, but the consistency usually matters more once you have real users and multiple clients.