Syed Jafer K

Its all about Trade-Offs

Benchmarking UUID4 vs ULID for DB indexing

In DB, for unique identifiers we usually use UUID (Universally Unique Identifier) and ULID (Universally Unique Lexicographically Sortable Identifier).

There is some debate going on between ULID and UUID on db performance. Today we are going to experiment this and will provide the facts.

TL;DR

CODE:https://github.com/syedjaferk/benchmarking_uuid_vs_ulid

There was no big difference between ULID and UUID on indexing runs.

  1. ULID ids performed good on reads compared to UUID.
  2. Insertion for both UUID and ULID are almost same.

UUID:

UUIDs are standardized 128-bit identifiers. There are different versions of it. In this experiment we use uuid-4 which is randomly generated id. Also this is the most used id in applications.

ULID:

ULIDs are 128-bit identifiers that are designed to be sortable based on time and provide a more compact and readable format. They are composed of two parts:

  1. Timestamp (48 bits): Encodes the number of milliseconds since Unix epoch (January 1, 1970).
  2. Randomness (80 bits): Provides uniqueness.

A ULID example looks like this: 01HZBFHT49F3NWC2QJVAZE4SS1

In this benchmarking experiment, we are going to check fact ULID performs better than UUID in DB (under the usecase where are just care about uniqueness)

Stack used for benchmarking

  1. Database: MongoDB
  2. Application: FastAPI
  3. Performance Testing: Locust

We are going to check,

  1. Insert 1M data with UUID to a new collection without having index.
  2. Insert 1M data with ULID to a new collection without having index.
  3. Insert 1M data with UUID to a new collection with index.
  4. Insert 1M data with ULID to a new collection with index.
  5. Run get requests with 100 users at 100 spawn rate for 2 min for ulid collection without index.
  6. Run get requests with 100 users at 100 spawn rate for 2 min for ulid collection with index.
  7. Run get requests with 100 users at 100 spawn rate for 2 min for uuid collection without index.
  8. Run get requests with 100 users at 100 spawn rate for 2 min for uuid collection with index.

Let’s create the base data for benchmarking

We are using Faker python package to generate 1M fake data.


from faker import Faker
import json
from ulid import ULID
import uuid


fake = Faker()

ulid_resource = {
    "data": []
}

uuid_resource = {
    "data": []
}

ulids = []
uuids = []

for itr in range(1, 100000):
    data = {
        "name": fake.name(),
        "description": fake.text(100)
    }

    _ulid = str(ULID().generate())
    _uuid = str(uuid.uuid4())

    ulid_dct = {
        "id": _ulid
    }
    ulid_dct.update(data)
    ulid_resource["data"].append(ulid_dct)
    ulids.append(_ulid)


    uuid_dct = {
        "id": _uuid
    }
    uuid_dct.update(data)
    uuid_resource["data"].append(uuid_dct)
    uuids.append(_uuid)


with open('uuid_mock_data.json', 'w') as f:
    json.dump(uuid_resource, f)

with open('ulid_mock_data.json', 'w') as f:
    json.dump(ulid_resource, f)

with open('ulids.json', 'w') as f:
    json.dump({"ids": ulids}, f)

with open('uuids.json', 'w') as f:
    json.dump({"ids": uuids}, f)

we have written this data to mock.json file. This will create all the test json files for our testing.

Benchmarking

Let’s spin up a mongodb container.


docker run -p 27017:27017 --name mongo_container --rm mongo and create a database named ulid_vs_uuid

  1. Insert 1M data with UUID to a new collection without having index.

Create a collection named, uuid_no_index .

The below script will read the data from mock.json file created using faker and inserts it into the db collection.


from pymongo.mongo_client import MongoClient
import uuid
from datetime import datetime
import json

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["uuid_no_index"]

mock_data = json.load(open('uuid_mock_data.json', 'r'))["data"]

start_time = datetime.now()

for data in mock_data:
    collection.insert_one(data)

end_time = datetime.now()
difference = end_time - start_time
print(f"UUID time {difference}")

  1. Insert 1M data with ULID to a new collection without having index.

create a collection named, ulid_no_index .

