8 ms·

Why Postgres

91 points by jashmenn 14y ago

davidw 14y ago
I love Postgres, and I usually get grumpy when forced to use Mysql, but ... rather than lots of fluffy features (most of the stuff listed here is not that compelling taken individually), the thing for me about Postgres is that it's solid, reliable, and dependable. It behaves correctly. It has transactions. It has DDL transactions. It can, by and large, be counted on to get things right, rather than take shortcuts. It's a well-crafted tool that you can get a lot of mileage out of if you know what you're doing.
- pjscott 14y ago
  In other words, it is what you find yourself sorely wanting when you use MySQL for a non-trivial length of time.
  - rimantas 14y ago
    Oh yes, transactions…
  - davidw 14y ago
    Well, yeah, that or some inanimate object to vent my frustrations on by kicking it
tesmar2 14y ago
I don't value the underlying database as much anymore since I have been working with Rails apps. The database is just an API. I want to write my code in such a way that I could unplug POSTgres and plug in SQL Server tomorrow without skipping a beat. Using AREL you should largely be able to do that.
- tesmar2 14y ago
  Oh, downvote for what would be a good business decision in most companies? (unless super high performance is necessary?)
  - staunch 14y ago
    Didn't downvote you but read the guidelines: Resist complaining about being downmodded. It never does any good, and it makes boring reading. http://ycombinator.com/newsguidelines.html http://ycombinator.com/newsguidelines.html
    - tesmar2 14y ago
      Good link, I'll not complain next time :)
- craigkerstiens 14y ago
  There's still some advantages to the database you choose. For example ActiveRecord will support the JSON datatype which will come with Postgres 9.2. While they may attempt to make this work in some form for other databases, the performance differences between the two will be quite vast.
  - taligent 14y ago
    The JSON data type is nothing more than a self validating varchar field i.e. fairly pointless. Better off just validating the JSON yourself in the application layer and maintaining cross database compatibility. To be honest it is rare that you would even need to explicitly validate as when you deserialize into objects it will just fail at that point.
    - willlll 14y ago
      Sure but combined with plv8 you trivially can have indexes on the json
      - deleted 14y ago
        [deleted]
- emmett 14y ago
  1. No app of significant size will actually allow this. If you've ever tried to switch over a large data set with a high query volume you'll know that it never "just works". 2. Just because there's an API behind it, doesn't mean that it doesn't matter which one you use. As a trivial example, Linux and OSX are both POSIX but it's hard to make an argument it doesn't matter which you use.
  - tesmar2 14y ago
    I bet after you switched your data over you wrote the conflicting SQL lines in a much more generic way.
    - emmett 14y ago
      No, the issue is not conflicting SQL. An ORM solves that problem extremely easily. It's the fact that query planners and performance optimizations implemented by each database are different, and thus directly porting your current schema and queries doesn't work.
  - taligent 14y ago
    > No app of significant size will actually allow this. Never heard of ORM ? In the Java world it is very, very common to have database independence. And most enterprise deployments (i.e. significant size) are Java.
    - emmett 14y ago
      Of course I've heard of ORM. Theoretical "Database independence" is easy to achieve: just follow the SQL standard. All your queries will execute. There's nothing java-specific about this at all. ORMs are extremely common across most languages. Practical "Database independence" is very hard to achieve, because the query planners and performance optimizations differ between databases.
      - fdr 14y ago
        I agree with you. I'd like to expand a little: The main people who can get a benefit from database independence are those who ship software to a client site to be executed. Imagine if you wrote some kind of accounting software package that you handed off to a corporation's IT group to manage: supporting more databases is probably more important than supporting more operating systems (mostly because the number of OSes requiring support has collapsed for most new applications). There will be installations running on different databases all the time. The second most beneficial effect is if you are planning to change databases frequently (why?). Personally I don't think this is usually worth it for technical reasons, you may as well accept it'll suck when it comes time to do this and take whatever shortcuts make life easier and more correct before that. However, it can be useful as a hedge against your database management system vendor trying to play the role of an extortionate gangster or, alternatively, lying down and dying. One thing I like about Postgres is that it is a project, and not a product. There is no overarching database vendor to extort you or die off suddenly: project death is conditional on a lack of interest -- both financial and personal -- in the project's maintenance and improvement. Truly, it will have to be completely obsolete in the marketplace to die. PGCon getting hit by a meteor would be a major setback, though. The downside is that a project can't often make strategic or speculative investments in whizbang features: there has to be some gestalt agreement that a new feature is both worthwhile and implemented very well (both in terms of correctness and maintainability: the odds of you being able to ask another programmer in real time what is going on in a module are very low), and that means quite a bit of humming and hawing before committing to a feature or approach. It's not for everyone: some whizbang feature other may enable your business grow fast enough and survive long enough to deal with the potential pitfalls of having a potentially extortionate or dead database vendor (the old generation: the usual proprietary-RDBMS suspects. Probably the new generation: database-implementation-by-wire only). But it's something to put on the scales, at least.
