MTM on arrays in PostgreSQL

In systems analysis, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities[1] A and B in which A may contain a parent instance for which there are many children in B and vice versa.

For example, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors.

In a relational database management system, such relationships are usually implemented by means of an associative table (also known as cross-reference table), say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).

Perfomance

--with arrays
SELECT "users".*
FROM "users"
WHERE "users"."id" IN (
SELECT unnest(user_ids)
FROM "docs"
WHERE "docs"."id" = ID
)
--with mtm table
SELECT "users".*
FROM "users"
WHERE "users"."id" IN (
SELECT "granted_accesses"."user_id"
FROM "granted_accesses"
WHERE "granted_accesses"."doc_id" = ID
)
--mtm-table
SELECT * FROM cards
JOIN granted_accesses ON doc_id = docs.id
JOIN users ON users.id = user_id
WHERE docds.id = ID;
--arr
SELECT * FROM cards
JOIN users ON users.id = ANY( docs.user_ids )
WHERE docs.id = ID;
--mtm
SELECT * FROM docs
JOIN granted_accesses ON card_id = docs.id
WHERE doc_id IN (?) AND user_id IN(?)
--arr
SELECT * FROM docs
WHERE id IN (?) AND user_ids && ARRAY[?]::bigint[];
--mtm
SELECT * FROM docs
JOIN granted_accesses ON doc_id = cards.id
WHERE doc_id IN (?) AND user_id IN(?) AND indexed_rare_key = ?
--arr
SELECT * FROM docs
WHERE id IN (?) AND user_ids && ARRAY[?]::bigint[] AND indexed_rare_key = ?;
--Don't forget to use array functions, for example:
UPDATE cards
SET user_ids = array_remove(user_ids, ?::bigint)
WHERE ...
#or
user.docs.update_all('user_ids = array_remove(user_ids,?)', user.id)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store