Use a PostgreSQL array column when the list is a value the row owns, and a junction table when the elements are references to rows that exist independently. The reason is not normalization theory. It is that PostgreSQL cannot put a foreign key on array elements, so an array of IDs is a many-to-many relationship with nothing to enforce it and nothing for a diagram to draw.

That second half is the part nobody mentions. Every discussion of int[] versus a join table is argued on query speed and disk size, both of which favour the array more often than the purists admit. Then a year later somebody opens the ERD to understand how the system fits together, and an entire relationship is simply not in the picture.

Why an array of IDs disappears from your ERD

An ERD draws a relationship line because there is a foreign key to draw it from. No foreign key, no line. And PostgreSQL will not give you one on an array column: foreign keys compare directly comparable things, and an array is not comparable to a scalar primary key. This is not an oversight waiting on a release. A patch adding array element foreign keys - an ELEMENT REFERENCES column constraint, so FOREIGN KEY (c1, ELEMENT c2) REFERENCES t1 (u1, u2) would validate every element - was proposed in 2011 and has never landed. The standing advice on the mailing lists is unchanged after fifteen years: write your own trigger on both tables, or use a details table.

So article.tag_ids int[] records a many-to-many relationship between articles and tags with no constraint behind it. Delete a tag row and the IDs stay in every array that mentioned it, pointing at nothing. There is no ON DELETE behaviour to configure, because there is no foreign key to configure it on - which puts array columns in the same blind spot as referential actions that never make it into the diagram, except worse, because here the reference itself does not exist as far as the engine is concerned.

Then the diagram tool makes it invisible a second way, by refusing to render the type at all. A dbdiagram.io user wrote on November 14 2021 that PostgreSQL array types are marked as errors, calling them “critical to my schemas (and to my continued use of dbdiagram.io)”. The official reply three days later was a workaround: quote the type as a string, name "text []", so the parser stops complaining. The export side has the same trouble - Postgres export fails on array has been open in the DBML repository since 2019, because a column declared as variants array exports as "variants" array, which PostgreSQL rejects. A column type that has to be smuggled past the parser as a quoted string is not a modelled type.

The net effect is a diagram that is wrong twice about the same column. The relationship is missing, and the field lies about what it holds.

Should I use a Postgres array column or a junction table?

Decide by asking what the elements are, not by asking which one is faster:

The speed column is where arrays earn their reputation, and it deserves an honest number rather than a hand-wave. Crunchy Data benchmarked exactly this on a tagging schema and found the three-tag lookup ran in roughly 120ms on an integer array against roughly 950ms on the relational model - “about seven times faster than the same query on the relational model”. Their conclusion is not that arrays win, though. It is that the array models are “faster to query, smaller to store, and simpler to query” while giving up two specific things: “there’s no general place to lookup all tags” and “there’s no way to create a simple constraint that guarantees integers exist in the tags table”.

Those two sentences are the whole decision. If you need a place to look up all the values, and a guarantee that every stored value is one of them, you have described a table with a foreign key pointing at it.

One row of that table deserves a footnote: “blocked by the composite primary key” is true of the junction table you get from an N:N gesture, and not automatically true of one that arrived by reverse-engineering a database somebody else designed. A link table with two foreign keys and no key over the pair accepts duplicates exactly as freely as the array does - and if its key is there but includes a nullable column, it covers only the rows where every key column has a value, which may be the design or may be an accident.

The test: is the element a value or a reference?

The reliable way to tell the two cases apart is to ask whether an element can exist on its own, before and after any row that mentions it.

A free-form tag typed into a text field is a value. It has no row anywhere, no ID, no attributes, no lifecycle. tags text[] on the article is an accurate model of that, and normalizing it into tags plus articles_tags buys you nothing but two joins and a table that is a list of strings with surrogate keys stapled on.

A tag that appears in an autocomplete, has a slug, has a colour, has a count, or can be renamed everywhere at once is a reference. It is a row. The moment it is a row, tag_ids int[] is storing a foreign key in a place a foreign key cannot go, and every fact about the relationship - that it exists, which direction the dependency runs, what happens on delete - drops out of the schema and lives only in whichever service happens to write that array.

