-- URI Register Schema
--
-- This schema provides a bidirectional mapping between URIs (text strings)
-- and integer IDs for efficient storage and lookup.

-- Create the uri_register table
-- By default, this creates a LOGGED table for durability
CREATE TABLE IF NOT EXISTS uri_register (
    id BIGSERIAL PRIMARY KEY,
    uri TEXT NOT NULL UNIQUE
);

-- Create index on URI for fast lookups
CREATE INDEX IF NOT EXISTS uri_register_uri_idx ON uri_register (uri);

-- Optional: Convert to UNLOGGED for faster writes (2-3x performance improvement)
-- WARNING: Data will be lost if PostgreSQL crashes
-- Only use this if you can rebuild the register from source data
--
-- To enable UNLOGGED mode:
-- ALTER TABLE uri_register SET UNLOGGED;
--
-- To revert back to LOGGED mode:
-- ALTER TABLE uri_register SET LOGGED;

-- Performance notes:
-- - LOGGED table: ~10K-50K URIs/sec insert, ~100K-200K URIs/sec lookup
-- - UNLOGGED table: ~30K-150K URIs/sec insert, similar lookup performance
-- - Batch operations are highly optimized and recommended over single inserts
