Supabase Postgres高效统计分类下未读订阅源数量方案咨询

背景与问题 #

我正在开发一款基于Supabase的订阅源阅读器应用，简化后的数据库表结构如下：

Table Categories
   id bigint (primary key)

Table Feeds
   id text (primary key),
   read_at timestamp with time zone null
   category_id bigint (foreign key btree index)

我需要统计各分类下的未读订阅源数量，最初通过创建视图实现，SQL代码如下：

create view
  categories_view as
select
  categories.id,
  (
    select
      count(*) as count
    from
      feeds
    where
      feeds.read_at is null
      and feeds.category_id = categories.id
  ) as unread_feeds
from
  categories;

但随着数据库数据量增长，该统计方法的性能越来越差。我的核心需求是：让客户端始终能获取实时准确的统计数据，无论是刷新订阅源时，还是标记单个、同分类多个或多分类多个订阅源为已读时。

我了解到PostgreSQL中使用pg_class是高效的全表计数方式，但它无法直接统计未读订阅源这类带条件的行数。

我想到了几种解决方案，希望得到高效实现的建议：

• 将Feeds表拆分为unreadFeeds和readFeeds两张表，这样就能用pg_class计数，但这种方式需要频繁迁移数据，且数据更新后计数能保证实时准确吗？
• 在Categories表中新增计数字段，每次新增订阅源或标记已读时手动更新，但这种方式逻辑较复杂，效率可能也更低？
• 是否存在我没想到的更优查询方案？

内容的提问来源于stack exchange，提问作者Benjamin Ledet