44 ms·

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 a

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.

mozumder 9y ago
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.