How to Index File Metadata in PostgreSQL for Cloud Upload Workflows #

Put the handful of attributes you filter and sort on into typed columns behind composite B-tree indexes, keep everything variable in one jsonb column behind a jsonb_path_ops GIN index, and add expression indexes only for the two or three attributes that appear in every WHERE clause your product ships.

This article sits under Metadata Indexing & Search, part of Backend Validation & Cloud Storage Architecture. The parent guide covers the ingest pipeline that produces the rows; this page is about the indexes themselves — which one to build, what each costs on write, and how to tell when the planner has quietly stopped using yours.

When to use this approach #

• Your catalogue is in the low millions to low hundreds of millions of rows and the queries are attribute filters — MIME type, owner, dimensions, tags, capture date — rather than fuzzy relevance ranking.
• The attribute set is open-ended: a camera adds a vendor tag, a new file type adds a page count, and you are not willing to run a migration for each one.
• You want filtering, sorting, pagination and count(*) to stay in one transactional store rather than being kept in sync with a separate search engine.

If your primary query is free-text relevance over titles and captions, the tsvector path in full-text search on file metadata with PostgreSQL is the better fit. If the attributes you care about are strictly technical media facts, storing image dimensions and duration metadata covers the extraction and unit normalisation that has to happen before any of this is worth indexing.

Prerequisites #

1. PostgreSQL 13 or later. Version 13 shipped B-tree deduplication and GIN improvements that materially change the sizing numbers below; on 12 expect indexes 20–30% larger.
2. CREATE EXTENSION privileges for btree_gin and, if you want prefix search on filenames, pg_trgm.
3. A row-per-object table already being populated by an object-created event — see direct-to-cloud upload patterns for how the event reaches your worker.
4. Node.js 20+ with the pg driver if you want to run the query helper at the end (npm i pg).

How a GIN index stores your metadata #

A B-tree indexes one value per row. A GIN index inverts that: it indexes many keys per row, and each key points at the set of rows that contain it. That single difference explains every performance property GIN has.

On insert, Postgres runs the row’s jsonb document through the operator class’s extraction function. With the default jsonb_ops, every key and every scalar value becomes a separate index entry — a document with six keys produces roughly twelve entries. With jsonb_path_ops, only complete root-to-leaf paths are hashed, producing one entry per leaf. Those entries are stored in the entry tree, an ordinary B-tree whose size scales with the number of distinct keys across the whole table, not with the row count. A single tenant uploading raw files with vendor MakerNote tags can add tens of thousands of distinct keys and double the entry tree on its own.

Each entry then points at the rows that contain it. If only a few rows match, their tuple identifiers are compressed inline as a posting list. Once a key becomes common — a "type":"image" that appears on nine million rows — the identifiers spill into a posting tree, a second B-tree of its own.

The recheck step at the bottom is the part people forget. GIN is lossy for containment: the index tells Postgres which rows might match, and the executor re-evaluates the real @> predicate against each heap tuple. A query whose index condition matches 200,000 rows but whose true predicate matches 40 still reads 200,000 rows from the heap. Narrowing the index condition — not the filter — is what makes those queries fast.

Implementation #

Everything below is one migration. It creates the table, then five indexes, each answering a specific query shape.

-- One row per uploaded object. Hot, typed columns first; everything variable in `metadata`.
CREATE TABLE files (
  file_id      uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id     uuid        NOT NULL,
  storage_key  text        NOT NULL,
  size_bytes   bigint      NOT NULL CHECK (size_bytes > 0),
  mime_type    text        NOT NULL,
  state        text        NOT NULL DEFAULT 'ready'
                           CHECK (state IN ('ready', 'scanning', 'deleted')),
  captured_at  timestamptz,
  uploaded_at  timestamptz NOT NULL DEFAULT now(),
  metadata     jsonb       NOT NULL DEFAULT '{}'::jsonb
                           CHECK (pg_column_size(metadata) <= 4096),
  UNIQUE (owner_id, storage_key)
);

-- 1. The listing query: newest first, per owner, tombstoned rows excluded entirely.
CREATE INDEX files_owner_recent
  ON files (owner_id, uploaded_at DESC, file_id)
  WHERE state <> 'deleted';

