Skip to content

Instantly share code, notes, and snippets.

@sandcastle

sandcastle/uuid_v7_optimized.sql

Created September 10, 2025 03:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sandcastle/993b350d274cbf488c4d026f0eb939bd to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sandcastle/993b350d274cbf488c4d026f0eb939bd to your computer and use it in GitHub Desktop.
Download ZIP
Postgres UUID v7 for versions before Postgres18 (which includes uuidv7)
Raw
uuid_v7_optimized.sql
-- ==============================================================================
-- Optimized UUIDv7 Implementation for PostgreSQL 17 on Google Cloud SQL
-- ==============================================================================
-- Main UUIDv7 function (current timestamp)
CREATE OR REPLACE FUNCTION uuid_v7() RETURNS uuid
LANGUAGE plpgsql VOLATILE AS $$
DECLARE
timestamp_ms bigint;
bytes bytea;
BEGIN
-- Get timestamp in milliseconds
timestamp_ms := floor(extract(epoch FROM clock_timestamp()) * 1000);
-- Build the 16-byte UUID: 6 bytes timestamp + 10 bytes random
bytes := substring(int8send(timestamp_ms) FROM 3) || gen_random_bytes(10);
-- Set version (0x7) in byte 6 high nibble
bytes := set_byte(bytes, 6, (get_byte(bytes, 6) & 15) | 0x70);
-- Set variant (10) in byte 8 high bits
bytes := set_byte(bytes, 8, (get_byte(bytes, 8) & 0x3F) | 0x80);
RETURN encode(bytes, 'hex')::uuid;
END;
$$;
-- UUIDv7 function with custom timestamp
CREATE OR REPLACE FUNCTION uuid_v7_at_time(ts timestamptz) RETURNS uuid
LANGUAGE plpgsql VOLATILE AS $$
DECLARE
timestamp_ms bigint;
bytes bytea;
BEGIN
-- Get timestamp in milliseconds from provided timestamp
timestamp_ms := floor(extract(epoch FROM ts) * 1000);
-- Build the 16-byte UUID: 6 bytes timestamp + 10 bytes random
bytes := substring(int8send(timestamp_ms) FROM 3) || gen_random_bytes(10);
-- Set version (0x7) in byte 6 high nibble
bytes := set_byte(bytes, 6, (get_byte(bytes, 6) & 15) | 0x70);
-- Set variant (10) in byte 8 high bits
bytes := set_byte(bytes, 8, (get_byte(bytes, 8) & 0x3F) | 0x80);
RETURN encode(bytes, 'hex')::uuid;
END;
$$;
-- ==============================================================================
-- Helper Functions
-- ==============================================================================
-- Extract timestamp from UUIDv7
CREATE OR REPLACE FUNCTION uuid_v7_to_timestamptz(uuid_val uuid) RETURNS timestamptz
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT to_timestamp(
-- Extract first 48 bits (6 bytes) as timestamp in milliseconds
(('x' || translate(substring(uuid_val::text, 1, 13), '-', ''))::bit(48)::bigint) / 1000.0
);
$$;
-- Extract timestamp as epoch milliseconds from UUIDv7
CREATE OR REPLACE FUNCTION uuid_v7_to_epoch_ms(uuid_val uuid) RETURNS bigint
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT ('x' || translate(substring(uuid_val::text, 1, 13), '-', ''))::bit(48)::bigint;
$$;
-- Check if UUID is version 7
CREATE OR REPLACE FUNCTION is_uuid_v7(uuid_val uuid) RETURNS boolean
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT (uuid_val::text ~ '^[0-9a-f]{8}-[0-9a-f]{4}-7[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$');
$$;
-- Get UUID version
CREATE OR REPLACE FUNCTION uuid_version(uuid_val uuid) RETURNS integer
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT ('x' || substring(uuid_val::text, 15, 1))::bit(4)::integer;
$$;
-- Compare UUIDv7 chronologically (returns -1, 0, or 1)
CREATE OR REPLACE FUNCTION uuid_v7_compare(uuid_a uuid, uuid_b uuid) RETURNS integer
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT CASE
WHEN uuid_v7_to_epoch_ms(uuid_a) < uuid_v7_to_epoch_ms(uuid_b) THEN -1
WHEN uuid_v7_to_epoch_ms(uuid_a) > uuid_v7_to_epoch_ms(uuid_b) THEN 1
ELSE 0
END;
$$;
-- ==============================================================================
-- Performance/Utility Views
-- ==============================================================================
-- Create a view for easy UUID analysis
CREATE OR REPLACE VIEW uuid_v7_analysis AS
SELECT
uuid_val,
uuid_version(uuid_val) as version,
is_uuid_v7(uuid_val) as is_v7,
uuid_v7_to_timestamptz(uuid_val) as extracted_timestamp,
uuid_v7_to_epoch_ms(uuid_val) as epoch_ms
FROM (SELECT '00000000-0000-7000-8000-000000000000'::uuid as uuid_val) t;
COMMENT ON FUNCTION uuid_v7() IS 'Generate UUIDv7 with current timestamp - optimized for PostgreSQL 17';
COMMENT ON FUNCTION uuid_v7_at_time(timestamptz) IS 'Generate UUIDv7 with specified timestamp';
COMMENT ON FUNCTION uuid_v7_to_timestamptz(uuid) IS 'Extract timestamp from UUIDv7 as timestamptz';
COMMENT ON FUNCTION uuid_v7_to_epoch_ms(uuid) IS 'Extract timestamp from UUIDv7 as epoch milliseconds';
COMMENT ON FUNCTION is_uuid_v7(uuid) IS 'Check if UUID is version 7';
COMMENT ON FUNCTION uuid_version(uuid) IS 'Get UUID version (1-5, 7, etc.)';
COMMENT ON FUNCTION uuid_v7_compare(uuid, uuid) IS 'Compare two UUIDv7s chronologically';
Raw
uuid_v7_test_suite.sql
-- ==============================================================================
-- UUIDv7 Test Suite - Comprehensive validation and performance testing
-- ==============================================================================
-- Clean up any existing test tables
DROP TABLE IF EXISTS uuid_test_rapid_v7;
DROP TABLE IF EXISTS uuid_test_rapid_v4;
DROP TABLE IF EXISTS uuid_performance_results;
-- ==============================================================================
-- Test 1: Basic Functionality Tests
-- ==============================================================================
DO $$
DECLARE
test_uuid uuid;
extracted_ts timestamptz;
epoch_ms bigint;
test_ts timestamptz := '2024-01-15 10:30:45.123456+00'::timestamptz;
custom_uuid uuid;
BEGIN
RAISE NOTICE 'Starting UUIDv7 Basic Functionality Tests...';
-- Test 1.1: Basic UUID generation
test_uuid := uuid_v7();
RAISE NOTICE 'Generated UUIDv7: %', test_uuid;
-- Test 1.2: Version validation
IF uuid_version(test_uuid) != 7 THEN
RAISE EXCEPTION 'FAIL: UUID version is not 7, got %', uuid_version(test_uuid);
END IF;
RAISE NOTICE 'PASS: UUID version is 7';
-- Test 1.3: Format validation
IF NOT is_uuid_v7(test_uuid) THEN
RAISE EXCEPTION 'FAIL: UUID does not match UUIDv7 format';
END IF;
RAISE NOTICE 'PASS: UUID matches UUIDv7 format';
-- Test 1.4: Timestamp extraction
extracted_ts := uuid_v7_to_timestamptz(test_uuid);
IF extracted_ts > now() + interval '1 second' OR extracted_ts < now() - interval '1 second' THEN
RAISE EXCEPTION 'FAIL: Extracted timestamp % is not close to current time %', extracted_ts, now();
END IF;
RAISE NOTICE 'PASS: Timestamp extraction works correctly: %', extracted_ts;
-- Test 1.5: Custom timestamp UUID generation
custom_uuid := uuid_v7_at_time(test_ts);
extracted_ts := uuid_v7_to_timestamptz(custom_uuid);
IF abs(extract(epoch from extracted_ts) - extract(epoch from test_ts)) > 0.001 THEN
RAISE EXCEPTION 'FAIL: Custom timestamp UUID does not match. Expected: %, Got: %', test_ts, extracted_ts;
END IF;
RAISE NOTICE 'PASS: Custom timestamp UUID generation works: %', custom_uuid;
-- Test 1.6: Epoch milliseconds extraction
epoch_ms := uuid_v7_to_epoch_ms(custom_uuid);
IF abs(epoch_ms - extract(epoch from test_ts) * 1000) > 1 THEN
RAISE EXCEPTION 'FAIL: Epoch milliseconds extraction failed. Expected: %, Got: %',
extract(epoch from test_ts) * 1000, epoch_ms;
END IF;
RAISE NOTICE 'PASS: Epoch milliseconds extraction works: %', epoch_ms;
RAISE NOTICE 'Basic Functionality Tests: ALL PASSED';
END $$;
-- ==============================================================================
-- Test 2: Rapid Generation and Monotonicity Tests
-- ==============================================================================
CREATE TABLE uuid_test_rapid_v7 (
id serial PRIMARY KEY,
uuid_val uuid NOT NULL,
generated_at timestamptz DEFAULT now(),
epoch_ms bigint
);
CREATE TABLE uuid_test_rapid_v4 (
id serial PRIMARY KEY,
uuid_val uuid NOT NULL,
generated_at timestamptz DEFAULT now()
);
DO $$
DECLARE
i integer;
prev_uuid uuid := '00000000-0000-0000-0000-000000000000';
curr_uuid uuid;
violations integer := 0;
start_time timestamptz;
end_time timestamptz;
total_time interval;
BEGIN
RAISE NOTICE 'Starting Rapid Generation Tests...';
-- Test 2.1: Generate 10,000 UUIDv7s rapidly
start_time := clock_timestamp();
FOR i IN 1..10000 LOOP
curr_uuid := uuid_v7();
INSERT INTO uuid_test_rapid_v7 (uuid_val, epoch_ms)
VALUES (curr_uuid, uuid_v7_to_epoch_ms(curr_uuid));
-- Check monotonicity (should be increasing or equal)
IF curr_uuid < prev_uuid THEN
violations := violations + 1;
END IF;
prev_uuid := curr_uuid;
END LOOP;
end_time := clock_timestamp();
total_time := end_time - start_time;
RAISE NOTICE 'Generated 10,000 UUIDv7s in %', total_time;
RAISE NOTICE 'Monotonicity violations: % out of 10,000', violations;
IF violations > 100 THEN -- Allow some violations due to rapid generation
RAISE WARNING 'High number of monotonicity violations: %', violations;
ELSE
RAISE NOTICE 'PASS: Acceptable monotonicity (violations: %)', violations;
END IF;
-- Test 2.2: Check for duplicates
IF (SELECT COUNT(*) FROM uuid_test_rapid_v7) != (SELECT COUNT(DISTINCT uuid_val) FROM uuid_test_rapid_v7) THEN
RAISE EXCEPTION 'FAIL: Duplicate UUIDs found in rapid generation';
END IF;
RAISE NOTICE 'PASS: No duplicate UUIDs in 10,000 rapid generations';
-- Test 2.3: Generate 10,000 UUIDv4s for comparison
start_time := clock_timestamp();
FOR i IN 1..10000 LOOP
INSERT INTO uuid_test_rapid_v4 (uuid_val) VALUES (gen_random_uuid());
END LOOP;
end_time := clock_timestamp();
total_time := end_time - start_time;
RAISE NOTICE 'Generated 10,000 UUIDv4s in %', total_time;
-- Test 2.4: Check UUIDv4 duplicates (should be none)
IF (SELECT COUNT(*) FROM uuid_test_rapid_v4) != (SELECT COUNT(DISTINCT uuid_val) FROM uuid_test_rapid_v4) THEN
RAISE EXCEPTION 'FAIL: Duplicate UUIDv4s found in rapid generation';
END IF;
RAISE NOTICE 'PASS: No duplicate UUIDv4s in 10,000 rapid generations';
END $$;
-- ==============================================================================
-- Test 3: Performance Comparison Tests
-- ==============================================================================
CREATE TABLE uuid_performance_results (
test_name text,
uuid_type text,
iterations integer,
total_time_ms numeric,
avg_time_per_uuid_ns numeric,
throughput_per_second numeric
);
DO $$
DECLARE
iterations integer := 50000;
start_time timestamptz;
end_time timestamptz;
total_time interval;
total_ms numeric;
i integer;
dummy_uuid uuid;
BEGIN
RAISE NOTICE 'Starting Performance Comparison Tests (%s iterations)...', iterations;
-- Test 3.1: UUIDv7 Performance
start_time := clock_timestamp();
FOR i IN 1..iterations LOOP
dummy_uuid := uuid_v7();
END LOOP;
end_time := clock_timestamp();
total_time := end_time - start_time;
total_ms := extract(epoch from total_time) * 1000;
INSERT INTO uuid_performance_results VALUES (
'Generation Performance',
'UUIDv7',
iterations,
total_ms,
(total_ms * 1000000 / iterations), -- nanoseconds per UUID
(iterations / extract(epoch from total_time)) -- UUIDs per second
);
RAISE NOTICE 'UUIDv7: %s iterations in %s (%.2f µs/uuid, %.0f/sec)',
iterations, total_time,
total_ms * 1000 / iterations,
iterations / extract(epoch from total_time);
-- Test 3.2: UUIDv4 Performance
start_time := clock_timestamp();
FOR i IN 1..iterations LOOP
dummy_uuid := gen_random_uuid();
END LOOP;
end_time := clock_timestamp();
total_time := end_time - start_time;
total_ms := extract(epoch from total_time) * 1000;
INSERT INTO uuid_performance_results VALUES (
'Generation Performance',
'UUIDv4',
iterations,
total_ms,
(total_ms * 1000000 / iterations),
(iterations / extract(epoch from total_time))
);
RAISE NOTICE 'UUIDv4: %s iterations in %s (%.2f µs/uuid, %.0f/sec)',
iterations, total_time,
total_ms * 1000 / iterations,
iterations / extract(epoch from total_time);
-- Test 3.3: UUIDv7 with custom timestamp performance
start_time := clock_timestamp();
FOR i IN 1..iterations LOOP
dummy_uuid := uuid_v7_at_time(now());
END LOOP;
end_time := clock_timestamp();
total_time := end_time - start_time;
total_ms := extract(epoch from total_time) * 1000;
INSERT INTO uuid_performance_results VALUES (
'Generation Performance',
'UUIDv7_custom_time',
iterations,
total_ms,
(total_ms * 1000000 / iterations),
(iterations / extract(epoch from total_time))
);
RAISE NOTICE 'UUIDv7 (custom time): %s iterations in %s (%.2f µs/uuid, %.0f/sec)',
iterations, total_time,
total_ms * 1000 / iterations,
iterations / extract(epoch from total_time);
END $$;
-- ==============================================================================
-- Test 4: Ordering and Sorting Tests
-- ==============================================================================
DO $$
DECLARE
uuid_array uuid[];
sorted_array uuid[];
epoch_array bigint[];
sorted_epochs bigint[];
i integer;
BEGIN
RAISE NOTICE 'Starting Ordering and Sorting Tests...';
-- Test 4.1: Generate UUIDs with different timestamps
FOR i IN 1..100 LOOP
uuid_array[i] := uuid_v7_at_time(now() + (i || ' milliseconds')::interval);
epoch_array[i] := uuid_v7_to_epoch_ms(uuid_array[i]);
END LOOP;
-- Test 4.2: Sort by UUID value
sorted_array := (SELECT array_agg(val ORDER BY val) FROM unnest(uuid_array) val);
-- Test 4.3: Sort by extracted epoch
sorted_epochs := (SELECT array_agg(val ORDER BY val) FROM unnest(epoch_array) val);
-- Test 4.4: Verify UUID sorting matches timestamp sorting
FOR i IN 1..100 LOOP
IF uuid_v7_to_epoch_ms(sorted_array[i]) != sorted_epochs[i] THEN
RAISE EXCEPTION 'FAIL: UUID sorting does not match timestamp sorting at position %', i;
END IF;
END LOOP;
RAISE NOTICE 'PASS: UUID sorting matches timestamp sorting perfectly';
-- Test 4.5: Verify chronological ordering
FOR i IN 2..100 LOOP
IF uuid_v7_to_epoch_ms(sorted_array[i]) < uuid_v7_to_epoch_ms(sorted_array[i-1]) THEN
RAISE EXCEPTION 'FAIL: UUIDs are not in chronological order at position %', i;
END IF;
END LOOP;
RAISE NOTICE 'PASS: UUIDs are in perfect chronological order';
END $$;
-- ==============================================================================
-- Test 5: Edge Cases and Validation Tests
-- ==============================================================================
DO $$
DECLARE
edge_uuid uuid;
test_time timestamptz;
extracted_time timestamptz;
BEGIN
RAISE NOTICE 'Starting Edge Case Tests...';
-- Test 5.1: Very old timestamp
test_time := '1970-01-01 00:00:01+00'::timestamptz;
edge_uuid := uuid_v7_at_time(test_time);
extracted_time := uuid_v7_to_timestamptz(edge_uuid);
IF abs(extract(epoch from extracted_time) - extract(epoch from test_time)) > 1 THEN
RAISE EXCEPTION 'FAIL: Edge case old timestamp failed';
END IF;
RAISE NOTICE 'PASS: Very old timestamp works: %', test_time;
-- Test 5.2: Far future timestamp
test_time := '2099-12-31 23:59:59+00'::timestamptz;
edge_uuid := uuid_v7_at_time(test_time);
extracted_time := uuid_v7_to_timestamptz(edge_uuid);
IF abs(extract(epoch from extracted_time) - extract(epoch from test_time)) > 1 THEN
RAISE EXCEPTION 'FAIL: Edge case future timestamp failed';
END IF;
RAISE NOTICE 'PASS: Far future timestamp works: %', test_time;
-- Test 5.3: Same millisecond generation
test_time := now();
DECLARE
uuid1 uuid := uuid_v7_at_time(test_time);
uuid2 uuid := uuid_v7_at_time(test_time);
BEGIN
IF uuid1 = uuid2 THEN
RAISE EXCEPTION 'FAIL: Same timestamp generated identical UUIDs';
END IF;
RAISE NOTICE 'PASS: Same timestamp generates different UUIDs';
END;
-- Test 5.4: NULL handling
BEGIN
SELECT uuid_v7_to_timestamptz(NULL);
RAISE EXCEPTION 'FAIL: NULL UUID should have returned NULL';
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'PASS: NULL UUID handled correctly';
END;
END $$;
-- ==============================================================================
-- Test 6: Index Performance Test (B-tree efficiency)
-- ==============================================================================
CREATE TABLE uuid_index_test_v7 (
id uuid PRIMARY KEY DEFAULT uuid_v7(),
data text DEFAULT 'test data',
created_at timestamptz DEFAULT now()
);
CREATE TABLE uuid_index_test_v4 (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
data text DEFAULT 'test data',
created_at timestamptz DEFAULT now()
);
-- Insert test data
INSERT INTO uuid_index_test_v7 (data)
SELECT 'test_data_' || i
FROM generate_series(1, 10000) i;
INSERT INTO uuid_index_test_v4 (data)
SELECT 'test_data_' || i
FROM generate_series(1, 10000) i;
-- Analyze tables
ANALYZE uuid_index_test_v7;
ANALYZE uuid_index_test_v4;
-- ==============================================================================
-- Test Results Summary
-- ==============================================================================
DO $$
BEGIN
RAISE NOTICE '============================================================';
RAISE NOTICE 'UUIDv7 Test Suite Summary';
RAISE NOTICE '============================================================';
RAISE NOTICE 'Performance Results:';
END $$;
-- Display performance comparison
SELECT
uuid_type,
ROUND(avg_time_per_uuid_ns::numeric, 2) as avg_ns_per_uuid,
ROUND(throughput_per_second::numeric, 0) as uuids_per_second,
ROUND(total_time_ms::numeric, 2) as total_time_ms
FROM uuid_performance_results
ORDER BY uuid_type;
-- Display rapid generation analysis
SELECT
'UUIDv7' as type,
COUNT(*) as total_generated,
COUNT(DISTINCT uuid_val) as unique_count,
COUNT(*) - COUNT(DISTINCT uuid_val) as duplicates,
MIN(epoch_ms) as min_epoch_ms,
MAX(epoch_ms) as max_epoch_ms,
MAX(epoch_ms) - MIN(epoch_ms) as time_span_ms
FROM uuid_test_rapid_v7
UNION ALL
SELECT
'UUIDv4' as type,
COUNT(*) as total_generated,
COUNT(DISTINCT uuid_val) as unique_count,
COUNT(*) - COUNT(DISTINCT uuid_val) as duplicates,
NULL as min_epoch_ms,
NULL as max_epoch_ms,
NULL as time_span_ms
FROM uuid_test_rapid_v4;
-- Display index statistics comparison
SELECT
'UUIDv7' as table_type,
schemaname,
tablename,
indexname,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'uuid_index_test_v7'
UNION ALL
SELECT
'UUIDv4' as table_type,
schemaname,
tablename,
indexname,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'uuid_index_test_v4';
-- Performance test queries
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM uuid_index_test_v7
WHERE id > (SELECT id FROM uuid_index_test_v7 LIMIT 1 OFFSET 5000)
LIMIT 100;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM uuid_index_test_v4
WHERE id > (SELECT id FROM uuid_index_test_v4 LIMIT 1 OFFSET 5000)
LIMIT 100;
-- Final completion message
DO $$
BEGIN
RAISE NOTICE '============================================================';
RAISE NOTICE 'UUIDv7 Test Suite Completed Successfully!';
RAISE NOTICE 'All tests passed. Check the query results above for detailed performance metrics.';
RAISE NOTICE '============================================================';
END $$;
-- ==============================================================================
-- Optional: Cleanup (uncomment to clean up test data)
-- ==============================================================================
/*
DROP TABLE IF EXISTS uuid_test_rapid_v7;
DROP TABLE IF EXISTS uuid_test_rapid_v4;
DROP TABLE IF EXISTS uuid_performance_results;
DROP TABLE IF EXISTS uuid_index_test_v7;
DROP TABLE IF EXISTS uuid_index_test_v4;
*/
Raw
uuid_v7_usage_examples.sql
-- ==============================================================================
-- UUIDv7 Usage Examples and Quick Start Guide
-- ==============================================================================
-- ==============================================================================
-- Basic Usage Examples
-- ==============================================================================
-- Generate a UUIDv7 with current timestamp
SELECT uuid_v7() as current_uuid;
-- Generate a UUIDv7 with specific timestamp
SELECT uuid_v7_at_time('2024-12-01 15:30:00+00'::timestamptz) as custom_uuid;
-- Extract timestamp from existing UUIDv7
SELECT
uuid_v7() as uuid_val,
uuid_v7_to_timestamptz(uuid_v7()) as extracted_timestamp,
uuid_v7_to_epoch_ms(uuid_v7()) as epoch_milliseconds;
-- Check if a UUID is version 7
SELECT
is_uuid_v7(uuid_v7()) as is_v7_true,
is_uuid_v7(gen_random_uuid()) as is_v7_false,
uuid_version(uuid_v7()) as version_7,
uuid_version(gen_random_uuid()) as version_4;
-- ==============================================================================
-- Practical Examples for Database Design
-- ==============================================================================
-- Example 1: Table with UUIDv7 primary key
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuid_v7(),
customer_id uuid NOT NULL,
order_date timestamptz DEFAULT now(),
total_amount decimal(10,2),
status text DEFAULT 'pending'
);
-- Insert some test data
INSERT INTO orders (customer_id, total_amount, status) VALUES
(uuid_v7(), 99.99, 'completed'),
(uuid_v7(), 149.50, 'pending'),
(uuid_v7(), 75.25, 'shipped');
-- Query orders with chronological sorting (UUIDv7 maintains order)
SELECT
id,
uuid_v7_to_timestamptz(id) as uuid_timestamp,
order_date,
total_amount,
status
FROM orders
ORDER BY id; -- This will be chronologically ordered!
-- ==============================================================================
-- Example 2: Audit Trail with UUIDv7
-- ==============================================================================
CREATE TABLE audit_log (
id uuid PRIMARY KEY DEFAULT uuid_v7(),
table_name text NOT NULL,
record_id uuid NOT NULL,
action text NOT NULL,
old_values jsonb,
new_values jsonb,
user_id uuid NOT NULL,
created_at timestamptz DEFAULT now()
);
-- Insert audit records
INSERT INTO audit_log (table_name, record_id, action, new_values, user_id) VALUES
('orders', (SELECT id FROM orders LIMIT 1), 'UPDATE', '{"status": "shipped"}', uuid_v7()),
('orders', (SELECT id FROM orders LIMIT 1 OFFSET 1), 'UPDATE', '{"status": "completed"}', uuid_v7());
-- Query audit trail in chronological order (efficient with UUIDv7)
SELECT
id,
table_name,
action,
uuid_v7_to_timestamptz(id) as audit_timestamp,
created_at
FROM audit_log
ORDER BY id;
-- ==============================================================================
-- Example 3: Time-based Partitioning with UUIDv7
-- ==============================================================================
-- Function to extract date for partitioning
CREATE OR REPLACE FUNCTION uuid_v7_date(uuid_val uuid) RETURNS date
LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT uuid_v7_to_timestamptz(uuid_val)::date;
$$;
-- Example partitioned table
CREATE TABLE events (
id uuid DEFAULT uuid_v7(),
event_type text,
data jsonb,
created_at timestamptz DEFAULT now()
) PARTITION BY RANGE (uuid_v7_date(id));
-- Create partitions for different date ranges
CREATE TABLE events_2024_12 PARTITION OF events
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE events_2025_01 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ==============================================================================
-- Example 4: Efficient Range Queries
-- ==============================================================================
-- Generate time-based UUIDs for a specific time range
DO $$
DECLARE
start_time timestamptz := '2024-12-01 00:00:00+00';
end_time timestamptz := '2024-12-01 23:59:59+00';
i integer;
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO events (event_type, data) VALUES
('user_action', jsonb_build_object('action', 'click', 'page', '/home'));
-- Add small delay to spread timestamps
PERFORM pg_sleep(0.001);
END LOOP;
END $$;
-- Efficient range query using UUIDv7 ordering
-- Find all events in a specific time range
SELECT *
FROM events
WHERE id >= uuid_v7_at_time('2024-12-01 10:00:00+00')
AND id <= uuid_v7_at_time('2024-12-01 11:00:00+00')
ORDER BY id;
-- ==============================================================================
-- Example 5: Migration from UUIDv4 to UUIDv7
-- ==============================================================================
-- Step 1: Add new UUIDv7 column
ALTER TABLE orders ADD COLUMN id_v7 uuid DEFAULT uuid_v7();
-- Step 2: Populate with UUIDs that maintain creation order
UPDATE orders
SET id_v7 = uuid_v7_at_time(order_date)
WHERE id_v7 IS NULL;
-- Step 3: Compare ordering
SELECT
id as old_uuid4,
id_v7 as new_uuid7,
order_date,
uuid_v7_to_timestamptz(id_v7) as extracted_timestamp
FROM orders
ORDER BY order_date;
-- ==============================================================================
-- Example 6: Performance Comparison Query
-- ==============================================================================
-- Compare index performance for range scans
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders
WHERE id > (SELECT id FROM orders ORDER BY order_date LIMIT 1 OFFSET 500);
-- vs UUIDv7 range scan (more efficient)
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders
WHERE id_v7 > (SELECT id_v7 FROM orders ORDER BY id_v7 LIMIT 1 OFFSET 500);
-- ==============================================================================
-- Example 7: UUID Generation Rate Test
-- ==============================================================================
-- Test how many UUIDs can be generated per second
DO $$
DECLARE
start_time timestamptz;
end_time timestamptz;
count integer := 0;
test_uuid uuid;
BEGIN
start_time := clock_timestamp();
-- Generate UUIDs for 1 second
WHILE clock_timestamp() < start_time + interval '1 second' LOOP
test_uuid := uuid_v7();
count := count + 1;
END LOOP;
end_time := clock_timestamp();
RAISE NOTICE 'Generated % UUIDv7s in %', count, end_time - start_time;
RAISE NOTICE 'Rate: % UUIDs per second', count;
END $$;
-- ==============================================================================
-- Example 8: Collision Detection in High-Throughput Scenario
-- ==============================================================================
-- Test for collisions in rapid generation
CREATE TEMPORARY TABLE collision_test AS
SELECT uuid_v7() as id, generate_series(1, 100000) as seq;
-- Check for any collisions
SELECT
COUNT(*) as total_generated,
COUNT(DISTINCT id) as unique_ids,
COUNT(*) - COUNT(DISTINCT id) as collisions
FROM collision_test;
-- Find any duplicate IDs (should be empty)
SELECT id, COUNT(*)
FROM collision_test
GROUP BY id
HAVING COUNT(*) > 1;
-- ==============================================================================
-- Example 9: Time-Series Data with UUIDv7
-- ==============================================================================
CREATE TABLE sensor_readings (
id uuid PRIMARY KEY DEFAULT uuid_v7(),
sensor_id text NOT NULL,
temperature decimal(5,2),
humidity decimal(5,2),
reading_time timestamptz DEFAULT now()
);
-- Insert time-series data
INSERT INTO sensor_readings (sensor_id, temperature, humidity)
SELECT
'sensor_' || (random() * 10)::int,
20 + (random() * 30),
30 + (random() * 40)
FROM generate_series(1, 10000);
-- Query recent readings using UUIDv7 ordering (very efficient)
SELECT
sensor_id,
temperature,
humidity,
uuid_v7_to_timestamptz(id) as precise_timestamp
FROM sensor_readings
WHERE id > uuid_v7_at_time(now() - interval '1 hour')
ORDER BY id DESC
LIMIT 100;
-- ==============================================================================
-- Example 10: Distributed System Coordination
-- ==============================================================================
-- Generate UUIDs that can be created on different servers but still maintain
-- global chronological order when combined
-- Server 1 timestamp
SELECT uuid_v7_at_time('2024-12-01 10:00:00.123+00') as server1_uuid;
-- Server 2 timestamp (slightly later)
SELECT uuid_v7_at_time('2024-12-01 10:00:00.456+00') as server2_uuid;
-- These will sort correctly even when combined from different sources
-- because the timestamp is embedded in the UUID
-- ==============================================================================
-- Cleanup Examples
-- ==============================================================================
-- Clean up example tables (uncomment to run)
/*
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS events_2024_12;
DROP TABLE IF EXISTS events_2025_01;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS sensor_readings;
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment