PostgreSQL Performance – TEMP Table Alternatives for PL/pgSQL

performancepostgresqlpostgresql-performancetemporary-tablesupsert

I am trying to emulate MERGE behavior with pl/pgsql:

-- Generate the data from funtion
CREATE TEMP TABLE temp_x  (id int, id2 int, data text, created_at timestamp, updated_at timestamp)  ON COMMIT DROP;
INSERT INTO temp_x SELECT * FROM set_gernating_function(p);

-- DELETE record with same id2
DELETE FROM x WHERE NOT EXISTS (SELECT 1 FROM temp_x WHERE temp_x.id=x.id) AND id2=p.id2;

-- UPSERT by (id, id2)
INSERT INTO x
    SELECT * FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET
      updated_at=excluded.updated_at,
      data=excluded.data;

But the temp table usage is generating bloat in pg_class and pg_attr table very fast — Faster then I could do vacuum — and affect other queries. Any idea?

There are some restrictions:

  1. Preserve the created_at time if the id already exist
  2. x have a delete-trigger, so no unnecessary delete.
  3. The set_generating_function is slow
  4. The size for temp_x is small. (< 50)
  5. The run rate is very high (thousands per second)

Best Answer

You don't need the temp table:

You can do this in a single statement:

with temp_x (id, id2, data, created_at, updated_at) AS (
   SELECT * 
   FROM set_gernating_function(p)
), deleted as (
  DELETE FROM x 
  WHERE NOT EXISTS (SELECT 1 
                    FROM temp_x 
                    WHERE temp_x.id = x.id) 
    AND id2=p.id2
)
-- UPSERT by (id, id2)
INSERT INTO x
SELECT * 
FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET updated_at = excluded.updated_at,
        data = excluded.data;
Related Question