Database Design: PostgreSQL as Your Archive Brain

Your digital archive needs a database that can handle millions of files, fuzzy text search, GPS queries, and flexible metadata without breaking a sweat. PostgreSQL is that database.

Not SQLite (too simple, no extensions). Not MySQL (weak JSON support, limited extensions). PostgreSQL with extensions turns your database into a search engine, a GIS system, and an AI-ready vector store all at once.

Why PostgreSQL

It scales forever. This archive runs on 3.5 million files. Fuzzy filename searches return in 50ms. GPS range queries (find all photos within 5km of Central Park) return in 30ms. Full-text searches across paths hit indexes and never scan tables.

Extensions are superpowers. Install pg_trgm and you get Google-style typo tolerance. Install PostGIS and you can answer “show me photos from my Europe trip within 10km of the Eiffel Tower.” Install pgvector and you are ready for semantic search when you add AI embeddings later.

JSONB is schemaless magic. Every camera writes different EXIF data. Some files have 5 fields, some have 50. JSONB stores it all, indexes it, and lets you query nested JSON with SQL. No schema migrations when a new camera appears.

It never corrupts. SQLite corrupts if you look at it wrong during a write. PostgreSQL has WAL (write-ahead logging), MVCC (multi-version concurrency control), and battle-tested durability. Your archive data is safe.

Extension Setup

Install these extensions before creating tables. Run this SQL once:

CREATE EXTENSION IF NOT EXISTS pg_trgm;        -- Trigram fuzzy text search
CREATE EXTENSION IF NOT EXISTS postgis;        -- Spatial queries (GPS)
CREATE EXTENSION IF NOT EXISTS vector;         -- AI embeddings (future)
CREATE EXTENSION IF NOT EXISTS ltree;          -- Hierarchical paths
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;  -- Levenshtein distance
CREATE EXTENSION IF NOT EXISTS unaccent;       -- Accent-insensitive search
CREATE EXTENSION IF NOT EXISTS btree_gin;      -- Better composite indexes

Why This Choice: Extensions Over Application Logic

Every feature these extensions provide (fuzzy matching, GPS math, accent folding) could be done in application code. But that is slow, buggy, and reinvents wheels. PostgreSQL extensions run in C, use optimized algorithms, and integrate with the query planner. A trigram search in pg_trgm is 100x faster than application-side Levenshtein distance.

Files Table Schema

This is the heart of the archive. Every scanned file gets one row. The scanner owns this table and updates it continuously.

CREATE TABLE files (
    id BIGSERIAL PRIMARY KEY,

    -- File identity
    path TEXT NOT NULL UNIQUE,
    filename TEXT NOT NULL,
    extension TEXT,
    size_bytes BIGINT NOT NULL,
    is_dir BOOLEAN NOT NULL DEFAULT FALSE,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMP WITH TIME ZONE NOT NULL,

    -- Content fingerprint
    content_hash TEXT,  -- xxHash64 hex string

    -- AI categorization
    category TEXT,
    category_source TEXT,  -- 'extension', 'content', 'ml_model'
    category_confidence FLOAT,

    -- Review flags
    needs_review BOOLEAN NOT NULL DEFAULT FALSE,
    review_reason TEXT,
    is_sensitive BOOLEAN NOT NULL DEFAULT FALSE,

    -- Media dimensions
    media_width INTEGER,
    media_height INTEGER,

    -- EXIF metadata
    exif_data JSONB,
    exif_date_taken TIMESTAMP WITH TIME ZONE,

    -- GPS location
    gps_lat DOUBLE PRECISION,
    gps_lon DOUBLE PRECISION,

    -- Spatial index helper (PostGIS)
    gps_location GEOGRAPHY(POINT, 4326)
        GENERATED ALWAYS AS (
            CASE
                WHEN gps_lat IS NOT NULL AND gps_lon IS NOT NULL
                THEN ST_SetSRID(ST_MakePoint(gps_lon, gps_lat), 4326)::geography
                ELSE NULL
            END
        ) STORED
);

Schema decisions:

  • path is unique, not content_hash. Duplicate files are fine. The scanner tracks every file path as a distinct entity.
  • content_hash is nullable. Directories have no hash. Huge files might skip hashing for performance.
  • JSONB for exif_data, not columns. EXIF schemas vary wildly. JSONB absorbs everything without schema changes.
  • Generated column for gps_location. PostGIS requires a GEOGRAPHY type. The generated column auto-creates the spatial point from lat/lon.
  • Timestamps with time zones. Always use TIMESTAMP WITH TIME ZONE. No timezone bugs, no DST confusion.

Index Strategy

Indexes make queries fast. No index means full table scans (slow death at 3.5M rows). These indexes cover every query pattern in the archive browser:

-- Standard B-tree indexes
CREATE INDEX idx_files_content_hash ON files(content_hash) WHERE content_hash IS NOT NULL;
CREATE INDEX idx_files_category ON files(category);
CREATE INDEX idx_files_extension ON files(extension);
CREATE INDEX idx_files_size_bytes ON files(size_bytes);
CREATE INDEX idx_files_modified_at ON files(modified_at);
CREATE INDEX idx_files_created_at ON files(created_at);
CREATE INDEX idx_files_exif_date_taken ON files(exif_date_taken) WHERE exif_date_taken IS NOT NULL;

-- Trigram indexes for fuzzy text search (GIN)
CREATE INDEX idx_files_filename_trgm ON files USING GIN (filename gin_trgm_ops);
CREATE INDEX idx_files_path_trgm ON files USING GIN (path gin_trgm_ops);

-- Spatial index for GPS queries (GiST)
CREATE INDEX idx_files_gps_location ON files USING GIST (gps_location)
    WHERE gps_location IS NOT NULL;

-- JSONB index for EXIF queries (GIN)
CREATE INDEX idx_files_exif_data ON files USING GIN (exif_data)
    WHERE exif_data IS NOT NULL;

-- Composite indexes for common filters
CREATE INDEX idx_files_category_modified ON files(category, modified_at);
CREATE INDEX idx_files_extension_size ON files(extension, size_bytes);

Why This Choice: GIN vs GiST

GIN (Generalized Inverted Index) is for discrete values: trigrams, JSONB keys, arrays. GiST (Generalized Search Tree) is for ranges and geometry: GPS points, timestamps, IP ranges. Use GIN for “contains” queries. Use GiST for “near” queries.

Index sizes at scale:

At 3.5M files, indexes consume about 40% of database size (12GB table, 5GB indexes). This is fine. Indexes are investments. A 5GB index that makes queries 1000x faster is a bargain.

JSONB for Flexible Metadata

EXIF data is chaos. Canon cameras store 120 fields. iPhones store 30. Scanned documents store none. JSONB absorbs this chaos without schema changes.

Example EXIF data:

{
  "Make": "Canon",
  "Model": "EOS 5D Mark IV",
  "DateTime": "2025:03:15 14:22:33",
  "ISO": 400,
  "FNumber": 2.8,
  "FocalLength": "50mm",
  "LensModel": "EF 50mm f/1.8 STM",
  "GPSLatitude": 40.748817,
  "GPSLongitude": -73.985428
}

Query nested JSONB:

-- Find all Canon photos
SELECT filename, exif_data->>'Model' AS camera
FROM files
WHERE exif_data->>'Make' = 'Canon';

-- Find photos shot at ISO 3200 or higher
SELECT filename, exif_data->>'ISO' AS iso
FROM files
WHERE (exif_data->>'ISO')::int >= 3200;

-- Find photos with lens info
SELECT filename, exif_data->'LensModel' AS lens
FROM files
WHERE exif_data ? 'LensModel';

The -> operator extracts JSON objects. The ->> operator extracts text. The ? operator checks key existence. The GIN index makes these queries instant.

Sample Queries

These are real queries from the archive browser UI.

Fuzzy filename search (typo-tolerant):

SELECT
    filename,
    similarity(filename, 'potery') AS score
FROM files
WHERE filename % 'potery'  -- Trigram match
ORDER BY score DESC
LIMIT 20;

Returns files matching “poetry” even though you typed “potery”. The % operator uses trigram similarity. The similarity() function scores matches (0 to 1). Queries run in 50ms on 3.5M files.

Find duplicate files (same content, different paths):

SELECT
    content_hash,
    COUNT(*) AS duplicate_count,
    ARRAY_AGG(path ORDER BY path) AS file_paths,
    size_bytes
FROM files
WHERE content_hash IS NOT NULL
GROUP BY content_hash, size_bytes
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, size_bytes DESC;

Groups files by hash. Returns only hashes with 2+ files. Aggregates paths into arrays. Sorts by most duplicates first, then largest files.

GPS range query (find photos near Central Park):

SELECT
    filename,
    gps_lat,
    gps_lon,
    ST_Distance(
        gps_location,
        ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326)::geography
    ) AS distance_meters
FROM files
WHERE gps_location IS NOT NULL
    AND ST_DWithin(
        gps_location,
        ST_SetSRID(ST_MakePoint(-73.9654, 40.7829), 4326)::geography,
        5000  -- 5km radius
    )
ORDER BY distance_meters;

The ST_DWithin() function uses the GiST spatial index. Returns all files within 5000 meters (5km). The ST_Distance() function calculates exact distances. Queries run in 30ms.

