Optimized count for large table using triggers, views, or external cache

Question

I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (according to pg_stat_statements) and I wish to optimize it.

Would creating a trigger that executes on insert/delete of the table and adjusts a count for each user be a conventional way of solving this issue? Or should I consider view or some simple external caches like Redis?

Of note: the table has very high write and read rates.

Answer 1

If you are considering using Redis, you could try the following approach. You can create a sorted set for every user containing the IDs of the related entities you are fetching. For example, ZADD user:1 1 1 2 2 3 3 ZADD user:2 4 4 5 5 and so on, where numbers like 1 1 are the score and the element in the respective sorted set which represent the IDs of the entities. When the data of a user is added or deleted in the DB, you sync it in Redis on the event. For example, when the entity with ID 1 is deleted from the data of user 1, you delete it in Redis ZREM user:1 1, and another is added for user 2 - ZADD user:2 123 123. The benefit of using Redis is that you can fetch the number of the related entities in constant time as follows ZCARD user:1. To fetch a paginated result set, you use ZRANGE user:1 0 99 for the first page and ZRANGE user:1 100 199 for the second one, where 0 99 and 100 199 are the indexes (rank) of the records (like in array), and you fetch the entities themselves from the database using the received IDs from Redis. In this case, updating the count manually is not required and is done in the Redis data structure automatically.