I'd most likely have two tables - one for user activity and one for each pixel (1 million rows only in that table). Selecting a million rows from that pixel table might be 200ms or whatever. I'd still have Redis cache, though, since you're getting 100ms.

Consider exactly what you are proposing. One table to store the entire history (one billion or more rows). A second denormalized table, whether updated at the application layer or via triggers, to store the most recent update to each of the one million cells (1000x1000 pixel grid = one million data points).

The simple fact of introducing a one-million-row read for the latest data of each "pixel cell" is fairly insane. You must have a cache for such data. "I'd still have have Redis cache, though" is not even debatable. It doesn't have to be Redis, but is definitely has to be a cache of one kind or another.

So, I just did a SELECT * from a table with 1 million single-byte character rows, and it ran in 90.51ms:

  place=> explain analyze select * from board_bitmap ;
                                                        QUERY PLAN                                                       
  -----------------------------------------------------------------------------------------------------------------------
   Seq Scan on board_bitmap  (cost=0.00..14425.00 rows=1000000 width=6) (actual time=0.009..57.295 rows=1000000 loops=1)
   Planning time: 0.160 ms
   Execution time: 90.510 ms
  (3 rows)

And, with triggers from an activity table, the entire write operation can be atomized so there aren't any race conditions.

I don't think you understand how fast Postgres is on modern hardware. What took a large cluster 5 years ago can be done on a single system with a fast NVMe drive today. We really might not even need Redis in this situation.

And, yes, I have to deal with viral content, so this is right up my alley.

> And to query the whole canvas you'd query a million rows on every read?

No, just using it to store the update log.

But I don't know if there's any obvious problem with querying a handful of megabytes once per second either.