Counter cache column in PostgreSQL

Question

In my database I have tasks and comments tables. Each task has many comments.

I'd like to create tasks.comments_count column that would be updated automatically by PostgreSQL, so I can get comments_count (and sort / filter by it) in O(1) time while selecting all tasks.

I know there are language-specific solutions like counter cache of ActiveRecord, but I don't want to use them (I find them fragile). I'd like PostgreSQL to take care of such counter caches.

I also know PostgreSQL supports triggers, but they are hard to write and use (not a solid solution)

Ideally it would be a PostgreSQL extension or some native feature I'm not aware of.

Lazy calculation of such counters would be a great bonus.

Answer 1

If you want Postgres to automatically do something on the basis of an insert/update/delete - i.e. if you want this operation to trigger some other action - then you need to write a trigger.

It's pretty straightforward. Simple enough that I doubt anyone would bother creating an extension (let alone a language feature) to save you the trouble. And it's certainly simpler (and as you pointed out, safer) than whatever ActiveRecord has going on under the hood.

Something like this is generally all it takes (I haven't tested this, so you might want to do so...):

CREATE FUNCTION maintain_comment_count_trg() RETURNS TRIGGER AS
$$
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    UPDATE tasks SET comment_count = comment_count - 1 WHERE id = old.task_id;
  END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    UPDATE tasks SET comment_count = comment_count + 1 WHERE id = new.task_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER maintain_comment_count
AFTER INSERT OR UPDATE OF task_id OR DELETE ON comments
FOR EACH ROW
EXECUTE PROCEDURE maintain_comment_count_trg();

If you want it to be airtight, you'd need an additional trigger for TRUNCATEs on comments; whether it's worth the trouble is up to you.

To handle updates to a tasks.id value which is being referenced (either via deferred constraints or ON UPDATE actions) then there's a bit more to it, but this is an uncommon case.

And if your client library / ORM is naive enough to send through every field in every UPDATE statement, you may want a separate UPDATE trigger which fires only when the value has actually changed:

CREATE TRIGGER maintain_comment_count_update
AFTER UPDATE OF task_id ON comments
FOR EACH ROW
WHEN (old.task_id IS DISTINCT FROM new.task_id)
EXECUTE PROCEDURE maintain_comment_count_trg();