Should I use UUIDv7 instead of auto-increment integer primary keys?

UUIDv7 is an excellent choice when you need globally unique IDs across distributed systems, want to generate IDs client-side, or need to merge data from multiple sources. Auto-increment integers are simpler and more compact (8 bytes vs 16 bytes), so they're still appropriate for single-database applications with no distribution needs. UUIDv7's sequential nature means its insert performance is close to auto-increment, unlike random UUIDv4.

How should I store UUIDv7 in MySQL -- BINARY(16) or CHAR(36)?

Use BINARY(16) for production workloads. Storing as BINARY(16) uses 16 bytes per row compared to 36 bytes for CHAR(36), saving over 55% storage on the column itself. This also means smaller indexes, faster comparisons, and better cache utilization. Use UUID_TO_BIN() and BIN_TO_UUID() functions in MySQL 8.0+ for conversion, or handle the conversion in your application layer.

Does PostgreSQL 17 support UUIDv7 natively?

Yes, PostgreSQL 17 (released September 2024) added the gen_random_uuid_v7() function for native UUIDv7 generation. For earlier versions, you can use the pgcrypto extension with a custom function, or generate UUIDv7 values in your application code. The native UUID column type works with UUIDv7 in all PostgreSQL versions since UUIDv7 uses the standard 128-bit UUID format.

Can UUIDv7 and UUIDv4 coexist in the same database column?

Yes. Both UUIDv7 and UUIDv4 are 128-bit values stored in the same UUID format, so they can coexist in the same column. You can start generating UUIDv7 for new records without migrating existing UUIDv4 values. The only caveat is that UUIDv4 records will not sort chronologically relative to UUIDv7 records, since UUIDv4 has no timestamp component.

Is UUIDv7 suitable for MongoDB's _id field?