The same test settles the cases that look ambiguous. A permissions text[] column on a role is a reference in disguise, because permissions are generated from the routing table and must be looked up as a set - which is why permissions in a multi-tenant RBAC schema get their own table and junction rather than an array of strings on the role. A preferred_contact_methods text[] on a user is a value, because the elements are vocabulary, not rows. And a list of file paths, a set of coordinates, a fixed-length matrix of scores: values, all of them, because you use the array as a whole.

There is a third option that people reach for arrays instead of, and it is worth naming: when the list is short, closed, and drawn from a fixed vocabulary, the question is really about a value set, and the choice between a Postgres enum, a check constraint and a lookup table is the one you are actually making. An array of strings with no constraint on the elements is the least legible member of that family, because nothing anywhere records what an element is allowed to be.

How Schemity renders array columns and junction tables

Schemity supports PostgreSQL array types as ordinary field types, so a text[] column is a text[] column on the canvas rather than a parser error or a quoted string workaround. When you reverse engineer a PostgreSQL or Supabase schema, the array columns come across as what they are. That matters most for the honest case: when the list genuinely is a value, you want the diagram to say so plainly, not to force you into a junction table just so the tool has something to draw.

The array is a toggle on the type rather than a type of its own: pick TEXT, tick [ ], and the field reads TEXT[] on the entity with its {} default intact, alongside the PK, Unique and Nullable flags any other column gets. The screenshot has both models in one frame. allowed_domains text[] on sso_configs is a value the row owns - a list of domain strings that exist nowhere else and are read as a whole - while member_roles and roles_pems are drawn as tables, because members, roles and permissions are rows with their own lifecycle. Nothing about the picture forces the first case into the shape of the second.

For the reference case, the junction table is generated rather than assembled. Drag between the two entities, pick N:N in the relationship dialog, and the junction table is created with one foreign key per parent, a composite primary key over the two so a pairing cannot repeat, and a name that follows the naming convention set on the connection. It is a real table you can rename and extend, not a notation shortcut - which is the point when the pairing turns out to need a column of its own, an added_at or an added_by, the kind of attribute an array element can never carry. The mechanics of that, and the rule for when a junction wants a composite primary key versus its own id, are covered in why many-to-many shouldn’t mean hand-building the junction table.

The practical difference on the canvas is that one of these two models is legible from across the room and the other is not:

Converting an array column to a junction table later

Most of these decisions get revisited, usually when the list grows an attribute or somebody finds orphaned IDs. The conversion is mechanical, and it is worth planning as three deploys rather than one.

Create the junction table with a foreign key to each parent. Backfill it by expanding the array with unnest, inserting one row per element. Then count the elements that failed to match a parent row, because that number is the thing the array was hiding - it is the size of the integrity gap you have been carrying, and it is almost never zero. Only after the application reads from the junction do you drop the array column, since that is the single irreversible step.

Schemity handles the schema half of that the way it handles any structural change: you edit the ERD, and it generates the migration SQL diff for you to read before anything runs against the database. Dropping a column that is still the only copy of a relationship is exactly the statement that deserves a human reading it first. Because the diagram is a plain JSON file in the workspace folder, the change is also a line in a pull request, so a reviewer sees a relationship appear in the model at the same time the migration appears in the diff - the same loop as not hand-translating between SQL and your ERD.

Store the list where the model can show it

An ERD is a single source of truth when every structural fact the database enforces is readable from the diagram and nowhere else is needed to understand it. An array of IDs breaks that quietly, because the fact it encodes - these two things are related, many to many - is enforced by nothing and drawn by nothing. The knowledge moves into application code, and from there into whoever happens to remember.

None of which makes array columns a mistake. They are a good fit for a list of values, they are genuinely faster on the query shapes they suit, and a tool that cannot even render text[] is failing you rather than protecting you. The mistake is only ever the substitution: using an array to store references because the junction table felt like too much ceremony to build. It is also why an array of foreign keys is one of the things a schema linter reports on the diagram: nothing checks that the referenced rows exist, so the finding states that consequence rather than calling the column wrong. When the junction table costs one gesture, that trade stops being tempting, and the schema goes back to carrying its own domain model instead of outsourcing half of it to the code that writes the arrays.