How We Built r/Place

Re:How We Built r/Place by developer2 on Friday April 14, 2017 @04:33PM (#14117096)

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.

[ Parent | on HN ]

Re:How We Built r/Place by mozumder on Saturday April 15, 2017 @05:00AM (#14120000)

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.

[ Parent | on HN ]