- jeffdavis 14y ago
  "The database is just an API." APIs matter a lot. In my opinion, software engineering could almost be defined as the practice of designing good APIs. I happen to think postgres, and it's brand of SQL, are great APIs. It has a lot to offer beyond a lowest-common-denominator API that works over any database.
- jacques_chester 14y ago
  > The database is just an API. Tell me how that leaky abstraction is working for you when it sinks.
nigma 14y ago
Here's a link to the previous discussion http://news.ycombinator.com/item?id=3910743 http://news.ycombinator.com/item?id=3910743
dude_abides 14y ago
Another cool feature: recursive queries using the WITH common table expressions. Say you have a table fs <id, name, parent_id> representing a hierarchical filesystem, and you want to print the full path of each file, here's how you can do it in a single query: WITH RECURSIVE path(id, name, parent_id, path, parent) AS ( SELECT id, name, parent_id, '/', NULL FROM fs WHERE id = 1 -- base case UNION SELECT fs.id, fs.name, fs.parent_id, parentpath.path || CASE parentpath.path WHEN '/' THEN '' ELSE '/' END || fs.name as path, parentpath.path as parent FROM fs INNER JOIN path AS parentpath ON fs.parent_id = parentpath.id ) SELECT id, name FROM path;
- aidos 14y ago
  I don't know the details but won't that be quite inefficient? (it's not a construct I've seen before - never used Postgres in anger) You're effectively working with a tree and there are much more relational friendly ways of doing that in SQL. I know I'm just picking on this specific use but I can't help imagining that recursive querying will always be slow. Would love to hear how it's implemented if that's not the case.
  - dude_abides 14y ago
    All that WITH RECURSIVE does is allow a WITH query to refer to its own output. Beyond that there is no overhead. Here is the query plan I got for my above query: QUERY PLAN CTE Scan on path (cost=292.79..304.41 rows=581 width=104) CTE path -> Recursive Union (cost=0.00..292.79 rows=581 width=72) -> Index Scan using fs_pkey on fs (cost=0.00..8.27 rows=1 width=40) Index Cond: (id = 1) -> Hash Join (cost=0.33..27.29 rows=58 width=72) Hash Cond: (public.fs.parent_id = parentpath.id) -> Seq Scan on fs (cost=0.00..21.60 rows=1160 width=40) -> Hash (cost=0.20..0.20 rows=10 width=36) -> WorkTable Scan on path parentpath (cost=0.00..0.20 rows=10 width=36)
    - aidos 14y ago
      But that could end up being quite deep couldn't it? Is it not like stacking up an unknown number of correlated queries? Could you even screw it up and have an infinite joining condition?
craigkerstiens 14y ago
Original author here, after the original post there was so much great feedback that it was worthwhile to publish a part two - http://craigkerstiens.com/2012/05/07/why-postgres-part-2/ http://craigkerstiens.com/2012/05/07/why-postgres-part-2/ Additionally there will probably be a third post when Postgres 9.2 releases soon highlighting some of the great new features such as the JSON datatype.
- deleted 14y ago
  [deleted]
