Big disclaimer: I haven't worked at reddit in five years, so everything I'm saying was true five years ago but may not be today. I also haven't admined a production postgres box since I left reddit.
> The general philosophy I got from it is that postgres is used for the source of truth, gets all the writes; and then reads can be offloaded to slave instances, or other specialized engines like Cassandra.
That was true at the time of the talk, I don't think it's true today. I believe Cassandra holds the source of truth for some data now.
> However there are no details at all about the replication solution. If you don't mind sharing, how was you general experience around it?
It was terrible. That was the part of Postgres that was totally unsolved. I once spent an entire family vacation resyncing databases while sitting in a hotel room.
> Was it trigger-based, WAL-based?
We started with Wal based replication, then moved to a tool called londiste (which is trigger based). We liked that tool because it was in Python so we could dig into the internals if we had to (which we did have to on occasion).
> Was it the postgres solution, or Slony/Bucardo/etc?
I think it was third party at the time. It looks slightly more official now?
> What were the hurdles/gotchas you encountered?
A stable replication system. :) The replication would keep dying because the nodes would get out of sync and the replication wouldn't be able to recover. They discovered after I left that the issue was an occasional pathological code branch that would make a write directly to a slave instead of a master. I think replication got a lot more stable after they fixed that.
> Did you have to compromise between synch/asynch?
It was all async and we designed around that. The nice thing was that anything we wrote to the database went to the cache too, so even if a read slave hadn't picked up the change yet it didn't matter because the read was never hitting Postgres -- the data was in the cache already. Sometimes we did have problems where out of date data would be read from a slave and then cached, but it was usually fixed on its own, either by being written again or just falling out of the cache.
> It is said that there are two big problems in computing: naming things and cache invalidation. Did you have to design a specific caching policy? How was it enforced?
The app developer could choose the level of caching they wanted for each piece of data. There were multiple caches available with different levels of globalness. For example we had caches right on the servers that were used to cache rendered html bits. Since the html was the same as long as the data was the same, which was part of the cache key, then you never had to invalidate it.
Basically, to solve the invalidation problem, as long as you write your code in a way where the value is tied to the cache key, you never have to invalidate the cache because the changing data does it for you, so for things like pre-rendered html this worked great, but for things where a value was being cached obviously not so much. In those cases, there was a global cache so you just had to deal with race conditions (but for something like points, the command that was sent was "increment 1", not "set to X").
> How did you decide what to put to Cassandra vs postgres slaves?
It depended on the use case. Things that were faster/easier to store in Postgres were stored there. For example, the list of the top 1000 links for every subreddit were stored there (and only there). For every vote, that list was recalculated from the data in Postgres and put in C*. So technically it was the source of truth for that data, but since it could always be recomputed from Postgres, we didn't call it that. (BTW there were shortcuts for not recomputing on every vote).
> Also, what features did you use in postgres? Did you chose it specifically for hstore?
Hstore didn't exist when we started using Postgres (or even when I left reddit for that matter).
We didn't use any special features in Postgres, we just liked that it was rock solid code and conformed to standard SQL better than MySql. It also had a way better query planner.
> From below: how on earth did moving to SSDs help Reddit avoid the Postgres autovacuum failure mode?!
It didn't, it just meant that when a vacuum happened the node was still useable, as opposed to with spinning disk. A vacuum on a read slave during peak sucked. A vacuum on a master during most any time was disastrous. I had to schedule the full vacuums for Saturday nights and hope that was sufficient.