Storing Image Dimensions and Duration Metadata #

Probe every asset once at ingest — Sharp for pixels, ffprobe for time — normalise the results into typed columns (width, height, duration_ms) with the format-specific leftovers in a jsonb column behind a GIN index, and questions like “portrait video longer than 60 seconds” become a millisecond index scan instead of a re-probe of the whole bucket.

This article sits inside Metadata Indexing & Search, part of Backend Validation & Cloud Storage Architecture. It assumes you already have the general table and index strategy from how to index file metadata in PostgreSQL; what follows is specific to technical media attributes and the unit traps that come with them.

When to use this approach #

• You serve a media catalogue where users or downstream jobs filter by shape or length — thumbnails needing a 16:9 crop, a moderation queue that only handles clips under two minutes, a billing job that meters transcoded seconds.
• Re-probing on read is too slow or too expensive: an ffprobe round trip against object storage costs 100–400 ms even when it only reads the header, and you cannot pay that per row in a listing endpoint.
• You want the answer in SQL rather than in application code, so pagination, sorting and count(*) all stay in the database.

If you only ever need dimensions to reject oversized decodes at upload time, that is a validation concern, not an indexing one — handle it alongside server-side file validation and do not persist anything.

Prerequisites #

1. Node.js 20+ with sharp 0.33+ and the pg driver (npm i sharp pg).
2. ffprobe on PATH — the FFmpeg 6.x or 7.x build, verified with ffprobe -version.
3. PostgreSQL 12+ for GENERATED ALWAYS AS … STORED columns; 14+ if you want the faster jsonb_path_ops planner behaviour under partial indexes.
4. A worker that already receives an object-created event after a direct upload — see direct-to-cloud upload patterns for how that event gets to you.

The schema #

The rule for deciding what becomes a column: promote an attribute if you filter, sort or aggregate on it; leave it in jsonb if you only display it or query it occasionally by exact match. Dimensions and duration are always promoted. Codec names, colour space, sample rate and frame counts almost never are.

CREATE TABLE media_assets (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  storage_key   text        NOT NULL UNIQUE,
  mime_type     text        NOT NULL,
  byte_size     bigint      NOT NULL CHECK (byte_size > 0),
  kind          text        NOT NULL CHECK (kind IN ('image','video','audio','other')),

  -- Display geometry: what a viewer sees, AFTER orientation/rotation is applied.
  width         integer     CHECK (width  > 0),
  height        integer     CHECK (height > 0),

  -- Integer milliseconds. Never a float of seconds, never a text column.
  duration_ms   integer     CHECK (duration_ms >= 0),

  -- Everything format-specific: codec, fps, colour space, audio channels.
  technical     jsonb       NOT NULL DEFAULT '{}'::jsonb,
  ingested_at   timestamptz NOT NULL DEFAULT now(),

  aspect_ratio  numeric(9,6) GENERATED ALWAYS AS (
    CASE WHEN height > 0 THEN round(width::numeric / height::numeric, 6) END
  ) STORED,

  orientation_class text GENERATED ALWAYS AS (
    CASE
      WHEN width IS NULL OR height IS NULL THEN NULL
      WHEN width > height THEN 'landscape'
      WHEN width < height THEN 'portrait'
      ELSE 'square'
    END
  ) STORED
);

-- The composite that answers the shape+length questions.
CREATE INDEX media_assets_shape_idx
  ON media_assets (kind, orientation_class, duration_ms DESC);

-- Near-16:9 and other ratio windows.
CREATE INDEX media_assets_aspect_idx
  ON media_assets (aspect_ratio) WHERE aspect_ratio IS NOT NULL;

-- Containment queries against the sparse half of the row.
CREATE INDEX media_assets_technical_gin
  ON media_assets USING gin (technical jsonb_path_ops);

aspect_ratio is stored, not virtual — PostgreSQL has no virtual generated columns before 18, and a stored column is what makes media_assets_aspect_idx possible. Six decimal places is enough to separate 1.777778 (16:9) from 1.775 (a 1420×800 export) without float noise; numeric also compares exactly, so a BETWEEN window behaves the way you wrote it.

jsonb_path_ops builds an index roughly 30–40% smaller than the default jsonb_ops and is faster for @>, at the cost of not supporting the key-existence operators ?, ?| and ?&. If you need technical ? 'audio' to be indexed, use the default opclass instead.

Implementation #

One extractor, two code paths, one normalised shape written back. The branch is on the family of the detected MIME type — detected from the bytes, not from the upload’s Content-Type header, which browsers get wrong routinely.

import { execFile } from "node:child_process";
import { promisify } from "node:util";
import sharp from "sharp";
import { Pool } from "pg";

const execFileAsync = promisify(execFile);

export interface ProbeResult {
  kind: "image" | "video" | "audio" | "other";
  width: number | null;      // display pixels, rotation applied
  height: number | null;
  durationMs: number | null; // integer milliseconds
  technical: Record<string, unknown>;
}

// EXIF orientations 5–8 all involve a 90° rotation, so the stored buffer is
// transposed relative to what a viewer sees.
const TRANSPOSING_ORIENTATIONS = new Set([5, 6, 7, 8]);

export async function probeImage(buf: Buffer): Promise<ProbeResult> {
  const m = await sharp(buf, { failOn: "error" }).metadata();
  if (!m.width || !m.height) throw new Error("sharp returned no pixel dimensions");

  const orientation = m.orientation ?? 1;          // undefined when there is no EXIF block
  const transposed = TRANSPOSING_ORIENTATIONS.has(orientation);
  const pageHeight = m.pageHeight ?? m.height;     // animated WebP/GIF: one frame, not the strip

  return {
    kind: "image",
    width: transposed ? pageHeight : m.width,
    height: transposed ? m.width : pageHeight,
    durationMs: null,
    technical: {
      image: {
        codec: m.format ?? null,                   // 'jpeg' | 'png' | 'webp' | 'avif' | 'tiff'
        stored_width: m.width,                     // pre-rotation, what the decoder allocates
        stored_height: pageHeight,
        orientation,
        colour_space: m.space ?? null,             // 'srgb' | 'cmyk' | 'b-w'
        channels: m.channels ?? null,
        has_alpha: m.hasAlpha ?? false,
        density_dpi: m.density ?? null,
        pages: m.pages ?? 1,                       // > 1 for animated or multi-page TIFF
      },
    },
  };
}

interface FfprobeStream {
  codec_type?: string;
  codec_name?: string;
  width?: number;
  height?: number;
  r_frame_rate?: string;
  avg_frame_rate?: string;
  nb_frames?: string;
  channels?: number;
  sample_rate?: string;
  tags?: Record<string, string>;
  side_data_list?: Array<{ rotation?: number }>;
}

interface FfprobeOutput {
  format?: { duration?: string; bit_rate?: string; format_name?: string };
  streams?: FfprobeStream[];
}

/** ffprobe reports frame rates as rationals: "30000/1001" → 29.97. */
function parseRational(value: string | undefined): number | null {
  if (!value) return null;
  const [num, den] = value.split("/").map(Number);
  if (!Number.isFinite(num) || !den) return null;              // "0/0" means unknown
  return Math.round((num / den) * 1000) / 1000;
}

/** Display-matrix rotation, normalised to 0/90/180/270. iPhone portrait clips report -90. */
function rotationOf(stream: FfprobeStream): number {
  const sideData = stream.side_data_list?.find((d) => typeof d.rotation === "number")?.rotation;
  const legacyTag = stream.tags?.rotate !== undefined ? Number(stream.tags.rotate) : undefined;
  const degrees = sideData ?? legacyTag ?? 0;
  return ((Math.round(degrees) % 360) + 360) % 360;
}

export async function probeAv(input: string): Promise<ProbeResult> {
  const { stdout } = await execFileAsync(
    "ffprobe",
    ["-v", "error", "-print_format", "json", "-show_format", "-show_streams", input],
    { maxBuffer: 8 * 1024 * 1024, timeout: 30_000 },
  );

  const probe = JSON.parse(stdout) as FfprobeOutput;
  const video = probe.streams?.find((s) => s.codec_type === "video");
  const audio = probe.streams?.find((s) => s.codec_type === "audio");

  // format.duration is a STRING of seconds, e.g. "63.166667". Round to whole ms.
  const seconds = Number(probe.format?.duration);
  const durationMs = Number.isFinite(seconds) ? Math.round(seconds * 1000) : null;

  let width: number | null = null;
  let height: number | null = null;
  let rotation = 0;
  if (video?.width && video?.height) {
    rotation = rotationOf(video);
    const transposed = rotation === 90 || rotation === 270;
    width = transposed ? video.height : video.width;
    height = transposed ? video.width : video.height;
  }

  const nominalFps = parseRational(video?.r_frame_rate);
  const averageFps = parseRational(video?.avg_frame_rate);

  return {
    kind: video ? "video" : "audio",
    width,
    height,
    durationMs,
    technical: {
      container: probe.format?.format_name ?? null,
      bitrate_bps: probe.format?.bit_rate ? Number(probe.format.bit_rate) : null,
      video: video
        ? {
            codec: video.codec_name ?? null,
            stored_width: video.width ?? null,
            stored_height: video.height ?? null,
            rotation_deg: rotation,
            nominal_fps: nominalFps,
            average_fps: averageFps,
            frame_count: video.nb_frames ? Number(video.nb_frames) : null,
            variable_frame_rate:
              nominalFps !== null && averageFps !== null
                ? Math.abs(nominalFps - averageFps) / nominalFps > 0.01
                : null,
          }
        : null,
      audio: audio
        ? {
            codec: audio.codec_name ?? null,
            channels: audio.channels ?? null,
            sample_rate_hz: audio.sample_rate ? Number(audio.sample_rate) : null,
          }
        : null,
    },
  };
}

export async function recordAsset(
  pool: Pool,
  storageKey: string,
  mimeType: string,
  byteSize: number,
  probe: ProbeResult,
): Promise<void> {
  await pool.query(
    `INSERT INTO media_assets
       (storage_key, mime_type, byte_size, kind, width, height, duration_ms, technical)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
     ON CONFLICT (storage_key) DO UPDATE SET
       width       = EXCLUDED.width,
       height      = EXCLUDED.height,
       duration_ms = EXCLUDED.duration_ms,
       technical   = media_assets.technical || EXCLUDED.technical,
       ingested_at = now()`,
    [
      storageKey,
      mimeType,
      byteSize,
      probe.kind,
      probe.width,
      probe.height,
      probe.durationMs,
      JSON.stringify(probe.technical),
    ],
  );
}

Line-by-line on the parameters that matter #

• sharp(buf, { failOn: "error" }) fails on a genuinely corrupt file but tolerates warnings such as a truncated JPEG tail. The default is "warning", which rejects perfectly loadable images that a browser would render; "none" swings too far and lets a half-downloaded object through as a valid asset.
• m.orientation is the raw EXIF tag value 1–8, not a rotation in degrees. Sharp does not apply it to the reported width/height — that is a decode-time transform you opt into with .rotate(). Reading the tag and swapping yourself is the only way to get display geometry without decoding pixels.
• m.pageHeight matters for animated WebP and GIF. When you construct with { animated: true } or { pages: -1 }, m.height becomes the height of every frame stacked vertically — a 60-frame 320×240 GIF reports height: 14400. pageHeight is always one frame.
• -show_format -show_streams in one invocation is deliberate: two ffprobe calls means two header reads and, over HTTP, two connections. -v error suppresses the banner so stdout is pure JSON.
• maxBuffer: 8 * 1024 * 1024 guards against a file with hundreds of chapter or subtitle streams overflowing the default 1 MB pipe buffer, which surfaces as Error: stdout maxBuffer length exceeded and loses the whole probe.
• technical = media_assets.technical || EXCLUDED.technical is a shallow merge. Re-probing a video replaces the entire video sub-object but preserves unrelated top-level keys such as an AI-tagging payload written by another worker. Nested merges need jsonb_set or jsonb_deep_merge from an extension.

Probing without downloading the whole object #

ffprobe speaks HTTP, so a worker never has to pull a 4 GB file to learn it is 96 seconds long:

ffprobe -v error -print_format json -show_format \
  -select_streams v:0 -show_streams \
  -probesize 5M -analyzeduration 5M \
  "$PRESIGNED_GET_URL"

FFmpeg issues ranged GETs and typically reads 1–3 MB. The exception is MP4 with the moov atom at the end of the file — a common output of naive encoders. FFmpeg then seeks to the tail, costing a second range request and, on some CDNs, a full-object fetch. Encoding with -movflags +faststart moves moov to the front and makes the probe consistently cheap; it is worth doing in the same post-upload job that generates a presigned URL for the worker.

-probesize and -analyzeduration cap how far FFmpeg reads before it gives up identifying streams. The defaults (5 MB / 5 s) fail on some MPEG-TS captures where the first video packet arrives late; raising them to 50M fixes Could not find codec parameters for stream 0 at the cost of a slower probe.

For images the equivalent trick is a ranged read: JPEG, PNG and WebP all carry dimensions within the first few kilobytes, so fetching bytes 0–65535 and handing that buffer to Sharp is usually enough. The same header-first idea drives client-side probing with FileReader and ArrayBuffer, where the browser reads the head of a file before the upload even starts.

Units and orientation pitfalls #

EXIF orientation 6 and 8 swap width and height #

A phone camera does not rotate pixels; it writes the sensor’s native landscape buffer and tags it. Orientation 6 means “rotate 90° clockwise for display”, orientation 8 means 90° counter-clockwise, and 5 and 7 add a mirror. Store the untransposed numbers and every portrait photo in your catalogue is classified as landscape.

Keep both numbers. width/height drive the catalogue; technical.image.stored_width is what a resize worker needs to estimate decode memory, because the decoder allocates the untransposed buffer. That estimate is the same one used to reject decompression bombs during server-side validation of file signatures.

ffprobe duration is a string of seconds #

format.duration comes back as "63.166667" — a JSON string, with microsecond precision, in seconds. Three failure modes follow from ignoring that:

• Comparing it directly (if (probe.format.duration > 60)) does a string comparison in JavaScript, so "9.5" > "60" is true.
• Inserting the parsed float into an integer column throws at the driver boundary rather than rounding.
• Storing seconds as numeric looks fine until someone writes duration > 60 against a column holding 59.999999 and misses a clip that any player rounds up to 60.

Integer milliseconds sidesteps all three: Math.round(seconds * 1000), one unit, exact comparisons, and int4 tops out at 24.8 days.

Stream-level duration is not a substitute. Matroska and WebM omit it entirely, so streams[0].duration is undefined while format.duration is correct. The reverse happens with raw elementary streams. Read format.duration first and fall back to the video stream only when it is absent.

VFR video reports a nominal frame rate #

r_frame_rate is FFmpeg’s guessed base frame rate — the smallest rate at which every timestamp lands on a frame boundary. For a constant-frame-rate file it is the truth. For a screen recording or a phone clip shot in low light, the encoder drops frames and r_frame_rate still reports 30/1 while avg_frame_rate (frames divided by duration) reports 17.83.

Never derive duration as nb_frames / r_frame_rate; on a variable-frame-rate file that is wrong by tens of percent, and nb_frames is missing entirely for most streaming containers. Store both rates and the variable_frame_rate flag, then let a transcoding job decide whether it needs to normalise with -vsync cfr. Counting real frames requires -count_frames, which decodes the whole file — minutes of CPU for a long clip, and not something to run in an ingest worker handling 500 MB uploads.

The queries that pay for the schema #

-- Portrait video longer than 60 seconds, newest-longest first.
SELECT storage_key, width, height, duration_ms
FROM media_assets
WHERE kind = 'video'
  AND orientation_class = 'portrait'
  AND duration_ms > 60000
ORDER BY duration_ms DESC
LIMIT 50;

-- Anything near 16:9, within half a percent.
SELECT storage_key, aspect_ratio
FROM media_assets
WHERE aspect_ratio BETWEEN 1.768889 AND 1.786667;

-- Codec sweep for a re-encode campaign, served by the GIN index.
SELECT count(*), sum(duration_ms) / 3600000.0 AS hours
FROM media_assets
WHERE technical @> '{"video": {"codec": "hevc"}}';

-- Assets whose probe failed or was never run.
SELECT storage_key, mime_type
FROM media_assets
WHERE (kind = 'image' AND width IS NULL)
   OR (kind = 'video' AND duration_ms IS NULL);

The first query is the one that justifies the two generated columns. Written against raw jsonb it would be (technical->'video'->>'height')::int > (technical->'video'->>'width')::int, which no index can serve and which costs a full scan plus a cast per row. With media_assets_shape_idx the plan is an index scan over a narrow range, and because the sort key is the trailing index column, ORDER BY duration_ms DESC LIMIT 50 needs no sort node at all.

Text search over filenames and captions is a different index and a different operator — combine the two by adding a tsvector column as described in full-text search on file metadata with PostgreSQL; the planner will happily intersect a GIN bitmap with the B-tree above.

Configuration gotchas #

Error: spawn ffprobe ENOENT — Node cannot find the binary. Containers built FROM node:20-slim have no FFmpeg. Install it (apt-get install -y ffmpeg), or depend on a packaged binary and pass its absolute path to execFile instead of the bare name. Never shell out through exec with a string to work around this; storage keys contain characters that will happily become shell metacharacters.

error: invalid input syntax for type integer: "63.166667" — a float of seconds reached the duration_ms parameter. node-postgres serialises JavaScript numbers as text, so 63.166667 arrives at an int4 column verbatim and Postgres rejects it. Fix it at the source with Math.round(seconds * 1000); do not add a ::numeric::int cast in SQL, because that hides the unit mistake from the next reader.

ERROR: cannot insert a non-DEFAULT value into column "aspect_ratio" / DETAIL: Column "aspect_ratio" is a generated column. — a bulk loader built its column list with SELECT * FROM media_assets LIMIT 0 and included the generated columns. Enumerate columns explicitly in INSERT and in COPY; generated columns must never appear in either.

Error: Input buffer contains unsupported image format — Sharp hit a HEIC/HEIF file and the installed libvips build has no libheif support. That is the default for the prebuilt binaries on several platforms, and it is exactly what an iPhone upload looks like when the user has not enabled “Most Compatible”. Detect the family from magic bytes before dispatching, route HEIC to ffprobe (which reads its dimensions from the container) or to a libvips build with HEIF enabled, and record technical.probe_error rather than dropping the row.

Verification #

Confirm the probe agrees with the file, then confirm the index is actually used:

# Ground truth for a portrait iPhone clip: stored landscape, rotated for display.
ffprobe -v error -select_streams v:0 -print_format json -show_streams portrait.mp4 \
  | jq '.streams[0] | {width, height, r_frame_rate, avg_frame_rate,
                       rotation: (.side_data_list[]? | .rotation)}'
# → { "width": 1920, "height": 1080, "r_frame_rate": "30/1",
#     "avg_frame_rate": "2997/100", "rotation": -90 }
# Your row must therefore read width 1080, height 1920, orientation_class 'portrait'.

EXPLAIN (ANALYZE, BUFFERS)
SELECT storage_key FROM media_assets
WHERE kind = 'video' AND orientation_class = 'portrait' AND duration_ms > 60000
ORDER BY duration_ms DESC LIMIT 50;
-- Expect: Index Scan using media_assets_shape_idx  (actual rows=50 loops=1)
-- A "Seq Scan" here means ANALYZE has never run, or you filtered on
-- technical->'video'->>'height' instead of the promoted columns.

// Unit assertion for the transpose rule — no fixtures required.
const cases: Array<[number, number, number, [number, number]]> = [
  [1, 4032, 3024, [4032, 3024]],
  [6, 4032, 3024, [3024, 4032]],
  [8, 4032, 3024, [3024, 4032]],
  [3, 4032, 3024, [4032, 3024]],
];
for (const [orientation, w, h, expected] of cases) {
  const transposed = new Set([5, 6, 7, 8]).has(orientation);
  const got: [number, number] = transposed ? [h, w] : [w, h];
  console.assert(got[0] === expected[0] && got[1] === expected[1], `orientation ${orientation}`);
}
console.log("orientation transpose rules verified");

Frequently Asked Questions