An Array of IDs Is a Foreign Key You Did Not Get

A post on the Schemity blog, republished on dev.to, restates a Postgres constraint that most int[] versus join table arguments skip: the database cannot put a foreign key on array elements, so an array of IDs is a many-to-many relationship with nothing to enforce it ^[1]. That matters because the trade study teams actually run is query speed against disk size, and the thing being surrendered does not show up in either column ^[2].

The mechanism is unglamorous. Foreign keys compare directly comparable things, and an array is not comparable to a scalar primary key ^[3]. This is not a feature in flight: according to the post, a patch adding an ELEMENT REFERENCES column constraint, so that FOREIGN KEY (c1, ELEMENT c2) REFERENCES t1 (u1, u2) would validate every element, was proposed in 2011 and has never landed ^[4]. The standing advice on the mailing lists, the post says, is unchanged fifteen years later: write your own trigger on both tables, or use a details table ^[5].

The operational consequence is what to plan around. Delete a tag row and its ID stays in every array that mentioned it, pointing at nothing ^[6]. There is no ON DELETE behaviour to configure, because there is no foreign key to configure it on ^[7]. Your cleanup is application code or a trigger you wrote and now own.

The second cost is documentation. An ERD draws a relationship line because there is a foreign key to draw it from; no foreign key, no line ^[8]. Tooling then hides the column a second way. A dbdiagram.io user wrote on November 14 2021 that Postgres array types are marked as errors, calling them "critical to my schemas (and to my continued use of dbdiagram.io)" ^[9]. The official reply three days later was a workaround: quote the type as a string, name "text []", so the parser stops complaining ^[10]. On the export side, an issue titled 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 Postgres rejects ^[11].

None of this means arrays lose. Crunchy Data benchmarked a tagging schema and found a three-tag lookup ran in roughly 120ms on an integer array against roughly 950ms on the relational model, which it described as about seven times faster ^[12][13]. Those two figures divide out to about 7.9x ^[14]. Crunchy Data's own conclusion is the useful part: 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" ^[15].

So the test is what the elements are, not which shape is faster. The post's rule: array when the list is a value the row owns, junction table when the elements are references to rows that exist independently ^[16]. If you need a canonical list of the values and a guarantee that every stored value is in it, you have described a table with a foreign key pointing at it ^[17].

Note the source. The author builds Schemity, a desktop ERD tool, and the post says Schemity treats Postgres array types as first-class field types, generates a junction table with its foreign keys and composite primary key from one N:N gesture, and renders a later conversion as a migration SQL diff you read before it runs ^[18]. Those are vendor claims about the vendor's product, unverified here.