A many-to-many relationship in SQL is a third table, the junction table, with one foreign key to each side and a key on the pair. Make the pair itself the primary key. Add a surrogate id only when another table needs to point at a single link row. Then index the pair in the other direction too, because the primary key only serves lookups that start from its first column, and PostgreSQL won't add the second index for you.
That is what we found across the 100 schemas in the drawDB gallery. They contain 169 tables keyed by a pair of foreign keys. 107 use a composite primary key and 62 use a surrogate id plus a unique constraint on the pair. The 22 link rows that other tables reference all have a surrogate id. And 40 of our 93 composite-keyed PostgreSQL junctions have no index for the reverse direction, which is the mistake this post spends the most time on.
What does a junction table look like?
Issues and labels are the textbook case. An issue has many labels, a label is on many issues, and the link itself carries one fact: when it was applied. This is what drawDB's PostgreSQL exporter generates for the GitHub schema:
CREATE TABLE "issues" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"title" VARCHAR(256) NOT NULL
);
CREATE TABLE "labels" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"name" VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS "issue_labels" (
"issue_id" BIGINT,
"label_id" BIGINT,
"applied_at" TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY("issue_id", "label_id")
);
ALTER TABLE "issue_labels"
ADD FOREIGN KEY("issue_id") REFERENCES "issues"("id")
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "issue_labels"
ADD FOREIGN KEY("label_id") REFERENCES "labels"("id")
ON UPDATE CASCADE ON DELETE CASCADE;The two parent tables are trimmed to what the example needs. The columns don't need an explicit NOT NULL, because a primary key implies it. Showing an issue's labels is a join through the junction table:
SELECT l.name
FROM issue_labels il
JOIN labels l ON l.id = il.label_id
WHERE il.issue_id = 4211;
That schema has one gap, which we left in to make a point: nothing indexes label_id on its own. More on that below.
Should a junction table use a composite primary key or a surrogate id?
Composite, unless something references the link. Two facts make the case.
The pair is already the identity. A surrogate id doesn't remove the need for a unique constraint on (issue_id, label_id), because without it the same label can be applied twice. So a surrogate key means two unique indexes instead of one. We loaded one million links into both designs on PostgreSQL 16:
| Design | Table | Indexes | Total |
|---|---|---|---|
PRIMARY KEY (issue_id, label_id) | 50 MB | 30 MB | 80 MB |
id primary key + UNIQUE (issue_id, label_id) | 57 MB | 52 MB | 109 MB |
That is 36% more disk and one more index to update on every insert, for a column nobody queries.
On clustered engines the cost is higher. MySQL's InnoDB, and SQL Server by default, store rows in primary key order. With a composite key, all of an issue's labels sit together in the clustered index and a lookup by issue_id reads them straight from it. With a surrogate key, the same lookup goes to the secondary unique index first and then back to the clustered index for each row. Lukas Eder measured that on MySQL: the surrogate design took about 60% longer on InnoDB, and there was almost no difference on MyISAM, which doesn't cluster.
A surrogate id earns its place when the link stops being a link. In the gallery, 22 junction tables are referenced by foreign keys from other tables, and every one of them has a surrogate id. The Coursera-style enrollments table is the clearest case. It joins a learner to a course session, and four other tables hang off the enrollment itself: item progress, assessment attempts, peer reviews and certificates. A composite key would force each of those to carry both user_id and session_id, so a single enrollment_id is the better reference.
The other 40 surrogate-keyed junctions in the gallery have no inbound references. Some anticipate one, and some came from habit. Habit is also the default in some frameworks: Django's ManyToManyField creates its hidden join table with an id column and a unique constraint on the pair. That works, and on PostgreSQL the size table above is what it costs. If you control the DDL, start composite. Adding a surrogate id later, when something needs to reference the link, is a single-table migration.
Does a junction table need an index on both columns?
Yes, and this is the rule we broke most often. A composite primary key on (issue_id, label_id) is a B-tree sorted by issue_id first. It answers "labels on this issue" directly and "issues with this label" not at all. PostgreSQL does not index the referencing side of a foreign key automatically, so the second direction has no index until you create one.
That missing index slows down two things, and the second is easy to overlook:
- Filtering by the second column. Listing issues tagged
bugscans the whole junction table. - Deleting from the second parent.
ON DELETE CASCADEonlabel_idmeans deleting a label must find its links, and without an index that is a sequential scan, holding locks while it runs.
On the million-row table, with 500 labels and about 2,000 issues per label:
| Operation | No reverse index | With (label_id, issue_id) |
|---|---|---|
SELECT issue_id … WHERE label_id = 42 | 40 ms, parallel seq scan, 6,370 buffers | 2.4 ms, index-only scan, 11 buffers |
DELETE FROM labels WHERE id = 7 (cascades 2,000 rows) | 31–42 ms | about 2 ms |
Those are milliseconds on a laptop with a warm cache. Both numbers grow with the table, and the cascade case grows with every label you delete in one statement. The fix is one line:
CREATE INDEX "idx_issue_labels_label_issue" ON "issue_labels" ("label_id", "issue_id");Index both columns, not just label_id. With both, PostgreSQL can return issue_id from the index without visiting the table, which is the index-only scan above. A single-column index on label_id was smaller in our test, 7 MB against 30 MB thanks to B-tree deduplication, but every match then costs a heap fetch. On a junction table the other id is almost always what the query wants.
In the gallery, 68 PostgreSQL junction tables have no index starting with the second column, and 52 of those cascade deletes from that side. Some are harmless: nobody lists "stories I viewed" in the Instagram schema. Others are plain misses. The GitHub schema can't filter issues by label without a scan. The Airbnb schema can't search listings by amenity. The Slack schema can't list a user's workspaces from workspace_members without one. It's one of the patterns behind the 773 unindexed foreign keys in our schema design audit.
MySQL is the exception. InnoDB creates an index for every foreign key that lacks one, so a MySQL junction gets the reverse index whether you asked for it or not. SQLite and SQL Server behave like PostgreSQL, and three of our SQLite junctions have the same gap.
What should ON DELETE do on a junction table?
Usually CASCADE on both sides. A link between an issue and a label has no meaning once either is gone. 139 of the gallery's 169 junctions cascade both ways.
The exceptions are links that have become records. 29 junctions restrict deletes on at least one side. Coursera-style enrollments restricts deleting a learner or a session, because grades and certificates hang off the enrollment. A banking schema's account_holders restricts both sides, because deleting the person who owns an account shouldn't quietly erase who owned it. If a link would show up in a dispute, an audit or an invoice, it shouldn't vanish because a parent row was cleaned up.
When should a junction table allow duplicates?
When the same pair can legitimately appear twice. A playlist can hold the same video twice, so the YouTube schema's playlist_items has a surrogate id and no unique constraint on (playlist_id, video_id). What it makes unique instead is the position:
CREATE TABLE "playlist_items" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
"playlist_id" BIGINT NOT NULL,
"video_id" BIGINT NOT NULL,
"position" INTEGER NOT NULL,
"added_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE ("playlist_id", "position")
);
CREATE INDEX "idx_playlist_items_video" ON "playlist_items" ("video_id");
Intervals are the other case. Zoom's breakout_room_members and Twitch's stream_category_segments link the same two rows again each time someone re-enters a room or a streamer switches back to a game, with entered_at/exited_at or started_at/ended_at on each row. There the identity is the pair plus a time, and a surrogate id is simpler than a three-column key. Outside ordered lists and intervals, a junction without a unique pair is a duplicate-row bug waiting to happen.
How do you model a self-referencing many-to-many relationship?
With a junction table whose two foreign keys point at the same table. 19 of our 169 junctions do this, and they come in two kinds.
Directed relationships are two independent facts. "Ana follows Ben" says nothing about Ben following Ana, so the Instagram, TikTok, Pinterest and X schemas all store a follows table keyed on (follower_id, followee_id). Both directions need an index, because "who follows me" and "whose posts do I read" are asked equally often. Instagram's schema indexes (followee_id, status) and (follower_id, status) for exactly that.
Symmetric relationships are one fact. A friendship either exists or it doesn't, so it should be one row, not two that can disagree. Store the pair in sorted order and make the database enforce the order:
CREATE TABLE "users" (
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);
CREATE TABLE "friendships" (
"user_low_id" BIGINT NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE,
"user_high_id" BIGINT NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE,
"status" VARCHAR(12) NOT NULL DEFAULT 'pending',
"requested_by_id" BIGINT NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE,
PRIMARY KEY ("user_low_id", "user_high_id"),
CHECK ("user_low_id" < "user_high_id")
);
CREATE INDEX "idx_friendships_high" ON "friendships" ("user_high_id", "user_low_id");
INSERT INTO "users" DEFAULT VALUES;
INSERT INTO "users" DEFAULT VALUES;
INSERT INTO "friendships" ("user_low_id", "user_high_id", "requested_by_id")
VALUES (LEAST(2, 1), GREATEST(2, 1), 2);LEAST and GREATEST put the ids in order however the request arrives, and the CHECK rejects any code path that forgets to. requested_by_id keeps the direction that sorting threw away, which the UI needs to show who is waiting on whom. To list someone's friends, query both columns: WHERE user_low_id = :me OR user_high_id = :me.
Our own Facebook schema uses this sorted pair, and its column note describes the CHECK. But the schema never declares the constraint, so the DDL you export from it doesn't have one. A rule that lives only in a comment is enforced by nobody, so add the CHECK yourself.
When does a junction table become its own entity?
Usually sooner than you planned. Only 35 of the gallery's 169 junctions hold nothing beyond the two keys and a timestamp. The other 134 carry attributes of the relationship itself. The most common are status (26 tables), as in a pending follow request, and role (17), as in an admin's membership in a workspace. After those come source, position, rating, price and quantity.
None of that changes the design. The link still gets its pair key and its reverse index, and the attributes are just more columns. What changes is the name. Call it memberships, enrollments or follows rather than users_workspaces, because once the link has a status and a role, the code talks about it as a thing. Name it that way from the start, and the day another table needs to reference it, adding the surrogate id is the only change.
To start from a working design, open GitHub in the editor, add the missing (label_id, issue_id) index to issue_labels, and export the DDL for your database.
Frequently asked questions
What do you call the table in the middle of a many-to-many relationship?
Junction table, join table, bridge table, link table, associative table and associative entity all mean the same thing: a table whose rows each hold one foreign key to each side. The name matters less than the table name you pick for it. A domain word like enrollments or memberships ages better than a mechanical one like users_courses, because the link usually grows its own columns.
Which column should come first in a composite primary key?
The side you query from most often, because the primary key index can only serve lookups on its leading column. For issue labels that is issue_id: showing an issue's labels happens on every page view, while listing issues by label is a filter. Then add a second index that starts with the other column, so both directions have one.
How do you insert a link only if it doesn't already exist?
Let the unique key do it. In PostgreSQL and SQLite use INSERT ... ON CONFLICT DO NOTHING, and in MySQL and MariaDB use INSERT IGNORE or ON DUPLICATE KEY UPDATE. Checking with a SELECT first races with a concurrent request; the constraint does not.
How do you find rows linked to all of a set of values, such as posts with every one of three tags?
Filter the junction table to those tag ids, group by the post id, and keep groups whose count of distinct tags equals the number of tags you asked for. With an index that starts with tag_id, the database reads only the rows for those tags rather than the whole table.
Can a junction table connect three tables?
Yes, with three foreign keys and a key across all three, for example a user's role within a project within an organization. Check first that the third column isn't really an attribute of the link: if each user has one role per project, role is a column on project_members, not a third side of the relationship.