The below script will read the data from mock.json file created using faker and inserts it into the db collection.


from pymongo.mongo_client import MongoClient
import uuid
from datetime import datetime
import json

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["ulid_no_index"]

mock_data = json.load(open('ulid_mock_data.json', 'r'))["data"]

start_time = datetime.now()

for data in mock_data:
    collection.insert_one(data)

end_time = datetime.now()
difference = end_time - start_time
print(f"UUID time {difference}")
  1. Insert 1M data with UUID to a new collection with index.

create a collection named uuid_with_index and set the index field to id with order as desc -1

The below script will read the data from mock.json file created using faker and inserts it into the db collection.


from pymongo.mongo_client import MongoClient
import uuid
from datetime import datetime
import json

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["uuid_with_index"]

mock_data = json.load(open('uuid_mock_data.json', 'r'))["data"]

start_time = datetime.now()

for data in mock_data:
    collection.insert_one(data)

end_time = datetime.now()
difference = end_time - start_time
print(f"UUID time {difference}")
  1. Insert 1M data with ULID to a new collection with index.

create a collection named ulid_with_index and set the index field to id with order as desc -1

The below script will read the data from mock.json file created using faker and inserts it into the db collection.


from pymongo.mongo_client import MongoClient
import uuid
from datetime import datetime
import json

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["ulid_with_index"]

mock_data = json.load(open('ulid_mock_data.json', 'r'))["data"]

start_time = datetime.now()

for data in mock_data:
    collection.insert_one(data)

end_time = datetime.now()
difference = end_time - start_time
print(f"UUID time {difference}")

After writing in to the db,

  1. Run get requests with 100 users at 100 spawn rate for 2 min for ulid collection without index.

For this test, we wrote a fastapi application, which connects to the mongodb and fetches the data from it. Run it using uvicorn <file_name>:app --port 8000


from fastapi import FastAPI, HTTPException
from pymongo.mongo_client import MongoClient

app = FastAPI()

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["ulid_no_index"]

@app.get("/ids/{id_val}")
def read_item(id_val: str):
    res = collection.find_one({"id": id_val}, {'_id':0})
    if res['id'] == id_val:
        return {"status": "success"}
    else:
        return {"status": "failure"}

To performance test it, we have created a locust file,


import random
import logging
from locust import task, constant
from locust.contrib.fasthttp import FastHttpUser
import json


log = logging.getLogger("rest-api-short-urls")

data = json.load(open('ulids.json', 'r'))["ids"]

class LocustClient(FastHttpUser):
    wait_time = constant(0)
    host = "http://localhost:8000/ids"


    def __init__(self, environment):
        """ Class constructor."""
        super().__init__(environment)


    @task
    def test_get_items(self):

        try:
            id_val = random.choice(data)
            with self.client.get(f'/{id_val}', name='get id value') as resp_of_api:
                if resp_of_api.status_code == 200:
                    log.info("API call resulted in success.")
                else:
                    log.error("API call resulted in failed.")
             
        except Exception as e:
            log.error(f"Exception occurred! details are {e}")

Run the locust file with 100 users 100 spawn rate for 2 min. locust -f <file_name.py> -u 100 -r 100 -t 2m

  1. Run get requests with 100 users at 100 spawn rate for 2 min for ulid collection with index.

For this test, we wrote a fastapi application, which connects to the mongodb and fetches the data from it. Run it using uvicorn <file_name>:app --port 8000


from fastapi import FastAPI, HTTPException
from pymongo.mongo_client import MongoClient

app = FastAPI()

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["ulid_with_index"]

@app.get("/ids/{id_val}")
def read_item(id_val: str):
    res = collection.find_one({"id": id_val}, {'_id':0})
    if res['id'] == id_val:
        return {"status": "success"}
    else:
        return {"status": "failure"}

To performance test it, we have created a locust file,



import random
import logging
from locust import task, constant
from locust.contrib.fasthttp import FastHttpUser
import json


log = logging.getLogger("rest-api-short-urls")

data = json.load(open('ulids.json', 'r'))["ids"]

