
What PostgreSQL has over other open source SQL databases: Part II - postila
https://www.compose.io/articles/what-postgresql-has-over-other-open-source-sql-databases-part-ii/
======
halayli
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
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.

~~~
simoncion
> 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....](http://www.postgresql.org/docs/9.1/static/high-
availability.html)

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

~~~
lsc
Sweet! thank you. that's just what I wanted.

~~~
simoncion
No problem! Good luck with your projects! :D

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

~~~
chrisutz
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
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
Part I of the series: [https://www.compose.io/articles/what-postgresql-has-
over-oth...](https://www.compose.io/articles/what-postgresql-has-over-other-
open-source-sql-databases/)

------
Veratyr
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
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
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;

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

~~~
dorfsmay
Have you tried DBeaver
([http://dbeaver.jkiss.org/](http://dbeaver.jkiss.org/))?

------
ww520
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
Is the information provided by

    
    
      explain select ...
    

insufficient?

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

~~~
mazelife
Or EXPLAIN (ANALYZE, BUFFERS), which is very useful when you suspect disk IO
may be a choke point.

~~~
brobinson
Didn't know about BUFFERS. Thanks!

------
djrobstep
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.

~~~
reactor
[http://dbeaver.jkiss.org](http://dbeaver.jkiss.org) works pretty well for me

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

~~~
reactor
I'm not sure as I'm not started playing with Jsonb yet! but give it a try.

------
ebbv
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
> [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
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
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.

------
resca79
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

~~~
spacemanmatt
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.

------
InfiniteEntropy
1) Its not MySQL

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

