The uuid-ossp extension

The uuid-ossp extension provides a suite of functions for generating Universally Unique Identifiers (UUIDs) directly within your Postgres database. UUIDs are essential for ensuring data uniqueness across distributed systems and are widely used as primary keys and for various other applications requiring unique IDs. This extension offers a variety of UUID generation methods, including time-based, random, and name-based UUIDs, providing flexibility for different use cases.

This guide provides an introduction to the uuid-ossp extension. You'll learn how to enable the extension on Neon, explore the functions for generating different types of UUIDs, understand common use cases where UUIDs are beneficial, and consider important aspects like performance and security.

Enable the uuid-ossp extension

You can enable the extension by running the following CREATE EXTENSION statement in the Neon SQL Editor or from a client such as psql that is connected to your Neon database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Version availability:

Please refer to the list of all extensions available in Neon for up-to-date extension version information.

UUID Functions

The uuid-ossp extension offers a range of functions for generating UUIDs, each with unique characteristics. Let's explore each function in detail:

Version 1 UUIDs (time-based)

The version 1 UUID generation functions in uuid-ossp are based on the time of creation and the MAC address of the generating machine. These UUIDs are suitable for scenarios where time-based ordering is important, and uniqueness across distributed systems is required.

• uuid_generate_v1():

  The uuid_generate_v1() function generates UUIDs based on the version 1 algorithm. Version 1 UUIDs are time-based, meaning they incorporate the current timestamp and the MAC address of the computer where the UUID is generated. This approach leverages the uniqueness of hardware identifiers and precise time to create UUIDs that are likely to be unique across different systems and over time.

  SELECT uuid_generate_v1();
  -- 506a753c-02fe-11f0-9122-6f83fcb8d092 (example output)

  Privacy and Security Considerations for Version 1 UUIDs

  It's crucial to be aware that Version 1 UUIDs embed the MAC address of the generating computer. This can present privacy and security concerns because:

  • Machine identification: The MAC address can potentially be used to identify the specific machine that generated the UUID, raising privacy issues if this information should remain confidential.
  • Predictability: The time component and the structure of Version 1 UUIDs make them somewhat predictable, which could be a security risk in certain applications where UUIDs are used for security-sensitive purposes.

  If privacy or predictability is a concern, consider using uuid_generate_v1mc() or version 4 UUIDs instead which are discussed below.

• uuid_generate_v1mc(): Version 1 UUIDs with multicast MAC address

  The uuid_generate_v1mc() function is similar to uuid_generate_v1() but addresses the privacy concerns by using a randomly generated multicast MAC address instead of the actual MAC address of the computer.

  SELECT uuid_generate_v1mc();
  -- 8b119520-02ff-11f0-9d55-6761ef62a796 (example output)

Version 3 UUIDs (name-based, MD5 hash)

• uuid_generate_v3(namespace uuid, name text):

  The uuid_generate_v3(namespace uuid, name text) function generates version 3 UUIDs. These UUIDs are name-based and deterministic, meaning they are generated by hashing an input name using the MD5 algorithm within a specified namespace. For the same namespace UUID and input name, this function will always produce the exact same UUID.

  SELECT uuid_generate_v3(uuid_ns_url(), 'https://example.com');
  -- 68794df6-5e20-385f-ab08-bb73f8a433cb (always the same for 'https://example.com')

  Here, uuid_ns_url() is a predefined UUID constant representing the URL namespace, which is used as the namespace argument for generating UUIDs based on URLs.

  Available predefined namespace UUIDs are discussed in the UUID Constants section below.

  Use cases:

  • Generating consistent identifiers for entities based on a name, such as creating a UUID for a URL, DNS name, or any other string identifier.
  • Scenarios where you need to ensure that generating a UUID for the same entity (identified by name within a namespace) always results in the same UUID across different systems or over time.
  • Content Management Systems where stable identifiers for content pieces are required, regardless of access time or location.

Version 4 UUIDs (random)

• uuid_generate_v4():

  The uuid_generate_v4() function generates version 4 UUIDs, which are derived entirely from random numbers. These are the most common type of UUIDs due to their simplicity and strong guarantee of uniqueness. Postgres also provides the built-in function gen_random_uuid() which is functionally equivalent to uuid_generate_v4().

  SELECT uuid_generate_v4();
  -- 08e776b5-0652-431e-a841-5840616b500b (example output)

  Key characteristics of Version 4 UUIDs:

  • Randomly generated: Based on high-quality random number generators.
  • High uniqueness probability: Extremely low probability of collision, making them suitable for most applications requiring unique identifiers.

  Use Cases:

  • General-purpose unique identifiers where predictability or specific ordering is not required.
  • Primary keys for database tables, especially in distributed systems.
  • Identifying records in systems where high randomness and uniqueness are paramount.
  • Simplifying UUID generation when deterministic or time-based approaches are not necessary.

Version 5 UUIDs (name-based, SHA-1 hash)

