8 ms·

What PostgreSQL has over other open source SQL databases: Part II

278 points by postila 11y ago

halayli 11y ago
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.
- lsc 11y ago
  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.
  - tacotuesday 11y ago
    Postgres has built in replication since 9.1
    - jeltz 11y ago
      Actually since 9.0, but the addition of pg_basebackup in 9.1 made creating the slave easier.
  - deleted 11y ago
    [deleted]
  - simoncion 11y ago
    > 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.html http://www.postgresql.org/docs/9.1/static/high-availability.... [1] http://www.postgresql.org/docs/9.4/static/high-availability.html http://www.postgresql.org/docs/9.4/static/high-availability....
    - billhathaway 11y ago
      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.
    - lsc 11y ago
      Sweet! thank you. that's just what I wanted.
      - simoncion 11y ago
        No problem! Good luck with your projects! :D
- rdtsc 11y ago
  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.
  - takeda 11y ago
    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-linster/postgres-outperforms-mongodb-and-ushers-new-developer-reality http://www.enterprisedb.com/postgres-plus-edb-blog/marc-lins... [2] Scaling out: http://www.datastax.com/wp-content/themes/datastax-2014-08/files/NoSQL_Benchmarks_EndPoint.pdf http://www.datastax.com/wp-content/themes/datastax-2014-08/f...
    - threeseed 11y ago
      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-mongodb-30-part-1-throughput-improvements-measured-ycsb https://www.mongodb.com/blog/post/performance-testing-mongod...
- brianwawok 11y ago
  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.
  - spacemanmatt 11y ago
    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.
    - brianwawok 11y ago
      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).
      - taf2 11y ago
        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?
        brianwawok 11y ago
        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.
      - spacemanmatt 11y ago
- intellectable 11y ago
  For replication instances run in a master-replica setup using Streaming Replication try: http://www.repmgr.org/ 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 https://github.com/2ndQuadrant/repmgr src: http://instagram-engineering.tumblr.com/post/13649370142/what-powers-instagram-hundreds-of-instances http://instagram-engineering.tumblr.com/post/13649370142/wha... For Sharding try: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/ 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-A-Highly-Available-Multi-Node-PostgreSQL-Cluster https://github.com/smbambling/pgsql_ha_cluster/wiki/Building... https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling 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.
  - technion 11y ago
    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.
    - 11y ago
- lobster_johnson 11y ago
  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 https://github.com/postgres-x2/postgres-x2 [2] http://www.postgres-xl.org/ http://www.postgres-xl.org/ [3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summary http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...
  - jacques_chester 11y ago
    > 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.
    - cmrdporcupine 11y ago
      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?
jtwebman 11y ago
Cool still going to use the best tool for the job.
InfiniteEntropy 11y ago
1) Its not MySQL
djrobstep 11y ago
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.
- DevX101 11y ago
  I just bought Postico. Best Postgres client I've come across so far for OSX. Would recommend if you're on a mac.
  - leesalminen 11y ago
    How does it compare to Sequel Pro for MySQL?
    - putlake 11y ago
      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.
  - devbug 11y ago
    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.
- phaedryx 11y ago
  I've been using Valentina Studio lately and like it so far. Postico looks good too.
- saltedshiv 11y ago
  I use Navicat, its fantastic.
resca79 11y ago
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
- jeltz 11y ago
  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).
- spacemanmatt 11y ago
  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.
ww520 11y ago
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.
- simoncion 11y ago
  Is the information provided by explain select ... insufficient?
  - brobinson 11y ago
    There's also EXPLAIN ANALYZE which may yield different output than just an EXPLAIN.
    - mazelife 11y ago
      Or EXPLAIN (ANALYZE, BUFFERS), which is very useful when you suspect disk IO may be a choke point.
      - brobinson 11y ago
        Didn't know about BUFFERS. Thanks!
