Designing a URL Shortener with PostgreSQL

Designing a URL Shortener with PostgreSQL

A URL shortener converts long web addresses into compact, shareable links that redirect to the original destination. Services like Bitly, TinyURL, and Rebrandly process billions of clicks daily. While cloud platforms offer turnkey solutions, building your own with PostgreSQL gives you complete control over data, branding, and analytics — all within a familiar relational database.

This tutorial walks through a production-ready design, from schema creation through advanced features like click tracking, collision handling, and performance optimization.

Core Architecture Overview

The system revolves around two operations: shortening (accept a long URL, return a short code) and expansion (accept a short code, return the original URL for redirection). PostgreSQL handles both through a combination of tables, functions, and indexes. The short code — typically a 6 to 8-character alphanumeric string — acts as the primary lookup key.

Why build this in PostgreSQL rather than using a key-value store? Several reasons:

• ACID guarantees — no duplicate short codes, even under concurrent load
• Rich indexing — B-tree, hash, and partial indexes for fast lookups
• Built-in functions — generate random strings, hash values, and timestamps without application code
• Analytics readiness — clicks, referrers, and geolocation data live alongside URL records
• Single dependency — no need to operate a separate caching tier for basic usage

Step 1 — Creating the Database and Core Tables

Start with a dedicated database. Then create the primary urls table and an auxiliary clicks table for analytics.

-- Create the database (run as superuser)
CREATE DATABASE urlshortener;

-- Connect to urlshortener, then:
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- for gen_random_uuid()

-- Core table: stores URL mappings
CREATE TABLE urls (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    short_code  TEXT        NOT NULL UNIQUE,
    long_url    TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at  TIMESTAMPTZ,
    owner_id    TEXT,       -- nullable, for multi-user scenarios
    is_active   BOOLEAN     NOT NULL DEFAULT TRUE
);

-- Click analytics table
CREATE TABLE clicks (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    url_id      BIGINT      NOT NULL REFERENCES urls(id) ON DELETE CASCADE,
    clicked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    referrer    TEXT,
    user_agent  TEXT,
    ip_address  INET
);

-- Indexes for performance
CREATE INDEX idx_urls_short_code ON urls (short_code);
CREATE INDEX idx_urls_owner_id ON urls (owner_id) WHERE owner_id IS NOT NULL;
CREATE INDEX idx_clicks_url_id ON clicks (url_id, clicked_at DESC);
CREATE INDEX idx_clicks_clicked_at ON clicks (clicked_at) 
    WHERE clicked_at > CURRENT_DATE - INTERVAL '90 days';

The short_code column has a UNIQUE constraint — this is critical for correctness. The expires_at column lets you automatically retire links. The partial index on clicks covers only recent data, keeping index size manageable as the table grows.

Step 2 — Generating Short Codes

Short code generation requires careful design. The naive approach — using a random string and retrying on collisions — works well up to moderate scale. For high-throughput systems, a pre-generated pool or a counter-based approach reduces contention. This tutorial implements a robust random-generation function with collision handling.

The function below generates a base62-encoded string (alphanumeric, no special characters) of configurable length. Base62 avoids ambiguous characters like 0/O and 1/l/I by using a safe alphabet.

CREATE OR REPLACE FUNCTION generate_short_code(p_length INT DEFAULT 7)
RETURNS TEXT AS $$
DECLARE
    alphabet TEXT := 'abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ23456789';
    result   TEXT := '';
    i        INT;
    rand_val INT;
BEGIN
    FOR i IN 1..p_length LOOP
        -- Generate a random index into the alphabet (0-based)
        rand_val := floor(random() * length(alphabet))::INT + 1;
        result := result || substr(alphabet, rand_val, 1);
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql VOLATILE;

The VOLATILE marking is essential because random() produces different results on each invocation. With a 7-character code from a 56-character alphabet, the space contains 56⁷ ≈ 1.7 × 10¹² unique values — ample for most applications.

Step 3 — The Shortening Function

Now build the core shorten_url function. It accepts a long URL, generates a short code, handles collisions gracefully, and returns the code. The function uses a loop that retries with an incrementally longer code if collisions persist.

CREATE OR REPLACE FUNCTION shorten_url(
    p_long_url   TEXT,
    p_owner_id   TEXT DEFAULT NULL,
    p_expires_in INTERVAL DEFAULT NULL
)
RETURNS TABLE(
    short_code  TEXT,
    long_url    TEXT,
    created_at  TIMESTAMPTZ,
    expires_at  TIMESTAMPTZ
) AS $$
DECLARE
    v_code       TEXT;
    v_attempts   INT := 0;
    v_max_attempts INT := 10;
    v_length     INT := 7;
    v_exists     BOOLEAN;
    v_url_id     BIGINT;
