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...
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.
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.
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.
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.
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)
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.
Not really suitable for the common scalability issues startups deal with today. Like working in multiple Amazon regions or supporting difference sets of servers.
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.
Comments
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
Nice, didn't know about the Berkeley connection! (wasn't in CS)
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.
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.
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.
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.
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.
Terrible, how?
Please don't feed the troll. Nothing anyone can say about PostgreSQL will ever make taligent respond with anything but negativity about it.
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#...
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.
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.
> Well there is no official PostgreSQL solution
Wrong. September of 2010: http://www.postgresql.org/about/news/1235/