• uuid_generate_v5(namespace uuid, name text):

  The uuid_generate_v5(namespace uuid, name text) function is similar to uuid_generate_v3() but uses the SHA-1 hashing algorithm instead of MD5. Version 5 UUIDs are also name-based and deterministic, producing the same UUID for the same input namespace and name.

  SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');
  -- cfbff0d1-9375-5685-968c-48ce8b15ae17 (always the same for 'example.com')

  Version 3 vs. Version 5 UUIDs

  While both Version 3 and Version 5 provide deterministic, name-based UUIDs, Version 5 is generally recommended due to the use of SHA-1 hashing, which is considered more secure than MD5. If security is a significant concern for your application, Version 5 is the better choice.

UUID constants

uuid-ossp also provides functions to return predefined UUID constants, which are particularly useful as standard namespace identifiers for Version 3 and Version 5 UUID generation:

• uuid_nil(): The Nil UUID constant

  The uuid_nil() function returns the predefined "nil" UUID constant: '00000000-0000-0000-0000-000000000000'.

  SELECT uuid_nil();

  Purpose of the Nil UUID:

  • Representing absence: Similar to NULL for other data types, the nil UUID is often used to indicate the absence of a UUID value or as a default placeholder.
  • Special value: It does not correspond to any real-world generated UUID and is a specific, non-existent UUID value for particular use cases.

  Use cases:

  • Initializing UUID columns when a valid UUID is not yet available.
  • Using it as a sentinel value in code or database operations to represent "no UUID".

• Namespace UUID constants (uuid_ns_dns(), uuid_ns_url(), uuid_ns_oid(), uuid_ns_x500()):

  These functions return constant UUIDs that are specifically designated as namespaces for different identifier types, as per the UUID specification. They are intended to be used as the namespace argument in uuid_generate_v3() and uuid_generate_v5() functions.

  • uuid_ns_dns()

    Represents the DNS namespace, intended for generating UUIDs from DNS names.

    SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');
    -- cfbff0d1-9375-5685-968c-48ce8b15ae17 (always the same for 'example.com')
    SELECT uuid_generate_v3(uuid_ns_dns(), 'example.com');
    -- 9073926b-929f-31c2-abc9-fad77ae3e8eb (always the same for 'example.com')

  • uuid_ns_url()

    Represents the URL namespace, for generating UUIDs from URLs.

    SELECT uuid_generate_v3(uuid_ns_url(), 'https://example.com');
    -- 68794df6-5e20-385f-ab08-bb73f8a433cb (always the same for 'https://example.com')

  • uuid_ns_oid()

    Represents the ISO Object Identifier (OID) namespace. Note that these OIDs refer to the ASN.1 standard and are distinct from PostgreSQL's internal OIDs.

    SELECT uuid_generate_v5(uuid_ns_oid(), '12345');
    -- bf547c8b-0674-5afe-97ad-d6e7556e56fa (always the same for '12345')

  • uuid_ns_x500()

    Represents the X.500 Distinguished Name (DN) namespace.

    SSELECT uuid_generate_v5(uuid_ns_x500(), 'CN=John Doe, DC=example, DC=com');
    -- e9ba549f-a675-5490-b054-ad862cb8c1d2 (always the same for 'CN=John Doe, DC=example, DC=com')

  Usage of namespace UUID constants:

  These constants are crucial for generating deterministic UUIDs based on specific namespaces, ensuring consistent UUIDs for the same input name across different systems.

Performance and storage considerations

While UUIDs offer significant advantages, it's important to be aware of potential performance and storage implications:

• Storage size: UUIDs are 128-bit values (16 bytes), which are larger than typical integer primary keys (4 bytes for integer, 8 bytes for bigint). This increased size can lead to higher storage requirements, especially in tables with a very large number of rows.

• Indexing performance: Randomly generated UUIDs (version 4) can lead to less efficient indexing compared to sequential integer IDs. Inserting rows with random UUIDs as primary keys can cause index fragmentation, as new entries are inserted at random locations in the index. This can slow down write operations and potentially affect read query performance, especially in very large tables and under high write loads. However, using sequential or time-ordered UUIDs (like version 1) can mitigate this issue.

Conclusion

The uuid_ossp extension is a valuable tool for UUID generation in Postgres. It offers diverse functions for creating UUIDs tailored to various needs: random, name-based, or time-based.

To effectively use uuid_ossp, remember these key recommendations:

• For general use, version 4 UUIDs (uuid_generate_v4() or gen_random_uuid()) are ideal for random unique IDs.
• For deterministic IDs, choose version 5 (uuid_generate_v5()) over version 3 (uuid_generate_v3()) for better security.
• Use version 1 UUIDs (uuid_generate_v1() or uuid_generate_v1mc()) only when time-based ordering is essential, keeping privacy implications in mind.

By selecting the appropriate UUID version based on your requirements, you can ensure the uniqueness and consistency of identifiers in your Postgres database.

Resources

• uuid-ossp on PostgreSQL Documentation
• RFC 4122 - A Universally Unique IDentifier (UUID) URN Namespace