ebbv 11y ago
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.
- simoncion 11y ago
  > [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.
  - mappu 11y ago
    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."
    - simoncion 11y ago
      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.
  - ebbv 11y ago
    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.
deleted 11y ago
[deleted]
chrisutz 11y ago
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!
- spacemanmatt 11y ago
  I wrote an upsert generator a long time ago. It was one of the easier things to work around.
  - jeltz 11y ago
    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.
  - chrisutz 11y ago
    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.
    - spacemanmatt 11y ago
      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.
chanux 11y ago
Part I of the series: https://www.compose.io/articles/what-postgresql-has-over-other-open-source-sql-databases/ https://www.compose.io/articles/what-postgresql-has-over-oth...
garyclarke27 11y ago
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-system/ http://blog.code4hire.com/2014/04/sublime-text-psql-build-sy...
- chucksmash 11y ago
  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.
- dorfsmay 11y ago
  Have you tried DBeaver (http://dbeaver.jkiss.org/ http://dbeaver.jkiss.org/)?
Veratyr 11y ago
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.
- b3n 11y ago
  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: SELECT foreign-id, COUNT(*) FROM Tags WHERE tag IN ('foo', 'bar', 'baz') GROUP BY foreign-id HAVING COUNT(*) = 3 It does seem rather hacky though.
  - Veratyr 11y ago
    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: SELECT * FROM things WHERE ARRAY['tag1', 'tag2'] <@ tags;

278 points by postila 11y ago

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.

lsc 11y ago
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.
- tacotuesday 11y ago
  Postgres has built in replication since 9.1
  - jeltz 11y ago
    Actually since 9.0, but the addition of pg_basebackup in 9.1 made creating the slave easier.
- deleted 11y ago
  [deleted]
- simoncion 11y ago
  > 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.html http://www.postgresql.org/docs/9.1/static/high-availability.... [1] http://www.postgresql.org/docs/9.4/static/high-availability.html http://www.postgresql.org/docs/9.4/static/high-availability....
  - billhathaway 11y ago
    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.
  - lsc 11y ago
    Sweet! thank you. that's just what I wanted.
    - simoncion 11y ago
      No problem! Good luck with your projects! :D
rdtsc 11y ago
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.
- takeda 11y ago
  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-linster/postgres-outperforms-mongodb-and-ushers-new-developer-reality http://www.enterprisedb.com/postgres-plus-edb-blog/marc-lins... [2] Scaling out: http://www.datastax.com/wp-content/themes/datastax-2014-08/files/NoSQL_Benchmarks_EndPoint.pdf http://www.datastax.com/wp-content/themes/datastax-2014-08/f...
  - threeseed 11y ago
    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-mongodb-30-part-1-throughput-improvements-measured-ycsb https://www.mongodb.com/blog/post/performance-testing-mongod...
brianwawok 11y ago
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.
- spacemanmatt 11y ago
  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.
  - brianwawok 11y ago
    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).
    - taf2 11y ago
      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?
      - brianwawok 11y ago
        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.
    - spacemanmatt 11y ago
intellectable 11y ago
For replication instances run in a master-replica setup using Streaming Replication try: http://www.repmgr.org/ 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 https://github.com/2ndQuadrant/repmgr src: http://instagram-engineering.tumblr.com/post/13649370142/what-powers-instagram-hundreds-of-instances http://instagram-engineering.tumblr.com/post/13649370142/wha... For Sharding try: http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... http://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/ 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-A-Highly-Available-Multi-Node-PostgreSQL-Cluster https://github.com/smbambling/pgsql_ha_cluster/wiki/Building... https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling 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.
- technion 11y ago
  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.
  - 11y ago
lobster_johnson 11y ago
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 https://github.com/postgres-x2/postgres-x2 [2] http://www.postgres-xl.org/ http://www.postgres-xl.org/ [3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summary http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...
- jacques_chester 11y ago
  > 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.
  - cmrdporcupine 11y ago
    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?

jtwebman 11y ago

Cool still going to use the best tool for the job.

InfiniteEntropy 11y ago

1) Its not MySQL

djrobstep 11y ago

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.

DevX101 11y ago
I just bought Postico. Best Postgres client I've come across so far for OSX. Would recommend if you're on a mac.
- leesalminen 11y ago
  How does it compare to Sequel Pro for MySQL?
  - putlake 11y ago
    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.
- devbug 11y ago
  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.
phaedryx 11y ago
I've been using Valentina Studio lately and like it so far. Postico looks good too.
saltedshiv 11y ago
I use Navicat, its fantastic.

resca79 11y ago

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

jeltz 11y ago
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).
spacemanmatt 11y ago
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.

ww520 11y ago

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.

simoncion 11y ago
Is the information provided by explain select ... insufficient?
- brobinson 11y ago
  There's also EXPLAIN ANALYZE which may yield different output than just an EXPLAIN.
  - mazelife 11y ago
    Or EXPLAIN (ANALYZE, BUFFERS), which is very useful when you suspect disk IO may be a choke point.
    - brobinson 11y ago
      Didn't know about BUFFERS. Thanks!

ebbv 11y ago

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.

simoncion 11y ago
> [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.
- mappu 11y ago
  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."
  - simoncion 11y ago
    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.
- ebbv 11y ago
  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.

deleted 11y ago

[deleted]

chrisutz 11y ago

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!

spacemanmatt 11y ago
I wrote an upsert generator a long time ago. It was one of the easier things to work around.
- jeltz 11y ago
  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.
- chrisutz 11y ago
  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.
  - spacemanmatt 11y ago
    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.

chanux 11y ago

Part I of the series: https://www.compose.io/articles/what-postgresql-has-over-other-open-source-sql-databases/ https://www.compose.io/articles/what-postgresql-has-over-oth...

garyclarke27 11y ago

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-system/ http://blog.code4hire.com/2014/04/sublime-text-psql-build-sy...

chucksmash 11y ago
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.
dorfsmay 11y ago
Have you tried DBeaver (http://dbeaver.jkiss.org/ http://dbeaver.jkiss.org/)?

Veratyr 11y ago

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.

b3n 11y ago
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: SELECT foreign-id, COUNT(*) FROM Tags WHERE tag IN ('foo', 'bar', 'baz') GROUP BY foreign-id HAVING COUNT(*) = 3 It does seem rather hacky though.
- Veratyr 11y ago
  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: SELECT * FROM things WHERE ARRAY['tag1', 'tag2'] <@ tags;