Metadata Indexing & Search #

The bytes are durable in object storage and the upload UI has gone green, but your product still cannot answer “show me every 4K clip this tenant uploaded in March that is longer than two minutes” — because object storage is a key-value store with no query engine, and ListObjectsV2 gets slower every week. Metadata indexing is the layer that turns a bucket into something searchable: a worker that extracts a small, bounded set of facts about each object and writes them into a database whose indexes answer the queries your UI actually issues.

This topic sits inside backend validation and cloud storage architecture, downstream of the byte-level work. By the time a job reaches an indexing worker, server-side file validation has already decided the object is safe to touch. What follows is the machine that reads it once, cheaply, and turns it into rows — plus the four or five ways that machine quietly corrupts your index in production.

Prerequisites #

• [ ] PostgreSQL 14 or newer. Version 14 brings jsonb subscripting and much cheaper ON CONFLICT planning; version 13 works but the GIN tuning knobs below behave differently.
• [ ] Node 20+ for the workers — the code uses AbortSignal.timeout(), node:buffer, top-level await and ESM.
• [ ] A bucket with event notifications or EventBridge enabled on the originals prefix, and a queue dedicated to indexing (not shared with transcoding).
• [ ] pg 8.11+, zod 3.23+, file-type 19+ and sharp 0.33+ installed for the deployment architecture, not the laptop architecture.
• [ ] IAM for the worker: s3:GetObject and s3:GetObjectVersion on originals/*, plus sqs:ReceiveMessage, sqs:DeleteMessage and sqs:ChangeMessageVisibility. No s3:PutObject — an indexer that can write to the bucket it watches is an event loop waiting to happen.
• [ ] A Postgres role for the worker with INSERT, UPDATE and SELECT on the metadata table only, so a compromised extractor cannot drop the index it is filling.

How it works #

Indexing is four stages, and each one has a delivery or accuracy property you have to design around rather than hope about.

The event. Storage emits a notification when the object becomes durable. Subscribe to s3:ObjectCreated:*, not s3:ObjectCreated:Put — a browser that switches to the multipart path described in direct-to-cloud upload patterns produces s3:ObjectCreated:CompleteMultipartUpload instead, and a Put-only rule silently never indexes anything over your part-size threshold. Every record carries a sequencer: a hex string that increases monotonically for writes to the same key. It is the only ordering signal S3 gives you, and it is the entire basis of the correctness guard in step 4.

The probe. The event tells you the key, the size and the ETag. It does not tell you whether the object is really a JPEG, how many pixels it has, or how long the video runs. Getting those means reading bytes — but almost never all of them. A 64 KiB header window covers the JPEG SOI plus its APP1/EXIF segment, the PNG IHDR chunk, the WebP VP8X chunk, an AVIF ftyp plus meta box, and the ISO-BMFF box table of a faststart MP4. That is 64 KiB out of a 900 MB file: about 0.007% of the transfer cost of a naive “download and inspect” worker.

The normalisation. Probe output is a mess of vendor-specific keys in inconsistent units — EXIF durations in rational pairs, ffprobe durations as decimal-second strings, dimensions that ignore orientation. The normaliser converts everything to a single canonical shape with integer units, rejects keys it does not recognise, and enforces a hard size cap before anything touches the database.

The write. One upsert per object, keyed on (tenant_id, storage_key), guarded so an older event can never overwrite a newer one. The message is deleted from the queue only after that write commits.

The three tiers of metadata, and what each costs #

Treating “metadata” as one thing is what makes indexing pipelines slow. There are three tiers with wildly different cost profiles, and they belong on different queues with different service levels.

Envelope metadata is free. Key, size, ETag, version id, storage class, declared Content-Type, and the upload’s own audit fields. It arrives in the event or in a single HeadObject call — 8–15 ms against a same-region bucket. It should be indexed within a second of the upload, because your UI needs it to render the file list at all.

Technical metadata is cheap if you are disciplined. Sniffed MIME type, pixel dimensions, colour space, page count, video duration and codec. Cost is one or two ranged GETs: 40–90 ms including TLS reuse. This is where storing image dimensions and duration metadata goes into the per-format specifics — orientation swaps, VFR frame rates, the difference between container and stream duration. Budget it for a second or two after upload.

Semantic metadata is expensive and belongs on its own queue entirely. OCR text, transcripts, embeddings, perceptual hashes, label detection. Seconds to minutes per object, and often billed per call. Never let it share a queue with envelope extraction: one 40-page scanned PDF blocking a batch turns a one-second index lag into a four-minute one, and users notice the file list before they notice the search.

The practical rule is that the indexing worker writes the row twice. The first write happens within a second and carries envelope plus technical facts. The second write, minutes later, merges in semantic fields with a jsonb_set-style partial update. Modelling this as one atomic “index the file” step is the most common reason indexing pipelines end up on the critical path of the upload.

Modelling the row #

The schema decision that matters is which facts get their own column and which live in JSONB. Get it wrong in the generous direction — everything in JSONB — and your GIN index grows faster than your table, plans become unpredictable, and a WHERE attrs->>'bytes' > '1000' comparison silently does a string comparison. Get it wrong in the strict direction — a column per format-specific field — and you are running a migration every time the product adds a file type.

The test I apply to every candidate field is: does a WHERE clause or an ORDER BY ever reference it? If yes, it is a hot column with a type and a NOT NULL where possible. If it is only ever displayed after the row has already been found, it goes in attrs. If it is unbounded — raw EXIF for a camera that emits 400 tags, the full text of a 90-page contract, a 1536-dimension embedding — it goes to a sidecar object in the bucket, and the row stores only its key.

That last rule has a hard economic backing. Postgres TOASTs any row wider than roughly 2 KB, moving the overflow to a side table with its own I/O path. A file_metadata row with the eight hot columns above is about 120 bytes; a row that inlines a 30 KB EXIF dump is a TOAST fetch on every SELECT *, and the GIN index over it grows with the number of distinct keys across the whole table, not the number of rows. One tenant uploading Nikon raws with vendor MakerNote tags can add tens of thousands of distinct keys and double your index size on its own.

CREATE TABLE file_metadata (
  tenant_id       uuid        NOT NULL,
  storage_key     text        NOT NULL,
  media_id        text        NOT NULL,
  size_bytes      bigint      NOT NULL CHECK (size_bytes >= 0),
  mime_type       text        NOT NULL,
  content_sha256  bytea,
  state           text        NOT NULL DEFAULT 'indexed'
                              CHECK (state IN ('indexed', 'enriching', 'deleted')),
  sequencer       text        NOT NULL,
  attrs           jsonb       NOT NULL DEFAULT '{}'::jsonb
                              CHECK (pg_column_size(attrs) <= 4096),
  sidecar_key     text,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  search_vector   tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', regexp_replace(split_part(storage_key, '/', -1),
                                                   '[^a-zA-Z0-9]+', ' ', 'g')), 'A')
    || setweight(to_tsvector('english', coalesce(attrs ->> 'title', '')), 'B')
  ) STORED,
  PRIMARY KEY (tenant_id, storage_key)
);

CREATE INDEX file_metadata_recent
  ON file_metadata (tenant_id, created_at DESC, storage_key)
  WHERE state <> 'deleted';

CREATE INDEX file_metadata_attrs
  ON file_metadata USING gin (attrs jsonb_path_ops);

CREATE INDEX file_metadata_fts
  ON file_metadata USING gin (search_vector);

Three details in that DDL are load-bearing. The primary key is (tenant_id, storage_key) rather than a surrogate UUID, so the upsert conflict target is the natural key and no second unique index is needed. jsonb_path_ops produces an index roughly 35% smaller than the default jsonb_ops and answers @> containment faster, at the cost of not supporting the ? key-existence operator — a trade worth making, because key-existence queries against user-supplied metadata are almost always a modelling mistake. And file_metadata_recent is a partial index that excludes tombstoned rows, which keeps the common listing query off the deleted set entirely. For the deeper tuning of these index types — operator classes, fastupdate, statistics targets — how to index file metadata in PostgreSQL is the reference; the ranking and dictionary side belongs to full-text search on file metadata with PostgreSQL.

Note the 'simple' dictionary on the filename half of the vector. Filenames are not English: stemming IMG_2024_final_v3.CR2 through the English dictionary throws away the token boundaries you actually want to match on. Splitting on non-alphanumerics and indexing unstemmed gives you working prefix search over filenames; the 'english' dictionary stays where prose lives.

Step-by-step implementation #

1. Turn the raw event into a routable job #

The event record is untrusted input from a system you do not control, and its key is URL-encoded with + for spaces — a detail that silently breaks every filename containing a space if you skip the decode. Parse strictly, derive the tenant from the key layout rather than from anything in the message body, and carry the sequencer through untouched.

// src/metadata/envelope.ts
import { z } from "zod";

const S3Record = z.object({
  eventName: z.string(),
  eventTime: z.string(),
  s3: z.object({
    bucket: z.object({ name: z.string().min(3) }),
    object: z.object({
      key: z.string().min(1),
      size: z.number().int().nonnegative().optional(),
      eTag: z.string().optional(),
      versionId: z.string().optional(),
      sequencer: z.string().min(1),
    }),
  }),
});

export interface IndexJob {
  bucket: string;
  key: string;
  tenantId: string;
  mediaId: string;
  sizeHint: number | null;
  etag: string | null;
  sequencer: string;
  observedAt: string;
}

/** originals/<tenant-uuid>/<yyyy>/<mm>/<dd>/<mediaId>/<filename> */
const KEY_SHAPE =
  /^originals\/([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12})\/\d{4}\/\d{2}\/\d{2}\/([0-9a-z]{8,32})\/[^/]+$/;