class LocustClient(FastHttpUser):
    wait_time = constant(0)
    host = "http://localhost:8000/ids"


    def __init__(self, environment):
        """ Class constructor."""
        super().__init__(environment)


    @task
    def test_get_items(self):

        try:
            id_val = random.choice(data)
            with self.client.get(f'/{id_val}', name='get id value') as resp_of_api:
                if resp_of_api.status_code == 200:
                    log.info("API call resulted in success.")
                else:
                    log.error("API call resulted in failed.")
             
        except Exception as e:
            log.error(f"Exception occurred! details are {e}")

Run the locust file with 100 users 100 spawn rate for 2 min. locust -f <file_name.py> -u 100 -r 100 -t 2m

After indexing we are able to handle more requests.

  1. Run get requests with 100 users at 100 spawn rate for 2 min for uuid collection without index.

For this test, we wrote a fastapi application, which connects to the mongodb and fetches the data from it. Run it using uvicorn <file_name>:app --port 8000.


from fastapi import FastAPI, HTTPException
from pymongo.mongo_client import MongoClient

app = FastAPI()

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["uuid_no_index"]

@app.get("/ids/{id_val}")
def read_item(id_val: str):
    res = collection.find_one({"id": id_val}, {'_id':0})
    if res['id'] == id_val:
        return {"status": "success"}
    else:
        return {"status": "failure"}

To performance test it, we have created a locust file, which consumes uuid.json (a file which contains the details of all the ids inserted in to the db)


import random

import logging
from locust import task, constant
from locust.contrib.fasthttp import FastHttpUser
import json


log = logging.getLogger("rest-api-short-urls")

data = json.load(open('uuids.json', 'r'))["ids"]

class LocustClient(FastHttpUser):
    wait_time = constant(0)
    host = "http://localhost:8000/ids"


    def __init__(self, environment):
        """ Class constructor."""
        super().__init__(environment)


    @task
    def test_get_items(self):

        try:
            id_val = random.choice(data)
            with self.client.get(f'/{id_val}', name='get id value') as resp_of_api:
                if resp_of_api.status_code == 200:
                    log.info("API call resulted in success.")
                else:
                    log.error("API call resulted in failed.")
             
        except Exception as e:
            log.error(f"Exception occurred! details are {e}")
  1. Run get requests with 100 users at 100 spawn rate for 2 min for uuid collection without index.

For this test, we wrote a fastapi application, which connects to the mongodb and fetches the data from it. Run it using uvicorn <file_name>:app --port 8000.


from fastapi import FastAPI, HTTPException
from pymongo.mongo_client import MongoClient

app = FastAPI()

# Mongo Db Connection
MONGO_CONN_STR = "mongodb://localhost:27017"
mongo_client = MongoClient(MONGO_CONN_STR)
database = mongo_client['ulid_vs_uuid']
collection = database["uuid_with_index"]

@app.get("/ids/{id_val}")
def read_item(id_val: str):
    res = collection.find_one({"id": id_val}, {'_id':0})
    if res['id'] == id_val:
        return {"status": "success"}
    else:
        return {"status": "failure"}

To performance test it, we have created a locust file, which consumes uuid.json (a file which contains the details of all the ids inserted in to the db)


import random
import logging
from locust import task, constant
from locust.contrib.fasthttp import FastHttpUser
import json


log = logging.getLogger("rest-api-short-urls")

data = json.load(open('uuids.json', 'r'))["ids"]

class LocustClient(FastHttpUser):
    wait_time = constant(0)
    host = "http://localhost:8000/ids"


    def __init__(self, environment):
        """ Class constructor."""
        super().__init__(environment)


    @task
    def test_get_items(self):

        try:
            id_val = random.choice(data)
            with self.client.get(f'/{id_val}', name='get id value') as resp_of_api:
                if resp_of_api.status_code == 200:
                    log.info("API call resulted in success.")
                else:
                    log.error("API call resulted in failed.")
             
        except Exception as e:
            log.error(f"Exception occurred! details are {e}")

Conclusion

From the above operations its clear that,

  1. ULID is performing better on read.
  2. ULID & UUID is performing same on write/insertion.
  3. ULID is preferred for Less memory usage compared to UUID.
  4. ULID is predictable.
  5. UUID4 is completely random.