Category breakdown with size totals:

SELECT
    category,
    COUNT(*) AS file_count,
    SUM(size_bytes) AS total_bytes,
    ROUND(AVG(size_bytes)) AS avg_bytes,
    MAX(modified_at) AS most_recent
FROM files
WHERE NOT is_dir
GROUP BY category
ORDER BY total_bytes DESC;

Photos by year (from EXIF date):

SELECT
    EXTRACT(YEAR FROM exif_date_taken) AS year,
    COUNT(*) AS photo_count,
    MIN(exif_date_taken) AS first_photo,
    MAX(exif_date_taken) AS last_photo
FROM files
WHERE exif_date_taken IS NOT NULL
GROUP BY year
ORDER BY year DESC;

Search by multiple criteria (extension + size + date range):

SELECT filename, size_bytes, modified_at
FROM files
WHERE extension IN ('jpg', 'png', 'heic')
    AND size_bytes > 5000000  -- Larger than 5MB
    AND modified_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY modified_at DESC
LIMIT 100;

The composite index idx_files_extension_size accelerates this query. PostgreSQL uses multi-column indexes intelligently.

Performance at Scale

Real performance numbers from a 3.5M row archive database:

Query times:

  • Fuzzy filename search: 50ms average
  • GPS range query (5km radius): 30ms average
  • Category aggregation: 120ms average
  • Duplicate hash query: 200ms average
  • JSONB EXIF query: 80ms average
  • Full-text path search: 60ms average

Database size:

  • Files table: 12GB
  • All indexes: 5GB
  • JSONB data: 2GB (included in table size)
  • Total: 17GB

Write performance:

The scanner inserts 1000 files/second during bulk import. Indexes slow writes slightly (raw inserts would be 3000/second), but queries would be unusable without indexes. This is the correct tradeoff.

Vacuum and analyze:

Run VACUUM ANALYZE files; weekly. PostgreSQL’s autovacuum handles most maintenance, but manual vacuums keep statistics fresh. Fresh statistics mean optimal query plans.

Why This Choice: Normalized vs Denormalized

This schema is intentionally denormalized. GPS lat/lon are stored twice (as separate columns and as a PostGIS GEOGRAPHY point). EXIF date is extracted into its own column even though it exists in the JSONB blob.

Why denormalize?

Speed. Querying a top-level column is faster than extracting from JSONB every time. Indexes on generated columns are faster than expression indexes. Denormalization trades storage for speed. At 3.5M rows, this tradeoff is obvious. Queries must be fast. Disk is cheap.

When to normalize:

If this archive had separate tables for “albums” or “tags” with many-to-many relationships, normalization would be correct. But the files table is a scanner output log. Every row is independent. Denormalization wins.

Future-Proofing with pgvector

The vector extension is installed but not used yet. When you add AI embeddings (semantic search, visual similarity, RAG), the schema is ready:

-- Future column (not added yet)
ALTER TABLE files ADD COLUMN embedding vector(1536);  -- OpenAI ada-002 size

-- Future index
CREATE INDEX idx_files_embedding ON files USING ivfflat (embedding vector_cosine_ops);

Semantic search query (future):

SELECT filename, embedding <=> query_embedding AS distance
FROM files
WHERE embedding IS NOT NULL
ORDER BY distance
LIMIT 10;

The <=> operator computes cosine distance. The ivfflat index makes vector queries fast. Add this when you are ready for AI features. The database design already supports it.

Schema Migrations

This schema evolves. New features need new columns. PostgreSQL migrations are simple:

Add a column:

ALTER TABLE files ADD COLUMN thumbnail_path TEXT;

Add an index:

CREATE INDEX CONCURRENTLY idx_files_thumbnail ON files(thumbnail_path);

The CONCURRENTLY keyword builds indexes without locking the table. Writes continue during index creation. This is critical for production databases.

Drop an index:

DROP INDEX CONCURRENTLY idx_files_old_column;

Never drop columns in production. Mark them deprecated and ignore them in application code. Dropping columns rewrites the entire table (slow at 3.5M rows). Add new columns instead.

Conclusion

PostgreSQL with extensions is a superpower database. Fuzzy search, GPS queries, flexible JSONB metadata, and AI-ready vector support all in one system. The schema handles 3.5 million files without sweating. Queries are fast. Data is safe. Indexes are tuned.

This is not over-engineering. This is designing for reality. A personal digital archive grows forever. The database must grow with it. PostgreSQL does.

Next: Web Application - Building the Rails interface for browsing your archive.

Back to top

AI Digital Archive - A system for organizing your digital life

This site uses Just the Docs, a documentation theme for Jekyll.