-- 2. Containment over the open-ended document.
CREATE INDEX files_metadata_gin
  ON files USING gin (metadata jsonb_path_ops);

-- 3. Two hot scalars promoted out of the document into partial expression indexes.
CREATE INDEX files_camera_model
  ON files ((metadata ->> 'cameraModel'))
  WHERE metadata ? 'cameraModel';

CREATE INDEX files_width
  ON files (((metadata ->> 'width')::int))
  WHERE metadata ? 'width';

-- 4. One index that answers `owner_id = $1 AND metadata @> $2` in a single scan.
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX files_owner_metadata
  ON files USING gin (owner_id, metadata jsonb_path_ops);

-- 5. Coarse time pruning on an append-mostly table, at roughly 1/400th of a B-tree's size.
CREATE INDEX files_uploaded_brin
  ON files USING brin (uploaded_at) WITH (pages_per_range = 32);

ANALYZE files;

What each line is doing #

• CHECK (pg_column_size(metadata) <= 4096) is the cheapest guard you have against index growth. GIN size tracks distinct keys table-wide, so one client dumping a full EXIF block per row is a size incident, not a row-level one. Failing the insert at 4 KB turns that into a loud error in the worker instead of a slow bloat curve. Strip the noisy tags before upload where you can — stripping EXIF metadata before upload removes most of it client-side.
• (owner_id, uploaded_at DESC, file_id) is column-ordered for the access pattern: equality first, then the sort key, then a tiebreaker so keyset pagination is deterministic. Reversing the first two columns produces an index the planner can still use but must sort afterwards.
• WHERE state <> 'deleted' makes it a partial index. On a table where 12% of rows are tombstones it is 12% smaller, and — more usefully — the planner gets an accurate row estimate for the only predicate the application ever sends.
• gin (metadata jsonb_path_ops) hashes each complete path plus its value into a single 32-bit entry. Fewer, smaller entries means a smaller index and fewer posting sets to union per query.
• ((metadata ->> 'width')::int) must be double-parenthesised: the outer pair delimits the index column list, the inner pair the expression. The ::int cast is what makes WHERE (metadata ->> 'width')::int > 3000 a range scan instead of a lexicographic text comparison where '900' sorts above '4032'.
• WHERE metadata ? 'cameraModel' keeps the expression index off the 80% of rows where the key is absent. The planner only uses a partial index when it can prove the query implies the predicate, so your application query must carry the same metadata ? 'cameraModel' clause.
• btree_gin lets a scalar column live inside a GIN index. Without it, owner_id = $1 AND metadata @> $2 needs a bitmap AND across two indexes; with it, one scan resolves both. It costs about 18% extra index size in exchange for skipping the bitmap merge.
• BRIN stores one min/max summary per 32 pages. On a table whose uploaded_at correlates with physical order it prunes 95%+ of pages for a date range at a few hundred kilobytes total. If you ever rewrite the table out of order — a pg_repack, a restore from a logical dump — re-summarise it with SELECT brin_summarize_new_values('files_uploaded_brin').

Choosing an operator class #

The operator class is the single decision that most affects index size and which queries the index can answer at all. Pick it once at CREATE INDEX time; changing it later means a full rebuild.

The ? operator is the only real casualty of jsonb_path_ops, and losing it matters less than it sounds. WHERE metadata ? 'cameraModel' asks “which rows have this key at all”, which on a table of tens of millions of rows is nearly always a low-selectivity predicate that ends in a heap scan regardless. Where you genuinely need it — as a partial index predicate, exactly as in the DDL above — it is evaluated as a filter and no index is involved.

The trigram row is worth knowing about even if you do not build it. gin (storage_key gin_trgm_ops) is what makes storage_key LIKE '%invoice%' indexable, including the leading wildcard that a B-tree cannot help with. It is also the most expensive index on the list at 2.4x the jsonb baseline, because every three-character window of every string becomes an entry.

Picking the index for a query shape #

Once the indexes exist, the application’s job is to emit predicates that match them. The most common way to lose an index is to build the containment document key by key in application code, producing metadata->>'a' = $1 AND metadata->>'b' = $2, which needs a separate expression index per key. Bind the whole document as one parameter instead.

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 8 });

interface FileQuery {
  ownerId: string;
  contains: Record<string, unknown>;
  before?: Date;
  limit?: number;
}

