Skip to content

Instantly share code, notes, and snippets.

@ItsWendell
Created May 4, 2021 14:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ItsWendell/1989ef0a84993d3a2415e5511f05730e to your computer and use it in GitHub Desktop.
Save ItsWendell/1989ef0a84993d3a2415e5511f05730e to your computer and use it in GitHub Desktop.
NanoID Implementation in PL/pgSQL for Postgres.
-- Highly performant NanoID implementation in PL/pgSQL.
--
-- NOTE: This is a concept implementation, hasn't been battle tested.
--
-- Version: 0.1
-- Inspired by https://github.com/Jakeii/nanoid-postgres
-- @author github.com/ItsWendell
CREATE OR REPLACE FUNCTION gen_nanoid(size int DEFAULT 21)
RETURNS text AS $$
DECLARE
id text := '';
BEGIN
id := encode(gen_random_bytes((3.0 * (size::float / 4.0))::int), 'base64'); -- Generate random base64 string with atleast 'size' of characters.
id := translate(id, '/+', '_-'); -- Translate base64 to url safe base64
id := left(id, size); -- Strip all unnessesary characters.
RETURN id;
END
$$ LANGUAGE PLPGSQL VOLATILE;
@ItsWendell
Copy link
Author

Any feedback would be appreciated, here's also a little benchmark:

-- Benchmark run on
-- Dell XPS 9300
-- OS: Fedora 34 (Workstation Edition) x86_64
-- CPU: Intel i7-1065G7 (8) @ 3.900GHz
-- Mem: 15583MiB
-- PostgresDB running within a docker container.


-- Testing pg_nanoid: (rust extension):
transaction type: ./tests/pg_nanoid.test.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 0.193 ms
tps = 51737.059940 (including connections establishing)
tps = 53139.341744 (excluding connections establishing)
statement latencies in milliseconds:
         0.183  SELECT gen_nanoid();

-- Testing nanoid: (function from https://github.com/Jakeii/nanoid-postgres):
transaction type: ./tests/nanoid.test.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 0.250 ms
tps = 40050.368016 (including connections establishing)
tps = 40781.515141 (excluding connections establishing)
statement latencies in milliseconds:
         0.241  select generate_nanoid();

-- Testing nanoid: (function from https://gist.github.com/ItsWendell/1989ef0a84993d3a2415e5511f05730e):
transaction type: ./tests/nanoid-v5.test.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 8
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 0.212 ms
tps = 47263.362727 (including connections establishing)
tps = 48326.853670 (excluding connections establishing)
statement latencies in milliseconds:
         0.203  select generate_nanoid_v5();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment