A Bridge to Terabit-ia

Actually, Terabyte-ia. Unless we get into Aurora Limitless, we may as well stick to convention.

The conceptual case for bridge tables is straightforward: a bridge table gives a relationship somewhere to live.

But that leaves a practical question unanswered.

Suppose the relationship does not need metadata, history, permissions, approval state, or any other independent behavior. Suppose all we need is a set of foreign-key-like identifiers attached to a parent row.

How much worse—or better—is a PostgreSQL array with a GIN index than a conventional bridge table?

This article makes the comparison reproducible.

It creates the same logical dataset twice:

articles_array.tag_ids integer[]

and:

articles_bridge
article_tags

The array representation gets a GIN index. The bridge representation gets ordinary B-tree indexes. The script then runs equivalent queries against each model, records repeated timings, measures storage, captures execution plans, and verifies that both models contain the same assignments.

The goal is not to prove that one representation wins every possible workload.

The goal is to make the tradeoff observable.

The Two Models

The array model stores all tag identifiers directly on the article:

CREATE TABLE articles_array (
    id bigint PRIMARY KEY,
    status smallint NOT NULL,
    published_on date NOT NULL,
    payload text NOT NULL,
    tag_ids integer[] NOT NULL
);

CREATE INDEX articles_array_tag_ids_gin
ON articles_array
USING gin (tag_ids);

The bridge model stores one row per assignment:

CREATE TABLE articles_bridge (
    id bigint PRIMARY KEY,
    status smallint NOT NULL,
    published_on date NOT NULL,
    payload text NOT NULL
);

CREATE TABLE article_tags (
    article_id bigint NOT NULL REFERENCES articles_bridge(id),
    tag_id integer NOT NULL REFERENCES tags(id),

    assigned_at timestamptz NOT NULL,
    source smallint NOT NULL,
    is_primary boolean NOT NULL,

    PRIMARY KEY (article_id, tag_id)
);

CREATE INDEX article_tags_tag_article_idx
ON article_tags (tag_id, article_id);

The edge metadata is included deliberately. It does not make every read query faster, but it demonstrates a capability that the array of IDs does not provide naturally: each assignment is already an independent row.

What the Benchmark Tests

The script compares equivalent operations:

• count rows containing one tag;
• count rows containing any of three tags;
• count rows containing all three tags;
• combine a tag filter with a property on the parent row;
• calculate the most frequently used tags;
• retrieve all tags for one parent row;
• compare table and index sizes;
• inspect representative execution plans and buffer use.

Each query pair is warmed once and then run repeatedly. Execution order alternates so the same representation is not always measured first.

These are warm-cache, application-observed timings. They are not a complete characterization of PostgreSQL performance. Hardware, PostgreSQL settings, data distribution, concurrency, cache state, write volume, and query shape can all change the result.

That is why the script records its configuration and emits the raw reports rather than presenting one universal conclusion.

Run the Benchmark

The only external requirement is psql and a PostgreSQL database the current user may create tables in.

Save the following as bridge_benchmark.sh, make it executable, and run it.

#!/usr/bin/env bash
set -euo pipefail

# A Bridge to Terabit-ia
# Actually, Terabyte-ia. Unless we get into Aurora Limitless, we may as well stick to convention.
#
# Compare a PostgreSQL int[] + GIN model with a conventional bridge table.
# The script creates an isolated schema, generates equivalent data, benchmarks
# representative reads, captures EXPLAIN output, and prints size/timing reports.

DATABASE_URL="${DATABASE_URL:-postgresql:///postgres}"
SCHEMA="${SCHEMA:-bridge_bench}"
ARTICLES="${ARTICLES:-1000000}"
TAGS="${TAGS:-10000}"
TAGS_PER_ARTICLE="${TAGS_PER_ARTICLE:-10}"
RUNS="${RUNS:-15}"
PAYLOAD_BYTES="${PAYLOAD_BYTES:-128}"
REPORT_DIR="${REPORT_DIR:-./bridge-benchmark-report}"
KEEP_SCHEMA="${KEEP_SCHEMA:-1}"

for name in ARTICLES TAGS TAGS_PER_ARTICLE RUNS PAYLOAD_BYTES; do
  value="${!name}"
  if [[ ! "$value" =~ ^[1-9][0-9]*$ ]]; then
    echo "$name must be a positive integer; got: $value" >&2
    exit 2
  fi
done

if (( TAGS_PER_ARTICLE > TAGS )); then
  echo "TAGS_PER_ARTICLE must be <= TAGS" >&2
  exit 2
fi

if [[ ! "$SCHEMA" =~ ^[a-zA-Z_][a-zA-Z0-9_]*$ ]]; then
  echo "SCHEMA must be a simple PostgreSQL identifier; got: $SCHEMA" >&2
  exit 2
fi

command -v psql >/dev/null 2>&1 || {
  echo "psql is required but was not found in PATH" >&2
  exit 127
}

mkdir -p "$REPORT_DIR"
SUMMARY_TSV="$REPORT_DIR/summary.tsv"
SIZES_TSV="$REPORT_DIR/sizes.tsv"
EXPLAIN_TXT="$REPORT_DIR/explain.txt"
METADATA_TXT="$REPORT_DIR/metadata.txt"

PSQL=(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 \
  -v schema="$SCHEMA" \
  -v articles="$ARTICLES" \
  -v tags="$TAGS" \
  -v tags_per_article="$TAGS_PER_ARTICLE" \
  -v runs="$RUNS" \
  -v payload_bytes="$PAYLOAD_BYTES")

cat > "$METADATA_TXT" <<META
started_at=$(date -u +%Y-%m-%dT%H:%M:%SZ)
database_url=$DATABASE_URL
schema=$SCHEMA
articles=$ARTICLES
tags=$TAGS
tags_per_article=$TAGS_PER_ARTICLE
runs=$RUNS
payload_bytes=$PAYLOAD_BYTES
META

printf '\n== PostgreSQL bridge-table benchmark ==\n'
printf 'Schema: %s\nArticles: %s\nTags: %s\nAssignments/article: %s\nRuns/query/model: %s\n\n' \
  "$SCHEMA" "$ARTICLES" "$TAGS" "$TAGS_PER_ARTICLE" "$RUNS"

"${PSQL[@]}" <<'SQL'
\echo 'Creating schema and benchmark tables...'
DROP SCHEMA IF EXISTS :"schema" CASCADE;
CREATE SCHEMA :"schema";
SET search_path TO :"schema", public;
SET client_min_messages = warning;
SET jit = off;

CREATE TABLE tags (
    id integer PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE articles_bridge (
    id bigint PRIMARY KEY,
    status smallint NOT NULL,
    published_on date NOT NULL,
    payload text NOT NULL
);

CREATE TABLE article_tags (
    article_id bigint NOT NULL REFERENCES articles_bridge(id) ON DELETE CASCADE,
    tag_id integer NOT NULL REFERENCES tags(id),
    assigned_at timestamptz NOT NULL,
    source smallint NOT NULL,
    is_primary boolean NOT NULL,
    PRIMARY KEY (article_id, tag_id)
);

CREATE TABLE articles_array (
    id bigint PRIMARY KEY,
    status smallint NOT NULL,
    published_on date NOT NULL,
    payload text NOT NULL,
    tag_ids integer[] NOT NULL,
    CHECK (cardinality(tag_ids) > 0)
);

CREATE TABLE benchmark_results (
    test_name text NOT NULL,
    model text NOT NULL CHECK (model IN ('array_gin', 'bridge')),
    run_number integer NOT NULL,
    duration_ms double precision NOT NULL,
    result_value bigint NOT NULL,
    measured_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

\echo 'Generating tags...'
INSERT INTO tags (id, name)
SELECT n, 'tag-' || lpad(n::text, 8, '0')
FROM generate_series(1, :tags::integer) AS g(n);

\echo 'Generating parent rows...'
INSERT INTO articles_bridge (id, status, published_on, payload)
SELECT
    n,
    (n % 5)::smallint,
    DATE '2020-01-01' + ((n * 17) % 2192)::integer,
    left(repeat(md5(n::text), ((:payload_bytes::integer / 32) + 1)), :payload_bytes::integer)
FROM generate_series(1, :articles::bigint) AS g(n);

\echo 'Generating bridge assignments...'
INSERT INTO article_tags (
    article_id,
    tag_id,
    assigned_at,
    source,
    is_primary
)
SELECT
    a.id,
    1 + ((a.id * 7919 + slot.n) % :tags::bigint)::integer,
    TIMESTAMPTZ '2020-01-01 00:00:00+00' + ((a.id + slot.n) % 31536000) * INTERVAL '1 second',
    ((a.id + slot.n) % 4)::smallint,
    slot.n = 0
FROM articles_bridge AS a
CROSS JOIN generate_series(0, :tags_per_article::integer - 1) AS slot(n);

\echo 'Building the equivalent array representation...'
INSERT INTO articles_array (id, status, published_on, payload, tag_ids)
SELECT
    a.id,
    a.status,
    a.published_on,
    a.payload,
    array_agg(at.tag_id ORDER BY at.tag_id)
FROM articles_bridge AS a
JOIN article_tags AS at ON at.article_id = a.id
GROUP BY a.id, a.status, a.published_on, a.payload;

\echo 'Creating indexes...'
CREATE INDEX articles_array_tag_ids_gin ON articles_array USING gin (tag_ids);
CREATE INDEX articles_array_status_id_idx ON articles_array (status, id);
CREATE INDEX articles_bridge_status_id_idx ON articles_bridge (status, id);
CREATE INDEX article_tags_tag_article_idx ON article_tags (tag_id, article_id);
CREATE INDEX article_tags_primary_idx ON article_tags (article_id) WHERE is_primary;

VACUUM (ANALYZE) tags;
VACUUM (ANALYZE) articles_bridge;
VACUUM (ANALYZE) article_tags;
VACUUM (ANALYZE) articles_array;

\echo 'Validating equivalent assignments...'
DO $$
DECLARE
    bridge_count bigint;
    array_count bigint;
    bad_rows bigint;
BEGIN
    SELECT count(*) INTO bridge_count FROM article_tags;
    SELECT sum(cardinality(tag_ids)) INTO array_count FROM articles_array;

    IF bridge_count <> array_count THEN
        RAISE EXCEPTION 'Assignment count mismatch: bridge %, array %', bridge_count, array_count;
    END IF;

    SELECT count(*) INTO bad_rows
    FROM articles_array AS aa
    JOIN LATERAL unnest(aa.tag_ids) AS x(tag_id) ON true
    LEFT JOIN article_tags AS at
      ON at.article_id = aa.id
     AND at.tag_id = x.tag_id
    WHERE at.article_id IS NULL;

    IF bad_rows <> 0 THEN
        RAISE EXCEPTION 'Found % array assignments missing from bridge table', bad_rows;
    END IF;
END
$$;

CREATE OR REPLACE FUNCTION run_benchmark_pair(
    p_test_name text,
    p_array_sql text,
    p_bridge_sql text,
    p_runs integer
) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    i integer;
    started timestamptz;
    finished timestamptz;
    value bigint;
BEGIN
    -- Warm both plans and their commonly used pages once before measurement.
    EXECUTE p_array_sql INTO value;
    EXECUTE p_bridge_sql INTO value;

    FOR i IN 1..p_runs LOOP
        -- Alternate execution order so one representation is not always first.
        IF i % 2 = 1 THEN
            started := clock_timestamp();
            EXECUTE p_array_sql INTO value;
            finished := clock_timestamp();
            INSERT INTO benchmark_results
            VALUES (p_test_name, 'array_gin', i,
                    EXTRACT(epoch FROM finished - started) * 1000.0, value, finished);

            started := clock_timestamp();
            EXECUTE p_bridge_sql INTO value;
            finished := clock_timestamp();
            INSERT INTO benchmark_results
            VALUES (p_test_name, 'bridge', i,
                    EXTRACT(epoch FROM finished - started) * 1000.0, value, finished);
        ELSE
            started := clock_timestamp();
            EXECUTE p_bridge_sql INTO value;
            finished := clock_timestamp();
            INSERT INTO benchmark_results
            VALUES (p_test_name, 'bridge', i,
                    EXTRACT(epoch FROM finished - started) * 1000.0, value, finished);

            started := clock_timestamp();
            EXECUTE p_array_sql INTO value;
            finished := clock_timestamp();
            INSERT INTO benchmark_results
            VALUES (p_test_name, 'array_gin', i,
                    EXTRACT(epoch FROM finished - started) * 1000.0, value, finished);
        END IF;
    END LOOP;
END
$$;

TRUNCATE benchmark_results;

\echo 'Running timed query pairs...'
DO $$
DECLARE
    t1 integer := 1 + ((:tags::integer * 17) % :tags::integer);
    t2 integer := 1 + ((:tags::integer * 41 + 7) % :tags::integer);
    t3 integer := 1 + ((:tags::integer * 73 + 13) % :tags::integer);
    article_target bigint := GREATEST(1, :articles::bigint / 2);
BEGIN
    -- Avoid duplicate chosen tags at very small scales.
    t1 := 1;
    t2 := CASE WHEN :tags::integer >= 2 THEN 2 ELSE 1 END;
    t3 := CASE WHEN :tags::integer >= 3 THEN 3 ELSE t2 END;

    PERFORM run_benchmark_pair(
        'single_tag_count',
        format('SELECT count(*) FROM articles_array WHERE tag_ids @> ARRAY[%s]::integer[]', t1),
        format('SELECT count(*) FROM article_tags WHERE tag_id = %s', t1),
        :runs::integer
    );

    PERFORM run_benchmark_pair(
        'any_three_tags_count',
        format('SELECT count(*) FROM articles_array WHERE tag_ids && ARRAY[%s,%s,%s]::integer[]', t1, t2, t3),
        format('SELECT count(DISTINCT article_id) FROM article_tags WHERE tag_id = ANY (ARRAY[%s,%s,%s]::integer[])', t1, t2, t3),
        :runs::integer
    );

    PERFORM run_benchmark_pair(
        'all_three_tags_count',
        format('SELECT count(*) FROM articles_array WHERE tag_ids @> ARRAY[%s,%s,%s]::integer[]', t1, t2, t3),
        format($q$
            SELECT count(*)
            FROM (
                SELECT article_id
                FROM article_tags
                WHERE tag_id = ANY (ARRAY[%s,%s,%s]::integer[])
                GROUP BY article_id
                HAVING count(DISTINCT tag_id) = 3
            ) AS matching
        $q$, t1, t2, t3),
        :runs::integer
    );

    PERFORM run_benchmark_pair(
        'single_tag_with_status',
        format('SELECT count(*) FROM articles_array WHERE status = 2 AND tag_ids @> ARRAY[%s]::integer[]', t1),
        format($q$
            SELECT count(*)
            FROM article_tags AS at
            JOIN articles_bridge AS a ON a.id = at.article_id
            WHERE at.tag_id = %s AND a.status = 2
        $q$, t1),
        :runs::integer
    );

    PERFORM run_benchmark_pair(
        'top_twenty_tag_frequencies_checksum',
        $q$
            SELECT coalesce(sum(tag_id * frequency), 0)::bigint
            FROM (
                SELECT x.tag_id, count(*)::bigint AS frequency
                FROM articles_array AS a
                CROSS JOIN LATERAL unnest(a.tag_ids) AS x(tag_id)
                GROUP BY x.tag_id
                ORDER BY frequency DESC, x.tag_id
                LIMIT 20
            ) AS top_tags
        $q$,
        $q$
            SELECT coalesce(sum(tag_id * frequency), 0)::bigint
            FROM (
                SELECT tag_id, count(*)::bigint AS frequency
                FROM article_tags
                GROUP BY tag_id
                ORDER BY frequency DESC, tag_id
                LIMIT 20
            ) AS top_tags
        $q$,
        :runs::integer
    );

    PERFORM run_benchmark_pair(
        'tags_for_one_article_checksum',
        format($q$
            SELECT coalesce(sum(t.id), 0)::bigint
            FROM articles_array AS a
            CROSS JOIN LATERAL unnest(a.tag_ids) AS x(tag_id)
            JOIN tags AS t ON t.id = x.tag_id
            WHERE a.id = %s
        $q$, article_target),
        format($q$
            SELECT coalesce(sum(t.id), 0)::bigint
            FROM article_tags AS at
            JOIN tags AS t ON t.id = at.tag_id
            WHERE at.article_id = %s
        $q$, article_target),
        :runs::integer
    );
END
$$;

\echo 'Benchmark complete.'
SQL

printf '\n== Timing summary (warm-cache application-observed latency) ==\n'
"${PSQL[@]}" -P pager=off -c "
SET search_path TO \"$SCHEMA\", public;
SELECT
    test_name,
    model,
    round(min(duration_ms)::numeric, 3) AS min_ms,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY duration_ms)::numeric, 3) AS median_ms,
    round(avg(duration_ms)::numeric, 3) AS mean_ms,
    round(percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms)::numeric, 3) AS p95_ms,
    round(max(duration_ms)::numeric, 3) AS max_ms,
    min(result_value) AS result_value
FROM benchmark_results
GROUP BY test_name, model
ORDER BY test_name, model;"

"${PSQL[@]}" -A -F $'\t' -P footer=off -c "
SET search_path TO \"$SCHEMA\", public;
SELECT
    test_name,
    model,
    round(min(duration_ms)::numeric, 3) AS min_ms,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY duration_ms)::numeric, 3) AS median_ms,
    round(avg(duration_ms)::numeric, 3) AS mean_ms,
    round(percentile_cont(0.95) WITHIN GROUP (ORDER BY duration_ms)::numeric, 3) AS p95_ms,
    round(max(duration_ms)::numeric, 3) AS max_ms,
    min(result_value) AS result_value
FROM benchmark_results
GROUP BY test_name, model
ORDER BY test_name, model;" > "$SUMMARY_TSV"

printf '\n== Storage summary ==\n'
"${PSQL[@]}" -P pager=off -c "
WITH relations(model, relation_name) AS (
    VALUES
        ('array_gin', '$SCHEMA.articles_array'),
        ('bridge', '$SCHEMA.articles_bridge'),
        ('bridge', '$SCHEMA.article_tags')
), sized AS (
    SELECT
        model,
        relation_name,
        pg_table_size(relation_name::regclass) AS table_bytes,
        pg_indexes_size(relation_name::regclass) AS index_bytes,
        pg_total_relation_size(relation_name::regclass) AS total_bytes
    FROM relations
)
SELECT
    model,
    relation_name,
    pg_size_pretty(table_bytes) AS table_size,
    pg_size_pretty(index_bytes) AS index_size,
    pg_size_pretty(total_bytes) AS total_size
FROM sized
UNION ALL
SELECT
    model,
    'MODEL TOTAL',
    pg_size_pretty(sum(table_bytes)),
    pg_size_pretty(sum(index_bytes)),
    pg_size_pretty(sum(total_bytes))
FROM sized
GROUP BY model
ORDER BY model, relation_name;"

"${PSQL[@]}" -A -F $'\t' -P footer=off -c "
WITH relations(model, relation_name) AS (
    VALUES
        ('array_gin', '$SCHEMA.articles_array'),
        ('bridge', '$SCHEMA.articles_bridge'),
        ('bridge', '$SCHEMA.article_tags')
)
SELECT
    model,
    relation_name,
    pg_table_size(relation_name::regclass) AS table_bytes,
    pg_indexes_size(relation_name::regclass) AS index_bytes,
    pg_total_relation_size(relation_name::regclass) AS total_bytes
FROM relations
ORDER BY model, relation_name;" > "$SIZES_TSV"

printf '\n== Capturing representative EXPLAIN (ANALYZE, BUFFERS, WAL) plans ==\n'
TARGET_TAG=1
TARGET_ARTICLE=$(( ARTICLES / 2 ))
(( TARGET_ARTICLE < 1 )) && TARGET_ARTICLE=1

"${PSQL[@]}" -P pager=off > "$EXPLAIN_TXT" <<SQL
SET search_path TO "$SCHEMA", public;
SET jit = off;

\echo '--- ARRAY + GIN: single tag ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT count(*)
FROM articles_array
WHERE tag_ids @> ARRAY[$TARGET_TAG]::integer[];

\echo '--- BRIDGE + BTREE: single tag ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT count(*)
FROM article_tags
WHERE tag_id = $TARGET_TAG;

\echo '--- ARRAY + GIN: single tag plus parent status ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT count(*)
FROM articles_array
WHERE status = 2
  AND tag_ids @> ARRAY[$TARGET_TAG]::integer[];

\echo '--- BRIDGE + BTREE: single tag plus parent status ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT count(*)
FROM article_tags AS at
JOIN articles_bridge AS a ON a.id = at.article_id
WHERE at.tag_id = $TARGET_TAG
  AND a.status = 2;

\echo '--- ARRAY: tags and relationship-like metadata for one article ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT t.id, t.name
FROM articles_array AS a
CROSS JOIN LATERAL unnest(a.tag_ids) AS x(tag_id)
JOIN tags AS t ON t.id = x.tag_id
WHERE a.id = $TARGET_ARTICLE;

\echo '--- BRIDGE: tags and edge metadata for one article ---'
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, SUMMARY)
SELECT t.id, t.name, at.assigned_at, at.source, at.is_primary
FROM article_tags AS at
JOIN tags AS t ON t.id = at.tag_id
WHERE at.article_id = $TARGET_ARTICLE;
SQL

cat >> "$METADATA_TXT" <<META
finished_at=$(date -u +%Y-%m-%dT%H:%M:%SZ)
postgres_version=$("${PSQL[@]}" -Atqc 'SHOW server_version')
server_version_num=$("${PSQL[@]}" -Atqc 'SHOW server_version_num')
shared_buffers=$("${PSQL[@]}" -Atqc 'SHOW shared_buffers')
effective_cache_size=$("${PSQL[@]}" -Atqc 'SHOW effective_cache_size')
work_mem=$("${PSQL[@]}" -Atqc 'SHOW work_mem')
random_page_cost=$("${PSQL[@]}" -Atqc 'SHOW random_page_cost')
META

printf '\nReports written to:\n'
printf '  %s\n  %s\n  %s\n  %s\n' "$SUMMARY_TSV" "$SIZES_TSV" "$EXPLAIN_TXT" "$METADATA_TXT"

if [[ "$KEEP_SCHEMA" == "0" ]]; then
  printf '\nDropping benchmark schema because KEEP_SCHEMA=0...\n'
  "${PSQL[@]}" -c "DROP SCHEMA IF EXISTS \"$SCHEMA\" CASCADE;"
else
  printf '\nSchema %s was retained for inspection. Set KEEP_SCHEMA=0 to remove it automatically.\n' "$SCHEMA"
fi

Make it executable:

chmod +x bridge_benchmark.sh

Run the default benchmark:

DATABASE_URL='postgresql://user:password@localhost/database' \
./bridge_benchmark.sh

The defaults create:

1,000,000 parent rows
10,000 tags
10 assignments per parent
10,000,000 bridge rows
15 measured runs per query and model

For a quick smoke test:

DATABASE_URL='postgresql://user:password@localhost/database' \
ARTICLES=10000 \
TAGS=1000 \
TAGS_PER_ARTICLE=10 \
RUNS=5 \
./bridge_benchmark.sh

For a larger run:

DATABASE_URL='postgresql://user:password@localhost/database' \
ARTICLES=5000000 \
TAGS=50000 \
TAGS_PER_ARTICLE=12 \
RUNS=25 \
./bridge_benchmark.sh

The script writes:

summary.tsv
sizes.tsv
explain.txt
metadata.txt

The benchmark schema is retained by default so the database can be inspected afterward. Set KEEP_SCHEMA=0 to remove it automatically.

Results

PK-ToDo

This section should be filled with the output from an actual benchmark run. Do not treat example numbers from another machine as universal PostgreSQL behavior.

Environment

PostgreSQL version:
CPU:
Memory:
Storage:
Operating system:
shared_buffers:
effective_cache_size:
work_mem:
random_page_cost:

Dataset

Articles:
Tags:
Assignments per article:
Total assignments:
Payload bytes per article:
Runs per query and model:

Timing Summary

Paste or convert summary.tsv here.

Test	Model	Minimum	Median	Mean	p95	Maximum	Result
Single tag	Array + GIN
Single tag	Bridge
Any three tags	Array + GIN
Any three tags	Bridge
All three tags	Array + GIN
All three tags	Bridge
Tag plus status	Array + GIN
Tag plus status	Bridge
Top tag frequencies	Array + GIN
Top tag frequencies	Bridge
Tags for one article	Array + GIN
Tags for one article	Bridge

Storage Summary

Paste or convert sizes.tsv here.

Model	Relation	Table bytes	Index bytes	Total bytes
Array + GIN	articles_array
Bridge	articles_bridge
Bridge	article_tags
Bridge	Model total

How to Read the Results

The interesting result is unlikely to be a simple declaration that one model is always faster.

Different queries reward different physical layouts.

A GIN index is designed to find rows containing indexed array elements. For direct containment tests, it may perform very well. The parent row already contains the complete list, so retrieving every tag for one article may also be pleasantly direct.

The bridge table gives PostgreSQL one ordinary row per assignment. That makes tag-frequency aggregation, per-assignment filtering, independent updates, joins through relationship attributes, and relational constraints natural.

The storage comparison also needs care. The bridge model contains a separate row for every connection, primary-key and foreign-key indexes, and deliberately included edge metadata. The array model stores identifiers inside the parent tuple and maintains a GIN index over their elements. Comparing total bytes is useful, but it does not tell us whether the two models provide equal semantics. They do not.

What This Benchmark Does Not Prove

This script does not establish a permanent performance ranking between arrays and bridge tables.

It does not test:

• concurrent writers;
• lock contention;
• high update rates;
• deleting one assignment from a large array;
• PostgreSQL version differences;
• cold-cache behavior under controlled operating-system cache eviction;
• skewed real-world tag distributions;
• partitioning;
• replication;
• distributed PostgreSQL;
• Aurora Limitless.

It also does not test JSONB. JSONB deserves a separate comparison because its operators, indexes, storage overhead, and likely use cases differ from a plain integer array.

The Modeling Decision Still Comes First

The benchmark can tell us how these two physical representations behave for selected operations.

It cannot decide whether the relationship deserves to be a row.

That remains the first question.

When the connection needs metadata, provenance, review state, ordering, permissions, history, or independent constraints, the bridge table is not merely a performance option. It is the clearer model.

When the values are truly just a small local set attached to one parent row, the array representation may be both simpler and sufficiently fast.

The benchmark does not replace the modeling decision.

It makes the cost of that decision measurable.