MongoDB's default ObjectId is already a 12-byte time-sortable identifier, so UUIDv7 doesn't provide the same performance uplift as in B-tree indexed relational databases. However, UUIDv7 can be useful in MongoDB if you need cross-system compatibility with standard UUIDs, want millisecond timestamp precision (vs ObjectId's second precision), or need to use the same ID format across both SQL and NoSQL stores.

UUIDv7 in Databases: PostgreSQL, MySQL & Performance Guide

How to use time-sortable UUIDs as primary keys for better database performance

TL;DR

UUIDv7 as a primary key gives you globally unique, time-sortable IDs with near auto-increment insert performance
PostgreSQL 17+ supports native generation via gen_random_uuid_v7()
MySQL storage: use BINARY(16) for 55% space savings over CHAR(36)
Performance: 2-3x faster inserts than UUIDv4 due to sequential B-tree ordering, with smaller indexes
Migration: UUIDv7 is a drop-in replacement for UUIDv4 -- same 128-bit format, same column type
Best for: distributed systems, microservices, event sourcing, and any table using UUID primary keys

Why UUIDv7 for Databases?

Traditional auto-increment integers are simple but break down in distributed systems. Random UUIDv4 solves the distribution problem but creates severe index fragmentation. UUIDv7 combines the best of both worlds -- globally unique IDs with sequential ordering that databases love.

Sequential Inserts

Time-ordered IDs append to the end of B-tree indexes, minimizing page splits

Smaller Indexes

Compact, non-fragmented indexes with better cache utilization

No Coordination

Generate IDs in any service, any region, without a central sequence

Built-in Timestamp

Extract creation time from the ID itself -- no extra column needed

Did you know?The Y2038 problem affects 32-bit systemsLearn more

PostgreSQL Implementation

PostgreSQL has native UUID support with a dedicated uuid column type that stores values efficiently as 16 bytes. PostgreSQL 17 added native UUIDv7 generation.

PostgreSQL 17+

-- Create table with UUIDv7 primary key (PG 17+)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert without specifying ID
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice');

-- IDs are naturally time-ordered
SELECT id, email FROM users ORDER BY id;
-- 01936b2e-1e85-7000-8000-4a6f7e3b2c1d | alice
-- 01936b2e-2f4a-7000-8000-9c3d5e7f1a2b | bob

PostgreSQL 13-16(pre-v17 workaround)

-- For PG versions before 17, generate UUIDv7
-- in your application code and pass it explicitly:
INSERT INTO users (id, email, name)
VALUES ('01936b2e-1e85-7000-8000-4a6f7e3b2c1d',
        '[email protected]', 'Alice');

-- Or use a PL/pgSQL function (community recipes)
-- that constructs UUIDv7 from clock_timestamp()

Extracting Timestamp

-- Extract creation timestamp from UUIDv7
-- Take first 12 hex chars → convert to ms epoch
SELECT
  id,
  to_timestamp(
    ('x' || lpad(replace(id::text, '-', ''), 12))
    ::bit(48)::bigint / 1000.0
  ) AS created_from_id
FROM users;

MySQL Implementation

MySQL doesn't have a native UUID column type. You have two storage options, each with different trade-offs.

Feature	BINARY(16)	CHAR(36)
Storage per row	16 bytes	36 bytes
Index size	Smaller (16 bytes)	Larger (36 bytes)
Comparison speed	Fast (binary)	Slower (string)
Human-readable	Needs conversion	Readable as-is
Recommendation	Production use	Development / debugging

MySQL 8.0+ (BINARY)Recommended

-- BINARY(16) storage with swap flag for sortability
CREATE TABLE orders (
  id BINARY(16) PRIMARY KEY,
  customer_id BINARY(16) NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert: convert UUID string to binary
-- swap_flag=1 rearranges bytes for time-ordering
INSERT INTO orders (id, customer_id, total)
VALUES (
  UUID_TO_BIN('01936b2e-1e85-7000-8000-4a6f7e3b2c1d', 1),
  UUID_TO_BIN('01936b2e-0a12-7000-8000-1b2c3d4e5f6a', 1),
  99.99
);

-- Query: convert binary back to readable UUID
SELECT BIN_TO_UUID(id, 1) AS id, total
FROM orders
ORDER BY id;

MySQL 8.0+ (CHAR)Simpler but less efficient

-- CHAR(36) storage — simpler, human-readable
CREATE TABLE events (
  id CHAR(36) PRIMARY KEY,
  type VARCHAR(50) NOT NULL,
  payload JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert: pass UUID string directly
INSERT INTO events (id, type, payload)
VALUES (
  '01936b2e-1e85-7000-8000-4a6f7e3b2c1d',
  'order.created',
  '{"order_id": "12345"}'
);

-- UUIDv7 strings sort correctly as CHAR
-- (timestamp prefix ensures chronological order)
SELECT id, type FROM events ORDER BY id;

Did you know?UUIDv7 outperforms UUIDv4 for database primary keysLearn more

Performance Benchmarks

The performance advantage of UUIDv7 over UUIDv4 comes from its sequential insertion pattern. Here's how different primary key types compare in typical database workloads.

Metric	Auto-Increment	UUIDv7	UUIDv4
Insert throughput	Fastest	~95% of auto-inc	~40-50% of auto-inc
Index fragmentation	None	Minimal	High
Key size	4-8 bytes	16 bytes	16 bytes
Time-sortable	Yes	Yes	No
Distributed generation	Requires coordination	No coordination	No coordination
Leaks ordering info	Yes (sequential)	Yes (timestamp)	No

Key takeaway: UUIDv7 delivers insert performance within 5-10% of auto-increment integers while providing globally unique, coordination-free ID generation. The gap between UUIDv7 and UUIDv4 widens significantly as table size grows beyond 1 million rows due to increasing index fragmentation with random inserts.

Migration Strategies

From Auto-Increment to UUIDv7

1Add a new UUID column alongside the existing integer PK

2Backfill existing rows with generated UUIDv7 values (use creation timestamp if available)

3Update application code to write both columns, read by UUID

4Update foreign keys to reference the UUID column

5Drop the old integer primary key once all references are migrated

-- Step 1: Add UUID column
ALTER TABLE users ADD COLUMN uuid UUID;

-- Step 2: Backfill (PG 17+)
UPDATE users SET uuid = gen_random_uuid_v7()
WHERE uuid IS NULL;

-- Step 3: Make it the primary key
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY (uuid);
ALTER TABLE users ALTER COLUMN uuid
  SET DEFAULT gen_random_uuid_v7();

From UUIDv4 to UUIDv7

This is the simplest migration -- no schema changes needed. Both formats use the same 128-bit UUID storage.

1Update your UUID generation code to produce UUIDv7 instead of UUIDv4

2Existing UUIDv4 rows remain valid -- no backfill required

3Over time, new UUIDv7 inserts will reduce index fragmentation

-- No schema change needed!
-- Just update the DEFAULT to use v7:

-- PostgreSQL 17+
ALTER TABLE users ALTER COLUMN id
  SET DEFAULT gen_random_uuid_v7();

-- Or change your application code:
-- Before: uuid.uuid4() / crypto.randomUUID()
-- After:  uuid7()      / uuidv7()

Did you know?The Y2038 problem affects 32-bit systemsLearn more

Frequently Asked Questions

Try Our UUIDv7 Tools

Generate UUIDv7 identifiers or extract timestamps from existing UUIDs.

UUIDv7 Generator UUIDv7 to Human Date