export class UnroutableKeyError extends Error {}

export function toIndexJob(raw: unknown): IndexJob {
  const rec = S3Record.parse(raw);
  // S3 percent-encodes the key and uses '+' for spaces. Decode before matching.
  const key = decodeURIComponent(rec.s3.object.key.replace(/\+/g, " "));
  const parts = KEY_SHAPE.exec(key);
  if (!parts) throw new UnroutableKeyError(`key does not match the originals layout: ${key}`);

  return {
    bucket: rec.s3.bucket.name,
    key,
    tenantId: parts[1],
    mediaId: parts[2],
    sizeHint: rec.s3.object.size ?? null,
    etag: rec.s3.object.eTag?.replaceAll('"', "") ?? null,
    sequencer: rec.s3.object.sequencer,
    observedAt: rec.eventTime,
  };
}

An UnroutableKeyError is a permanent failure, not a transient one. Throwing it should acknowledge the message and emit a metric, never trigger a retry — otherwise a single stray object dropped into the bucket by a console user occupies a worker slot five times before reaching the dead-letter queue.

2. Probe with a byte budget you can state out loud #

The whole design rests on reading a fixed, small window rather than the object. HeadObject gives you the authoritative size and the declared type; a single ranged GET gives you the header. Only MP4 and QuickTime files written without -movflags +faststart need a second read, because their moov atom — the box holding duration, track list and codec parameters — sits at the end of the file.

