PostgreSQL 9.6 stored procedure performance improvement

Question

i have two tables users and products and the association between them is that User has many products. I want to store the count of products in the users table and it should be updated at every insert or delete. So i have written a stored procedure in the database for it and trigger to fire it. The problem is when i am inserting thousands of products at once it is executing the trigger per row insertion and it is taking too much time.

  CREATE FUNCTION update_product_count()
  RETURNS trigger AS $$
  BEGIN
    IF TG_OP = 'DELETE' THEN
      UPDATE users SET products_count = products_count - 1 WHERE id = OLD.user_id;
    END IF;

    IF TG_OP = 'INSERT' THEN
      UPDATE users SET products_count = products_count + 1 WHERE id = NEW.user_id;
    END IF;

    RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER update_user_products_count
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE PROCEDURE update_product_count();

UPDATE

1. i have added: SET CONSTRAINTS update_promotion_products_count DEFERRED; but seems like it is not making any progress because right now it is taking 6100ms which is somewhat similar to before.

2. Tried DEFERRABLE INITIALLY DEFERRED but it is still not working. I think FOR EACH ROW is the actual issue. But when i tried it with FOR EACH STATEMENT it throws statement invalid error.

3. Rewrote the above procedure like this:

   CREATE FUNCTION update_product_count()
    RETURNS trigger AS $$
     BEGIN
       IF TG_OP = 'DELETE' OR TG_OP = 'INSERT' THEN
         UPDATE users SET products_count = (SELECT COUNT(1) FROM products WHERE products.user_id = users.id);
       END IF;
   
       RETURN NULL;
     END;
     $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER update_user_products_count
   AFTER INSERT OR UPDATE OR DELETE ON products
   FOR EACH STATEMENT EXECUTE PROCEDURE update_product_count();
   

But the problem is then when you have 1000 usres with 10000 products each, you recalculate the count of every user (even when just insert a single product in the database)

I'm using PostgreSQL 9.6.

Answer 1

As a_horse_with_no_name noted in comments, Postgres 10 can do this much more efficiently using a FOR EACH STATEMENT trigger which updates all users records at once based on the statement's transition table.

In earlier versions, you can get some of the benefit by queueing the changes in a temp table, and applying them at the end of the statement with a single UPDATE.

Initialise the queue at the start of the statement:

CREATE FUNCTION create_queue_table() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  CREATE TEMP TABLE pending_changes(user_id INT UNIQUE, count INT) ON COMMIT DROP;
  RETURN NULL;
END
$$;

CREATE TRIGGER create_queue_table_if_not_exists
  BEFORE INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH STATEMENT
  WHEN (to_regclass('pending_changes') IS NULL)
  EXECUTE PROCEDURE create_queue_table();

Record the change for each row:

CREATE FUNCTION queue_change() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP IN ('DELETE', 'UPDATE') THEN
    INSERT INTO pending_changes (user_id, count) VALUES (old.user_id, -1)
    ON CONFLICT (user_id) DO UPDATE SET count = pending_changes.count - 1;
  END IF;

  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    INSERT INTO pending_changes (user_id, count) VALUES (new.user_id, 1)
    ON CONFLICT (user_id) DO UPDATE SET count = pending_changes.count + 1;
  END IF;
  RETURN NULL;
END
$$;

CREATE TRIGGER queue_change
  AFTER INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH ROW
  EXECUTE PROCEDURE queue_change();

Apply the changes at the end of the statement:

CREATE FUNCTION process_pending_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  UPDATE users
  SET products_count = products_count + pending_changes.count
  FROM pending_changes
  WHERE users.id = pending_changes.user_id;

  DROP TABLE pending_changes;
  RETURN NULL;
END
$$;

CREATE TRIGGER process_pending_changes
  AFTER INSERT OR UPDATE OF user_id OR DELETE
  ON products
  FOR EACH STATEMENT
  EXECUTE PROCEDURE process_pending_changes();

This may or may not be noticeably faster, depending on the details of your case, but it performed significantly better in an artificial test (184ms vs. 4073ms).

As I noted in a similar answer, this implementation has some potential deadlocks which you might want to address if you're running this concurrently.