TaeHyeong Lee | Software Engineer

Using TSID as Database PK

Using TSID as Database PK

Taehyeong Lee's photo
Taehyeong Lee
·

3 min read

Overview

  • TSID stands for Time-Sorted Unique Identifier and was born to replace the traditional UUID. As the name suggests, it features the ability to be sorted by creation time using a minimal size random string. It occupies less space while being sortable by creation time, making it a suitable replacement for the database's AUTO INCREMENT ID.

TSID Features

  • Can be represented as a 64-bit integer(equals to an 18-character string) or a 13-character URL-safe Base 32 string. (Considering clients based on JavaScript that cannot handle integers larger than 53 bits, I prefer the latter. It saves 65% compared to UUID.)

  • Since the application itself is the entity generating the value, the value can be known before the data is stored.

  • It combines time information in 1/1000th of a second units with random information, ensuring uniqueness. This makes horizontal scaling of the database and concurrent deployment in multiple regions easier.

  • The inclusion of random information makes it difficult to understand the sequential relationship. On the other hand, it allows for sorting by creation date, taking advantage of the performance benefits of the B+Tree index. It's also possible to obtain the creation date from the value itself.

build.gradle.kts

  • Add the following content to build.gradle.kts in the project root.
dependencies {
    implementation("io.hypersistence:hypersistence-utils-hibernate-60:3.7.3")
}

Generating TSID

  • TSID can be generated as follows.
// Generate TSID
// Stores the time in units of 1/1000th of a second at the moment of execution
val tsid = TSID.fast()

// Output as Long type (fixed 64-bit length)
// 556028620837202474
tsid.toLong()

// Output as uppercase String type (fixed 13-character length)
// 0FDV92PPZ7AHA
tsid.toString()

// Output as lowercase String type (fixed 13-character length)
// 0fdv92ppz7aha
tsid.toLowerCase()

// Retrieve the stored Instant object
// 2024-03-14T08:19:13.719Z
tsid.instant

// Restore TSID object from TSID string
TSID.from(tsid.toString())

Applying TSID in JPA Entities

  • JPA entities can be designed to automatically generate TSID as PK as follows.
import io.hypersistence.utils.hibernate.id.Tsid
import jakarta.persistence.Column
import jakarta.persistence.Entity
import jakarta.persistence.Id
import jakarta.persistence.Table
import java.io.Serializable

@Entity
@Table(name = "foo")
class Foo : Serializable {

    // [Method 1] Generate 64-bit integer TSID
    @Id
    @Tsid
    @Column
    var id: Long? = null

    // [Method 2] Generate 13-character Base 32 string TSID
    @Id
    @Tsid
    @Column(columnDefinition = "CHAR(13)")
    var id: String? = null
    ...
}
  • When using TSID of type String, it can be expressed in DDL as follows:
CREATE TABLE `foo` (
  `id` CHAR(13) NOT NULL,
  ...
  PRIMARY KEY (`id`) USING BTREE
)
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
  • Generating actual data results in the following, confirming that it is possible to query by time order:
#id, TSID.from(id).instant
0FGA6Z3X7BRPW, 2024-03-22T00:15:00.072115Z
0FGA6Z3TFBRQR, 2024-03-22T00:15:00.050725Z
0FGA6Z3RKBRPP, 2024-03-22T00:15:00.035553Z
0FGA6Z3PZBRPH, 2024-03-22T00:15:00.022884Z
0FGA6Z3NQBRQQ, 2024-03-22T00:15:00.011920Z

Creating TSID Generation Script

  • In a Linux console, there might be an urgent need to generate a TSID. It's convenient to have a script prepared as follows for such occasions.
# Install kotlin and kscript with SDKMAN
$ sdk install kotlin
$ sdk install kscript

$ nano tsid.kts
#!/usr/bin/env kscript
@file:DependsOn("io.hypersistence:hypersistence-utils-hibernate-60:3.7.3")

import io.hypersistence.tsid.TSID

val tsid = TSID.fast()
println(tsid.toLong())
println(tsid.toString())
println(tsid.toLowerCase())
println(tsid.instant)

$ chmod +x tsid.kts

$ ./tsid.kts
558185822630215770
0FFRK1HE8H02T
0ffrk1he8h02t
2024-03-20T07:11:10.706Z

References

KotlinDatabases