Sharding Pinterest: How we scaled our MySQL fleet
In this technical retrospective (2015), @jparise shares practical architectural lessons learned from running, scaling, or migrating production software under lean constraints.
Core Principles for Independent Builders:
Defaulting to simpler architectures (such as monolithic frameworks, SQLite, or managed VPS) prevents early engineering bankruptcy.
Evaluating true compute and egress costs reveals that high cloud bills can often be slashed by 80%+ through judicious re-platforming.
Code you write at 2 AM as a solo engineer must be easy to debug six months later without complex microservice dependencies.
Community Debate Themes:
HN engineers debated the tradeoffs between cloud convenience and raw server economics, database indexing strategies, and resisting premature architectural complexity.
CATEGORY Frugal Engineering →
HISTORICAL ERA Cloud & Mobile Era (2014–2019)
COMMUNITY SCORE ★ 144 points
TOTAL DISCUSSIONS 85 comments
I've seen a different approach where you keep a version number on the row, do your read, modify in memory on the app server, then do your write like this
``` UPDATE db03429.pins SET blob=’’ WHERE local_id=7075733 AND version=53
``` then look at the result and make sure that it modified one row. If it returned zero rows, you retry (or show a failure to the user, whichever is appropriate for your use case).
The reason you'd do this is so you can't ever have the row locked for a long period of time. A lot of people don't think about database scalability so _even if they know_ that the code they're writing runs while a transaction is held open, they don't care that that transaction is blocking anything else that is trying to read the row they're working on.
This can lead to row lock bloat over time, which can cause scalability / availability issues as app servers wait longer and longer to get read (or write) locks on all the rows they care about for their current request. This is mitigated a bit if you're requiring / encouraging people to read from slaves instead of master, though.
These can be worked through, with discipline. You probably only need a fully consistent few on a surprisingly small number of pages.
Generally you'll want to read from the master right after a user changes something (if you have a model where user clicks a button -> you go to a different page). User changes setting on a pin, clicks 'save', you render a new page showing their updated pin. This page view should probably come from the master, or else you risk the user's change not showing up, causing confusion.
Reads from slaves are fine, as long as you're not using something that was read out of a slave as an input to a database write somewhere else (which you shouldn't be doing anyway!). If you render a page that lets a user change their profile (say site.com/edit_profile), the user data can come from a slave, but if you take _all_ the field values and blindly write those into the master, that's where you run into "time travel" bugs. You just need to find out what the user changed and only make those changes in the master.
Can someone explain to my why people are still recommending mysql over postgres? This is a serious question, it just seems that Postgres has more features and I cant think of any good reasons mysql would scale any differently other than it has been along a little longer (so there are more blog posts + experienced engineers) ?
"MySQL is mature, stable and it just works. Not only do we use it, but it’s also used by plenty of other companies pushing even bigger scale. MySQL supports our need for ordering data requests, selecting certain ranges of data and row-level transactions. It has a hell of a lot more features, but we don’t need or use them. But, MySQL is a single box solution, hence the need to shard our data. Here’s our solution:"
what about that paragraph is not true of postgres also ?
UPDATE:
This schemaless json reminds me of this friendfeed blog post from years ago:
https://backchannel.org/blog/friendfeed-schemaless-mysql
a minor con of this approach is that you have to add an extra layer to your application to do these operations, i.e. to abstract them. But probably the speed & other gains surpass the cons, and in such a big team, i'm sure they will easily handle such an abstraction layer.
One good idea is to open source it, so if other people can take advantage of it, they will also help you maintain it and find bugs for it.
A question: If you started this now, would you consider using Postgres-XL AFAIK it supports similar shardings, in a more transparent manner for the developers. Any thoughts on this?
How are they going to solve the problem of querying into the data that's stored as json? E.g. trying to find all pins whose "link" is from, say, reddit. Just pull out all data and filter them through in client side? That's not gonna scale. Or having a sort of cron job that periodically picking out interested fields in new json data and store them in a separate table? <-- this is essentially what we do in one of our projects, but curious to see how they do it, or alternatives.