PostgreSQL中满足int8密钥生成三类约束的最优方案问询

Great question—you’ve clearly thought through the tradeoffs of partial solutions, so let’s zero in on an approach that checks all three of your boxes:

First, let’s recap your non-negotiable requirements to stay aligned:

• Unpredictable randomness: Keys can’t reveal how many rows exist or their creation order
• Database-wide uniqueness: No schema changes needed when merging tables or adding Table-Per-Class parent entities
• Bounded range: Adjustable int8 scope that works smoothly with base58 encoding for URLs

The Winning Implementation: Cryptographically Random Keys + Global Unique Tracking + Efficient Pooling #

Here’s a step-by-step setup that delivers on every constraint, with fixes for the collision, performance, and lock issues you noted:

1. Use Cryptographically Secure Randomness #

PostgreSQL’s built-in random() isn’t strong enough for unpredictable keys—it’s prone to pattern detection. Instead, enable the pgcrypto extension, which provides gen_random_int8(): a true cryptographically secure random number generator that eliminates any chance of deriving row count or order.

Enable it first:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. Enforce Database-Wide Uniqueness with a Global Registry #

To avoid duplicate keys across all tables (and skip schema changes later when merging or adding parents), create a single global table to track every key that’s ever been used. This acts as a single source of truth for uniqueness:

CREATE TABLE global_used_keys (
  key int8 PRIMARY KEY,
  created_at timestamptz DEFAULT now()
);

3. Bounded Key Generation (Adjustable Range) #

Create a helper function to generate keys within your desired range—you can tweak min_key and max_key anytime to expand or shrink the scope:

CREATE OR REPLACE FUNCTION generate_bounded_random_key(min_key int8, max_key int8)
RETURNS int8 AS $$
BEGIN
  -- Generate a random int8 within the specified bounds
  RETURN gen_random_int8() % (max_key - min_key + 1) + min_key;
END;
$$ LANGUAGE plpgsql VOLATILE;

For example, start with a range of 1 to 10^12 (plenty for most apps, easy to expand later) and adjust as your needs grow.

4. Fix Collisions & Performance with a Pre-Generated Key Pool #

Generating keys on-the-fly risks collisions and lock contention. Instead, use a pre-populated pool of available keys to serve requests quickly. Here’s how to set it up:

a. Create the Available Keys Pool

CREATE TABLE available_keys (
  key int8 PRIMARY KEY,
  created_at timestamptz DEFAULT now()
);

b. Populate the Pool (Run Periodically)

Use a cron job or PostgreSQL background worker to batch-generate keys and add them to the pool (skipping duplicates automatically):

-- Insert 1000 new bounded keys into the pool (adjust batch size for your traffic)
INSERT INTO available_keys (key)
SELECT generate_bounded_random_key(1, 1000000000000)
FROM generate_series(1, 1000)
ON CONFLICT DO NOTHING;

c. Retrieve Keys Fast (Lock-Free)

Use SELECT ... FOR UPDATE SKIP LOCKED to grab an available key without waiting for locks, then mark it as used in the global registry:

WITH selected_key AS (
  SELECT key FROM available_keys
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
DELETE FROM available_keys ak
USING selected_key sk
WHERE ak.key = sk.key
RETURNING sk.key;

-- Immediately log the key as used to prevent future collisions
INSERT INTO global_used_keys (key) VALUES (:retrieved_key) ON CONFLICT DO NOTHING;

5. Integrate with Your Business Tables #

When inserting into user tables, product tables, etc., pull a key from the pool instead of generating it directly. For example:

INSERT INTO users (id, name, email)
VALUES (
  (SELECT key FROM (
    WITH selected_key AS (
      SELECT key FROM available_keys LIMIT 1 FOR UPDATE SKIP LOCKED
    )
    DELETE FROM available_keys ak USING selected_key sk WHERE ak.key = sk.key RETURNING sk.key
  ) AS temp),
  'Jane Smith',
  'jane@example.com'
);

Why This Hits All Your Constraints #

• Unpredictable randomness: gen_random_int8() is cryptographically secure—no one can guess the next key, derive row counts, or figure out creation order.
• Database-wide uniqueness: The global_used_keys table ensures no duplicates across any table. Merging tables or adding parent classes won’t require changing how keys are generated.
• Bounded range: The generate_bounded_random_key function lets you adjust the min/max values anytime. Int8’s 64-bit size converts to ~11-12 characters in base58—perfect for clean URLs.

Bonus: Fallback for When the Pool Runs Dry #

Add a helper function to generate a key on-the-fly with automatic retry if a collision occurs:

CREATE OR REPLACE FUNCTION get_or_generate_key(min_key int8, max_key int8)
RETURNS int8 AS $$
DECLARE
  new_key int8;
BEGIN
  -- First try to grab from the pool
  WITH selected_key AS (
    SELECT key FROM available_keys LIMIT 1 FOR UPDATE SKIP LOCKED
  )
  DELETE FROM available_keys ak USING selected_key sk WHERE ak.key = sk.key RETURNING sk.key INTO new_key;
  
  IF new_key IS NOT NULL THEN
    INSERT INTO global_used_keys (key) VALUES (new_key) ON CONFLICT DO NOTHING;
    RETURN new_key;
  END IF;

  -- Fallback: generate on-the-fly with collision retry
  LOOP
    new_key := generate_bounded_random_key(min_key, max_key);
    BEGIN
      INSERT INTO global_used_keys (key) VALUES (new_key);
      RETURN new_key;
    EXCEPTION WHEN unique_violation THEN
      -- Collision detected, try again
      CONTINUE;
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

内容的提问来源于stack exchange，提问作者semicolon