3 ms·
So in that case, each pixel would be stored as a separate row in a relational database? And to query the whole canvas you'd query a million rows on every read?
by d23 10y ago
So in that case, each pixel would be stored as a separate row in a relational database? And to query the whole canvas you'd query a million rows on every read?
I lean towards just using the ratelimiting stuff we already have in place (via memcached, which we talked about in a previous post). We just overlooked it.
- Dylan16807 10y ago> 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.
- mozumder 10y agoI'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.
- developer2 10y agoConsider 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 10y agoSo, 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.