Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient

Question

Microsoft SQL Server offers the NEWID command to generate a new GUID (the Microsoft version of UUID) value that can be used as a primary key value (in their uniqueidentifier data type). These are not sequential in nature, so updating an index can be inefficient.

Alternatively, MS SQL Server offers the NEWSEQUENTIALID command. To quote their documentation:

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

Is there a way to get the more efficiently-indexed UUID in Postgres?

Answer 1

uuid-ossp module

PostgreSQL uses the standardized UUID generation algorithms provided by ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122. From the docs on uuid-ossp,

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants.

uuid_generate_v1() This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.

So long as the MAC address does not change, you'll be golden.

That all said, I agree with @a_horse_with_no_name,

From my understanding this is only necessary in SQL Server because tables are stored in a clustered index which makes random insertions slower then with a heap table. Postgres has no such concept, so I don't think that would make a difference in Postgres

In fact, given the chance of fewer collisions and more security, I would take it. And to that I would use uuid_generate_v4()

uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.

Answer 2

Yes, Tomas Vondra created a sequential uuid extension for PostgreSQL based on the MSSQL newsequentialid only less predictable.

Primary key's are used by indexes to find data quicker as they are sequential so by using completely random UUID's they miss the advantage.

Using perfectly sequential UUID become predictable and works against the original idea of using UUID's in that they are globally unique.

Vondra's extension is using bits of both worlds: " ... Instead of generating a perfectly sequential prefix, the value is sequential for a while, but also wraps around once in a while. The wrapping eliminates the predictability..."

How to install it into your PostgreSQL: Prerequires on Ubuntu:

postgresql-server-dev-11

Install:

git clone https://github.com/tvondra/sequential-uuids.git
cd sequential-uuids/
make
sudo make install

inside PostgreSQL:

CREATE EXTENSION sequential-uuids;

Make it work by example:

DROP SEQUENCE IF EXISTS tablename_names_seq;
DROP TABLE IF EXISTS names;
CREATE SEQUENCE tablename_names_seq AS integer;
CREATE TABLE names(id uuid NOT NULL DEFAULT uuid_sequence_nextval('tablename_names_seq'::regclass),name varchar(40));
INSERT INTO names (name) VALUES ('William McKinley'),('Theodore Roosevelt'),('William Taft'),('Woodrow Wilson'),('Warren Harding'),('Calvin Coolidge'),('Herbert C. Hoover'),('Franklin Delano Roosevelt'),('Harry S Truman'),('Dwight David Eisenhower'),('John Fitzgerald Kennedy'),('Lyndon Baines Johnson'),('Richard Milhous Nixon'),('Gerald R. Ford'),('James (Jimmy) Earl Carter, Jr.'),('Ronald Wilson Reagan'),('George H. W. Bush'),('William (Bill) Jefferson Clinton'),('George W. Bush'),('Barack Obama'),('Donald Trump');

...will create in my case:

db=# select * from names;
id | name
--------------------------------------+----------------------------------
00005b41-8b07-3daa-216e-30b3e8177705 | William McKinley
00004ce4-1183-7689-47a0-a56d7e8e987c | Theodore Roosevelt
...
000042a0-2e72-c92b-6d61-7a79a5bf3b7e | Barack Obama
00002d51-34c3-a682-12ab-0d6287394899 | Donald Trump

source: https://github.com/tvondra/sequential-uuids https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/

Answer 3

NEWSEQUENTIALID doesn't conform to RFC 4122, so it sounds like you're willing to consider nonconforming UUID-esque "types". ULID offers lexicographic sorting and is a "drop-in" replacement for UUIDs, being 16 bytes. (KSUID is another option, though it's 20 bytes, so you'll need to make it a bytea.) PostGres doesn't require UUIDs to conform to the spec, so the following works:

CREATE TABLE mytable (
  id uuid NOT NULL,
  CONSTRAINT uuid_pkey PRIMARY KEY (id)
);
INSERT INTO public.mytable(id) VALUES ('00dead00-0000-0000-0000-0000beef0000');

The above lacks the version/variant required by RFC 4122, but PostGres will happily store it, as well as any ULID. The ULID spec links to implementations in different languages, but none for PostGres currently exists. You'll need to write your own function or generate the ULID in your application.

Edit: I found some PostGres implementations: A function. An extension. A Go extension. A work in progress.

A warning: one should not have ULIDs and RFC 4122 compliant UUIDs in the same column:

...newsequentialid() GUIDs are not RFC 4122 compliant! As a result, GUIDs from newsequentialid() have a higher likelihood of colliding with RFC 4122 compliant GUIDs such as sequential or random GUIDs. - Stephen Cleary

Answer 4

Nowadays, as of June 20, 2023, there are the proposed UUID version 6, 7 and 8, all three of which have a sortable byte encoding.

Particularly UUIDv7 is nice. It uses a UNIX timestamp instead of a Gregorian-based timestamp, and “[t]he least significant 62 bits may be used either for sub-second precision, some sort of sequence counter or filled with pseudo-random data.”

Support for UUIDv7 can be added to your database via the pg_uuidv7 PostgreSQL extension.

pg_uuidv7 is not the only Postgres extension that provided UUIDv7 support. There is also pg_idkit (and this Gist). But, in my opinion pg_uuidv7 deserves the most momentum:

• pg_uuidv7 only supports UUIDv7, and doesn't come with the baggage of UUID alternatives like ULID that I don't personally need..
• pg_uuidv7 is implemented in C and thus introduces no additional dependencies.
• The naming of uuid_generate_v7() is consistent with uuid_generate_v1() through uuid_generate_v5 from the standard uuid-ossp contrib module, which will make it easy for me to swap to that contrib module if/when that function is added there.