hopeless 14y ago
A few week ago I discovered Postgres Schemas (worst named feature ever -- nothing to do with SQL schema definitions). Basically, schemas allow you to have multiple databases within a single database. So, you only need to manage (or host/pay for) a single database but each company can have their own schema which gives you complete separation of data across all the tables. These, together with the Apartment gem for Rails, makes building isolated multi-tenant applications really easy. It also makes migrated to a true multi-database/multi-server/sharded setup much simpler.
- sitharus 14y ago
  If you were using Oracle or MS SQL you'd be quite familiar with what Postgres calls a schema. The SQL standard as I understand it is very vague on how schemas should be implemented beyond the information schema.
thaumaturgy 14y ago
Could someone that's really familiar with Postgres give me the skinny on replication? I like Postgres a lot, but finally moved all of our data storage to MySQL because, according to the documentation I read just a couple months ago, Postgres couldn't really do multi-master replication. This post, dated prior to the documentation I read before the switch, seems to suggest it would do it just fine as long as it was asynchronous. I'm not clear though on just how ugly its async replication might be (http://www.postgresql.org/docs/8.4/static/high-availability.html http://www.postgresql.org/docs/8.4/static/high-availability....). I'd like to use dbmail to store mail in a database replicated across multiple servers (which would need fairly reliable replication), as well as syslog and other logging facilities to sql (where reliability isn't quite so big of a deal). Is Postgres up to this now, or not?
- rosser 14y ago
  No, Postgres' built-in replication doesn't support multi-master. With one of the many third-party replication packages that have grown around PostgreSQL (specifically, Bucardo v5 which is in beta right now), you can do multiple — as in more than two — writeable masters, though, as the linked page notes, the replication is asynchronous. With older versions of Bucardo, you can do two masters — again, asynchronously (though you can technically get more than two if you want to engage in some significant configuration gymnastics). What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed.
  - xentronium 14y ago
    > What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed. I am not sure I understood, what is the difference between the two?
heretohelp 14y ago
To answer his question, yes virginia, we (people that use MySQL) use replication a lot. Especially at mid-grade levels of scaling where custom sharding and replication middleware hasn't necessitated itself yet but vertical scaling is out of the question. I'd say the bigger issue with Postgres now is that middleware like http://code.google.com/p/vitess/ http://code.google.com/p/vitess/ for it hasn't been deployed in the large yet. Most Postgres scaling anecdotes I've heard were: "Well we put 64 gb of ram in the server and installed a RAID array of SSDs and stopped writing dumb unindexed queries." Well that's just dandy, but what if my indices don't even fit in the ram of a single machine?
- twerquie 14y ago
  > I'd say the bigger issue with Postgres now is that middleware like http://code.google.com/p/vitess/ http://code.google.com/p/vitess/ for it hasn't been deployed in the large yet. That simply isn't true. Take a look at PgPool (http://www.pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/mediawiki/index.php/Main_Page) - it provides many of the same core features as Vitess, goes beyond to provide replication and flexible sharding/partitioning and is a good number of years more mature than Vitess. PL/Proxy is similar middleware (though implemented as stored procedures) developed by and used at Skype for massive Pg partitioning. There are dozens more packages like this at Pgfoundry (http://pgfoundry.org/ http://pgfoundry.org/). What specifically do you feel is missing?
  - deleted 14y ago
    [deleted]
  - heretohelp 14y ago
    >What specifically do you feel is missing? A very large deployment of it that has shaken out the bugs.
    - jacques_chester 14y ago
      Skype doesn't count?
    - 14y ago
jpdoctor 14y ago
I'd add "business risk" into the discussion. Mysql has an interesting road ahead of it, and there are certain people that just won't put open-source Oracle in their critical path.
- falcolas 14y ago
  Fortunately, there are at least two drop-in replacements, if Oracle should drop the ball. Percona Server, and MariaDB. Both are based on the well-vetted core of MySQL, and both are continuing to make huge strides in the capabilities and stability of MySQL. Of course, Oracle hasn't dropped the ball yet; 5.5 was a good, solid release from them.
23david 14y ago
Interesting articles. I read part I and part II. As a long-term mysql user who has dabbled with production Postgres machines I still don't see how Postgres really has any huge advantages that would cause me to want to run it for any new projects and deal with a new and possibly daunting learning curve. Postgres seems to do some things better perhaps, and in general seems to have more flexibility than MySQL. But it also seems to lack focus as a end-to-end db solution. The clustering / replication complexity of Postgres is a huge issue for me, since scaling databases is not a trivial thing in the best of cases. With postgres, there seem to be 10+ different cluster / replication systems (both open-source and commercial?) and it's just a mess. Giving people tons of options is great, but ease of use for developers and an amazing & simple out of box experience is so important. MySQL won the hearts and minds of developers this way... and now MongoDB is doing the same thing by improving on what MySQL what able to accomplish. The Postgres team should consider implementing something similar.
- rosser 14y ago
  One thing to consider is that because of its MVCC architecture (whereby reads can never block writes, and writes can never block reads), and especially with the locking changes coming in 9.2, you can probably get more concurrent activity out of a single, well-tuned Postgres instance than you could a slew of MySQL hosts of comparable size. That wards off your need to go down the (admittedly sometimes complicated) path of PostgreSQL replication, and I say that as a guy who basically makes his living off setting up replicated/HA Postgres...
  - cagenut 14y ago
    Figure I should provide some info rather than just downvote: http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioning.html http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioni...
    - rosser 14y ago
      I'm well aware that InnoDB is MVCC-based. Unfortunately, its implementation of MVCC is hindered by the way the MySQL kernel handles locks, which are completely outside the domain of a storage engine. Consider: -- on MySQL, be sure to say "engine=innodb" before the semicolon... CREATE TABLE locks_suck (id int primary key, val text); -- or however you'd say "generate_series(start, end)" in MySQL... INSERT INTO locks_suck (id) VALUES (generate_series(1, 10)); Now, say the following in one session: BEGIN; UPDATE locks_suck SET val = 'Transaction 1' WHERE id BETWEEN 1 AND 5; -- Note that I haven't committed yet... And then, in a second session: BEGIN; UPDATE locks_suck SET val = 'Transaction 2' WHERE id BETWEEN 6 AND 10; In MySQL, you'll hang for a moment, and then see: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction In Postgres, you'll see: UPDATE 5 That's the kind of concurrency MVCC can buy you. Readers can't block writers, writers can't block readers, and writers can only block other writers trying to write the same row.
pbreit 14y ago
Is Oracle's ownership of MySQL becoming an issue? I realize it's open source but still it seems like things have become to deteriorate slightly. Is that causing any alarm?
- cagenut 14y ago
  Yes and no. Oddly enough, Oracle has owned the most important part of mysql (innodb) for years, so them buying the rest of it was almost a benefit to have it all under one roof. They then went on to put out a very good release (5.5) that didn't suffer from the "don't use it till its in the teens" birthing pains that most previous releases did. The thing is, most of the improvements in that release were really the work of a bunch of companies that had given up getting their patches accepted upstream and were trading around and maintaining their own patchsets. "The Google patches", "The Facebook patches", and "The Percona patches", etc. Mysql/Oracle brought their long stagnant release up to where the rest of the community had already gotten. The nice thing is that means its already been proven that if they go dark and just squat on a stale code base for years, its not actually going to hold anything back. The two things they have done are raise prices, and stop providing source code for new unit tests. Neither is a good thing, but they're pretty trivial in the overall scheme of things.
  - army 14y ago
    That isn't entirely fair to the InnoDB/MySql developers at Oracle - from what I've heard there has in general been an increase in development activity within Oracle. Some of that is merging patches from downstream, but most of the work has been on other improvements. Performance in 5.6, for example, has improved a lot even over patched versions - e.g. https://www.facebook.com/notes/mysql-at-facebook/can-innodb-do-100k-iops/10150907613720933 https://www.facebook.com/notes/mysql-at-facebook/can-innodb-...
jbarham 14y ago
One thing I appreciate about using Django and its DRM is that it hardly matters what database I use. If I'm writing SQL by hand I much prefer Postgres to MySQL since it is more standards conforming and has fewer random quirks. But given that Django generates the SQL for me, which database to use is largely a matter of ease of deployment, and on AWS that's MySQL (via RDS) so that's what I use.

91 points by jashmenn 14y ago

I love Postgres, and I usually get grumpy when forced to use Mysql, but ... rather than lots of fluffy features (most of the stuff listed here is not that compelling taken individually), the thing for me about Postgres is that it's solid, reliable, and dependable. It behaves correctly. It has transactions. It has DDL transactions. It can, by and large, be counted on to get things right, rather than take shortcuts. It's a well-crafted tool that you can get a lot of mileage out of if you know what you're doing.

pjscott 14y ago
In other words, it is what you find yourself sorely wanting when you use MySQL for a non-trivial length of time.
- rimantas 14y ago
  Oh yes, transactions…
- davidw 14y ago
  Well, yeah, that or some inanimate object to vent my frustrations on by kicking it

tesmar2 14y ago

I don't value the underlying database as much anymore since I have been working with Rails apps. The database is just an API. I want to write my code in such a way that I could unplug POSTgres and plug in SQL Server tomorrow without skipping a beat. Using AREL you should largely be able to do that.

tesmar2 14y ago
Oh, downvote for what would be a good business decision in most companies? (unless super high performance is necessary?)
- staunch 14y ago
  Didn't downvote you but read the guidelines: Resist complaining about being downmodded. It never does any good, and it makes boring reading. http://ycombinator.com/newsguidelines.html http://ycombinator.com/newsguidelines.html
  - tesmar2 14y ago
    Good link, I'll not complain next time :)
craigkerstiens 14y ago
There's still some advantages to the database you choose. For example ActiveRecord will support the JSON datatype which will come with Postgres 9.2. While they may attempt to make this work in some form for other databases, the performance differences between the two will be quite vast.
- taligent 14y ago
  The JSON data type is nothing more than a self validating varchar field i.e. fairly pointless. Better off just validating the JSON yourself in the application layer and maintaining cross database compatibility. To be honest it is rare that you would even need to explicitly validate as when you deserialize into objects it will just fail at that point.
  - willlll 14y ago
    Sure but combined with plv8 you trivially can have indexes on the json
    - deleted 14y ago
      [deleted]
emmett 14y ago
1. No app of significant size will actually allow this. If you've ever tried to switch over a large data set with a high query volume you'll know that it never "just works". 2. Just because there's an API behind it, doesn't mean that it doesn't matter which one you use. As a trivial example, Linux and OSX are both POSIX but it's hard to make an argument it doesn't matter which you use.
- tesmar2 14y ago
  I bet after you switched your data over you wrote the conflicting SQL lines in a much more generic way.
  - emmett 14y ago
    No, the issue is not conflicting SQL. An ORM solves that problem extremely easily. It's the fact that query planners and performance optimizations implemented by each database are different, and thus directly porting your current schema and queries doesn't work.
- taligent 14y ago
  > No app of significant size will actually allow this. Never heard of ORM ? In the Java world it is very, very common to have database independence. And most enterprise deployments (i.e. significant size) are Java.
  - emmett 14y ago
    Of course I've heard of ORM. Theoretical "Database independence" is easy to achieve: just follow the SQL standard. All your queries will execute. There's nothing java-specific about this at all. ORMs are extremely common across most languages. Practical "Database independence" is very hard to achieve, because the query planners and performance optimizations differ between databases.
    - fdr 14y ago
      I agree with you. I'd like to expand a little: The main people who can get a benefit from database independence are those who ship software to a client site to be executed. Imagine if you wrote some kind of accounting software package that you handed off to a corporation's IT group to manage: supporting more databases is probably more important than supporting more operating systems (mostly because the number of OSes requiring support has collapsed for most new applications). There will be installations running on different databases all the time. The second most beneficial effect is if you are planning to change databases frequently (why?). Personally I don't think this is usually worth it for technical reasons, you may as well accept it'll suck when it comes time to do this and take whatever shortcuts make life easier and more correct before that. However, it can be useful as a hedge against your database management system vendor trying to play the role of an extortionate gangster or, alternatively, lying down and dying. One thing I like about Postgres is that it is a project, and not a product. There is no overarching database vendor to extort you or die off suddenly: project death is conditional on a lack of interest -- both financial and personal -- in the project's maintenance and improvement. Truly, it will have to be completely obsolete in the marketplace to die. PGCon getting hit by a meteor would be a major setback, though. The downside is that a project can't often make strategic or speculative investments in whizbang features: there has to be some gestalt agreement that a new feature is both worthwhile and implemented very well (both in terms of correctness and maintainability: the odds of you being able to ask another programmer in real time what is going on in a module are very low), and that means quite a bit of humming and hawing before committing to a feature or approach. It's not for everyone: some whizbang feature other may enable your business grow fast enough and survive long enough to deal with the potential pitfalls of having a potentially extortionate or dead database vendor (the old generation: the usual proprietary-RDBMS suspects. Probably the new generation: database-implementation-by-wire only). But it's something to put on the scales, at least.
jeffdavis 14y ago
"The database is just an API." APIs matter a lot. In my opinion, software engineering could almost be defined as the practice of designing good APIs. I happen to think postgres, and it's brand of SQL, are great APIs. It has a lot to offer beyond a lowest-common-denominator API that works over any database.
jacques_chester 14y ago
> The database is just an API. Tell me how that leaky abstraction is working for you when it sinks.

nigma 14y ago

Here's a link to the previous discussion http://news.ycombinator.com/item?id=3910743 http://news.ycombinator.com/item?id=3910743

dude_abides 14y ago

Another cool feature: recursive queries using the WITH common table expressions. Say you have a table fs <id, name, parent_id> representing a hierarchical filesystem, and you want to print the full path of each file, here's how you can do it in a single query: WITH RECURSIVE path(id, name, parent_id, path, parent) AS ( SELECT id, name, parent_id, '/', NULL FROM fs WHERE id = 1 -- base case UNION SELECT fs.id, fs.name, fs.parent_id, parentpath.path || CASE parentpath.path WHEN '/' THEN '' ELSE '/' END || fs.name as path, parentpath.path as parent FROM fs INNER JOIN path AS parentpath ON fs.parent_id = parentpath.id ) SELECT id, name FROM path;

aidos 14y ago
I don't know the details but won't that be quite inefficient? (it's not a construct I've seen before - never used Postgres in anger) You're effectively working with a tree and there are much more relational friendly ways of doing that in SQL. I know I'm just picking on this specific use but I can't help imagining that recursive querying will always be slow. Would love to hear how it's implemented if that's not the case.
- dude_abides 14y ago
  All that WITH RECURSIVE does is allow a WITH query to refer to its own output. Beyond that there is no overhead. Here is the query plan I got for my above query: QUERY PLAN CTE Scan on path (cost=292.79..304.41 rows=581 width=104) CTE path -> Recursive Union (cost=0.00..292.79 rows=581 width=72) -> Index Scan using fs_pkey on fs (cost=0.00..8.27 rows=1 width=40) Index Cond: (id = 1) -> Hash Join (cost=0.33..27.29 rows=58 width=72) Hash Cond: (public.fs.parent_id = parentpath.id) -> Seq Scan on fs (cost=0.00..21.60 rows=1160 width=40) -> Hash (cost=0.20..0.20 rows=10 width=36) -> WorkTable Scan on path parentpath (cost=0.00..0.20 rows=10 width=36)
  - aidos 14y ago
    But that could end up being quite deep couldn't it? Is it not like stacking up an unknown number of correlated queries? Could you even screw it up and have an infinite joining condition?

craigkerstiens 14y ago

Original author here, after the original post there was so much great feedback that it was worthwhile to publish a part two - http://craigkerstiens.com/2012/05/07/why-postgres-part-2/ http://craigkerstiens.com/2012/05/07/why-postgres-part-2/ Additionally there will probably be a third post when Postgres 9.2 releases soon highlighting some of the great new features such as the JSON datatype.

deleted 14y ago
[deleted]

hopeless 14y ago

A few week ago I discovered Postgres Schemas (worst named feature ever -- nothing to do with SQL schema definitions). Basically, schemas allow you to have multiple databases within a single database. So, you only need to manage (or host/pay for) a single database but each company can have their own schema which gives you complete separation of data across all the tables. These, together with the Apartment gem for Rails, makes building isolated multi-tenant applications really easy. It also makes migrated to a true multi-database/multi-server/sharded setup much simpler.

sitharus 14y ago
If you were using Oracle or MS SQL you'd be quite familiar with what Postgres calls a schema. The SQL standard as I understand it is very vague on how schemas should be implemented beyond the information schema.

thaumaturgy 14y ago

Could someone that's really familiar with Postgres give me the skinny on replication? I like Postgres a lot, but finally moved all of our data storage to MySQL because, according to the documentation I read just a couple months ago, Postgres couldn't really do multi-master replication. This post, dated prior to the documentation I read before the switch, seems to suggest it would do it just fine as long as it was asynchronous. I'm not clear though on just how ugly its async replication might be (http://www.postgresql.org/docs/8.4/static/high-availability.html http://www.postgresql.org/docs/8.4/static/high-availability....). I'd like to use dbmail to store mail in a database replicated across multiple servers (which would need fairly reliable replication), as well as syslog and other logging facilities to sql (where reliability isn't quite so big of a deal). Is Postgres up to this now, or not?

rosser 14y ago
No, Postgres' built-in replication doesn't support multi-master. With one of the many third-party replication packages that have grown around PostgreSQL (specifically, Bucardo v5 which is in beta right now), you can do multiple — as in more than two — writeable masters, though, as the linked page notes, the replication is asynchronous. With older versions of Bucardo, you can do two masters — again, asynchronously (though you can technically get more than two if you want to engage in some significant configuration gymnastics). What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed.
- xentronium 14y ago
  > What they're calling "synchronous multi-master" replication is actually implemented using "two-phase commit", which isn't replication per se, but rather application code using a database feature that allows specifically crafted database interactions to be written to multiple databases, and only successfully committing if the participating nodes agree that they've all committed. I am not sure I understood, what is the difference between the two?

heretohelp 14y ago

To answer his question, yes virginia, we (people that use MySQL) use replication a lot. Especially at mid-grade levels of scaling where custom sharding and replication middleware hasn't necessitated itself yet but vertical scaling is out of the question. I'd say the bigger issue with Postgres now is that middleware like http://code.google.com/p/vitess/ http://code.google.com/p/vitess/ for it hasn't been deployed in the large yet. Most Postgres scaling anecdotes I've heard were: "Well we put 64 gb of ram in the server and installed a RAID array of SSDs and stopped writing dumb unindexed queries." Well that's just dandy, but what if my indices don't even fit in the ram of a single machine?

twerquie 14y ago
> I'd say the bigger issue with Postgres now is that middleware like http://code.google.com/p/vitess/ http://code.google.com/p/vitess/ for it hasn't been deployed in the large yet. That simply isn't true. Take a look at PgPool (http://www.pgpool.net/mediawiki/index.php/Main_Page http://www.pgpool.net/mediawiki/index.php/Main_Page) - it provides many of the same core features as Vitess, goes beyond to provide replication and flexible sharding/partitioning and is a good number of years more mature than Vitess. PL/Proxy is similar middleware (though implemented as stored procedures) developed by and used at Skype for massive Pg partitioning. There are dozens more packages like this at Pgfoundry (http://pgfoundry.org/ http://pgfoundry.org/). What specifically do you feel is missing?
- deleted 14y ago
  [deleted]
- heretohelp 14y ago
  >What specifically do you feel is missing? A very large deployment of it that has shaken out the bugs.
  - jacques_chester 14y ago
    Skype doesn't count?
  - 14y ago

jpdoctor 14y ago

I'd add "business risk" into the discussion. Mysql has an interesting road ahead of it, and there are certain people that just won't put open-source Oracle in their critical path.

falcolas 14y ago
Fortunately, there are at least two drop-in replacements, if Oracle should drop the ball. Percona Server, and MariaDB. Both are based on the well-vetted core of MySQL, and both are continuing to make huge strides in the capabilities and stability of MySQL. Of course, Oracle hasn't dropped the ball yet; 5.5 was a good, solid release from them.

23david 14y ago

Interesting articles. I read part I and part II. As a long-term mysql user who has dabbled with production Postgres machines I still don't see how Postgres really has any huge advantages that would cause me to want to run it for any new projects and deal with a new and possibly daunting learning curve. Postgres seems to do some things better perhaps, and in general seems to have more flexibility than MySQL. But it also seems to lack focus as a end-to-end db solution. The clustering / replication complexity of Postgres is a huge issue for me, since scaling databases is not a trivial thing in the best of cases. With postgres, there seem to be 10+ different cluster / replication systems (both open-source and commercial?) and it's just a mess. Giving people tons of options is great, but ease of use for developers and an amazing & simple out of box experience is so important. MySQL won the hearts and minds of developers this way... and now MongoDB is doing the same thing by improving on what MySQL what able to accomplish. The Postgres team should consider implementing something similar.

rosser 14y ago
One thing to consider is that because of its MVCC architecture (whereby reads can never block writes, and writes can never block reads), and especially with the locking changes coming in 9.2, you can probably get more concurrent activity out of a single, well-tuned Postgres instance than you could a slew of MySQL hosts of comparable size. That wards off your need to go down the (admittedly sometimes complicated) path of PostgreSQL replication, and I say that as a guy who basically makes his living off setting up replicated/HA Postgres...
- cagenut 14y ago
  Figure I should provide some info rather than just downvote: http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioning.html http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioni...
  - rosser 14y ago
    I'm well aware that InnoDB is MVCC-based. Unfortunately, its implementation of MVCC is hindered by the way the MySQL kernel handles locks, which are completely outside the domain of a storage engine. Consider: -- on MySQL, be sure to say "engine=innodb" before the semicolon... CREATE TABLE locks_suck (id int primary key, val text); -- or however you'd say "generate_series(start, end)" in MySQL... INSERT INTO locks_suck (id) VALUES (generate_series(1, 10)); Now, say the following in one session: BEGIN; UPDATE locks_suck SET val = 'Transaction 1' WHERE id BETWEEN 1 AND 5; -- Note that I haven't committed yet... And then, in a second session: BEGIN; UPDATE locks_suck SET val = 'Transaction 2' WHERE id BETWEEN 6 AND 10; In MySQL, you'll hang for a moment, and then see: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction In Postgres, you'll see: UPDATE 5 That's the kind of concurrency MVCC can buy you. Readers can't block writers, writers can't block readers, and writers can only block other writers trying to write the same row.

pbreit 14y ago

Is Oracle's ownership of MySQL becoming an issue? I realize it's open source but still it seems like things have become to deteriorate slightly. Is that causing any alarm?

cagenut 14y ago
Yes and no. Oddly enough, Oracle has owned the most important part of mysql (innodb) for years, so them buying the rest of it was almost a benefit to have it all under one roof. They then went on to put out a very good release (5.5) that didn't suffer from the "don't use it till its in the teens" birthing pains that most previous releases did. The thing is, most of the improvements in that release were really the work of a bunch of companies that had given up getting their patches accepted upstream and were trading around and maintaining their own patchsets. "The Google patches", "The Facebook patches", and "The Percona patches", etc. Mysql/Oracle brought their long stagnant release up to where the rest of the community had already gotten. The nice thing is that means its already been proven that if they go dark and just squat on a stale code base for years, its not actually going to hold anything back. The two things they have done are raise prices, and stop providing source code for new unit tests. Neither is a good thing, but they're pretty trivial in the overall scheme of things.
- army 14y ago
  That isn't entirely fair to the InnoDB/MySql developers at Oracle - from what I've heard there has in general been an increase in development activity within Oracle. Some of that is merging patches from downstream, but most of the work has been on other improvements. Performance in 5.6, for example, has improved a lot even over patched versions - e.g. https://www.facebook.com/notes/mysql-at-facebook/can-innodb-do-100k-iops/10150907613720933 https://www.facebook.com/notes/mysql-at-facebook/can-innodb-...

jbarham 14y ago

One thing I appreciate about using Django and its DRM is that it hardly matters what database I use. If I'm writing SQL by hand I much prefer Postgres to MySQL since it is more standards conforming and has fewer random quirks. But given that Django generates the SQL for me, which database to use is largely a matter of ease of deployment, and on AWS that's MySQL (via RDS) so that's what I use.