// src/metadata/probe.ts
import { S3Client, HeadObjectCommand, GetObjectCommand } from "@aws-sdk/client-s3";
import { fileTypeFromBuffer } from "file-type";
import sharp from "sharp";
import type { IndexJob } from "./envelope.js";

const s3 = new S3Client({ region: process.env.AWS_REGION ?? "eu-west-1" });

/** Covers JPEG APP1, PNG IHDR, WebP VP8X, AVIF meta and a faststart ISO-BMFF box table. */
export const HEAD_WINDOW = 64 * 1024;
/** Only read when the moov atom was not relocated to the front of the file. */
export const TAIL_WINDOW = 256 * 1024;

export interface Probe {
  sizeBytes: number;
  declaredType: string | null;
  sniffedType: string | null;
  width: number | null;
  height: number | null;
  moovLocation: "head" | "tail" | "absent" | null;
  bytesRead: number;
}

async function readRange(bucket: string, key: string, range: string): Promise<Buffer> {
  const out = await s3.send(
    new GetObjectCommand({ Bucket: bucket, Key: key, Range: range }),
    { abortSignal: AbortSignal.timeout(5_000) },
  );
  const chunks: Uint8Array[] = [];
  for await (const chunk of out.Body as AsyncIterable<Uint8Array>) chunks.push(chunk);
  return Buffer.concat(chunks);
}

