I don't think this access pattern is generally applicable to social networks, and ditto for your operational considerations.
"Four shards" and "no replicas" is a complete non-starter for a popular social network. That low shard count means a hardware failure on a shard master results in downtime for 25% of your users. Putting ~400 billion rows on a single shard also means backups, restores, schema changes, db cloning, etc all can take a tremendous amount of time. No read replicas means you can't geographically distribute your reads to be closer to users.
I'm not sure why you're assuming that social networks aren't 'very careful in designing for not just "scalability" but economies of scale'. I can assure you, from first-hand experience as both a former member of Facebook's database team and the founding member of Tumblr's database team, that you have a lot of incorrect assumptions here about how social networks design and plan their databases.
I can't comment on any of the PG-specific stuff as I don't work with PG.
Append-only design is not usable today for social networks / user-generated content. That approach is literally illegal in the EU. In years past, afaik Twitter operated this way but I was always skeptical of the cost/benefit math for their approach.
The "200 expensive beefy database servers" I was describing were relative to 2012. By modern standards, they were each a tiny fraction of the size of the hardware you just described. That server count also included replicas, both read-replicas and HA/standby ones (the latter because we did not use online cloning at the time).
I haven't said anything about event sourcing as it generally is not used by social networks, except maybe LinkedIn.
"Four shards" and "no replicas" is a complete non-starter for a popular social network. That low shard count means a hardware failure on a shard master results in downtime for 25% of your users. Putting ~400 billion rows on a single shard also means backups, restores, schema changes, db cloning, etc all can take a tremendous amount of time. No read replicas means you can't geographically distribute your reads to be closer to users.
I'm not sure why you're assuming that social networks aren't 'very careful in designing for not just "scalability" but economies of scale'. I can assure you, from first-hand experience as both a former member of Facebook's database team and the founding member of Tumblr's database team, that you have a lot of incorrect assumptions here about how social networks design and plan their databases.
I can't comment on any of the PG-specific stuff as I don't work with PG.
Append-only design is not usable today for social networks / user-generated content. That approach is literally illegal in the EU. In years past, afaik Twitter operated this way but I was always skeptical of the cost/benefit math for their approach.
The "200 expensive beefy database servers" I was describing were relative to 2012. By modern standards, they were each a tiny fraction of the size of the hardware you just described. That server count also included replicas, both read-replicas and HA/standby ones (the latter because we did not use online cloning at the time).
I haven't said anything about event sourcing as it generally is not used by social networks, except maybe LinkedIn.