Handling Growth with Postgres | Hacker News Reader

Handling Growth with Postgres

instagram-engineering.tumblr.com

305 pointsmikeyk83 comments

On a somewhat meta note:

>Over the last two and a half years, we’ve picked up a few tips and tools about scaling Postgres that we wanted to share—things we wish we knew when we first launched Instagram.

A common failure mode for myself and, I suspect, others, is thinking that we have to know every single thing before we start. Good old geek perfectionism of wanting an ideal, elegant setup. A sort of Platonic Ideal, if you will.

These guys went on to build one of the hottest web properties on earth, and they didn't get it all right up front.

If you're postponing something because you think you need to master all the intricacies of EC2, Postgres, Rails or $Technology_Name, pay close attention to this example. While they were launching growing, and being acquired for a cool billion, were you agonizing over the perfect hba.conf?

More a note to myself than anything else :)

Yes, chasing a platonic ideal is a good way of putting it. It's right there in the name: best practices. It's important to realize there aren't really any _best_ practices. To actually start a project, we have to give ourselves permission (or straight up force ourselves) to use good, or maybe even just acceptable, practices and then polish from there.

I like how you phrase it: We need to give ourselves permission. I think that sometimes, we're afraid that someday "they" will discover the imperfections behind our code, infrastructure, etc. that lies beneath what we build. I suspect that ofttimes, the 'they' is really ourselves.

A shame, really: The very thing that drives us to be the best ends up holding us back.

I think Instagram shows pretty much the opposite example of the "throw anything at the wall with any techno you want and fix later" mentality that is trendy todays.

They were seasoned enough to choose Python and Postgres, two technologies that are both relatively easy to start with and to scale later.

And, oddly enough, those two technologies are on the "do things right" side, and do not (usually) sacrifice their correctness to some convenience or fashion.

So, sure, one cannot expect to know every corner of the techno one chooses, but still choosing carefully the best options and shielding oneself against current hotness is still not a waste of time or energy.

I can't remember where I read/heard it, but they received advice about what to use, and change to use in order to scale better, and were willing to listen to that advice.

So the lesson is not just be good at what you do, but be willing to listen to other people who knows better than you do.

Sure, they didn't try to do anything too crazy, but on a scale of cobol <=> new hotness, I think django would fall closer to new hotness.

I think django was a good choice; I just don't think you can draw those conclusions from it.

I think you are correct in that you don't need to know everything to get started. But it also shows the benefits of maturity of the technology stack. As much as I would love to develop my geospatial analytics app in Clojure...I'm doing it in Python/GeoDjango. And unlike my attempt in Clojure, it is going incredibly smoothly. The language bugs me, but it is better than tracking down bugs due to the immaturity of the ecosystem.

Maybe you are giving instagram too much credit? Isnt it just a photo uploading website. Sure the "scalability" challenges might be non-trivial but definitely not rocket science

Photo uploads aren't hard to scale. Activity streams are. Instagram's activity stream is one if their most important features.

One of the largest services on the net, and their summary of their database experience is "Overall, we’ve been very happy with Postgres’ performance and reliability."

Go Bears. That's awesome. And we should all take a hint...

My thoughts exactly! And this is timely, I just came across this (not yet available, but I subscribed):

http://postgresweekly.com

Well they had to write their own sharding implementation which is something that 99% of startups wouldn't want to be doing. Combine that with a pretty terrible list of replication options and PostgreSQL is far from being ideal when it comes to scalability.

Terrible, how?

Well there is no official PostgreSQL solution. It's a bunch of third party solutions with varying levels of quality, documentation, support and use.

Every notable PostgreSQL deployment has had to 'roll their own'.

There is an official pg solution since 9.0.

http://www.postgresql.org/docs/9.2/static/warm-standby.html#...

That is simply master/slave.

Not really suitable for the common scalability issues startups deal with today. Like working in multiple Amazon regions or supporting difference sets of servers.

I was responding to you saying that there was no official replication method for postgresql. There has been for about 2.5 years.

If you are wanting master-master, look into http://postgres-xc.sourceforge.net.

you're well aware that the for example the couchbase cross-datacenter-replication has it's own share of problems such as "what happens when the same dataset gets modified in both clusters?". IIRC it just drops the older change and keeps the newer version. That may or may not be a problem to you, but for others that might just be the nail in the coffin. Every datastore out there has different trade-off that are acceptable for different use-cases. And postgres has it's own share of tradeoffs, but it works quite nicely for a lot of use-cases.

Slony has been around since at least 2004.

It was included in the Postgres source code repository. I always considered that to be a pretty official solution.

> Well there is no official PostgreSQL solution

Wrong. September of 2010: http://www.postgresql.org/about/news/1235/

Please don't feed the troll. Nothing anyone can say about PostgreSQL will ever make taligent respond with anything but negativity about it.

Nothing is 'ideal' when it comes to scalability -- but if you're looking for adequate, PostgreSQL is the best tool out there right now.

Instagram seems to agree.

You don't need to worry about solving for scalability until you actually have scaling problems, which most startups will never face. Yet, weirdly, I've seen many companies sink massive amounts of time and money into solving future scaling issues that never materialize.

Solve the demand problem first, and use that to pay to fix the supply problem.

Especially, when you get Facebook-big, you hit a new wall of scaling challenges, and this wall will be very specific to your company. Solving those challenges is tough and expensive, which is just fine, because Instagram-level growth brings with it the money to pay for solving those problems.

With databases like Couchbase, MongoDB, Cassandra, Riak, MySQL Cluster issues like sharding and horizontal scaling have largely been solved for you. Meaning it's neither tough nor expensive.

And some of us run startups that have to deal with large volumes of data from day one. So this idea of "wait until you're big" is simply bad advice.

No, it isn't bad advice just overly simplified and generalised.

The vast majority of companies won't need to face scaling or big data issues, they're too busy going after that next sale to keep their heads above water. There are, however, some problems that require lots of data very early on so in these situations it's appropriate to look for solutions like MongoDB, CouchDB, Riak et al. What ends up happening all too often is someone hears about MongoDB being the best new cool thing and decides to implement their company CRUD + sales platform on top of it.

The question you have to ask yourself is why isn't Postgres suitable for you. That might be huge amounts of data and heavy reads and rapidly changing schemas that make MongoDB a better choice.

In any case this post was great because it shows that Postgres can scale if you're willing to put some money, thought and effort into it. I doubt many people here have Instagram's data size or scaling issues.

Sure my use case is simple and common for even the smallest startups:

I want my app to work in multiple Amazon EC2 regions.

What's wrong with the replication built in to PostgreSQL 9?

Why are you thinking about this when you're not even sure that your app will need to run on multiple EC2 regions? This smells like premature optimisation to me.

You'd be surprised. There are a lot of small to midsized companies with data-intensive products. There are a dozen different fleet-tracking-as-a-service companies, several thousand inventory-management, medical-billing management, etc.

The Silicon Valley Tech Bubble is not where the bulk of data usage happens.

Only one of those technologies fills the same role as Postgres and MySQL (_especially_ MySQL Cluster) brings a host of other problems to the table. There is no silver bullet and it ALL comes down to use case.

I work in a GIS shop, and I do mapping and location based stuff on the side. I am a fan of Postgres. MySQL may have some spatial components, but it's like saying that you're a pro painter cause you bought a $200 paint sprayer at Home Depot. MySQL does have cluster/replication support, but their bolt on stuff feels, well.... bolted on! Every time I use a new Postgres feature, I feel like it was actually developed. (hstore is the newest thing I'm working with)

What's wrong with PostgreSQL 9 replication?

Postgres replication is great, but it does have flaws.

The big one is that it's on a per-cluster (ie., database instance) level. It's not possible to have different databases with different replication settings: You have to replicate everything or nothing.

Another gripe is that it's awkward to set up the first time; you have to do a base backup, rsync over, etc. It would have been great if you could just start a slave and tell it to stream the entire master database over. Possibly something that gets easier in 9.3.

Another gripe, as a developer, is that read-only queries can fail. You will eventually get a nice "ERROR: Canceling statement due to conflict with recovery"; and you will simply need to retry the query at that point. (We actually switch back to the master and retry.) We use long timeouts for the pertinent settings (see http://www.postgresql.org/docs/9.2/static/hot-standby.html), but we still get these.

Some MySQL fans would probably say that Postgres replication being single-master/multiple-slave is a problem, but I don't mind myself.

Nice, didn't know about the Berkeley connection! (wasn't in CS)

If high-performance PostgreSQL is critical to your job, here are some resources:

http://wiki.postgresql.org/wiki/Slow_Query_Questions

Query analysis tool http://explain.depesz.com

The mailing list http://www.postgresql.org/list/pgsql-performance/

Greg Smith's book http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm...

#postgresql on freenode.net

Some good resources there.

Don't forget to mention PgAdmin III has a built-in graphical explain analyze, which is absolutely amazing yet rarely talked about.

http://www.postgresonline.com/journal/archives/27-Reading-Pg...

I am really glad to see all the adoption and recognition that Postgres is receiving nowadays, there was a time when all you could hear about was MySQL (or maybe this is just my perception). It seems to me that it has picked up even more after the Oracle takeover of MySQL, but it could also be that their feature set has reached a pretty mature point, or maybe a combination of both.

I like Postgres much more than MySQL, but my theory is that it's taken off because so many developers have been forced to try it out in order to use Heroku.

But you're right, a ton of great stuff has been added in 8.4+.

Anecdotally speaking, yes that was exactly why I first tried it out (when Heroku first launched a few years back.)

Glad I did though.

I think it's taken off since Oracle bought MySQL, Inc.

Yep, there's probably a pretty big bit of truth to that. Postgres was on the up before (at least in the Python community where most frameworks had decided to run with it following Django's pretty significant endorsements of Django over MySQL), but after seeing MySQL bought I guess people didn't have much confidence in either Oracle or Monty's new venture and decided to take a good look at the other "big guy" of the Open Source world.

Which coincidentally followed the (probably much-needed) performance improvements which started landing heavily in the 8.x series.

Postgres has also been heavily endorsed by Django since the early days and used heavily in the Python community. Instagram is a Django site, so the fact that they use Postgres is not too surprising.

Nah, it's been steadily growing through various discovery paths over the last 2-3 years.

Personally I caught on once I saw the feature list of 9.0. And 9.1... then 9.2... they just keep adding cool stuff that's made well. It becomes difficult to ignore.

I suspect I wasn't alone in seeking a freely available and "real" database a few years ago and initially going with MySQL because I followed the herd. Then, while using MySQL for some significant projects, I learned that it had some quite serious limitations in even relatively basic functionality like transactions. That prompted my personal interest in Postgres, and I saw a project with all of the same advantages that had made MySQL attractive, but also a technically better product. I've never used MySQL since.

Having seen the internals of Postgres, it's a better product. It's pretty simple: it comes from a better core, with more sound theoretical underpinnings, from smarter people. It deserves the recognition and growth, and it's incredible to me that it continues to keep its high level of quality.

Read scalability has been greatly improved in Postgres 9.2. It scales pretty much linearly to 64 concurrent clients. Goes up to 350,000 queries per second!

Write throughput has been improved as well.

Check out Josh Berkus's (one of 7 core team members of the Postgres dev team) presentation on what's new in Postgres 9.2:

http://developer.postgresql.org/~josh/releases/9.2/92_grand_...

Dear Instagram,

How do you deploy database updates? With Rails-style migrations?

One thing that bugs me about migrations is that if you use functions or views, the function/view definition has to be copied to a new file. It makes it difficult to see what's been changed. I'm looking forward to http://sqitch.org/ for this reason. (slides: http://www.slideshare.net/justatheory/sqitch-pgconsimple-sql...)

The short answer is we don't do it very often, and it's often complex (data sizes, sharding, and availability concerns), so we do it on an adhoc basis and in a semi-automated fashion.

The partial index tip is great for that kind of problem (you need a fast query for a subset of your data).

It's great, it was new to me too. There's a discussion we had about it the other day over here http://news.ycombinator.com/item?id=5039042

I wrote a gem to help you create partial indexes (among other things) in Rails projects:

https://github.com/pjungwir/db_leftovers

I enjoyed this article and also found a link to this one which I found equally interesting:

http://instagram-engineering.tumblr.com/post/10853187575/sha...

I worked with the Flickr-style ticket DB id setup at Etsy, and while it was lovely once it was all set up, it's way more complicated (requiring two dedicated servers and a lot of software and operations stuff.) The solution outlined by instagram of just having a clever schema layout and stored procedure that safely allocates IDs locally on each logical shard is elegant and I'm having a hard time blowing holes in it.

We're very happy with it--given the constraints it's working well. The biggest drawback with our sharding/ID scheme is that it's harder to split off a single user if you need to special case.

> we’re now pushing over 10,000 likes per second at peak

These kinds of stats always sound so impressive, but let's imagine:

    - 8 byte timestamp
    - 8 byte user ID
    - 8 byte post ID
    - 128 bytes DBMS overhead
    - 128 bytes for user->like index
    - 128 bytes for post->like index

= 3.96MiB/second, or ~1015 IOPs/second, or 342GB per day absolute worst case. A single economy machine with an even remotely decent SSD could handle a full day's data at these rates.

This would seem to assume that this is the only operation that hypothetical machine would need to handle.

Good point, although it's still within the remit of a cheap SSD, e.g. the Crucial M4 can push 40k+ IOPs. My remark was more geared towards silly but awesome sounding statistics than anything else, it really undermines otherwise decent content.

The statistic is just to show the growth in our data volumes since last year. As with most social sites (and the web in general), the number of writes is a very tiny slice of the I/O pie.

We use a lot of PostGIS via GeoDjango, and I made a mental note to remember this article if I my postgres instances ever start ailing. Unfortunately, we haven't pushed these limits nearly as much as Instagram.

Just a +1 on this. PostGIS and PostgreSQL are completely awesome. I've been doing a lot of geospatial projects recently and have been stunned by just how fast, robust and scalable PostGIS and PostgreSQL are. If you're storing latitudes/longitudes in a database then check out PostGIS now.

I don't understand why anyone would advocate for autocommit. It's a horrible feature that leads to broken data.

can you elaborate?

Sure.

1) Code that depends on autocommit is hard to unit test, since you have to mock out whatever internal method autocommit calls

2) Code that depends on autocommit is hard to reason about, since you don't have a consistent view of your data, especially in multi-step update methods.

3) Because of 2, your updates will (not "may", "will definitely") be corrupted at some point, leaving broken bad data in the database. Comprehensive constraints help, but if you're relying on autocommit chances are you're not using constraints very well either.

1. is a strawman

2. autocommit is explicitly mentioned in terms of single select reads. Besides - if you use transactions in your update, it doesn't affect anything - postgres will do the right thing. Similarly, postgres will wrap single statement updates in transactions for you. Using it in multi-step procedures is a no-no - fortunately autocommit is per-connection, so if you are being careful about what connection you use, you can have both.

3. Another strawman - using autocommit for single selects doesn't preclude not using it for places where data integrity is a concern.

He does mention it is good for read only primarily...

They specifically mentioned using it for read queries, where transactions are irrelevant and waste bandwidth and processing power on both the server and the client.

They're not irrelevant if you have parallel users modifying the database. The integrity checks in particular are only guarantee a coherent snapshot during a transaction, so if you do two queries and someone sneaks in a modification between them you'll get an incoherent snapshot. Whether or not this matters will depend on what sort of aggregate data you're creating.

The cost of a short-lived read transaction is incredibly low, especially with Postgres. All the data for the last so many transactions will be in the database anyway until a vacuum happens. There's no CPU cost to read transactions that I'm aware of.

Instagram is heavily sharded, so they've pretty much already given up a snapshot view of all their data anyways.

Oh, I trust they understand what they're up to. It's just not advice everybody should follow blindly.

Transactions are most certainly not irrelevant for reads. They are quite necessary.

Explicit transaction blocks for single-statement reads are pointless. Extra packets for the transaction demarcation even more so.

It's ok to just use transactions all the time when you don't need performance, but those transactions come with a cost, and normaly most queries of a system won't need them.

I thought that autocommit simply wraps each SQL statement in its own transaction? Wouldn't a good programming practice, be to ensure that everything that is supposed to be atomic, occur in a single, possibly large, SQL statement anyways?

Autocommit omits the begin/commit pair. This is 'implicit' transaction mode. There is no way to turn off transactions in postgres.

The performance savings comes from the roundtrip latency of the BEGIN TRANSACTION / COMMIT packets.

    "Wouldn't a good programming practice, be to ensure that everything that is supposed to be atomic, occur in a single, possibly large, SQL statement anyways?"

The simplistic answer to that question is a resounding yes.

This isn't clear from the blog post, but we only use autocommit for read-only queries. Using autocommit is only really a major boost if you have a very high skew of reads to writes like we do (50:1).

Thank you for clarifying. Do you segregate autocommit based on whole chunks of application code (for example, front-end web servers are totally read-only at the connection level and therefore autocommit is fine) or is it more ad-hoc?

Do you have a source for how it can "lead to broken data?" It's my understanding that autocommit simply makes postgresql commit a statement automatically after it's submitted, unless it's wrapped in a transaction.

Does anyone know if Heroku does any of this sort of "here's what we learned" posts re: postgres?

Peter van Hardenberg (@pvh) and Craig Kerstiens (@craigkerstiens) have done many Postgres presentations over the last few years. I didn't see any specifically on lessons learned, but their blogs/quora/tweets have lots of good info based on their experience.

http://www.craigkerstiens.com/ http://www.quora.com/Peter-van-Hardenberg

That autocommit point just made thousands of Java EE/hibernate guys cry out in terror.

How can you tell?

Anyone use one of these tools? First I've heard of them, and would seem to make my inner control-freak happy.

    pg_reorg can re-organize tables without any locks, and can be a better
    alternative of CLUSTER and VACUUM FULL. This project is not active now,
    and fork project "pg_repack" takes over its role. See
    https://github.com/reorg/pg_repack for details.

Can anyone recommend a good guide to administering Postgres? Best practices etc.... The Postgres docs are fairly light about all that.

Good to see people spreading WAL-E love.

they read postgres docs, that's just.. amazing:)

The PostgreSQL docs, and source code, are an absolute shining star, in Open Source or otherwise. I'm hard pressed to think of a project that does it better, really.

Reply on news.ycombinator.com