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/

by postila • 11 years ago

278 93 11 years ago

halayli• 11 years 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.

lsc2• 11 years 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• 11 years ago

Postgres has built in replication since 9.1

simoncion6• 11 years 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....

[1] http://www.postgresql.org/docs/9.4/static/high-availability....

rdtsc• 11 years 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.

takeda3• 11 years 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-lins...

[2] Scaling out: http://www.datastax.com/wp-content/themes/datastax-2014-08/f...

brianwawok3• 11 years 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.

spacemanmatt10• 11 years 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.

intellectable• 11 years ago

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.

technion• 11 years 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.

lobster_johnson6• 11 years 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

[2] http://www.postgres-xl.org/

[3] http://git.postgresql.org/gitweb/?p=postgres-xl.git;a=summar...

jacques_chester5• 11 years 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.

jtwebman• 11 years ago

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

InfiniteEntropy• 11 years ago

1) Its not MySQL

djrobstep• 11 years 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• 11 years 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• 11 years ago

How does it compare to Sequel Pro for MySQL?

devbug• 11 years 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• 11 years ago

I've been using Valentina Studio lately and like it so far. Postico looks good too.

saltedshiv• 11 years ago

I use Navicat, its fantastic.

eropple• 11 years ago

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.

reactor2• 11 years ago

http://dbeaver.jkiss.org works pretty well for me

sandGorgon• 11 years ago

This looks awesome! Are you able to use the jsonb data types through this?

aw3c2• 11 years ago

I never heard of this and I was looking for something like this for ages. Thank you!

phunge• 11 years ago

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.

pacquiao882• 11 years ago

For Windows users, HeidiSQL is a very solid open source client for interacting with Postgres.

zenocon2• 11 years ago

Use JetBrains 0xDBE -- still available now as early pre-release for free.

resca79• 11 years 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

jeltz3• 11 years 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).

spacemanmatt10• 11 years 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• 11 years 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.

simoncion6• 11 years ago

Is the information provided by

  explain select ...

insufficient?

brobinson2• 11 years ago

There's also EXPLAIN ANALYZE which may yield different output than just an EXPLAIN.

ebbv3• 11 years 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.

simoncion6• 11 years 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• 11 years 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."

ebbv3• 11 years 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.

chmaynard• 11 years ago

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.

simoncion6• 11 years ago

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.

dragonwriter• 11 years ago

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.

chrisutz2• 11 years 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!

spacemanmatt10• 11 years ago

I wrote an upsert generator a long time ago. It was one of the easier things to work around.

jeltz3• 11 years 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.

chrisutz2• 11 years 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.

chanux• 11 years ago

Part I of the series: https://www.compose.io/articles/what-postgresql-has-over-oth...

garyclarke27• 11 years 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-sy...

chucksmash• 11 years 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.

dorfsmay2• 11 years ago

Have you tried DBeaver (http://dbeaver.jkiss.org/)?

Veratyr2• 11 years 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• 11 years 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.

Veratyr2• 11 years 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;

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

Related Stories