What are the Cons of using triggers in place of count(*) for counting related entities?

Question

1. What are the cons of using the below approach?
2. What steps can be taken to prevent someone from incorrectly updating the count?
3. Is the below approach an anti-pattern? If yes, what better approaches exist?

---

Assumptions:

Application is a public wiki/forum (like stackexchange)

1. The application is read-heavy. Selects will be 99% of the queries made by end-users
2. bulk inserts are not performed by end-users. They might only be needed for development/maintenance work
3. Concurrent writes to the table are necessary
4. Count estimates are good enough. Exact count will be cherry on top

---

Approach

Consider two entities, Students and Courses each having their corresponding tables. We also have a table student_courses that stores the Many-to-Many mapping b/w Students and Courses.

create table students (
  id bigserial primary key,
  name text
);
create table courses (
  id bigserial primary key,
  content text
);
create table student_courses (
  student_id bigint not null references students,
  course_id bigint not null references courses,
  primary key (student_id, course_id)
);
create index on student_courses (course_id);

To find the number of courses for a given student, we can perform

select count(1)
from student_courses
where student_id = 123;

If these count queries are frequent (say you wish to always display student name with course count), one way to optimize would be to maintain a count variable inside both entities, and then place triggers for insert and delete.

alter table students add column course_count bigint default 0;
alter table courses add column student_count bigint default 0;
create function increment_student_course_count() returns trigger as
$$begin
  update students
    set course_count := course_count + 1
    where student_id = new.student_id;
  update courses
    set student_count := student_count + 1
    where course_id = new.course_id;
  return new;
end;$$
create trigger after_insert_update_counts
after insert
on student_courses
for each row execute procedure increment_student_course_count();
create function decrement_student_course_count() returns trigger as
$$begin
  update students
    set course_count := course_count - 1
    where student_id = new.student_id;
  update courses
    set student_count := student_count - 1
    where course_id = new.course_id;
  return new;
end;$$
create trigger after_delete_update_counts
after delete
on student_courses
for each row execute procedure decrement_student_course_count();

Once this is in place, finding the course count for a student is simply

select course_count
from students where student_id = 123;

Answer 1

Concurrent writes to the table are necessary

In addition to what Laurenz already brought up, this increases the likelihood of deadlocks if transactions contain operations on more than a single row. Since your trigger locks a row in the students table and another one in the courses table, you better make sure that all write access that can possibly intertwine strictly follows some universal sort order. Seems especially problematic for the courses table, where many students are likely to register around the same time in separate transactions. If these transactions do more than writing a single row in student_courses, the problem may arise.

I don't see a danger of data errors due to concurrency, as each transaction has to wait for competing transactions to finish before it can update a contested row - which highlights the potential cost penalty for write operations. Keep your transactions with write activity tight.

Another issue is that the repeated writes bloat your main tables students and courses. Every UPDATE writes a new row version. The wider those rows, the bigger the impact. Especially in the face of concurrent write access, visibility may be impacted (see visibility map). autovacuum may be hard put to it to keep up. Otherwise possible index-only scans may be disabled. Tables may start to bloat. The whole effort aiming at faster reads may end up slowing reads!

Depending on the frequency of writes, consider "vertical partitioning" for the counts. This may also be relevant if many queries only fetch the count to begin with and don't need the rest of the (wide?) main row. Basically a MATERIALIZED VIEW focusing on just the counts, but managed manually with triggers instead of a fully fledged MATERIALIZED VIEW. The point is to write to a separate table with tiny rows to counter lock contention, visibility issues and bloat. Yet more added overhead, of course. See:

• Many columns vs few tables - performance wise

With the PK and the additional index on (course_id) for student_courses, you already have the perfect setup for index or index-only scans for both counts. This query will be extremely fast:

SELECT count(*) FROM student_courses WHERE student_id = 123;

Fractions of a millisecond. Combined with caching on the application level, that may be all you need. All of the above really only comes into play if writes are extremely rare as compared to reads (the mentioned 99% may not warrant the effort, yet), and getting the counts quickly is of paramount importance.

In Postgres, count(*) is implemented separately from count(<value>). The former does not even begin to look at the expression or row values to be as fast as possible. (count(1) is only slightly more expensive, as the constant also does not involve row values, but still.)
Don't be fooled by the * symbol which in the context of SELECT * imposes maximum cost, fetching all columns. The effect in this context is really the opposite.

Answer 2

The disadvantages are:

• reduced DML performance

  That is the price you are paying: faster SELECT against slower data modifications. If you are running statements that modify many rows at once, you may be more efficient with statement level triggers that use transition tables.

• danger of inconsistencies through redundancy

  There is little protection you can have against manual updates of the count. The best you can do is to use a different user as table owner than you use for data modifications. Then you can use column level permissions to keep the latter user from modifying the column. The trigger function would have to be SECURITY DEFINER.

If the importance of fast queries is paramount, I think your approach is acceptable.