14 ms·
Handling Growth with Postgres
- andrewljohnson 14y agoWe 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.
- twp 14y agoJust 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.
- dotborg 14y agothey read postgres docs, that's just.. amazing:)
- jpitz 14y agoThe 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.
- calinet6 14y agoOne 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...
- thibaut_barrere 14y agoMy thoughts exactly! And this is timely, I just came across this (not yet available, but I subscribed): http://postgresweekly.com http://postgresweekly.com
- taligent 14y agoWell 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.
- jpitz 14y agoTerrible, how?
- taligent 14y agoWell 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'.
- joevandyk 14y agoThere is an official pg solution since 9.0. http://www.postgresql.org/docs/9.2/static/warm-standby.html#STREAMING-REPLICATION http://www.postgresql.org/docs/9.2/static/warm-standby.html#...
- taligent 14y agoThat 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.
- joevandyk 14y agoI 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 http://postgres-xc.sourceforge.net.
- 14y ago
- redwood 14y agoNice, didn't know about the Berkeley connection! (wasn't in CS)
- zrail 14y agoI don't understand why anyone would advocate for autocommit. It's a horrible feature that leads to broken data.
- gingerlime 14y agocan you elaborate?
- zrail 14y agoSure. 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.
- sophacles 14y ago1. 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.
- jensenbox 14y agoHe does mention it is good for read only primarily...
- nuclear_eclipse 14y agoThey specifically mentioned using it for read queries, where transactions are irrelevant and waste bandwidth and processing power on both the server and the client.
- hcarvalhoalves 14y agoThe partial index tip is great for that kind of problem (you need a fast query for a subset of your data).
- aidos 14y agoIt'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 http://news.ycombinator.com/item?id=5039042
- pjungwir 14y agoI wrote a gem to help you create partial indexes (among other things) in Rails projects: https://github.com/pjungwir/db_leftovers https://github.com/pjungwir/db_leftovers
- elteto 14y agoI 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.
- pjungwir 14y agoI 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+.
- melvinram 14y agoAnecdotally speaking, yes that was exactly why I first tried it out (when Heroku first launched a few years back.) Glad I did though.
- acegopher 14y agoI think it's taken off since Oracle bought MySQL, Inc.
- masklinn 14y agoYep, 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.
- forsaken 14y agoPostgres 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.
- yRetsyM 14y agoDoes anyone know if Heroku does any of this sort of "here's what we learned" posts re: postgres?
- csears 14y agoPeter 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.craigkerstiens.com/ http://www.quora.com/Peter-van-Hardenberg http://www.quora.com/Peter-van-Hardenberg
- joevandyk 14y agoDear 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/ http://sqitch.org/ for this reason. (slides: http://www.slideshare.net/justatheory/sqitch-pgconsimple-sql-change-management-with-sqitch http://www.slideshare.net/justatheory/sqitch-pgconsimple-sql...)
- rbranson 14y agoThe 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.
- gfodor 14y agoI enjoyed this article and also found a link to this one which I found equally interesting: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram 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.
- mikeyk 14y agoWe'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.
- jpitz 14y agoIf high-performance PostgreSQL is critical to your job, here are some resources: http://wiki.postgresql.org/wiki/Slow_Query_Questions http://wiki.postgresql.org/wiki/Slow_Query_Questions Query analysis tool http://explain.depesz.com http://explain.depesz.com The mailing list http://www.postgresql.org/list/pgsql-performance/ http://www.postgresql.org/list/pgsql-performance/ Greg Smith's book http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm... #postgresql on freenode.net
- No1 14y agoSome 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-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg...
- atsaloli 14y agoRead 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_prix.pdf http://developer.postgresql.org/~josh/releases/9.2/92_grand_...
- oz 14y agoOn 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 :)
- deleted 14y ago[deleted]
- gilrain 14y agoYes, 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.
- oz 14y agoI 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.
- gbog 14y agoI 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.
- willlll 14y agoGood to see people spreading WAL-E love.
- mbell 14y agoThat autocommit point just made thousands of Java EE/hibernate guys cry out in terror.
- dscrd 14y agoHow can you tell?
- hosay123 14y ago> 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.
- brown9-2 14y agoThis would seem to assume that this is the only operation that hypothetical machine would need to handle.
- hosay123 14y agoGood 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.
- rbranson 14y agoThe 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.
- weaksauce 14y agoCan anyone recommend a good guide to administering Postgres? Best practices etc.... The Postgres docs are fairly light about all that.
- mixmastamyk 14y agoAnyone 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.