# Source: https://neon.com/llms/extensions-uuid-ossp.txt

# The uuid-ossp extension

> The document explains how to use the uuid-ossp extension in Neon to generate universally unique identifiers (UUIDs) within PostgreSQL databases.

## Source

- [The uuid-ossp extension HTML](https://neon.com/docs/extensions/uuid-ossp): The original HTML version of this documentation

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.

   **Note**: `uuid-ossp` is a widely-used Postgres extension that offers a range of UUID generation methods beyond the basic built-in functions. It is particularly valuable when you need specific types of UUIDs or require deterministic, name-based UUIDs for consistent identifiers.

## Enable the `uuid-ossp` extension

You can enable the extension by running the following `CREATE EXTENSION` statement in the [Neon SQL Editor](https://neon.com/docs/get-started/query-with-neon-sql-editor) or from a client such as [psql](https://neon.com/docs/connect/query-with-psql-editor) that is connected to your Neon database.

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```

**Version availability:**

Please refer to the [list of all extensions](https://neon.com/docs/extensions/pg-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](https://datatracker.ietf.org/doc/html/rfc4122#section-4.2.2). 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.

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

     **Important** 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.

  ```sql
  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](https://en.wikipedia.org/wiki/MD5) within a specified `namespace`. For the same `namespace` UUID and input `name`, this function will always produce the exact same UUID.

  ```sql
  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](https://neon.com/docs/extensions/uuid-ossp#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()`](https://neon.com/postgresql/postgresql-tutorial/postgresql-uuid#generating-uuid-values) which is functionally equivalent to `uuid_generate_v4()`.

  ```sql
  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](https://en.wikipedia.org/wiki/SHA-1) instead of MD5. Version 5 UUIDs are also name-based and deterministic, producing the same UUID for the same input namespace and name.

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

     **Tip** 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'`.

  ```sql
  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.

    ```sql
    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.

    ```sql
    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.

    ```sql
    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.

    ```sql
    SELECT 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](https://www.postgresql.org/docs/current/uuid-ossp.html)
- [RFC 4122 - A Universally Unique IDentifier (UUID) URN Namespace](https://www.rfc-editor.org/rfc/rfc4122)