Efficiently create many-to-many records with resolving IDs

Question

I have the following PostgreSQL tables structure:

CREATE TABLE parent
(
    pk serial
        CONSTRAINT parent_pk PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE UNIQUE INDEX parent_name_uindex
    ON PARENT (name);

CREATE UNIQUE INDEX parent_pk_uindex
    ON parent (pk);

CREATE TABLE child
(
    pk serial
        CONSTRAINT child_pk PRIMARY KEY,
    name VARCHAR(512) NOT NULL,
);

CREATE UNIQUE INDEX child_pk_uindex ON child (pk);
CREATE UNIQUE INDEX child_name_uindex ON child (name);

CREATE TABLE parent_child (
  child_id    serial REFERENCES child (pk),
  parent_id serial REFERENCES parent (pk),
  CONSTRAINT parent_child_pkey PRIMARY KEY (child_id, parent_id)
);

In my code, I get a hashmap with all parents as a key and its children as a value. One child may have multiple parents. I want to create the associations in parent_child table, but I only have the parent and children names.

I'd like to efficiently put them into the database. The most obvious way to achieve it would be to fetch the ID's of every parent and child and perform a query that would create parent_child instance by these IDs. But I think if it wouldn't be better to use these ID's and some kind of nested query to resolve the ID from the name. I think it should use less memory from the application, and the database should be pretty efficient by it, but I'm not sure if for large datasets where one child is used very often it wouldn't make redundant operations.

Let's assume the following structure:

parent1:
   child1
   child2
parent2:
   child1
   child3
parent3:
   child1

I was thinking about making the following query:

INSERT INTO parent_child (parent_id, child_id)
VALUES ((SELECT pk FROM parent where name = 'parent1'), (SELECT pk FROM child where name = 'child1')),
       ((SELECT pk FROM parent where name = 'parent1'), (SELECT pk FROM child where name = 'child2')),
       ((SELECT pk FROM parent where name = 'parent2'), (SELECT pk FROM child where name = 'child1')),
       ((SELECT pk FROM parent where name = 'parent2'), (SELECT pk FROM child where name = 'child3')),
       ((SELECT pk FROM parent where name = 'parent3'), (SELECT pk FROM child where name = 'child1'));

The query SELECT pk FROM child where name = 'child1' is repeated here 3 times. Would the database use a cached result or execute the query every time? In reality, there will be thousands of records and thousands of such repeated queries.

The parent or child may not exist at the time of making the association. Would it be possible to create one if It doesn't already exist? Would that be efficient?

What would be the most efficient approach to do that?

Answer 1

If you pass in the whole set as JSON (or JSONB for efficiency), you can unpivot the whole thing and do a joined insert.

For example, if you passed in JSON of the form

[
  {
    "name":"parent1",
    "children":[
      "child1",
      "child2"
    ]
  },
  {
    "name":"parent2",
    "children":[
      "child1",
      "child3"
    ]
  },
  {
    "name":"parent3",
    "children":[
      "child1"
    ]
  }
]

You can do something like this

INSERT INTO parent_child (parent_id, child_id)
SELECT p.pk, c.pk
FROM jsonb_to_recordset(@json) AS jp(name text, children jsonb)
JOIN parent p ON p.name = jp.name
CROSS JOIN jsonb_array_elements(jp.children) jc(name)
JOIN child c ON c.name = jc.name;