使用触发器代替 count(*) 来计算相关实体的缺点是什么？

1. 使用以下方法有什么缺点？
2. 可以采取哪些措施来防止有人错误地更新计数？
3. 以下方法是反模式吗？如果是，有什么更好的方法存在？

假设：

应用程序是一个公共 wiki/论坛（如 stackexchange）

1. 该应用程序读取量很大。选择将是最终用户提出的 99% 的查询
2. 最终用户不执行批量插入。它们可能仅用于开发/维护工作
3. 需要同时写入表
4. 计数估计已经足够好了。确切的数量将是最重要的

方法

考虑两个实体，Students每个实体Courses都有对应的表。我们还有一个student_courses存储多对多映射 b/w 学生和课程的表。

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);

要找到给定学生的课程数量，我们可以执行

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

如果这些计数查询很频繁（假设您希望始终显示学生姓名和课程计数），优化的一种方法是在两个实体中维护一个计数变量，然后放置插入和删除触发器。

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();

一旦到位，查找学生的课程数量很简单

select course_count
from students where student_id = 123;