Seeking Advice: Designing a High-Scale PostgreSQL System for Immutable Text-Based Identifiers

I’m designing a system to manage

Millions of unique, immutable text identifiers and would appreciate feedback on scalability and cost optimisation. Here’s the anonymised scenario: **Core Requireme

I’m designing a system to manage

Millions of unique, immutable text identifiers and would appreciate feedback on scalability and cost optimisation. Here’s the anonymised scenario:

Core Requirements 1.

Data Model: * Each record is a unique, unmodifiable text string (e.g., xxx-xxx-xxx-xxx-xxx). (The size of the text might vary and the the text might only be numbers 000-000-000-000-000) * No truncation or manipulation allowed—original values must be stored verbatim. 2.

Scale: * Initial dataset: 500M+ records, growing by millions yearly. 3.

Workload: *

Lookups: High-volume exact-match queries to check if an identifier exists. *

Updates: Frequent single-field updates (e.g., marking an identifier as "claimed"). 4.

Constraints: * Queries do not include metadata (e.g., no joins or filters by category/source). * Data must be stored in PostgreSQL (no schema-less DBs).

Current Design *

Hashing: Use a 16-byte BLAKE3 hash of the full text as the primary key. *

Schema: ​ CREATE TABLE identifiers ( id_hash BYTEA PRIMARY KEY, -- 16-byte hash raw_value TEXT NOT NULL, -- Original text (e.g., "a1b2c3-xyz") is_claimed BOOLEAN DEFAULT FALSE, source_id UUID, -- Irrelevant for queries claimed_at TIMESTAMPTZ ); *

Partitioning: Hash-partitioned by id_hash into 256 logical shards.

Open Questions 1.

Indexing: * Is a B-tree on id_hash still optimal at 500M+ rows, or would a BRIN index on claimed_at help for analytics? * Should I add a composite index on (id_hash, is_claimed) for covering queries? 2.

Hashing: * Is a 16-byte hash (BLAKE3) sufficient to avoid collisions at this scale, or should I use SHA-256 (32B)? * Would a non-cryptographic hash (e.g., xxHash64) sacrifice safety for speed? 3.

Storage: * How much space can TOAST save for raw_value (average 20–30 chars)? * Does column order (e.g., placing id_hash first) impact storage? 4.

Partitioning: * Is hash partitioning on id_hash better than range partitioning for write-heavy workloads? 5.

Cost/Ops: * I want to host it on a VPS and manage it and connect my backend API and analytics via pgBouncher * Any tools to automate archiving old/unclaimed identifiers to cold storage? Will this apply in my case? * Can I effectively backup my database in S3 in the night?

Challenges *

Bulk Inserts: Need to ingest 50k–100k entries, maybe twice a year. *

Concurrency: Handling spikes in updates/claims during peak traffic.

Alternatives to Consider? · Is Postgresql the right tool here, given that I require some relationships? A hybrid option (e.g., Redis for lookups + Postgres for storage) is an option however, the record in-memory database is not applicable in my scenario. * Would a columnar store (e.g., Citus) or time-series DB simplify this?

What Would You Do Differently? * Am I overcomplicating this with hashing? Should I just use raw_value as the PK? * Any horror stories or lessons learned from similar systems? · I read the use of partitioning based on the number of partitions I need in the table (e.g., 30 partitions), but in case there is a need for more partitions, the existing hashed entries will not reflect that, and it might need fixing. (chartmogul). Do you recommend a different way? * Is there an algorithmic way for handling this large amount of data? Thanks in advance—your expertise is invaluable!