BEGIN
    -- Validate input
    IF p_long_url IS NULL OR length(trim(p_long_url)) = 0 THEN
        RAISE EXCEPTION 'URL cannot be empty';
    END IF;

    -- Normalize URL: ensure it has a scheme
    IF NOT (p_long_url ~* '^https?://') THEN
        p_long_url := 'https://' || p_long_url;
    END IF;

    LOOP
        v_attempts := v_attempts + 1;
        
        -- Generate a code (increase length after repeated collisions)
        IF v_attempts > 5 THEN
            v_length := v_length + 1;
        END IF;
        
        v_code := generate_short_code(v_length);
        
        -- Check if code already exists
        SELECT EXISTS(SELECT 1 FROM urls WHERE short_code = v_code) INTO v_exists;
        
        IF NOT v_exists THEN
            EXIT;
        END IF;
        
        IF v_attempts >= v_max_attempts THEN
            RAISE EXCEPTION 'Unable to generate unique short code after % attempts', v_max_attempts;
        END IF;
    END LOOP;

    -- Insert the new URL mapping
    INSERT INTO urls (short_code, long_url, owner_id, expires_at)
    VALUES (v_code, p_long_url, p_owner_id,
            CASE WHEN p_expires_in IS NOT NULL 
                 THEN now() + p_expires_in 
                 ELSE NULL 
            END)
    RETURNING urls.id INTO v_url_id;

    -- Return the created record
    RETURN QUERY
    SELECT u.short_code, u.long_url, u.created_at, u.expires_at
    FROM urls u
    WHERE u.id = v_url_id;
END;
$$ LANGUAGE plpgsql VOLATILE;

This function normalizes URLs by prepending https:// if no scheme is present. It retries up to 10 times with progressively longer codes. The RETURNING clause fetches the inserted row efficiently, avoiding a separate SELECT.

Test the function:

-- Shorten a URL
SELECT * FROM shorten_url('https://www.example.com/very/long/path/that/needs/shortening');

-- Shorten with expiration and owner
SELECT * FROM shorten_url(
    'example.com/some-page',
    'user_42',
    INTERVAL '30 days'
);

Step 4 — Expanding and Redirecting

The expansion function looks up a short code and returns the original URL. It also increments a click counter — we implement this through the clicks table rather than an in-row counter to preserve normal form and avoid row-level locking contention.

CREATE OR REPLACE FUNCTION expand_url(
    p_short_code TEXT
)
RETURNS TABLE(
    long_url    TEXT,
    is_active   BOOLEAN,
    expires_at  TIMESTAMPTZ
) AS $$
DECLARE
    v_url_id    BIGINT;
    v_long_url  TEXT;
    v_active    BOOLEAN;
    v_expires   TIMESTAMPTZ;
BEGIN
    -- Lookup the short code
    SELECT u.id, u.long_url, u.is_active, u.expires_at
    INTO v_url_id, v_long_url, v_active, v_expires
    FROM urls u
    WHERE u.short_code = p_short_code;

    -- If not found, return empty
    IF v_url_id IS NULL THEN
        RETURN;
    END IF;

    -- Check if URL has expired
    IF v_expires IS NOT NULL AND v_expires < now() THEN
        -- Optionally deactivate the URL
        UPDATE urls SET is_active = FALSE WHERE id = v_url_id;
        RETURN;
    END IF;

    -- Check if URL is inactive
    IF NOT v_active THEN
        RETURN;
    END IF;

    -- Return the long URL
    RETURN QUERY SELECT v_long_url, TRUE AS is_active, v_expires AS expires_at;
END;
$$ LANGUAGE plpgsql STABLE;

For a production system, the application layer calls this function and issues an HTTP 302 redirect. The click recording happens separately to keep the lookup fast:

CREATE OR REPLACE FUNCTION record_click(
    p_short_code TEXT,
    p_referrer   TEXT DEFAULT NULL,
    p_user_agent TEXT DEFAULT NULL,
    p_ip_address INET DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO clicks (url_id, referrer, user_agent, ip_address)
    SELECT u.id, p_referrer, p_user_agent, p_ip_address
    FROM urls u
    WHERE u.short_code = p_short_code
      AND u.is_active = TRUE
      AND (u.expires_at IS NULL OR u.expires_at > now());

    -- Silently ignore clicks for non-existent/expired URLs
END;
$$ LANGUAGE plpgsql VOLATILE;

Step 5 — Collision-Resilient Bulk Shortening

For high-throughput scenarios — processing thousands of URLs per minute — the loop-based approach can cause contention. An alternative design uses a sequence-backed counter combined with a hash to eliminate collisions entirely.

CREATE SEQUENCE url_counter START 1 INCREMENT 1 NO CYCLE;

CREATE OR REPLACE FUNCTION shorten_url_bulk(
    p_long_urls TEXT[]  -- array of URLs to shorten
)
RETURNS TABLE(
    original_index INT,
    short_code     TEXT,
    long_url       TEXT
) AS $$
DECLARE
    v_url     TEXT;
    v_hash    TEXT;
    v_counter BIGINT;
    v_code    TEXT;
    v_idx     INT;
BEGIN
    FOR v_idx IN 1..array_length(p_long_urls, 1) LOOP
        v_url := p_long_urls[v_idx];
        
        -- Normalize
        IF NOT (v_url ~* '^https?://') THEN
            v_url := 'https://' || v_url;
        END IF;
        
        -- Get next counter value
        v_counter := nextval('url_counter');
        
        -- Create hash from URL + counter for uniqueness
        v_hash := encode(
            digest(v_url || ':' || v_counter::TEXT, 'sha256'),
            'hex'
        );
        
        -- Take first 8 chars of hash as code
        v_code := lower(left(v_hash, 8));
        
        -- Insert with ON CONFLICT handling
        INSERT INTO urls (short_code, long_url)
        VALUES (v_code, v_url)
        ON CONFLICT (short_code) DO NOTHING;
        
        -- If conflict occurred, append counter suffix
        IF NOT FOUND THEN
            v_code := lower(left(v_hash, 6)) || to_hex(v_counter % 256);
            INSERT INTO urls (short_code, long_url)
            VALUES (v_code, v_url);
        END IF;
        
        RETURN QUERY SELECT v_idx, v_code, v_url;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

This approach uses ON CONFLICT DO NOTHING to detect duplicates and falls back to a modified code. The sha256 hash distributes codes uniformly, while the counter ensures determinism.

Step 6 — Analytics and Reporting

With click data accumulating, PostgreSQL's window functions and aggregation capabilities shine. Here are practical queries for common reporting needs:

-- Top 10 most-clicked URLs in the last 7 days
SELECT 
    u.short_code,
    u.long_url,
    COUNT(*) AS click_count
FROM urls u
JOIN clicks c ON c.url_id = u.id
WHERE c.clicked_at > now() - INTERVAL '7 days'
GROUP BY u.id, u.short_code, u.long_url
ORDER BY click_count DESC
LIMIT 10;

-- Daily click counts for a specific URL
SELECT 
    date_trunc('day', c.clicked_at) AS day,
    COUNT(*) AS clicks
FROM clicks c
JOIN urls u ON u.id = c.url_id
WHERE u.short_code = 'abc123x'
  AND c.clicked_at > now() - INTERVAL '30 days'
GROUP BY day
ORDER BY day;

-- Top referrers across all URLs
SELECT 
    c.referrer,
    COUNT(*) AS referral_count,
    COUNT(DISTINCT c.url_id) AS unique_urls
FROM clicks c
WHERE c.referrer IS NOT NULL
  AND c.clicked_at > now() - INTERVAL '30 days'
GROUP BY c.referrer
ORDER BY referral_count DESC
LIMIT 20;

-- Active URLs expiring in the next 7 days
SELECT short_code, long_url, expires_at
FROM urls
WHERE expires_at BETWEEN now() AND now() + INTERVAL '7 days'
  AND is_active = TRUE
ORDER BY expires_at;

Step 7 — Maintenance and Cleanup

Over time, expired URLs and old click data accumulate. Scheduled cleanup keeps the database lean and queries fast. Use PostgreSQL's built-in cron-like extension pg_cron or application-level schedulers to run these periodically:

-- Deactivate expired URLs (run hourly)
UPDATE urls
SET is_active = FALSE
WHERE expires_at < now()
  AND is_active = TRUE;

-- Archive old clicks (keep 90 days in main table)
-- Create archive table first
CREATE TABLE clicks_archive () INHERITS (clicks);

-- Move old records
WITH moved AS (
    DELETE FROM clicks
    WHERE clicked_at < now() - INTERVAL '90 days'
    RETURNING *
)
INSERT INTO clicks_archive SELECT * FROM moved;

-- Remove completely orphaned URLs (no clicks, expired > 30 days ago)
DELETE FROM urls
WHERE id NOT IN (SELECT DISTINCT url_id FROM clicks)
  AND expires_at < now() - INTERVAL '30 days'
  AND created_at < now() - INTERVAL '90 days';

The archive strategy uses table inheritance — clicks_archive inherits the structure of clicks but stores data separately. Queries against clicks automatically exclude archived rows, while full historical queries can target clicks_archive explicitly.

Performance Best Practices

• Use UNIQUE on short_code — this is non-negotiable. It prevents duplicates and provides the B-tree index needed for sub-millisecond lookups.
• Keep the short code column narrow — 6 to 8 characters of TEXT is ideal. Avoid wide columns in indexes.
• Partial indexes for click data — index only recent clicks with a WHERE clause. This cuts index size by 80-90% while covering 95% of queries.
• Batch click inserts — use INSERT INTO clicks ... VALUES (...), (...), (...) for bulk operations rather than single-row inserts.
• Connection pooling — use PgBouncer or a similar pooler in front of PostgreSQL for web workloads. Shortening and expansion are quick operations, but connection overhead adds up.
• Monitor clicks table growth — it can become your largest table. Consider partitioning by month using declarative partitioning:

-- Partitioned clicks table (PostgreSQL 10+)
CREATE TABLE clicks_partitioned (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY,
    url_id      BIGINT NOT NULL,
    clicked_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    referrer    TEXT,
    user_agent  TEXT,
    ip_address  INET
) PARTITION BY RANGE (clicked_at);

-- Create monthly partitions
CREATE TABLE clicks_2025_01 
    PARTITION OF clicks_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE clicks_2025_02 
    PARTITION OF clicks_partitioned
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Index each partition individually
CREATE INDEX ON clicks_2025_01 (url_id, clicked_at DESC);
CREATE INDEX ON clicks_2025_02 (url_id, clicked_at DESC);

Security Considerations

• Validate and sanitize URLs — reject or escape URLs containing SQL injection patterns. The shorten_url function above normalizes schemes but a production system should also block javascript: and data: URIs.
• Rate limiting — implement at the application layer or via a PostgreSQL function that checks recent insert counts per IP or owner_id.
• Expiration as a safety net — always consider adding expires_at for user-generated links. Permanent links are convenient but become attack vectors if the destination changes hands.
• Row-level security for multi-tenant — if multiple users share the database, enable RLS:

ALTER TABLE urls ENABLE ROW LEVEL SECURITY;

CREATE POLICY owner_access ON urls
    FOR ALL
    TO authenticated_users
    USING (owner_id = current_setting('app.current_user_id'))
    WITH CHECK (owner_id = current_setting('app.current_user_id'));

Complete Application Integration Example

Here is how a typical Node.js or Python application layer interacts with these database functions. The application handles HTTP routing while PostgreSQL owns the data logic:

-- Example: Node.js using node-postgres (pg)
-- Route: POST /api/shorten
-- The application calls:
const result = await db.query(
    `SELECT * FROM shorten_url($1, $2, $3)`,
    [longUrl, userId, '30 days']
);
// Returns { short_code, long_url, created_at, expires_at }

-- Route: GET /:shortCode
-- The application calls:
const result = await db.query(
    `SELECT * FROM expand_url($1)`,
    [shortCode]
);
if (result.rows.length > 0) {
    // Asynchronously record the click
    db.query(
        `SELECT record_click($1, $2, $3, $4)`,
        [shortCode, req.headers.referer, req.headers['user-agent'], req.ip]
    ).catch(() => {}); // fire-and-forget
    
    res.redirect(302, result.rows[0].long_url);
} else {
    res.status(404).send('Link not found or expired');
}

This pattern keeps the database as the single source of truth. The application never writes to urls or clicks directly — all mutations flow through the functions, preserving consistency and making the system auditable.

Conclusion

PostgreSQL provides everything needed for a robust, scalable URL shortener — unique constraints prevent duplicates, functions encapsulate business logic, and indexes deliver sub-millisecond lookups even on tables with millions of rows. By generating base62 codes with collision handling, normalizing URLs at the database level, and tracking clicks in a partitioned analytics table, you create a system that is correct, fast, and maintainable. The design patterns shown here — retry loops for uniqueness, partial indexes for time-bound data, and declarative partitioning for growth — apply broadly beyond URL shortening to any system that maps compact keys to larger payloads. Build it once, and PostgreSQL handles the heavy lifting for years to come.