Contents

• pg_slug_gen
  • Features
  • Installation
    • From Source
    • Enable Extension
    • Using Docker (Development)
  • Usage
    • Function Signature
    • Interface
    • Timestamp Precision
    • Slug Format
    • Examples
  • How It Works
    • Algorithm
    • Character Buckets (QWERTY Layout)
    • Uniqueness Guarantee
  • Development
    • Project Structure
    • Building with Docker
    • Running Tests
  • License
  • Author
  • Contributing

pg_slug_gen

PostgreSQL extension for generating unique random slugs based on timestamp.

Features

• Guaranteed Uniqueness: Based on timestamp - unique when max 1 insert per time unit
• Cryptographically Random: Uses pg_strong_random() for character selection
• Configurable Precision: seconds, milliseconds, microseconds, or nanoseconds
• URL-Friendly: Only letters A-Z, a-z with hyphen separator
• QWERTY Layout: Characters distributed following keyboard layout

Installation

From Source

make
make install

Enable Extension

CREATE EXTENSION pg_slug_gen;

Using Docker (Development)

./dev.sh start
./dev.sh rebuild
./dev.sh psql

Usage

Function Signature

gen_random_slug(slug_length int DEFAULT 16) RETURNS text

Interface

gen_random_slug()      -- default: 16 (microseconds)
gen_random_slug(10)    -- seconds
gen_random_slug(13)    -- milliseconds
gen_random_slug(16)    -- microseconds
gen_random_slug(19)    -- nanoseconds

Timestamp Precision

Each precision level corresponds to Unix timestamp digits:

Precision	Digits	Timestamp Example	Max Inserts (collision-free)
Seconds	10	1732056789	1/second
Milliseconds	13	1732056789123	1,000/second
Microseconds	16	1732056789123456	1,000,000/second
Nanoseconds	19	1732056789123456789	1 billion/second

Slug Format

The slug includes a hyphen separator at the midpoint:

Precision	Format	Example Output	Total Length
10 (sec)	5-5	AbCdE-FgHiJ	11 chars
13 (ms)	6-7	AbCdEf-GhIjKlM	14 chars
16 (μs)	8-8	AbCdEfGh-IjKlMnOp	17 chars
19 (ns)	9-10	AbCdEfGhI-JkLmNoPqRs	20 chars

Default: 16 (microseconds) - 17 characters

Examples

Basic Usage

-- Default (microseconds precision)
SELECT gen_random_slug();
-- Result: 'qWeRtYuI-oPasDfGh'

-- Specific precision
SELECT gen_random_slug(10);   -- seconds: 11 chars
SELECT gen_random_slug(13);   -- milliseconds: 14 chars
SELECT gen_random_slug(16);   -- microseconds: 17 chars
SELECT gen_random_slug(19);   -- nanoseconds: 20 chars

As Column Default

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    slug text DEFAULT gen_random_slug() UNIQUE
);

INSERT INTO products (name) VALUES ('My Product');
-- slug is automatically generated

In INSERT Statement

INSERT INTO products (name, slug)
VALUES ('Another Product', gen_random_slug(13));

How It Works

Algorithm

1. Get current timestamp with specified precision
2. Convert each digit (0-9) to a letter using QWERTY-based bucket mapping
3. Randomly select one letter from the bucket for each digit
4. Insert hyphen at midpoint

Character Buckets (QWERTY Layout)

Digit 0: qWeRtY (6 letters)
Digit 1: QwErTy (6 letters)
Digit 2: uIoPa  (5 letters)
Digit 3: UiOpA  (5 letters)
Digit 4: sDfGh  (5 letters)
Digit 5: SdFgH  (5 letters)
Digit 6: jKlZx  (5 letters)
Digit 7: JkLzX  (5 letters)
Digit 8: cVbNm  (5 letters)
Digit 9: CvBnM  (5 letters)

Each bucket contains unique characters - no overlap between buckets.

Uniqueness Guarantee

• Different timestamps = different slugs (at least one digit differs)
• Same timestamp = possible collision (~1 in 10 million with microseconds)

Precision	Collision-free if…
seconds	max 1 insert/second
milliseconds	max 1 insert/millisecond
microseconds	max 1 insert/microsecond
nanoseconds	max 1 insert/nanosecond

Development

Project Structure

pg_slug_gen/
├── pg_slug_gen.c           # Main C source code
├── pg_slug_gen.control     # Extension metadata
├── sql/
│   └── pg_slug_gen--1.0.sql # SQL installation script
├── test/
│   ├── sql/
│   │   └── basic.sql          # Regression tests
│   └── expected/
│       └── basic.out          # Expected output
├── Makefile
├── Dockerfile
├── docker-compose.yml
├── dev.sh                     # Development helper
└── README.md

Building with Docker

./dev.sh start     # Start PostgreSQL container
./dev.sh build     # Build extension
./dev.sh install   # Install in database
./dev.sh rebuild   # Build + Install

Running Tests

./dev.sh test      # Run regression tests
./dev.sh quicktest # Quick manual test

License

MIT License - see LICENSE file for details.

Author

Fernando Olle

Contributing

Contributions are welcome! Please open an issue or submit a pull request.