export async function findFiles({ ownerId, contains, before, limit = 50 }: FileQuery) {
  // `@> $2::jsonb` keeps the whole filter as ONE bound parameter, so the planner sees a
  // single indexable containment predicate rather than N separate ->> comparisons.
  const { rows } = await pool.query(
    `SELECT file_id, storage_key, size_bytes, uploaded_at
       FROM files
      WHERE owner_id = $1
        AND state <> 'deleted'
        AND metadata @> $2::jsonb
        AND uploaded_at < $3
      ORDER BY uploaded_at DESC, file_id DESC
      LIMIT $4`,
    [ownerId, JSON.stringify(contains), before ?? new Date(), limit],
  );
  return rows;
}

Note the state <> 'deleted' clause: it is there so the planner can match the partial index, not because the caller asked for it. Partial-index predicates have to be restated in the query verbatim, and the cheapest way to guarantee that is to build the clause into the helper rather than leaving it to each call site.

Configuration reference #

Configuration gotchas #

ERROR: functions in index expression must be marked IMMUTABLE #

Thrown by CREATE INDEX files_captured ON files (((metadata ->> 'capturedAt')::timestamptz));. The text to timestamptz cast is STABLE, not IMMUTABLE, because its result depends on the session TimeZone. Index the timezone-free type instead — ((metadata ->> 'capturedAt')::timestamp) — or, better, normalise the value to UTC at ingest and promote it to the real captured_at column, which is what that column is in the DDL for.

ERROR: data type text has no default operator class for access method "gin" #

You asked for CREATE INDEX ON files USING gin (storage_key). GIN has no built-in support for plain text; you must name an operator class. Run CREATE EXTENSION pg_trgm; and use USING gin (storage_key gin_trgm_ops). The identical error with bigint or uuid in place of text means you wanted btree_gin instead.

ERROR: index row size 2896 exceeds btree version 4 maximum 2704 for index "files_title_idx" #

A B-tree expression index over a free-text JSONB field hits the ~2704-byte per-tuple ceiling as soon as one document carries a long description. Index a digest rather than the value — ((md5(metadata ->> 'description'))) for equality lookups — or move that field to a tsvector and let full-text search handle it.

The index exists and the planner refuses it #

No error, just a Seq Scan in the plan. Three causes, in the order they actually occur. First, a type mismatch: WHERE (metadata ->> 'width')::int > 3000 cannot use an index built on (metadata ->> 'width') without the cast, and vice versa — the expressions must match textually after parsing. Second, a partial index whose predicate the query does not restate. Third, a row estimate so wrong that a sequential scan really does look cheaper; ANALYZE files; fixes most of these, and ALTER TABLE files ALTER COLUMN metadata SET STATISTICS 1000; ANALYZE files; fixes the rest.

Verification #

Confirm the plan before you ship, and confirm the index is still being used a month later.

-- 1. Does the containment query use the GIN index, and how much heap does it touch?
EXPLAIN (ANALYZE, BUFFERS)
SELECT file_id, storage_key
  FROM files
 WHERE owner_id = '6f1b8a52-9c3e-4f77-9d2b-4a1f0c8e5d31'::uuid
   AND metadata @> '{"tags": ["scan"]}'::jsonb;

-- 2. Which indexes are earning their write cost? idx_scan = 0 after a full week is a delete.
SELECT indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 WHERE relname = 'files'
 ORDER BY idx_scan ASC;

-- 3. How much of the GIN index is still sitting in the unmerged pending list?
SELECT pending_pages, pending_tuples
  FROM gin_metapage_info(get_raw_page('files_metadata_gin', 0));

Statement 3 needs CREATE EXTENSION pageinspect; and superuser or pg_read_all_data. A pending_tuples figure in the hundreds of thousands during steady state means your vacuum cadence is behind the write rate — lower autovacuum_vacuum_scale_factor on the table before you reach for fastupdate = off.

Run the same EXPLAIN against a cold cache before you believe the timing. shared hit=402 read=11 means 97% of the pages were already in memory; the first user of the day sees the version where all 413 are reads. If the index no longer fits in shared_buffers, the number that changes is read, and it changes by two orders of magnitude.

Frequently Asked Questions