export async function probeObject(job: IndexJob): Promise<Probe> {
  const head = await s3.send(
    new HeadObjectCommand({ Bucket: job.bucket, Key: job.key }),
    { abortSignal: AbortSignal.timeout(3_000) },
  );
  const sizeBytes = head.ContentLength ?? 0;
  if (sizeBytes === 0) {
    return {
      sizeBytes, declaredType: head.ContentType ?? null, sniffedType: null,
      width: null, height: null, moovLocation: null, bytesRead: 0,
    };
  }

  const headEnd = Math.min(HEAD_WINDOW, sizeBytes) - 1;
  const headBytes = await readRange(job.bucket, job.key, `bytes=0-${headEnd}`);
  let bytesRead = headBytes.length;

  const sniffed = await fileTypeFromBuffer(headBytes);
  let width: number | null = null;
  let height: number | null = null;
  let moovLocation: Probe["moovLocation"] = null;

  if (sniffed?.mime.startsWith("image/")) {
    const meta = await sharp(headBytes, { failOn: "none" }).metadata();
    // EXIF orientation 5–8 stores the pixels rotated; report display dimensions.
    const swapped = typeof meta.orientation === "number" && meta.orientation >= 5;
    width = (swapped ? meta.height : meta.width) ?? null;
    height = (swapped ? meta.width : meta.height) ?? null;
  }

  if (sniffed?.mime === "video/mp4" || sniffed?.mime === "video/quicktime") {
    const marker = Buffer.from("moov", "ascii");
    if (headBytes.includes(marker)) {
      moovLocation = "head";
    } else if (sizeBytes > HEAD_WINDOW) {
      const from = Math.max(headEnd + 1, sizeBytes - TAIL_WINDOW);
      const tailBytes = await readRange(job.bucket, job.key, `bytes=${from}-${sizeBytes - 1}`);
      bytesRead += tailBytes.length;
      moovLocation = tailBytes.includes(marker) ? "tail" : "absent";
    } else {
      moovLocation = "absent";
    }
  }

  return {
    sizeBytes,
    declaredType: head.ContentType ?? null,
    sniffedType: sniffed?.mime ?? null,
    width,
    height,
    moovLocation,
    bytesRead,
  };
}

A worker running this against a mixed corpus reads a median of 65,536 bytes per object and a p99 of 327,680. Log bytesRead as a histogram — the day someone adds “just parse the whole thing with a library” to this file, that histogram is where you will see it before the S3 bill does. When moovLocation comes back "tail", that is also a signal worth surfacing to whoever produces the video: files without faststart cannot be progressively played, so the same flag drives both your index and a warning in post-upload media transcoding.

3. Normalise to canonical units before anything reaches the database #

The normaliser is the only place in the pipeline allowed to invent a key name. Everything it emits is in one unit system: bytes as integers, durations as integer milliseconds, timestamps as RFC 3339 in UTC, dimensions post-orientation. It uses a strict schema so an unknown key is an error rather than a silent addition to your GIN index.

// src/metadata/normalise.ts
import { z } from "zod";

export const ATTRS_MAX_BYTES = 4096;
export const ATTRS_MAX_KEYS = 40;

const Attrs = z
  .object({
    title: z.string().max(200).optional(),
    width: z.number().int().positive().max(65_535).optional(),
    height: z.number().int().positive().max(65_535).optional(),
    durationMs: z.number().int().nonnegative().max(86_400_000).optional(),
    pageCount: z.number().int().positive().max(20_000).optional(),
    capturedAt: z.string().datetime({ offset: true }).optional(),
    cameraModel: z.string().max(64).optional(),
    colourSpace: z.enum(["srgb", "display-p3", "rec2020", "cmyk", "gray"]).optional(),
    faststart: z.boolean().optional(),
  })
  .strict();

export type NormalisedAttrs = z.infer<typeof Attrs>;

export class AttrsTooLargeError extends RangeError {}

export function normaliseAttrs(input: Record<string, unknown>): NormalisedAttrs {
  const parsed = Attrs.parse(input);
  const attrs = Object.fromEntries(
    Object.entries(parsed).filter(([, value]) => value !== undefined),
  ) as NormalisedAttrs;

  const keyCount = Object.keys(attrs).length;
  if (keyCount > ATTRS_MAX_KEYS) {
    throw new AttrsTooLargeError(`attrs has ${keyCount} keys, cap is ${ATTRS_MAX_KEYS}`);
  }
  const encoded = Buffer.byteLength(JSON.stringify(attrs), "utf8");
  if (encoded > ATTRS_MAX_BYTES) {
    throw new AttrsTooLargeError(`attrs is ${encoded} bytes, cap is ${ATTRS_MAX_BYTES}`);
  }
  return attrs;
}

/** ffprobe reports duration as a decimal string of seconds; EXIF gives rationals. */
export function toDurationMs(raw: string | [number, number] | number): number {
  if (typeof raw === "number") return Math.round(raw * 1000);
  if (Array.isArray(raw)) {
    const [numerator, denominator] = raw;
    if (denominator === 0) throw new RangeError("rational with zero denominator");
    return Math.round((numerator / denominator) * 1000);
  }
  const seconds = Number.parseFloat(raw);
  if (!Number.isFinite(seconds)) throw new RangeError(`unparseable duration: ${raw}`);
  return Math.round(seconds * 1000);
}

AttrsTooLargeError is another permanent failure. Retrying it will not make the document smaller, and letting it through would breach the pg_column_size(attrs) <= 4096 check constraint anyway — the database would reject the insert with new row for relation "file_metadata" violates check constraint "file_metadata_attrs_check", which is a much less informative place to discover the problem.

4. Upsert with a monotonic guard #

This is the step that decides whether your index is correct. The write is a single statement with a conflict target on the natural key and a WHERE on the update branch that refuses to move the row backwards in time.

INSERT INTO file_metadata (
  tenant_id, storage_key, media_id, size_bytes, mime_type,
  content_sha256, state, sequencer, attrs, sidecar_key, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, $10, now())
ON CONFLICT (tenant_id, storage_key) DO UPDATE
SET media_id       = EXCLUDED.media_id,
    size_bytes     = EXCLUDED.size_bytes,
    mime_type      = EXCLUDED.mime_type,
    content_sha256 = EXCLUDED.content_sha256,
    state          = EXCLUDED.state,
    sequencer      = EXCLUDED.sequencer,
    attrs          = file_metadata.attrs || EXCLUDED.attrs,
    sidecar_key    = COALESCE(EXCLUDED.sidecar_key, file_metadata.sidecar_key),
    updated_at     = now()
WHERE EXCLUDED.sequencer > file_metadata.sequencer
RETURNING (xmax = 0) AS inserted, sequencer;

Two idioms in there earn their keep. attrs = file_metadata.attrs || EXCLUDED.attrs merges rather than replaces, so the semantic-enrichment pass from tier three can add title without wiping the width the fast pass wrote. And RETURNING (xmax = 0) AS inserted distinguishes a fresh insert from an update in the same round trip — xmax is zero only for a tuple this transaction created — which is how you get a truthful “new files indexed” metric rather than one that counts every duplicate delivery.

// src/metadata/upsert.ts
import { Pool } from "pg";
import type { IndexJob } from "./envelope.js";
import type { NormalisedAttrs } from "./normalise.js";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 8,
  statement_timeout: 4_000,
  idle_in_transaction_session_timeout: 10_000,
});

const UPSERT_SQL = `
INSERT INTO file_metadata (
  tenant_id, storage_key, media_id, size_bytes, mime_type,
  content_sha256, state, sequencer, attrs, sidecar_key, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb, $10, now())
ON CONFLICT (tenant_id, storage_key) DO UPDATE
SET media_id = EXCLUDED.media_id, size_bytes = EXCLUDED.size_bytes,
    mime_type = EXCLUDED.mime_type, content_sha256 = EXCLUDED.content_sha256,
    state = EXCLUDED.state, sequencer = EXCLUDED.sequencer,
    attrs = file_metadata.attrs || EXCLUDED.attrs,
    sidecar_key = COALESCE(EXCLUDED.sidecar_key, file_metadata.sidecar_key),
    updated_at = now()
WHERE EXCLUDED.sequencer > file_metadata.sequencer
RETURNING (xmax = 0) AS inserted`;

export type UpsertOutcome = "inserted" | "updated" | "stale";

export async function upsertMetadata(
  job: IndexJob,
  row: { mimeType: string; sizeBytes: number; sha256: Buffer | null; sidecarKey: string | null },
  attrs: NormalisedAttrs,
): Promise<UpsertOutcome> {
  const result = await pool.query<{ inserted: boolean }>(UPSERT_SQL, [
    job.tenantId, job.key, job.mediaId, row.sizeBytes, row.mimeType,
    row.sha256, "indexed", job.sequencer, JSON.stringify(attrs), row.sidecarKey,
  ]);
  // Zero rows means the guard fired: an event older than what is already stored.
  if (result.rowCount === 0) return "stale";
  return result.rows[0].inserted ? "inserted" : "updated";
}

A "stale" outcome is a success, not an error. Acknowledge the message, increment a counter, and move on. If that counter is more than about 2% of throughput you have a real ordering problem worth investigating — usually a client retrying a PUT to the same key rather than using retrying fetch uploads with idempotency keys.

5. Serve the query with tenant scoping and keyset pagination #

The search endpoint is where a well-built index gets ruined. Two rules: the tenant predicate is never optional and never comes from the request body, and pagination is keyset, not OFFSET. OFFSET 20000 makes Postgres materialise and discard twenty thousand rows; on a 40-million-row table that is the difference between 4 ms and 900 ms on page 1000.

// src/metadata/search.ts
import { Pool } from "pg";

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

export interface SearchCursor {
  createdAt: string;
  storageKey: string;
}

export interface SearchQuery {
  tenantId: string;
  text?: string;
  mimePrefix?: string;
  attrsFilter?: Record<string, unknown>;
  after?: SearchCursor;
  limit?: number;
}

const SEARCH_SQL = `
SELECT storage_key, media_id, mime_type, size_bytes, attrs, created_at
FROM file_metadata
WHERE tenant_id = $1
  AND state <> 'deleted'
  AND ($2::text  IS NULL OR search_vector @@ websearch_to_tsquery('english', $2))
  AND ($3::text  IS NULL OR mime_type LIKE $3 || '%')
  AND ($4::jsonb IS NULL OR attrs @> $4)
  AND ($5::timestamptz IS NULL OR (created_at, storage_key) < ($5, $6))
ORDER BY created_at DESC, storage_key DESC
LIMIT $7`;

export async function searchMetadata(q: SearchQuery) {
  const limit = Math.min(Math.max(q.limit ?? 25, 1), 100);
  const { rows } = await pool.query(SEARCH_SQL, [
    q.tenantId,
    q.text?.trim() || null,
    q.mimePrefix ?? null,
    q.attrsFilter ? JSON.stringify(q.attrsFilter) : null,
    q.after?.createdAt ?? null,
    q.after?.storageKey ?? null,
    limit,
  ]);
  const last = rows.at(-1);
  return {
    items: rows,
    nextCursor: rows.length === limit && last
      ? { createdAt: last.created_at.toISOString(), storageKey: last.storage_key }
      : null,
  };
}

The ($5, $6) row-comparison in the cursor predicate is what makes keyset pagination match the composite ORDER BY exactly; comparing the two columns separately with AND produces a subtly wrong page boundary whenever two files share a created_at to the microsecond, which happens constantly in batch imports. Because q.tenantId comes from the verified session and never from user input, the same endpoint is safe to expose publicly behind the throttles described in upload rate limiting and abuse protection.

Configuration reference #

Query shapes and what they cost #

Every metadata search your product ships resolves to one of five shapes, and the difference between them is four orders of magnitude. The figures below come from a 40-million-row table on an 8 vCPU / 32 GB instance with the indexes above warm in shared buffers.

The fourth bar is the one that catches teams out. ORDER BY ts_rank(search_vector, query) DESC LIMIT 20 cannot use an index for the sort, because the rank is computed per row after the match. If the query matches 400,000 rows, Postgres computes 400,000 ranks and top-N sorts them. The fix is to narrow before you rank: apply the tenant, date-range and MIME predicates first so the candidate set is in the low thousands, and only then rank. In practice that means the ranking query should carry a created_at > now() - interval '2 years' guard even when the UI does not ask for one.

The fifth bar is what you get when the planner declines your index — most often because a comparison forced a cast, as in WHERE attrs->>'durationMs' > '120000', which compares text lexicographically and matches '99' as greater than '120000'. Both wrong and slow. Cast explicitly on both sides, or promote the field to a hot column.

Edge cases and gotchas #

Out-of-order events overwrite newer metadata #

Two writes to the same key seconds apart produce two events, and nothing guarantees they are consumed in the order they were produced. A visibility-timeout expiry, an uneven consumer, or plain queue fan-out is enough to reverse them. Without a guard, the second-consumed-but-first-written event restores the old size, the old MIME type and the old dimensions — and because nothing errors, you find out weeks later when a user reports that their replaced logo is the wrong shape.

Two caveats on sequencer. It is only comparable within the same key — comparing sequencers across objects is meaningless. And it is a variable-length hex string, so compare it as text with the values left-padded to a common length, or store it padded on write. Comparing '0080' against '00801B' as text works because the shorter string sorts first, but only as long as every value shares the same prefix length; padding to 32 characters on insert removes the doubt entirely.

The object is gone before the worker reads it #

HeadObject returning 404 NotFound, or GetObject failing with NoSuchKey: The specified key does not exist, is routine rather than exceptional. Someone deleted the upload while the message sat in the queue; a temporary-upload expiry rule from setting up S3 lifecycle rules for temporary uploads reaped it; the quarantine step moved an infected object out from under you.

Treat it as a terminal outcome: write a tombstone row with state = 'deleted' if a row already exists, acknowledge the message, and do not retry. Retrying a 404 five times with backoff wastes four minutes of worker capacity for a result that cannot change. The one case worth distinguishing is 403 AccessDenied on a bucket where you know the key exists — that is an IAM regression, and it should page rather than silently drain to the dead-letter queue, because with a broad Deny in place S3 returns 403 for missing objects too.

The declared Content-Type is fiction #

Content-Type on the object is whatever the uploader set, and browsers guess it from the file extension. Indexing on it means a .jpg extension on a PDF puts that PDF in your photo grid, and an application/octet-stream fallback from a mobile client makes half your library unfilterable. This is the same problem described in why browser MIME types are unreliable, arriving one layer later.

Index the sniffed type as mime_type and keep the declared value in attrs.declaredType only when the two disagree — which in a mixed consumer corpus is 3–6% of objects. Storing the disagreement rather than just the winner gives you a queryable signal: a tenant whose disagreement rate jumps to 40% overnight is either a broken integration or someone probing your validation.

GIN pending-list flushes show up as random slow queries #

With fastupdate = on — the default — GIN inserts land in an unordered pending list and are merged into the index in bulk later. The merge is performed by whichever backend happens to trip the gin_pending_list_limit threshold, which means one unlucky SELECT occasionally pays for thousands of other sessions’ writes. The symptom is a p99 that is 50–100x the median with no pattern in the query text.

Confirm it by querying pg_stat_user_indexes alongside pgstattuple on the GIN index, or simply by watching whether the slow queries stop when you set fastupdate = off:

ALTER INDEX file_metadata_fts SET (fastupdate = off);
-- Existing pending entries are merged on the next VACUUM, or force it now:
VACUUM (ANALYZE) file_metadata;

The trade is real: with fastupdate off, every insert updates the index directly, and bulk ingestion of a million rows slows by roughly 30–40%. For a read-heavy search table that is usually the right side of the trade; for the write-heavy ingest window of a migration, turn it back on and flush afterwards.

Reindexing 40 million rows without taking the table down #

Changing the search_vector expression, switching operator class, or adding a field to the weighted vector all mean rebuilding a GIN index. CREATE INDEX takes an ACCESS EXCLUSIVE lock and will stop your application dead for the duration — 20 to 40 minutes at this size. CREATE INDEX CONCURRENTLY takes only a SHARE UPDATE EXCLUSIVE lock, at the cost of two table passes and the possibility of failing part-way.

-- Build alongside the live index; this can take 2–3x as long as a blocking build.
CREATE INDEX CONCURRENTLY file_metadata_fts_v2
  ON file_metadata USING gin (search_vector);

-- Watch it from another session.
SELECT phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

-- A concurrent build that fails leaves an INVALID index behind. Find and drop it.
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

BEGIN;
DROP INDEX file_metadata_fts;
ALTER INDEX file_metadata_fts_v2 RENAME TO file_metadata_fts;
COMMIT;

Raise maintenance_work_mem to 1 GB for the session first. The difference between the 64 MB default and 1 GB on a 40-million-row GIN build is roughly 38 minutes versus 11 in my last measurement, entirely because the smaller setting spills the intermediate posting lists to disk.

Deletes, overwrites and the rows nobody cleans up #

A hard DELETE on metadata is almost always wrong. Object storage deletes are eventually consistent from the perspective of your event pipeline, so a late-arriving ObjectCreated for a key you just deleted resurrects the row — and now you have a searchable file that does not exist. Write state = 'deleted' with the deleting event’s sequencer instead, let the partial index exclude it from queries, and reap tombstones older than 30 days with a scheduled job.

Overwrites are the mirror image. The same key with new content keeps the same primary key, so the upsert path handles it — but content_sha256 changes, and anything derived from the old content (thumbnails, transcripts, the sidecar object) is now stale. Bump a content_version in attrs on every sequencer advance and make derivative keys include it, exactly as post-upload media transcoding does with its recipe version, so a stale CDN cache cannot serve the previous file’s poster frame.

Backfilling an existing bucket without a queue storm #

Turning indexing on for a bucket that already holds ten million objects has no events to consume. The instinct is to list the bucket and enqueue everything, which produces a ten-million-message burst that saturates your database connections and, if your workers autoscale on queue depth, a bill you will remember.

