What PostgreSQL has over other open source SQL databases: Part II(compose.io)
compose.io
What PostgreSQL has over other open source SQL databases: Part II
https://www.compose.io/articles/what-postgresql-has-over-other-open-source-sql-databases-part-ii/
95 comments
I wish postgreSQL focuses more on clustering, sharding and replication and bundle these features in the product rather than asking us to use other thirdparty tools like slony and pgpoolII. Other than that, postgreSQL is one of the cleanest SQL implementations. It's source code is incredibly clean, consistent and easy to follow.
There are a bunch of commercial engines built on Postgres that implement sharding and parallel querying at huge scale: Amazon Redshift, Truviso, Netezza, ParAccel, Aster Data and CitusDB come to mind. It's a shame that none of this has tricked down into the open-source version.
There's Postgres-X2 (formerly Postgres-XC), sponsored by NTT, which implements fully consistent multimaster replication and partitioning. After many, many years apparently still isn't production-ready nor particularly scalable, and the likelihood that it will ever be merged into mainline Postgres is zero (its planner changes alone are apparently several hundred thousand lines of code).
More interestingly, there's Postgres-XL, which is apparently a merger of Postgres-XC and a different implementation called StormDB that was bought by a company called TransLattice. (TransLattice also sponsored or acquired an earlier project that went nowhere, Postgres-R.) Unlike XC/X2, they say they aim to contribute changes back to the mainline, and they also claim their distributed query model is superior. With the commerical support behind it (it's used as the basis of a commercial product), it's possible that this is something that will be usable. Unfortunately, it seems very quiet and not very open-sourcy; most of the development seems to be by just one guy [3], and nobody seems to be using it in production at this point.
[1] https://github.com/postgres-x2/postgres-x2
[2] http://www.postgres-xl.org/
[3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...
There's Postgres-X2 (formerly Postgres-XC), sponsored by NTT, which implements fully consistent multimaster replication and partitioning. After many, many years apparently still isn't production-ready nor particularly scalable, and the likelihood that it will ever be merged into mainline Postgres is zero (its planner changes alone are apparently several hundred thousand lines of code).
More interestingly, there's Postgres-XL, which is apparently a merger of Postgres-XC and a different implementation called StormDB that was bought by a company called TransLattice. (TransLattice also sponsored or acquired an earlier project that went nowhere, Postgres-R.) Unlike XC/X2, they say they aim to contribute changes back to the mainline, and they also claim their distributed query model is superior. With the commerical support behind it (it's used as the basis of a commercial product), it's possible that this is something that will be usable. Unfortunately, it seems very quiet and not very open-sourcy; most of the development seems to be by just one guy [3], and nobody seems to be using it in production at this point.
[1] https://github.com/postgres-x2/postgres-x2
[2] http://www.postgres-xl.org/
[3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...
> It's a shame that none of this has tricked down into the open-source version.
Greenplum is an MPP database forked from the Postgres 8.x codebase. It'll be opensourced soon.
Greenplum is an MPP database forked from the Postgres 8.x codebase. It'll be opensourced soon.
Oh wow, I had no idea Greenplum was being open sourced, that's great news. I fiddled with it 6 years agocomparing it and Vertica for some large scale analytics work and it was pretty impressive.. I wonder how hard to merge Postgres head features back into Greenplum though, or vice versa. Probably not gonna happen?
It's hard for me to answer you because I work for Pivotal Labs and I've heard stuff in confidence about the current thinking of what to do next. But once it's opensource I think all sorts of possibilities open up legally, technically and economically.
Are the newer PostgreSQL development been merged back in greenplum, or is it based on an older version?
Is the recent Dell acquisition putting the open sourcing plans at risk?
Is the recent Dell acquisition putting the open sourcing plans at risk?
I'm afraid I couldn't answer these questions even if I knew.
Blah blah a lot of it I don't know rhubarb doctrine of confidence murmer cough professional ethics mumble mutter SEC regulations yadda yadda.
Blah blah a lot of it I don't know rhubarb doctrine of confidence murmer cough professional ethics mumble mutter SEC regulations yadda yadda.
That's interesting. 8.x is pretty antique, though; has anything from the 9.x mainline been merged back in? I'd love to hear about a release date, too.
We announced our opensource plans in February[1]. I don't know when Greenplum will be ready to be opened up, as I work in Labs, which is a different division.
So far we have opened Apache HAWQ (incubating)[2], which is an SQL query engine for Hadoop based on the MPP query planner in Greenplum.
And Apache Geode (incubating)[3], which is based on Gemfire, which is the most successful in-memory grid database you've never heard of.
Edit: I suppose I should mention, entirely in my own self-interest, that we are hiring in our Big Data division across all our products. You can look at our webpage (https://pivotal.io/careers) or email me ([email protected]).
[1] http://pivotal.io/big-data/press-release/pivotal-introduces-...
[2] http://hawq.incubator.apache.org/
[3] http://geode.incubator.apache.org/
So far we have opened Apache HAWQ (incubating)[2], which is an SQL query engine for Hadoop based on the MPP query planner in Greenplum.
And Apache Geode (incubating)[3], which is based on Gemfire, which is the most successful in-memory grid database you've never heard of.
Edit: I suppose I should mention, entirely in my own self-interest, that we are hiring in our Big Data division across all our products. You can look at our webpage (https://pivotal.io/careers) or email me ([email protected]).
[1] http://pivotal.io/big-data/press-release/pivotal-introduces-...
[2] http://hawq.incubator.apache.org/
[3] http://geode.incubator.apache.org/
Looks like you got GemFire from Gemstone, which is also owned by VMware? That explains why it's written in Java, whereas I was happily surprised that HAWQ is written in C++.
It's great that you're releasing stuff as open source. Perhaps you could persuade a colleague at your other division to talk about Greenplum in this thread?
It's great that you're releasing stuff as open source. Perhaps you could persuade a colleague at your other division to talk about Greenplum in this thread?
We (Pivotal) had a bunch of EMC and VMWare companies, technologies and technologies spun into us when the company was founded a few years ago.
Including: Cloud Foundry, Greenplum, Spring, Gemfire and of course the namesake -- Pivotal Labs.
Email me and I will connect you with our director of engineering for Big Data.
Including: Cloud Foundry, Greenplum, Spring, Gemfire and of course the namesake -- Pivotal Labs.
Email me and I will connect you with our director of engineering for Big Data.
In general that is not something that can be easily slapped on later easily. There are some solutions but they feel like add-ons.
For software some things can be added on later, but some can't. Things like fault tolerance, distribution, security can't be easily bolted on after the product has already been developed (as those usually cut right through the whole stack).
People who like Postgres and make fun of NoSQL products for not having well ... SQL, usually forget that NoSQL isn't as much about not having SQL as about having a good distributed/scalable backend story.
For software some things can be added on later, but some can't. Things like fault tolerance, distribution, security can't be easily bolted on after the product has already been developed (as those usually cut right through the whole stack).
People who like Postgres and make fun of NoSQL products for not having well ... SQL, usually forget that NoSQL isn't as much about not having SQL as about having a good distributed/scalable backend story.
The whole SQL vs NoSQL is misunderstood (most likely to the silly name). It is not about that Relational (SQL) database was designed in a way that is hard to make it distributed. It's all about what trade offs are you ok with. If you want ACID (Atomicity, Consistency, Isolation, Durability - if you are not sure, then you do want it) then you go with a relational database. If you are ok with data not always being consistent, or sometimes even lost (for example sessions, statistics, user tracking etc) you can use NoSQL.
There are also databases coughMongoDBcough which claim to have it all, but in reality they have neither[1][2].
[1] Performance of single instance: http://www.enterprisedb.com/postgres-plus-edb-blog/marc-lins...
[2] Scaling out: http://www.datastax.com/wp-content/themes/datastax-2014-08/f...
There are also databases coughMongoDBcough which claim to have it all, but in reality they have neither[1][2].
[1] Performance of single instance: http://www.enterprisedb.com/postgres-plus-edb-blog/marc-lins...
[2] Scaling out: http://www.datastax.com/wp-content/themes/datastax-2014-08/f...
There's no such thing as "NoSQL" - it's a meaningless term. It's far better to just refer to the actual type of database: relational, key/value, wide-column, graph, rdf, etc.
Any one of these database types can have a SQL interface (in various dialects and standards), ACID, different consistency models and scale-out options. It's just up to the specific database product in what it actually offers.
Any one of these database types can have a SQL interface (in various dialects and standards), ACID, different consistency models and scale-out options. It's just up to the specific database product in what it actually offers.
That's a good point, I do hate that term, but there's not a good term that encompasses them all. Perhaps it should be relational vs specialized database.
Perhaps it should be said that relational is DB that's for general purpose, while specialized (key/value, graph, etc) requires you to understand your data so you can make proper trade-offs when picking specialized db. You are not getting those features advertised by other databases for free.
Perhaps it should be said that relational is DB that's for general purpose, while specialized (key/value, graph, etc) requires you to understand your data so you can make proper trade-offs when picking specialized db. You are not getting those features advertised by other databases for free.
They're all specialized DBs. There is no general case.
You don't need any terms, they're all just databases.
Relational is specialized too - in dealing with relational data, foreign keys, normalization, joins, etc.
ACID is just a feature of a product, not tied to relational as there are relational databases that don't offer it or can have it disabled. Tradeoffs and understanding your data apply to every single type of database.
Relational is specialized too - in dealing with relational data, foreign keys, normalization, joins, etc.
ACID is just a feature of a product, not tied to relational as there are relational databases that don't offer it or can have it disabled. Tradeoffs and understanding your data apply to every single type of database.
I think you are the one adding to the confusion by tarring all NoSQL databases with the same brush.
HBase is strongly consistent and given that it a core part of the Hadoop stack and used by Facebook for Messages would indicate it is hardly has systemic data loss issues. Cassandra likewise is used for PSN, Steam, EA Online, Spotify and is quite capable of running in strongly consistent mode. That's just two of the many NoSQL databases that are strongly consistent and being used for major systems where data loss would be unacceptable.
Also your comparisons between PostgreSQL and MongoDB are meaningless. The complete overhaul of the default engine in MongoDB 3 (WiredTiger) has increased performance massively with a notable reduction in disk space usage. It's basically a whole new database.
https://www.mongodb.com/blog/post/performance-testing-mongod...
HBase is strongly consistent and given that it a core part of the Hadoop stack and used by Facebook for Messages would indicate it is hardly has systemic data loss issues. Cassandra likewise is used for PSN, Steam, EA Online, Spotify and is quite capable of running in strongly consistent mode. That's just two of the many NoSQL databases that are strongly consistent and being used for major systems where data loss would be unacceptable.
Also your comparisons between PostgreSQL and MongoDB are meaningless. The complete overhaul of the default engine in MongoDB 3 (WiredTiger) has increased performance massively with a notable reduction in disk space usage. It's basically a whole new database.
https://www.mongodb.com/blog/post/performance-testing-mongod...
HBase is not ACID and therefore not strongly consistent. The consistency in CAP is different than consistency in ACID.
Regarding the Mongo, the second link I provided was comparing WiredTiger. In link you provided they made an improvement, but 2.x was already slow. Initially MongoDB gained its popularity because it supposed to be fast. And it was when it did not worry about making sure the written data ends up on disk. Once people started losing data when system crashed they changed defaults, but then became much slower. And worst of all it still was losing data[1]. There's no good reason to use it, even if you would want to use it as a cache, there are far better solutions.
[1] https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-read...
Regarding the Mongo, the second link I provided was comparing WiredTiger. In link you provided they made an improvement, but 2.x was already slow. Initially MongoDB gained its popularity because it supposed to be fast. And it was when it did not worry about making sure the written data ends up on disk. Once people started losing data when system crashed they changed defaults, but then became much slower. And worst of all it still was losing data[1]. There's no good reason to use it, even if you would want to use it as a cache, there are far better solutions.
[1] https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-read...
> HBase is not ACID and therefore not strongly consistent. The consistency in CAP is different than consistency in ACID.
You're arguing the word "consistent" is used wrong, and then you go on to mixing ACID and CAP — two acronyms which use the word "consistent" with completely different meanings.
The "C" in ACID stands for data consistency, which is that you shouldn't be allowed to store any data which isn't internally consistent with the schema's consistency rules. For example, you're not allowed to store a "null" value in a "not null" column, and you can't delete a row that is pointed to by a different table's foreign key constraint.
The "C" in Brewer's original CAP theorem really represents atomic consistency. It's the idea that if you submit data, you should get the exact same data back, even if you ask a different node. It sort of represents both the "A" (atomicity, in the sense that either everything or nothing is updated) in ACID, as well as the "I" (isolation, in the sense that nodes don't see partial writes) and "D" (durability, in the sense that data written stays written).
In CAP terms, HBase is strongly consistent, I believe, as long as you query the primary region server and not a replica. But HBase is not transactional, and therefore it's pretty meaningless to talk about ACID.
You're arguing the word "consistent" is used wrong, and then you go on to mixing ACID and CAP — two acronyms which use the word "consistent" with completely different meanings.
The "C" in ACID stands for data consistency, which is that you shouldn't be allowed to store any data which isn't internally consistent with the schema's consistency rules. For example, you're not allowed to store a "null" value in a "not null" column, and you can't delete a row that is pointed to by a different table's foreign key constraint.
The "C" in Brewer's original CAP theorem really represents atomic consistency. It's the idea that if you submit data, you should get the exact same data back, even if you ask a different node. It sort of represents both the "A" (atomicity, in the sense that either everything or nothing is updated) in ACID, as well as the "I" (isolation, in the sense that nodes don't see partial writes) and "D" (durability, in the sense that data written stays written).
In CAP terms, HBase is strongly consistent, I believe, as long as you query the primary region server and not a replica. But HBase is not transactional, and therefore it's pretty meaningless to talk about ACID.
As mentioned I think you are confused about what consistent means.
By the ACID definition a database can either be consistent or not. It can't sometimes be one or the other (unless there is some bug. I was referring to the CAP definition which I assume is what you are referring to. And by that definition HBase is absolutely strong consistent.
And that second link was pretty bad (with all respect to the great guys over at Datastax). It is benchmarking a release candidate build of MongoDB (2.8.0-rc4) where quite a number of important fixes went in before GM. They should have just waited until 3.0 was released.
Also it would be good for that Call Me Maybe test to be redone considering that stale-read bug has been fixed:
https://jira.mongodb.org/browse/SERVER-18022
By the ACID definition a database can either be consistent or not. It can't sometimes be one or the other (unless there is some bug. I was referring to the CAP definition which I assume is what you are referring to. And by that definition HBase is absolutely strong consistent.
And that second link was pretty bad (with all respect to the great guys over at Datastax). It is benchmarking a release candidate build of MongoDB (2.8.0-rc4) where quite a number of important fixes went in before GM. They should have just waited until 3.0 was released.
Also it would be good for that Call Me Maybe test to be redone considering that stale-read bug has been fixed:
https://jira.mongodb.org/browse/SERVER-18022
> It is not about that Relational (SQL) database was designed in a way that is hard to make it distributed
Not entirely accurate. Relational database schemas are typically normalized into many tables and make heavy use of joins; it's fair to say that the concept of joins are integral the relational model. But joins, by definition, require fast access to data; it's extremely inefficient to join across network boundaries.
So-called NoSQL databases aren't just about ACID or CAP, but also about denormalization. In fact, I would go so far as to say that the single founding tenet of all of "NoSQL" was denormalization across multiple nodes. Google's Bigtable (on which HBase is based, and which arguably started the public's interest in "NoSQL") depends on extreme denormalization in its data model‚ which does away with tables and instead treats everything as columns, and also discards referential integrity (the "C" in ACID, which isn't the same as in CAP) and isolation (the "I"). Sharding flows naturally out of denormalization. Eventual consistency came later with Amazon's Dynamo paper; Bigtable was strongly consistent.
A consequence of denormalization is that you can hit any database node and get consistent performance. No query will ever get bogged down in complicated, long-running nested loops and hash joins because the mechanisms don't exist. It creates an even playing field for queries because they're mostly individual key lookups; the result is (in theory) predictable, stable performance across the cluster. The complexity of join logic that was in the relational database is moved into the client, which gets the burden of untangling data. Which is why Google also invented early solutions like MapReduce and Sawzall, because once you have a huge, denormalized, distributed cluster of data in this poor man's relational model, your individual clients can no longer access the data efficiently. So denormalization also leads to parallel computation, and especially precomputation, rather than the ad-hoc queries that relational databases are good at.
It's all about scale, and fitting the data model to work in your favour at that scale.
So yeah, I would definitely say that relational databases are designed in a way that make them hard to distribute. The above is just the tip of the iceberg. How do you enforce referential integrity (foreign keys, etc.) across nodes? How do you assign unique primary keys? You talk about making tradeoffs, but would you really want a distributed relational database without distributed transactions?
Can you implement a performant distributed database with joins and other relational features? Yes. VoltDB and Vertica come to mind. They are designed from the ground up to do this, and have complicated logic for partitioning queries. They make other tradeoffs: for example, VoltDB is in-memory and to perform well, all transactional access code has to be written as a "stored procedure", a Java JAR file that you upload to the server, and transactions are expected to be very short-lived. But they will, pretty much by definition, not perform at scale the way that "NoSQL" databases are designed to.
Not entirely accurate. Relational database schemas are typically normalized into many tables and make heavy use of joins; it's fair to say that the concept of joins are integral the relational model. But joins, by definition, require fast access to data; it's extremely inefficient to join across network boundaries.
So-called NoSQL databases aren't just about ACID or CAP, but also about denormalization. In fact, I would go so far as to say that the single founding tenet of all of "NoSQL" was denormalization across multiple nodes. Google's Bigtable (on which HBase is based, and which arguably started the public's interest in "NoSQL") depends on extreme denormalization in its data model‚ which does away with tables and instead treats everything as columns, and also discards referential integrity (the "C" in ACID, which isn't the same as in CAP) and isolation (the "I"). Sharding flows naturally out of denormalization. Eventual consistency came later with Amazon's Dynamo paper; Bigtable was strongly consistent.
A consequence of denormalization is that you can hit any database node and get consistent performance. No query will ever get bogged down in complicated, long-running nested loops and hash joins because the mechanisms don't exist. It creates an even playing field for queries because they're mostly individual key lookups; the result is (in theory) predictable, stable performance across the cluster. The complexity of join logic that was in the relational database is moved into the client, which gets the burden of untangling data. Which is why Google also invented early solutions like MapReduce and Sawzall, because once you have a huge, denormalized, distributed cluster of data in this poor man's relational model, your individual clients can no longer access the data efficiently. So denormalization also leads to parallel computation, and especially precomputation, rather than the ad-hoc queries that relational databases are good at.
It's all about scale, and fitting the data model to work in your favour at that scale.
So yeah, I would definitely say that relational databases are designed in a way that make them hard to distribute. The above is just the tip of the iceberg. How do you enforce referential integrity (foreign keys, etc.) across nodes? How do you assign unique primary keys? You talk about making tradeoffs, but would you really want a distributed relational database without distributed transactions?
Can you implement a performant distributed database with joins and other relational features? Yes. VoltDB and Vertica come to mind. They are designed from the ground up to do this, and have complicated logic for partitioning queries. They make other tradeoffs: for example, VoltDB is in-memory and to perform well, all transactional access code has to be written as a "stored procedure", a Java JAR file that you upload to the server, and transactions are expected to be very short-lived. But they will, pretty much by definition, not perform at scale the way that "NoSQL" databases are designed to.
Take a look at MemSQL - a much better distributed relational database with regular mysql protocol access.
Thanks, but I'm not going to consider a database that's not open source, sorry.
For replication instances run in a master-replica setup using Streaming Replication try:
http://www.repmgr.org/
repmgr is an open-source tool suite to manage replication and failover in a cluster of PostgreSQL servers. It enhances PostgreSQL's built-in hot-standby capabilities with tools to set up standby servers, monitor replication, and perform administrative tasks such as failover or manual switchover operations.
repmgr has provided advanced support for PostgreSQL's built-in replication mechanisms since they were introduced in 9.0, and repmgr 2.0 supports all PostgreSQL versions from 9.0 to 9.4.
https://github.com/2ndQuadrant/repmgr src: http://instagram-engineering.tumblr.com/post/13649370142/wha...
For Sharding try:
http://instagram-engineering.tumblr.com/post/10853187575/sha... http://rob.conery.io/2014/05/29/a-better-id-generator-for-po...
For clustering try:
https://github.com/smbambling/pgsql_ha_cluster/wiki/Building... https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
I too wish there existed a nice dashboard db management tool like the ones Rethinkdb and Couchbase provide out of the box.
http://www.repmgr.org/
repmgr is an open-source tool suite to manage replication and failover in a cluster of PostgreSQL servers. It enhances PostgreSQL's built-in hot-standby capabilities with tools to set up standby servers, monitor replication, and perform administrative tasks such as failover or manual switchover operations.
repmgr has provided advanced support for PostgreSQL's built-in replication mechanisms since they were introduced in 9.0, and repmgr 2.0 supports all PostgreSQL versions from 9.0 to 9.4.
https://github.com/2ndQuadrant/repmgr src: http://instagram-engineering.tumblr.com/post/13649370142/wha...
For Sharding try:
http://instagram-engineering.tumblr.com/post/10853187575/sha... http://rob.conery.io/2014/05/29/a-better-id-generator-for-po...
For clustering try:
https://github.com/smbambling/pgsql_ha_cluster/wiki/Building... https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
I too wish there existed a nice dashboard db management tool like the ones Rethinkdb and Couchbase provide out of the box.
One of the risks here is that it's very hard to ascertain the support situation around some of these third party products.
If you design your application to communicate to your database through a certain sharding solution, and the you find that sharding product becomes abandoned, you can be in a very difficult position.
That wiki lists at least one product as "stalled".
If I run into a Postgresql bug, am I going to get told "we have no way of debugging that with your third party clustering extension in place"?
These are all obstacles that can be overcome, but just jumping on a random clustering project should be done with caution.
As a related part of clustering, I'd love to be able to do downtime free version upgrades like in Oracle - it would remove one of the major reasons for clustering.
If you design your application to communicate to your database through a certain sharding solution, and the you find that sharding product becomes abandoned, you can be in a very difficult position.
That wiki lists at least one product as "stalled".
If I run into a Postgresql bug, am I going to get told "we have no way of debugging that with your third party clustering extension in place"?
These are all obstacles that can be overcome, but just jumping on a random clustering project should be done with caution.
As a related part of clustering, I'd love to be able to do downtime free version upgrades like in Oracle - it would remove one of the major reasons for clustering.
PostgreSQL is a pretty solid platform for, and you can reason about it much like any piece of complex-but-coherent software. In practice, we debug and diagnose it right along with 3rd party extensions. Think about it like debugging a kernel with apps running. You don't get a "clean" case, you have to make it work for all cases cleanly.
For sure. One line FT with auto fail over would make PG so much more useful for me. I can't have a DB be a SPOF in an app. But the current PGpool etc situation is not ideal.
When I worked for a loan processor, they were quite happy running over $100k/hr revenue through a single instance of PostgreSQL. There were slaves to offload analytic and report loads but all transactions went through a "SPOF" and we just manned up to keep that database running. Don't tell me "real apps" are all that redundant and bullet-proof. Designing and operating redundant infrastructure is much harder and costlier than most make it out to be.
Most people don't get this and they over-engineer the f#$%k out of everything.
I am not even talking about sharding. I am talking about automatic failover.
Which you can do, but it takes pgpool + VIP trickery, and is a lot of setup for a small project. Hosted from AWS or similar is a lot better as they do the crazy setup, but not for local setup. Nice Digital Ocean finally got floating IPs this week which should help there (have not looked into if it will work for PG failover).
Which you can do, but it takes pgpool + VIP trickery, and is a lot of setup for a small project. Hosted from AWS or similar is a lot better as they do the crazy setup, but not for local setup. Nice Digital Ocean finally got floating IPs this week which should help there (have not looked into if it will work for PG failover).
The thing is, if your database goes down, it likely needs to stay down until you understand why it went down and whether it's business-safe to turn it back. PostgreSQL almost does not crash. And when it does, you should care about your system's stability, and a crash is a giant message about stability.
Plan for uptime and you'll be more likely to get it, than planning around a buggy server process.
(Also, in this case, it would have violated SOX and PCI rules, as well as general sanity, to host a 512GB 64-core server in the cloud)
Plan for uptime and you'll be more likely to get it, than planning around a buggy server process.
(Also, in this case, it would have violated SOX and PCI rules, as well as general sanity, to host a 512GB 64-core server in the cloud)
I'm not sure the floating ip solution from digitalocean is going to be the solution - as it looks like it's only for public ip addresses instead of internal ips... Assuming you only want you db listening on internal ip right?
yah so not perfect for sure. With how PG permissions work I am not sure you lose a lot listening on public interface vs digital ocean private but really semi-private interface.
yeah. So I haven't set up a postgres or mysql server in more than five years, but I was seriously into both before that. In the early to mid-aughts, I was heavily into PostgreSQL when I had a choice, but spent a lot of time on MySQL when other people did the deciding.
So last week I was setting up a sql server for a side project I was doing with a friend, and went with MySQL just because I knew it would take me ten minutes to setup async replication, whereas with PostgreSQL, I'd have to spend a bunch of time figuring out what third party thing to use now.
Even just having simple master/slave asynchronous replication functionality built in would help a lot.
So last week I was setting up a sql server for a side project I was doing with a friend, and went with MySQL just because I knew it would take me ten minutes to setup async replication, whereas with PostgreSQL, I'd have to spend a bunch of time figuring out what third party thing to use now.
Even just having simple master/slave asynchronous replication functionality built in would help a lot.
> Even just having simple master/slave asynchronous replication functionality built in would help a lot.
Check out [0], or the docs on the same topic for the latest version at [1]. It looks like built-in async master/slave replication has been available since at least 2010-09-20, in Postgres 9.0.
[0] http://www.postgresql.org/docs/9.1/static/high-availability....
[1] http://www.postgresql.org/docs/9.4/static/high-availability....
Check out [0], or the docs on the same topic for the latest version at [1]. It looks like built-in async master/slave replication has been available since at least 2010-09-20, in Postgres 9.0.
[0] http://www.postgresql.org/docs/9.1/static/high-availability....
[1] http://www.postgresql.org/docs/9.4/static/high-availability....
I've recently gotten into deploying postgres with replication and the support for async or mostly async with one "floating" synchronous replica on the current release is pretty solid.
There are some parts I'd like to see smoother like checking replication status without DB superuser creds, but the general built-in replication seems fine.
There are some parts I'd like to see smoother like checking replication status without DB superuser creds, but the general built-in replication seems fine.
Postgres has built in replication since 9.1
Actually since 9.0, but the addition of pg_basebackup in 9.1 made creating the slave easier.
[deleted]
Having worked with MySQL for years, my mind was blown using Postgres for the first time this year (mainly due to HStore, CTEs, and transactional DDL statements). However, one constant pain point for me was the lack of an upsert. Glad to read Postgres will be getting it in 9.5!
I wrote an upsert generator a long time ago. It was one of the easier things to work around.
I have seen quite many broken home-rolled UPSERT implementations, so it is apparently not that easy to get it right. Still not very hard though once you have understood the potential problems involved.
Alot of the difficulty was explaining to others that there wasn't an easy way to upsert, and trying to ensure everyone did it the proper way.
In OSS communities it's a serious problem. At work, it was routine: this is yet another thing that can be more easily screwed up if you don't use a library solution we've provided you.
Part I of the series: https://www.compose.io/articles/what-postgresql-has-over-oth...
I needed to implement tagging a while ago, particularly looking up a row with multiple tags. As far as I know, the main way to do this is usually to put the tags in another table and join multiple times (once per tag) to find a row that has them all. This performed rather badly (100s of ms for 5 tags from memory).
But with Postgres, you can put a GIN index on an Array column. So I moved my tags into an array column and suddenly querying for every one of several million rows that had matched a set of 7 tags took ~10ms.
Postgres is awesome.
But with Postgres, you can put a GIN index on an Array column. So I moved my tags into an array column and suddenly querying for every one of several million rows that had matched a set of 7 tags took ~10ms.
Postgres is awesome.
I don't know much about SQL, but are you sure you need multiple joins for that?
I'd do something like this (psuedocode) on the table with the tags:
I'd do something like this (psuedocode) on the table with the tags:
SELECT foreign-id, COUNT(*)
FROM Tags
WHERE tag IN ('foo', 'bar', 'baz')
GROUP BY foreign-id
HAVING COUNT(*) = 3
Hmm, I don't know much about it either and it looks like you're right. I don't believe that querying it is nearly as fast as querying a GIN index (or as easy) still.
With Postgres I just do this and it gives me a result in a couple ms:
With Postgres I just do this and it gives me a result in a couple ms:
SELECT * FROM things WHERE ARRAY['tag1', 'tag2'] <@ tags;Re GUI clients - I've tried most purchased several - I agree pgAdmin is quite clunky and bit ugly also crashes regularly on Mac but not windows in my experience, it's main problem is the query editor is so weak. I don't understand why Postico has so many positive comments, I bought it and was very disappointed, yes it looks pretty but its capability is pathetic, much weaker than pgAdmin, I was hoping its query editor would be better but this is weak also, does not even give line numbers when reporting errors so useless for large queries I often run.
I've found I great solution though - Sublime Text an amazing this awesome query editor lighting fast - rock solid loads of great plugins for sql and postgres, auto complete, snippits, the search and replace ability alone makes it worth using compared to pgAdmin.
The build system makes it easy to execute queries inside ST, I used to copy paste queries from Notepad++ into PgAdmin years ago before I discovered the joys of direct execution and immediate feedback possible in Sublime Text.
See this link for details on how to set up - only takes a few minutes http://blog.code4hire.com/2014/04/sublime-text-psql-build-sy...
Have you tried DBeaver (http://dbeaver.jkiss.org/)?
pgAdmin is unfortunately unusable on a Linux desktop with high pixel count.
The GUI support for the system DPI setting is very haphazard and leads to problems like query results where the line height of the text respects the setting but the height of the cell itself doesn't so you only see the middle two quarters of each glyph.
The GUI support for the system DPI setting is very haphazard and leads to problems like query results where the line height of the text respects the setting but the height of the cell itself doesn't so you only see the middle two quarters of each glyph.
Postgres has come a long way. Some of these features are very cool, like the WITH RECURSIVE. I wonder what's the performance implication is. Often you can gauge the performance by looking at the query, like a table scan or using index. Assuming the parent topic column is indexed, would the recursive walk take INDEX + SCAN? Where the INDEX is the parent topic look up and the SCAN is the aggregate look up of the subtopic.
Is the information provided by
explain select ...
I absolutely love Postgres, for these and other reasons, but the one weak point is the official GUI client. It's garbage, and unfortunately it's one of the first things people ask me about when I encourage them to use Postgres.
I had the same sentiment. I eventually ditched the gui and went back to psql (the CLI) based on this advice: http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos...
The CLI is really really good for a CLI. Wagon is also good, my coworkers have been using it to pull data and like it a lot.
Overall love postgres, it's been 6 months (since starting a new job) and I'll never go back.
The CLI is really really good for a CLI. Wagon is also good, my coworkers have been using it to pull data and like it a lot.
Overall love postgres, it's been 6 months (since starting a new job) and I'll never go back.
http://dbeaver.jkiss.org works pretty well for me
This looks awesome! Are you able to use the jsonb data types through this?
I'm not sure as I'm not started playing with Jsonb yet! but give it a try.
I never heard of this and I was looking for something like this for ages. Thank you!
I just bought Postico. Best Postgres client I've come across so far for OSX. Would recommend if you're on a mac.
I just purchased Postico a few days ago. It's already money well spent. Obviously Postico needs time to ripen, but it's already been a huge productivity boost.
How does it compare to Sequel Pro for MySQL?
Sequel Pro for MySQL is better because it's a more mature product. I've been using it for a while so there is a little familiarity bias.
I have also paid for Postico (before they launched on the Mac App Store). As far as Postgres clients go, it is the best.
Edit: When evaluating Postgres GUI clients, make sure they support the new JSON and JSONB data types.
I have also paid for Postico (before they launched on the Mac App Store). As far as Postgres clients go, it is the best.
Edit: When evaluating Postgres GUI clients, make sure they support the new JSON and JSONB data types.
For Windows users, HeidiSQL is a very solid open source client for interacting with Postgres.
I've been using Valentina Studio lately and like it so far. Postico looks good too.
I use Navicat, its fantastic.
I've been a long-time Navicat user, but it has a lot of trouble with any Postgres-specific features. I kind of recommend Postico, even in its incomplete state, for wrangling Postgres.
Use JetBrains 0xDBE -- still available now as early pre-release for free.
This article and the previous one are interesting but it's just highlighting features Postgres has that MySQL/MariaDB don't have. I was expecting a "fair" comparison and that's not what these are.
> [The article is] just highlighting features Postgres has that MySQL/MariaDB don't have. [That's not a fair comparison.]
I disagree. If one project has a significant, useful feature that another project does not, it behooves someone who is comparing the projects to mention this fact.
I disagree. If one project has a significant, useful feature that another project does not, it behooves someone who is comparing the projects to mention this fact.
Isn't that the GP's point? The article elides the reverse case, features that postgres lacks but MySQL/MariaDB have. "It behooves someone who is comparing the projects to mention this fact."
A few things:
* The article's title is "What PostgreSQL has over other open source SQL databases: Part II". It's PostgreSQL focused, and part 2 in an N-part series.
* When a similar feature appears in MySQL, MariaDB, or Firebird, the author makes mention of it, its limitations, and provides a brief comparison of the implementation differences and/or limitations of that feature across databases.
Given that the usual form of these sorts of article series is to have a one-half or one article feature comparison in the reverse direction, and given that the author takes the time to briefly call out details of other DB's implementations of a given feature -rather than just proclaiming that "POSTGRES IS THE BESTEST!!1!"- I look askance at ebbv's claim that the article is making unfair comparisons.
* The article's title is "What PostgreSQL has over other open source SQL databases: Part II". It's PostgreSQL focused, and part 2 in an N-part series.
* When a similar feature appears in MySQL, MariaDB, or Firebird, the author makes mention of it, its limitations, and provides a brief comparison of the implementation differences and/or limitations of that feature across databases.
Given that the usual form of these sorts of article series is to have a one-half or one article feature comparison in the reverse direction, and given that the author takes the time to briefly call out details of other DB's implementations of a given feature -rather than just proclaiming that "POSTGRES IS THE BESTEST!!1!"- I look askance at ebbv's claim that the article is making unfair comparisons.
It depends on what your definition of fair is, that's why I had it in quotes. Clearly you think it's fair to just focus on features you specifically are interested in.
My implication was that I don't think it is. I think you have to highlight the differences between two things and then based on those differences, explain why you think one is best. If all you do is highlight the things you like about one without talking about where it differs from the other, then you're presenting a biased view, IMHO.
My implication was that I don't think it is. I think you have to highlight the differences between two things and then based on those differences, explain why you think one is best. If all you do is highlight the things you like about one without talking about where it differs from the other, then you're presenting a biased view, IMHO.
If it's a feature comparison and MySQL doesn't even support some features, MySQL is the loser in that case. It's like saying we're going to race, and you don't even have legs. It's not unfair if you show up to the race with streamers on, claiming you're a viable candidate all the way there.
But that's my point. MySQL also has features that Postgres doesn't support. These articles don't bring those up. It seems like you have illustrated my point perfectly; because these articles don't bring up the features MySQL has that Postgres lacks, you think it's a one sided issue.
What features MySQL has that PostgreSQL lacks?
Not a huge number, as of Postgres 9.5 (which isn't out yet, to be fair, but I think the only thing it adds that MySQL has already is upsert). Easy-to-configure failover is the obvious one. Index-organized tables are another (it doesn't have non-index-organized tables, though :P). It also uses direct I/O instead of going through the OS, so it can be considerably faster for some types of queries in InnoDB (I've seen over 2x better performance for something like raw insert). It also has a pluggable storage engine (though Postgres sorta does too, in the sense of multiple backends, most of its functionality is closely tied to the default one). InnoDB's MVCC implementation is different from Postgres's so it probably has advantages in some situations (though I don't know what those might be). It uses a thread per connection, rather than a process, and doesn't have to allocate the majority of the per-session memory up front, so it can support more connections than Postgres by default (aka without using some third-party tool like pgbouncer to create a connection pool).
There's probably more stuff? However, Postgres has a ton of other features that weren't mentioned in the article... I think the general response from MySQL developers on this topic is that in practice companies don't want all the features Postgres has. I can't comment on the general developer, but I can say that for me virtually all of its features have been useful. And BTW, the list of "stuff MySQL has but not Postgres" used to be a lot longer.
I'd be happy for people more experienced with MySQL to correct me or add anything I was missing. But I think in general the notion that this article is super biased and MySQL has a ton of stuff Postgres doesn't is incorrect.
There's probably more stuff? However, Postgres has a ton of other features that weren't mentioned in the article... I think the general response from MySQL developers on this topic is that in practice companies don't want all the features Postgres has. I can't comment on the general developer, but I can say that for me virtually all of its features have been useful. And BTW, the list of "stuff MySQL has but not Postgres" used to be a lot longer.
I'd be happy for people more experienced with MySQL to correct me or add anything I was missing. But I think in general the notion that this article is super biased and MySQL has a ton of stuff Postgres doesn't is incorrect.
That's a lot of things.
PostgreSQL has had index organized tables (aka clustered indexes) for a long time.
I can't consider a pluggable storage engine a feature. Databases are about little except reliable storage. From memory, the only time pluggable storage drivers affected me is when I realized MyISAM was inadequate and I should have used InnoDB in the first place.
Show me meaningful benchmarks about this 2x performance advantage. Is this raw insert comparable to a local copy with triggers/constraints deferred? Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. My guess is you are unfamiliar with the PostgreSQL-equivalent functionality in this case.
Please don't assert "different so probably better" about MVCC implementations. That's far from rational.
Thread vs process per connection is an artifact of design. More connections don't get you more throughput, rather just consume more RAM. I'm not seeing a feature in this. Real applications pool connections whether in-app or using pgBouncer.
I don't know how you get a "general response" that people don't want "all the features" that PostgreSQL has. I've yet to find an organization using a database that doesn't experience a nearly constant feature demand as they integrate what they have and seek the next level for their operation.
I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
PostgreSQL has had index organized tables (aka clustered indexes) for a long time.
I can't consider a pluggable storage engine a feature. Databases are about little except reliable storage. From memory, the only time pluggable storage drivers affected me is when I realized MyISAM was inadequate and I should have used InnoDB in the first place.
Show me meaningful benchmarks about this 2x performance advantage. Is this raw insert comparable to a local copy with triggers/constraints deferred? Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores. My guess is you are unfamiliar with the PostgreSQL-equivalent functionality in this case.
Please don't assert "different so probably better" about MVCC implementations. That's far from rational.
Thread vs process per connection is an artifact of design. More connections don't get you more throughput, rather just consume more RAM. I'm not seeing a feature in this. Real applications pool connections whether in-app or using pgBouncer.
I don't know how you get a "general response" that people don't want "all the features" that PostgreSQL has. I've yet to find an organization using a database that doesn't experience a nearly constant feature demand as they integrate what they have and seek the next level for their operation.
I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
> PostgreSQL has had index organized tables (aka clustered indexes) for a long time.
Index ordered and clustered tables are different. Postgres' CLUSTER command simply rebuilds the the index after sorting the underlying data, in a on-off, blocking, operation. That means further operations will slowly reduce the "sortedness" and that it can't be relied upon when correctness is relevant (e.g. to skip a sort step for an ORDER BY).
Real index ordered tables would be nice, but I don't see them in the near future.
> Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores.
I'm not aware of any really low-hanging fruits that we know of. While I'd like to improve INSERT performance a bit more, I think at the moment real bottlenecks are elsewhere. I mean on a halfway good single server I can do ~240k INSERTS/sec, without pipelining and about 1100k with. That's not exactly nothing. With COPY instead of INSERTs you can do a lot more.
I think write performance can be a improved a fair bit, but it's not the low hanging fruit level anymore. The biggest things I know are 1) replacing the buffer mapping hash table with a lock free datastructure (radix tree is what I/we are experimenting with) 2) better cache replacement implementation, suitable for very large memory sizes with a high turnover 3) make buffer pins lock free (patch exists) 4) make relation extension scale better
> I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
I wish we could all use a bit less adversarial tone in these kinds of discussions. Mysql does some things better. Postgres some others. For some others it's not yet clear which direction is better. Of course I personally prefer PostgreSQL, but that shouldn't make me blind that they got some things right that we didn't.
EDIT: Updated throughput number from 800k to 1.1 mio.
Index ordered and clustered tables are different. Postgres' CLUSTER command simply rebuilds the the index after sorting the underlying data, in a on-off, blocking, operation. That means further operations will slowly reduce the "sortedness" and that it can't be relied upon when correctness is relevant (e.g. to skip a sort step for an ORDER BY).
Real index ordered tables would be nice, but I don't see them in the near future.
> Odds are poor that there is such low-hanging fruit the PostgreSQL team simply ignores.
I'm not aware of any really low-hanging fruits that we know of. While I'd like to improve INSERT performance a bit more, I think at the moment real bottlenecks are elsewhere. I mean on a halfway good single server I can do ~240k INSERTS/sec, without pipelining and about 1100k with. That's not exactly nothing. With COPY instead of INSERTs you can do a lot more.
I think write performance can be a improved a fair bit, but it's not the low hanging fruit level anymore. The biggest things I know are 1) replacing the buffer mapping hash table with a lock free datastructure (radix tree is what I/we are experimenting with) 2) better cache replacement implementation, suitable for very large memory sizes with a high turnover 3) make buffer pins lock free (patch exists) 4) make relation extension scale better
> I'm thinking the article is accurate, even if it seems gloating to MySQL fans.
I wish we could all use a bit less adversarial tone in these kinds of discussions. Mysql does some things better. Postgres some others. For some others it's not yet clear which direction is better. Of course I personally prefer PostgreSQL, but that shouldn't make me blind that they got some things right that we didn't.
EDIT: Updated throughput number from 800k to 1.1 mio.
Just FYI, I don't primarily use MySQL and I'm much more familiar with Postgres. I don't think you read my post charitably (or correctly, in some cases: for instance, I didn't say InnoDB's MVCC implementation was better in general, just that it seemed unlikely that Postgres's is better in all cases). I listed everything I am aware of, feature-wise, that might put MySQL over Postgres for some people. I could write a far longer post in the reverse direction.
> This article and the previous one are interesting but it's just highlighting features Postgres has that MySQL/MariaDB don't have.
That's exactly what the title says.
> I was expecting a "fair" comparison and that's not what these are.
Assuming by a "'fair' comparison" you mean "an article laying out the relative advantages and disadvantages of Postrgres and other open source databases", how on earth is that even a remotely reasonable expectation of an article titled "What PostgreSQL has over other open source SQL databases".
Its like walking into a clearly-labeled strip club and complaining that you expected fully-clothed, family-friendly entertainment.
That's exactly what the title says.
> I was expecting a "fair" comparison and that's not what these are.
Assuming by a "'fair' comparison" you mean "an article laying out the relative advantages and disadvantages of Postrgres and other open source databases", how on earth is that even a remotely reasonable expectation of an article titled "What PostgreSQL has over other open source SQL databases".
Its like walking into a clearly-labeled strip club and complaining that you expected fully-clothed, family-friendly entertainment.
Agreed. The article implies that PostgreSQL has everything the other open-source databases have, plus these additional hot features. If that's true, the author should make it explicit.
> The article implies that...
The world would be a far better place if fewer people would read between the lines of a given statement, and more people would make a habit of carefully parsing statements that others make.
When one writes words for general consumption, it's oh so tiresome to front-load one's writing with a bevy of disclaimers and preemptive clarifications. You will inevitably fail to include one clause or another, causing someone, somewhere to successfully misunderstand your words.
It's better to write exactly what you mean, as clearly as you are able, and -when writing for capable adults- trust that your audience is capable adults who know how to read what has been written and know not to infer meaning in inappropriate places.
The world would be a far better place if fewer people would read between the lines of a given statement, and more people would make a habit of carefully parsing statements that others make.
When one writes words for general consumption, it's oh so tiresome to front-load one's writing with a bevy of disclaimers and preemptive clarifications. You will inevitably fail to include one clause or another, causing someone, somewhere to successfully misunderstand your words.
It's better to write exactly what you mean, as clearly as you are able, and -when writing for capable adults- trust that your audience is capable adults who know how to read what has been written and know not to infer meaning in inappropriate places.
Good article, but according to the post Title, I'd like to read how each features make postgres better than mysql or other, in terms of performance for example.
Nice to have a list of unique features of postgres but having many indexing feature doesn't mean to be better than other dbs.
P.s. Postgres is my favourite db
P.s. Postgres is my favourite db
Many features supported by PostgreSQL but not by MySQL are the features that make PostgreSQL better for many purposes. I think you should compare the suitability of the database to the task, not databases to databases.
Having many indexing features allows PostgreSQL to be useful for more types of users. Many of the indexing features are primarily useful for the GIS community, where they are used by PostGIS. BRIN indexes on the other hand will primarily help for data warehousing workloads by trading performance for reduced disk space.
And GIN indexes allows fast indexing of JSON documents and for PostgreSQL's full-text search (mostly useful for quickly adding search to simple applications).
And GIN indexes allows fast indexing of JSON documents and for PostgreSQL's full-text search (mostly useful for quickly adding search to simple applications).
[deleted]
1) Its not MySQL
Cool still going to use the best tool for the job.