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.

Matadi

Which Postgres version are you using? With Postgres 10 you could do this with a statement level trigger – Wharfinger

The version is 9.6. Updated in the question. – Matadi

@a_horse_with_no_name any ideas ? – Matadi

@Ahmad: You could write a row-level trigger which queues up changes in a temp table, and a statement-level trigger which applies the changes at the end: stackoverflow.com/a/47909709 – Nosey

@NickBarnes I'm sorry but actually i dont exactly understand it. It would be great if you can write in question's context in the answer so that i can better talk to you about it and mark it as an accepted answer. – Matadi

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.

Nosey

Answer 2

In your case count will not update when user_id of the product gets change, So, i'll recommend counter_cache of rails

class Product < ActiveRecord::Base
  belongs_to :user, counter_cache: true
end

Also have a look at this gem

Note :- This will not solve your per row insertion problem though

You have to write custom counter then, something like following

class Product < ApplicationRecord
  has_many :products
  attr_accessor :update_count

  belongs_to :user#, counter_cache: true

  after_save do
    update_counter_cache
  end

  after_destroy do
    update_counter_cache
  end

  def update_counter_cache
    return unless update_count
    user.products_count = user.products.count
    user.save
  end
end

in rails console

10.times{|n| Product.new(name: "Latest New Product #{n}", update_count: n == 9, user_id: user.id).save}

Inactive

per row insertion is my problem because i'm inserting 20k records at once. – Matadi