List with ListObjectsV2 and a continuation token, enqueue in batches of ten, and rate-limit the producer to whatever your database can absorb — a useful starting point is 200 messages per second per database vCPU. Synthesise a sequencer for backfilled objects that sorts below any real one (a run of zeroes works, since real sequencers are non-zero hex), so a genuine event arriving mid-backfill always wins the guard. And run the backfill against a replica for the read side if you can: ListObjectsV2 at 1,000 keys per call is 10,000 requests for ten million objects, which is trivial, but the ten million upserts are not.

Verification #

Prove the pipeline works at three layers: the write is idempotent, the index is actually used, and the queue drains.

First, confirm the guard by replaying an old event. Insert a row, then re-run the upsert with a lower sequencer and check that nothing moves:

// scripts/verify-guard.ts
import assert from "node:assert/strict";
import { upsertMetadata } from "../src/metadata/upsert.js";

const base = {
  bucket: "uploads-prod",
  key: "originals/6f1a2b3c-4d5e-6f70-8192-a3b4c5d6e7f8/2026/07/26/9f3c1b2a/source.jpg",
  tenantId: "6f1a2b3c-4d5e-6f70-8192-a3b4c5d6e7f8",
  mediaId: "9f3c1b2a",
  sizeHint: null,
  etag: null,
  observedAt: new Date().toISOString(),
};
const row = { mimeType: "image/jpeg", sizeBytes: 240_112, sha256: null, sidecarKey: null };

const first = await upsertMetadata({ ...base, sequencer: "0080".padEnd(32, "0") }, row, { width: 4032, height: 3024 });
const newer = await upsertMetadata({ ...base, sequencer: "0081".padEnd(32, "0") }, { ...row, sizeBytes: 512_000 }, { width: 6000, height: 4000 });
const older = await upsertMetadata({ ...base, sequencer: "0079".padEnd(32, "0") }, { ...row, sizeBytes: 1 }, { width: 8, height: 8 });

assert.equal(first, "inserted");
assert.equal(newer, "updated");
assert.equal(older, "stale");
console.log("guard holds: inserted → updated → stale");

Second, confirm the planner is using the indexes rather than guessing. Run each query shape under EXPLAIN (ANALYZE, BUFFERS) and read the node types, not the timings:

EXPLAIN (ANALYZE, BUFFERS)
SELECT storage_key, created_at FROM file_metadata
WHERE tenant_id = '6f1a2b3c-4d5e-6f70-8192-a3b4c5d6e7f8'
  AND state <> 'deleted'
ORDER BY created_at DESC, storage_key DESC
LIMIT 25;

The plan you want opens with Index Only Scan Backward using file_metadata_recent, reports Heap Fetches: 0, and shows Buffers: shared hit in the low tens. A Bitmap Heap Scan with a large Rows Removed by Filter means the partial index predicate does not match your WHERE clause literally — Postgres only uses a partial index when it can prove the query predicate implies the index predicate, and state != 'deleted' written as state IS DISTINCT FROM 'deleted' does not qualify.

Third, check the index is earning its storage. An index with idx_scan = 0 after a week of production traffic is pure write amplification:

SELECT indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'file_metadata'
ORDER BY idx_scan ASC;

Finally, verify the end-to-end lag from a shell. Upload an object, then poll for the row and print the delta — anything above two seconds at steady state means the queue is backing up, not that extraction is slow:

aws s3 cp ./fixture.jpg \
  "s3://uploads-prod/originals/6f1a2b3c-4d5e-6f70-8192-a3b4c5d6e7f8/2026/07/26/9f3c1b2a/fixture.jpg"

psql "$DATABASE_URL" -Atc "
  SELECT storage_key,
         mime_type,
         attrs ->> 'width' AS w,
         round(extract(epoch FROM (updated_at - created_at)) * 1000) AS write_lag_ms
  FROM file_metadata
  WHERE media_id = '9f3c1b2a';"

aws sqs get-queue-attributes \
  --queue-url "$INDEX_QUEUE_URL" \
  --attribute-names ApproximateNumberOfMessagesVisible ApproximateAgeOfOldestMessage

ApproximateAgeOfOldestMessage is the metric to alarm on, not queue depth. Depth spikes are normal during a burst upload; an oldest-message age climbing past 60 seconds means consumers are failing or under-provisioned, and it is the single alarm that catches every failure mode above.

Frequently Asked Questions