
PostgreSQL Rising - petercooper
http://wekeroad.com/2012/07/19/postgresql-rising
======
mekoka
On my way to build a multi-tenant application I went through a great deal of
articles recommending various architecture strategies. I was looking for an
approach to organize the data for the app's various customers (multi-tenant).
Most recommendations revolved around 2 solutions: 1 db per tenant, or 1 db for
all tenants with a tenant_id in each table. _Lucky me_ , I eventually stumbled
upon a thread where someone mentioned Postgresql's schemas, which kinda give
you the best of both worlds.

It's unfortunate that the web development world has been so very much MySQL
(and PHP) centric, because it often constrains us to see solutions to problems
with the capabilities of only these technologies.

I had heard much good about Postgresql in the past, unfortunately it was also
introduced with the spectre of a costly adaptation period, which resulted in
making me postpone the move. This time though, I was tempted enough to give it
a shot and in that same evening I jumped in.

It's been about a month now and things have been surprisingly smooth. The only
tool I use to interact directly with the db is the psql client. Other than
that the symbiosis is nearly perfect with Python (I use SQLAlchemy with
psycopg2). If I have a recommendation to current MySQL users who would like to
try Postgresql, _just give in_. It's really not as hostile a transition as
some seem to make it. Not everything will work as you'd expect from your MySQL
background, but most issues are known, the tools are great, the community is
great and the documentation is simply top-notch.

~~~
SkyMarshal
Wow, just this week I started a project that will be my first multitenant
website, using PostgreSQL no less, and have been wondering how to handle that
in the db.

I take a break, pop onto HN, and the top comment of the top story explains how
to do exactly that. Thanks!

You wouldn't happen to have come across any good tutorials on using PG schemas
for this purpose have you?

Also, do schemas provide enough separation of data, when strict client/tenant
confidentially is a requirement?

~~~
mekoka
Unfortunately information is a bit scattered across the web about it. The main
lines though are this:

\- at the start of your request, grab a connection to the database and make
sure that it's only accessible to that specific request (i.e. ensure thread-
safety).

\- start a transaction within that connection.

\- assuming you'll have a <http://subdomain.domain.tld/url> scheme such as
<http://clienta.myapp.com/some/controller>, to switch to schema 'clienta'
you'll execute query "SET search_path = clienta;"

\- now you can execute other statements and transactions within that main
transaction.

\- at end of request, commit the main transaction, reset and release the
connection to make it available to other threads.

That's a broad description of the approach, but it should be enough to get you
started.

As for the data segregation, see what this commenter had to say about it in
his (her?) 3rd point <http://news.ycombinator.com/item?id=1567089> .

I found this video that has some illustration near the end of the
presentation. The presenter uses Rails. I use Flask and SQLAlchemy. Flask has
some handy utilities (Flask.before_request(), Flask.teardown_request()) for
this type of setting/unsetting of connection and schema. I imagine Django also
has a pretty straightforward approach to this.

[http://aac2009.confreaks.com/06-feb-2009-14-30-writing-
multi...](http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-
applications-in-rails-guy-naor.html)

------
juiceandjuice
I'd love to move away from Oracle to Postgres, I really would. I'm trying to.
But for massive amounts of data the partitioning and some other features of
Oracle just work better. The partitioning is a huge thing, especially for our
data which is partitioned by week then organized according to a hierarchical
triangular mesh with bitmapped indexes. This works so well for us (at 8
billion rows) it's silly. MySQL couldn't do this and hacking this into
Postgres was sloppy. Stored procedures in Oracle work pretty good as well
(especially because of shared memory), but I'm trying to eliminate them as
much as possible to try to accommodate other people.

I'm quick to complain about Oracle (especially the fucking cost based
optimizer and how it falls back to retarded hash joins for me frequently,
leading me to add hints, rewrite SQL or run the tuning advisor), but for it's
faults, it does some things pretty well.

That being said, I'm wholeheartedly rooting for Postgres.

~~~
benrhughes
There's a reason Oracle can charge an arse-load of money, and it's not because
they're "evil": it's because their DB does some frigging incredible things
when you know how to use it. I've worked with a 4 billion row star schema,
partitioned by day then sub-partitioned for query optimization. It was OK to
be "stale", so we inserted each day's data into an indexless table then
swapped it in as a new partition over night.

Billions of rows in performance critical apps is pretty edge case. I think a
lot of people paying for Oracle could pretty easily migrate away.

~~~
SCdF
As much as I hate Oracle you're right, they are very much in the Cisco "makes
the easy things hard and the hard things possible" zone.

Nearly every situation I've used Oracle under would have worked fine in any
free DB (including SQLite3 in some silly instances) but I don't doubt there
are situations out there where it excels.

------
rickmb
The primary target audience for hacker-to-hacker Postgres evangelism is MySQL
users. Because let's face it, the choice for DBs like Oracle is usually made
upstairs, and for very different reasons.

So why do Postgres advocates insist on dissing MySQL with false and misleading
arguments? The usual target is some default settings, when obviously there are
three kinds of MySQL users: the ones that actually have a reason to want less
strictness, the ones that know how and when to change the settings, and the
ones that just don't give a fuck.

You're not going to convince the latter until you are #1, and you're insulting
the intelligence of the first two, whilst also making them question whether
the arguments in favor of Postgres are actually true, since you're not being
particularly honest in your criticism of MySQL.

And you're wondering why Postgres doesn't get any more love?

Most MySQL users are well aware that Postgres is technically a better DB. They
mostly (right or wrong) feel MySQL serves their needs sufficiently, and are
not particularly attracted to an open source community with such a douchy
attitude.

If even Oracle doesn't scare people of sufficiently to jump ship and switch to
a better, free and open database, you've got a serious image problem. Stop
blaming it on other people's "stupidity".

~~~
justincormack
It is a lot easier to convince someone who wants to use Oracle to use Postgres
than to use Mysql. There is serious commercial support available, and it is
built for reliability.

~~~
einhverfr
Not just that. PostgreSQL is a lot more like Oracle than it is like MySQL.
Both are development platforms in a box. Both are built for many applications
to run off the same database (this is a use case that MySQL really sucks at
btw-- MySQL is ideal for one app per database deployments). Both let you write
stored procedures in some variation of PL/SQL and Java.

Oracle on the other hand has a setting for everything. A good Oracle DBA can
spend all his time fiddling with Oracle settings to get the best possible
performance. On the other hand, a good Oracle DBA can spend _all_ his time
fiddling with settings to get that little bit of extra performance gain.
PostgreSQL is simpler.

PostgreSQL on the other hand lets you write your stored procedures in any
language. On the other hand, you can spend all your time writing and debugging
stored procedures written in brainfuck.....( and in case you want to do that,
download the handler at <https://github.com/mikejs/pl-bf> and go get started!)

There are a few other differences as well. In general, I think Oracle is
better for some things, PostgreSQL for others, but they have more overlap than
either does with MySQL.

------
gfodor
I'm a huge PostgreSQL fanboy but I think it's worth mentioning that it's
usually not a good idea to use too many esoteric database features when
building an app, since it couples your system with a particular database.

That said, even if you don't use PostgreSQL's whiz bang features, its
stability, performance, and outright sanity with regards to handling data make
it the right database to reach for in many cases. And, for smaller projects
where you _are_ willing to couple yourself with it, its additional data types,
query features, and so on are awesome.

~~~
pvh
I absolutely disagree. Drop-in portability between databases is an operational
myth for any production application anyway. Whether you're using Postgres,
Riak, Mongo or Oracle, you're going to have to do a lot of work to change your
database infrastructure.

Further, every database, noSQL or otherwise, offers a different set of
features and functionality. Why the heck wouldn't you take advantage of
k-nearest-neighbors indices? Why would you want to roll your own full text
search functionality? The alternative is to introduce whole other services to
your infrastructure!

In closing: if you've got 'em, smoke 'em.

~~~
gfodor
It's not a matter of drop-in portability, it's a matter of reducing the
complexity of migration as well as developer confusion. Often the case for
using custom data types, for example, is quite weak, when considering the
tradeoffs. They move complicated logic into the database, are unfamiliar to
most developers, and end up needing to be reverse engineered if you want to
move your data into different storage. I think they should be an option of
last resort, or used in situations where the cost/benefit ratio is so skewed
in their favor you'd be crazy to not take advantage. Generally speaking these
are the rarest of cases.

~~~
jeffdavis
"it's a matter of reducing the complexity of migration"

You put the code in your application so you can switch out the database, but
why do you want to switch databases?

You can't switch out the database for one with more features, because then
you're not using the lowest common denominator any more, and you can't switch
back.

It can't be licensing costs, because postgresql licenses are free.

The only other reason I can think of is performance. But trying to avoid
database features just because you might want to migrate to a less-featureful
database later seems more likely to result in performance problems than
prevent them.

I agree you don't want to go out on a limb with crazy features just because
you can. But those features are there for a reason, and might drastically
simplify portions of your application if you use them. And a lot of them _are_
SQL standard, just not supported properly in all database systems.

Also, I'd like to point out that database migrations themselves are quite rare
once they become established in an organization. You might be able to migrate
one fairly simple application if you bend over backwards trying to use only
the simplest features; but once a few applications are depending on it, it's
just too expensive.

~~~
sandGorgon
One reason if you are building a shippable product that needs to work with a
variety of client databases rather than a choice of your own.

99% of the time, this is why you build DB agnostic apps.

~~~
yread
I am building a product that can work with SQL Server, MySQL, PgSQL and
Oracle. The reason was if the company who buys the software already has the
infrastructure and DBAs in that database, they can leverage it. If I were
making the decision now I would definitely choose just one and stick with it,
because

\- astonishing amount of things are done differently on these four dbms
(idenities/sequences and getting their last value, paging, DDL)

\- each of these dbms has specific features which would improve the
performance or help us develop stuff but we can't use them because they
require different architecture (and we want to keep it the same)

Don't do software for multiple databases. It's just not worth the trouble.

~~~
jeltz
Not to mention even the basic data types are differently named and work
differently (text vs varchar vs nvarchar vs varchar2). It is especially hard
to when you have to work with date and time types/functions.

------
AaronBBrown
I just watched a 10 minute video whereby nearly all the issues pointed out can
be solved with one line in my.cnf or dynamically by setting SET GLOBAL
server_sql_mode=TRADITIONAL. Yeah the default is no good. Learn your RDBMS and
the problem goes away. Watching him point and click and move windows around
also made it very difficult to follow.

PostgreSQL is an awesome RDBMS, but adoption will never eclipse MySQL until
they have a scalable replication model that allows tiered replication, multi-
master replication, writable slaves (very useful for reporting boxes), and the
ability to purge data on a master without purging it on the slave. That, and
the ability to _reliably_ upgrade your binary without performing a massively
time-consuming dump and reloading. Replication and upgradability is everything
when you are in operations, which are things many (not all) developers do not
consider.

~~~
jeffdavis
Your concerns are constructive and well-placed. It happens that they are all
either done or actively being improved, but that's not obvious unless you
follow very closely, so keep 'em coming.

"a scalable replication model that allows tiered replication"

They do in 9.2 (currently beta), it's called cascading replication.

The other replication features you mention are under active development by a
team of reputed hackers.

"That, and the ability to reliably upgrade your binary without performing a
massively time-consuming dump and reloading."

pg_upgrade has been available and you can upgrade from 8.3 to the latest
without the dump/reload cycle. It has been a little rocky (by postgres
standards) and some people have been hesitant, but it gets the job done and
it's been improving a lot. A talk at a recent conference spoke quite highly of
pg_upgrade despite running into some challenges (like a library versioning
issue related to some perl functions they had and some multibyte characters I
think). Not for the faint of heart, but if you really need this and don't mind
reading a little (or hiring a consultant), you can make it work.

Every issue or every missing feature seems like a showstopper if you don't
step back for a minute. No DBMS is perfect. Everyone has their list of
"postgres is missing X,Y, and Z". The funny thing to me is that X, Y, and Z
change with each release because the previous X and Y were added (often with
greater flexibility than originally imagined), and there's a new alternative
to Z.

Postgresql puts out a very high quality release _every year_ with major
features. Usually, it's a good balance of features requested by (potential?)
users like you and new innovative features that move the database world ahead.

~~~
AaronBBrown
As I mentioned, I do like PostgreSQL for its consistency and I want to like it
even more. Cascading replication looks interesting - does it support the
topology that I mention where the master has a subset of the from the slaves
and the slaves can be written to? The documentation is difficult to decipher
and lacks examples.

I don't mean this to be MySQL vs PostgreSQL, but MySQL has had these features
for over 10 years and the kinks are already worked out. It's one of the main
reasons for the success of the platform. My X,Y, and Z list has been
unchanging for 5 years when I first started maintaining a medium sized Slony-I
cluster that required a complete rebuild and several hours of downtime every
few months. I suspect that's true of most operations-focused folks who have
maintained PostgreSQL clusters. Devs love it, DBAs love it, but it is
Operations that holds the keys to the kingdom.

~~~
jeffdavis
"Cascading replication looks interesting - does it support the topology that I
mention where the master has a subset of the [tables??] from the slaves and
the slaves can be written to?"

No. Cascading replication basically means that you can replicate from slaves
to other slaves, rather than always replicating from the master. That allows
you to form hierarchies, which is I thought what you meant by "tiered".

The current built-in replication is a form of _phsyical_ replication, meaning
that data pages are (more or less) identical on the master and slave. What you
want is _logical_ replication, which is being actively worked on now as a part
of core postgres.

In the meantime, there are external logical replication systems like Slony,
Bucardo, and Londiste. Slony is deserving of a reputation for being complex,
but it's being actively developed and can be used for everything you mention
(based on the very high level descriptions that you gave). I understand that
"not in core, and hard to use" are pretty damning for a lot of cases, but it
really does get the job done. And hopefully there will be in-core solutions in
later versions of postgres.

"I don't mean this to be MySQL vs PostgreSQL, but MySQL has had these features
for over 10 years and the kinks are already worked out."

I'm glad to hear that it's working for you.

"My X,Y, and Z list has been unchanging for 5 years"

For future reference, all of these features will fall under the heading
"logical replication". When you see that, if it doesn't have all of the
features you need, then they are probably going to arrive soon.

Postgres has had external logical replication (e.g. Slony) this whole time,
which I think has somewhat reduced the demand to invest huge amounts of work
in core. It looks like it fell short for your use case, unfortunately; and
hopefully the current logical replication in core project will not.

~~~
AaronBBrown
Thank you for the clarification about the PostgreSQL terminology. That will
help me keep track of the progress. I am very pleased that the PostgreSQL
community has finally started implementing native logical replication, which
is really one of the biggest obstacles to widespread adoption.

Slony, in a word, is awful. It's probably as good as it can be given the
limitations of working within a trigger-based world, but in the 3 years I
spent maintaining Slony clusters, I lost count of the number of times that I
had to take a site offline (sometimes for hours) in order to rebuild from
scratch because of a failed schema change. Many times, I was able to recover
by careful manipulation of the sl_events table, but far too frequently, the
error was unrecoverable. Slony is slow, unreliable, not tolerant of high
latency connections, and fragile. It was always one of my greatest
frustrations that the PostgreSQL community saw it as a solution given how
completely unreliable it is. Perhaps things have changed in the 2 years since
I last touched it, but I am skeptical.

Please try to understand that my few objections to PostgreSQL come from actual
real world experience with both MySQL and PostgreSQL, not from reading blog
posts. Both products have great features as well as unpolished turds...it's
just a matter of deciding which color of poop you are willing to clean out of
the diaper. :)

------
cageface
Back when I was still doing web development, I had a big stack of things I had
to deal with to actually get html on somebody's screen. Most of them were
slightly annoying to deal with and I would often push them back to the one day
a week I reserved to futz with annoying little details.

But working with Postgres was always a joy. Everything seemed to be designed
in a logical, straightforward way and worked the first time without any hacks
or workarounds. It always felt like there was so much untapped _potential_
humming there behind the prompt.

Eventually my company switched to Oracle and DB hacking quickly became another
one of those annoying things.

------
spudlyo
The complaint that MySQL is by default loosey-goosey with your data is valid,
but it's an easy default to change. Here is what happens when you run some of
the commands shown in that 'Why Not MySQL?' video on a sanely configured MySQL
system by setting SQL_MODE to TRADITIONAL. This mode also allows you to not
have dates with zeroes, etc.

    
    
        mysql> alter table test change column my_money my_money decimal(2,0);
        Query OK, 2 rows affected (0.03 sec)
        Records: 2  Duplicates: 0  Warnings: 0
    
        mysql> insert into test values (4,'bar', 100);
        ERROR 1264 (22003): Out of range value adjusted for column 'my_money' at row 1

~~~
einhverfr
1) can applications set the SQL_MODE themselves? Can an admin configure the
server so applications cannot specify mode? If not, what good is it since it
won't guarantee your data?

2) My larger frustration with MySQL is I have run into cases of single
transactions deadlocking against themselves. These always happen when the
following is true:

* Executing an insert statement in the form of INSERT foo (bar) VALUES (1), (2), (3), (4);

* Only one connection/session active at a time (for example during a data migration to MySQL)

* Frequency goes up when more rows are inserted per statement

* Which inserts trigger the deadlocks are not reproducible

I believe this is an issue with race conditions and threads, perhaps a lock
contention that isn't being handled properly between index and table writes or
the like. I can reproduce it by inserting a couple million rows into a table,
a few thousand at a time, but the statements where this occurs varies from one
run to the next.

I have _never_ seen braindead locking behavior on PostgreSQL.

~~~
epimenov
There definitely was braindead locking behavior on PostgreSQL at least once:
[http://www.mail-archive.com/pgsql-
hackers@postgresql.org/msg...](http://www.mail-archive.com/pgsql-
hackers@postgresql.org/msg157869.html)

~~~
einhverfr
That certainly is odd behavior however reading through the whole email thread
that seems to be one complicated issue with a two processes obtaining
different locks to the same row. Probably not ideal, but maybe I need to
recalibrate my definition of braindead because in real-world examples, that
would be highly annoying.

~~~
epimenov
It happened to us in the real world. And was fixed in 9.2

~~~
jeltz
Actually it is not fixed yet, the patch was pushed to 9.3 and Alvaro is still
working on it.

It has happened to us too, though it was very easily fixed in our case due to
the excellent deadlock reporting. And I can also confirm that the case for
Joel (the original reporter) is also a real world case.

Link to talk about it <http://www.pgcon.org/2012/schedule/events/483.en.html>

Edit: Not sure if I would call it braindead though. Highly annoying, yes.
Alvaro has spent a lot of time into solving this, it is much harder than it
initially seems.

------
lazyjones
One of the best features of Postgres is Tom Lane. We've been using Postgres
since 2000 and following the (user-oriented) mailing lists closely and he's
always been responsive, helpful, extremely competent and tactful - a rare mix
indeed (unfortunately). He should be the primary role model for FOSS
developers, where quirky characters dominate the field. Thanks, Tom!

------
pvh
People often ask us at Heroku -- why Postgres?

The short answer is: we needed to do something, and it's the best.

~~~
pjmlp
> we needed to do something, and it's the _best_.

For such a statement I would rather take one of DB2, Oracle or SQL Server.

~~~
reidrac
In my experience you don't want to provide X as a service based on a third
party closed product (meaning: anything not open source & open community).

~~~
pjmlp
I imagine you don't do business in the enterprise world.

~~~
reidrac
Yes, I do. I said "in my experience" and I totally understand why Heruku is
offering PostgreSQL and not DB2, Oracle or SQL Server as a service.

Amazon RDS started with MySQL, and only two years later Oracle support was
added. SQL Server three years later.

~~~
pjmlp
Sorry about that, should have read better your comment.

On my area of work we also do a lot of open source, but most customers won't
think twice about using proprietary database solutions, specially taking into
consideration the types of deployments we do.

In my career in the enterprise world, I've mostly used Oracle, Informix, DB2,
Microsoft SQL Server, Sybase SQL Server.

The only two project I used MySQL so far, one was for a prototype application,
to be shown in computer fairs. The other was an online survey.

------
arjn
My opinion is contrary to most of the comments here. About a year ago we
switched a multi-tiered app from MySQL/MSSqlServer to Postgres/Postgres. While
everything works fine, after months of using it I find writing programs for
and with MySql or MSSqlServer seems easier. One specific complaint I have is
that there is no transaction control within pgsql stored procedures
(functions). I had to kludge the db link module to get transactions to work.
Another related complaint is the DB Link module has poor functionality when
compared to MSSqlServer's DB Link. I brought this up yesterday at the PostGres
booth at OSCON and they said they're aware of it. No news on when they'll get
around to fixing it.

A feature we miss is MySql's ability to use different DB engines such as
Archive or Memory. AFAIK there is no equivalent to these in Postgres. We put
mysql's Archive engine to good use before we migrated our app. Now we have to
export data to gz files via script.

In general I don't think there is a strong reason for the majority of apps to
switch to Postgres when MySql works for most needs. (I should mention that
pgsql has some nice locking functions that we found useful)

~~~
jeffdavis
"One specific complaint I have is that there is no transaction control within
pgsql stored procedures (functions). I had to kludge the db link module to get
transactions to work."

Yes. Functions work within a transaction, which is usually a blessing. But
there are cases where you want to do the transaction control in the procedure
itself. This is a known request that is discussed as two different features:
"stored procedures" (which is probably what you want) and "autonomous
transactions".

------
rufugee
As a long time PostgrSQL fan and user, it's nice to see it get well-deserved
attention. It's kept our company of 3000 employees going strong for nearly a
decade and has NEVER failed us or lost any of our data. (Knock on wood ;-) )

------
spitfire
What tool is he using to do the postgres part of that video? I've been looking
for a decent postgres gui and can't find one.

EDIT: Navicat it looks like.

One comment, way back int he mists of times (the 90's) MySQL was known as the
database that would eat your data. It had a number of data gobbling bugs, as
well as no transactions. I'm genuinely surprised how far good marketing and
ease of use will go to make a product successful. The postgres guys might
learn a bit there. Ease of use will drive a lot of adoption.

~~~
sudont
Unrelated, but hopefully a good place to stick this:

What was that Mac 10.7-only, command-line but standalone database tool,
designed for high-level analysis of data via queries?

~~~
radq
Are you thinking of this? <http://inductionapp.com/>

~~~
sudont
Yes, __Thank You!__

I've been spending hours at a time looking for it, spamming here was a last
resort. (Throwing "Postgres" in my searches was what was fouling them—turns
out it was just spawned by Heroku's Postgres team...)

------
sxcurry
Interestingly, Ingres was the first database I ever used - running on Unix (v6
or v7?) on a PDP 11/34. Since then, I've ambled through Oracle, SQL Server,
etc, but I'm currently using PostgreSQL on a project, and loving it. The
native spatial types are really handy for what I'm implementing, and the speed
and stability are great.

------
ondrae
The recent release of PostGIS 2, the spatial extension of Postgres, has been a
huge benefit to geo software. It has increased the ease and speed at which we
can now work with large data sets and do spatial computation on them.

My favorite part of PostGIS 2, besides much of the cleaned up syntax, is the
new Geography datatype. It has the spatial projection built in to it, so it
automatically does great arc computations. Before we had to write more clunky
SQL to achieve this. Was a significant barrier for my learning postgis, which
has now been removed.

~~~
robe2
Geography data type came out in 1.5 NOT 2.0. Raster and better 3D support were
the big story in 2.0 But stay tuned for 2.1 --> Geography will become MUCH
faster: <http://blog.opengeo.org/2012/07/12/making-geography-faster/>

------
smsm42
I was reading this article with great interest, exactly up to the part where
it claims MySQL is stupid. Maybe it's just me but I've had enough of articles
that are written with the idea that the best way to promote your product is to
bash your perceived competition and call it stupid, worthless, idiotic and
broken. People, here's the news for you: people don't use your competition (or
your beloved tool's competition) because they are stupid. They use it because
of variety of reasons, but it almost never involves them being stupid, and
calling their choice stupid is almost never a good way to get them to consider
changing their preferences.

Even more disappointing, when the author proceeds from showing how stupid
MySQL is (random collection of choices which can be plausibly decided in any
way) to showing how much better Postgres it, he doesn't show anything useful.
He just shows random collection of features that may be useful in some use
case he probably encountered in his practice, but no compelling picture of a
superior product. Nobody changes DB server because one has "tomorrow" keyword
and another does not.

And when performance comparison is done - no numbers _at all_ are quoted
except for initial data size. Just bare claims "it performed well" - how well?
Are these results comparable to what I can expect on my data with my setup?
How much manual work (mentioned in passing as 2 experienced DBAs were working
on it - but how complex was what they did?) would it require to make it
perform? How much better would it perform if I hired a very good DBA? These
are very important questions to consider when you choose the DB for your
project, but completely omitted in the article.

------
davyjones
I would really appreciate it folks can try out my free and open source GUI
client for postgres: <http://pgXplorer.com>. It is available for Mac, Ubuntu
(64) and Windows (64).

~~~
yorhel
Just tried to compile from git, but the qt-psql requirement wasn't very easy
to satisfy on Arch. Your Ubuntu binary worked fine.

Looks quite nice, and there certainly is a need for more PostgreSQL GUIs. Some
feedback from the minute I used it:

\- You don't handle bytea columns very nicely. I'd expect those to be
displayed in hex or so.

\- Browsing a table is very slow on tables with large columns. I was testing
on a table with a ~10KiB text column.

\- I personally prefer a more compact interface, having an option for smaller
buttons would be a good start. :-)

(Edit: formatting)

~~~
davyjones
Thanks a lot for your response!

> qt-psql requirement wasn't very easy to satisfy on Arch

Whoops! My primary dev machine is Arch. Let me fire up a clean VM and try and
reproduce the problem. Are you rolled to the latest?

> \- You don't handle bytea columns very nicely. I'd expect those to be
> displayed in hex or so.

Unfortunately, the database types to Qt types get mapped in a pretty
undesirable way (for this use case atleast). For example, I do custom handling
of timestamp types within reason. Let me take a look at bytea as well.

> \- Browsing a table is very slow on tables with large columns. I was testing
> on a table with a ~10KiB text column.

I strongly suspect the Delegate class of Qt. This class handles the painting
of cell data and it can be customized to a high degree. And I think even the
default Delegate is pretty heavy leading to extreme stress when painting cells
that carry a lot of data. Unfortunately, I don't see this as something that
can be fixed quickly unless I start from very low level classes on Qt to
display table data.

> \- I personally prefer a more compact interface, having an option for
> smaller buttons would be a good start. :-)

Actually, I was thinking about having the scroll wheel adjust the icon size on
toolbars. Let me look into that as well.

Thanks a lot for your input; I really appreciate it.

~~~
j_s
> \- Browsing a table is very slow on tables with large columns.

Last time I did this I used a fixed-width column when displaying text fields.
Everything built-in was able to handle things smoothly when clipping to just
the first __ characters. (Of course you can't see it all then, but I've found
it useful enough... and can paste a cell elsewhere to see all of it.)

~~~
davyjones
Thanks a ton. That is really helpful. I will see if changing to fixed width is
feasible when string length is above a threshold.

------
marquis
What are the scaling differences between MySQL and PostgreSQL? That's the main
reason we haven't shifted and we have a new project coming up that I've been
interested to use PostgreSQL with as one our developers prefers it, but are we
opening a whole new can of worms on that front?

~~~
chimi
PostgreSQL is massively more scalable than MySQL.

~~~
spudlyo
Definitely. When your application needs hundreds of read slaves in order to
scale the load, PostgreSQL has always been everyone's first choice due to it's
mature and historically awesome replication system. That's why companies who
need to scale big (YouTube, Facebook, Yahoo, LinkedIn, Wikipedia, Twitter etc)
all have hundreds (if not thousands) of PostgreSQL machines in their
infrastructure.

~~~
joshhart
Im going to assume this is sarcasm since Facebook is built entirely on sharded
MySQL. Don't want anyone else to get confused in case it doesn't come through
for them.

~~~
chimi
Facebook, et al, use memcache and a plethora of other products to improve the
scalability of MySQL. Here's one scalability comparison between the two:
<http://tweakers.net/reviews/649/7>

~~~
AaronBBrown
That is a comparison from 2006 using MySQL 5.0.20 (ancient!) and as such is
completely irrelevant to any discussion on performance in 2012. Dramatic
scalability improvements went into late 5.1.x releases, and then even more in
5.5.

------
mmaunder
I think the reason that MySQL became more popular than PostgreSQL is because
it did one thing really good: It was a very fast file access daemon.
PostgreSQL tried to do too much, ended up not doing any of it very well
especially in the area of performance and didn't become as popular. Maybe
useful to remember this going forward.

~~~
jeffdavis
I see it as a major success story.

Postgres was developed over a long period of time with careful attention to
architecture, robustness, and extensibility. It also focused on both external
_and internal documentation and cleanliness_. And, it followed the traditional
database system model with a cost-based optimizer and everything else.

As a result, we see a very robust developer community. It's a real machine --
churning out high quality releases every year with a great mix of newsworthy
features. Enterprises and startups alike are flocking to postgres for a huge
variety of use cases.

Slow and steady wins the race (and it doesn't hurt to be 5 tons and have
tusks) and isn't tired afterward. And in databases, it's good to play for the
long term because that's how long the data will last.

I wouldn't trade that for some first-to-market popularity.

~~~
taligent
Your entire argument falls apart when you look at all of the companies that
chose MySQL over PostgreSQL and are still using them today. Basically every
major internet site today relies on MySQL.

~~~
jeffdavis
First of all, the success of postgresql and mysql are not mutually exclusive.
Postgres operates in a lot of markets that MySQL doesn't (as far as I know)
like telecom and finance. Postgres gets entirely new users with a variety of
use cases, as well as people from Oracle, SQL Server, MySQL, and even some
converts from people who tried NoSQL and found it lacking in some way.

An undeniably, there is a shift happening, even in parts of the market that
were a MySQL stronghold. Heroku and their customers use postgres for multi-
tenancy. Instagram uses postgres for photo-sharing.

I think MySQL has been very complacent because they are the default for a lot
of simple web apps, and that keeps their numbers high. They aren't really
breaking into new markets -- postgres is winning the geospatial market big
time, and is always coming out with new features to break into new markets
(personally, I am trying to advance postgres into the temporal database
space).

~~~
darnaut
> Postgres operates in a lot of markets that MySQL doesn't [..] like telecom
> and finance

MySQL cluster is highly popular in telecom.
<http://www.mysql.com/customers/industry/?id=78>

~~~
jeffdavis
I stand corrected.

------
mgkimsal
Something that tends to get overlooked in pg/mysql discussions is
administration - more specifically account management. all of this is done in
the 'mysql' database via SQL directly in mysql ('insert into user', 'grant
permission', etc). _Most_ tutorials on PG I've seen over the years show these
things being done from the commandline, and assume a unix environment, and an
understanding of connecting from various account shells and such.

i've been made aware that you that there are other ways of dealing with
user/perm management in pg, but from the perspective of people running shared
hosting, the default tutorials feel extremely burdensome compared to managing
everything in one database via SQL (no need for multiple user accounts on the
system - just multiple user accounts on the database).

~~~
robe2
CREATE USER is a standard in many relational databases e.g MySQL, PostgreSQL
etc. You really shouldn't be inserting directly into mysql system tables. It's
not portable.

PostgreSQL has phppgadmin and pgAdmin which provide a nice GUI experience for
role management.

------
jasonkostempski
One thing I've always loved about SQL Server is the Adventure Works OLTP and
data warehouse DB samples, they are absolutely priceless when it comes to
experimenting and learning. Is there something like that for Postgres? Is
Postgres even intended to be used as a data warehouse and, if so, are there
any decent business intelligence tools that can work with a Postgres
warehouse? I'm not looking for an open source Analysis Services/Business
Intelligence Studio equivalent, just something that I can build some cubes
with and run MDX queries against.

------
sams99
Can the lack of mixed DML/DDL transactions be worked around with a change to
my.conf?

non transactional migrations always seem to trip me up in mysql, cleaning up
the mess is a nightmare

------
stef25
I had to use Postgres at a job or about 6 months and hated it due to lack of a
decent GUI. The "official" one has many bugs and quirks and takes quite a bit
of getting used to especially after coming from phpMyAdmin. So it's nice to
see there is now an .app for it.

------
mixmastamyk
Anyone interested in a pgadmin-like console client? I started one, and if a
few people with more in-depth experience helped out I think it could be handy.

<https://bitbucket.org/mixmastamyk/dumbo/>

------
imalolz
I've been neck deep in MySQL for the past 5-6 years, simply b/c that's what my
company uses.

I've been wanting to pick up postgres but my only motivation has been
curiosity. Any good resources/tutorials people here can recommend?

~~~
robe2
Our site is dedicated to that: <http://www.postgresonline.com/>

Also check out our new book which came out this month.
<http://shop.oreilly.com/product/0636920025061.do>

Its primary target audience are people coming from other relational databases.

------
dotborg2
\- functional index

\- index partitioning

some great features of postgres, which were not mentioned so far

------
snambi
i believe one of the main reasons for mysql's popularity is that it is
available by default with many hosting providers who provide PHP based hosting
for a very cheap price. These are low-medium volume sites developed by a small
teams. They just don't need or care about the benefits of using a better
database like 'postgres'.

IMHO, postgres developers and support organizations need to work with hosting
companies to make postgres available along with myql.

------
pwpwp
The article summary is #ccc on white.

------
systems
better names would have been PostSQL or PostDB not Postgres

------
kami8844
AAAAAAAAAAAAAAAAAAAA

BBBBBBBBBBBBBBBBBBBB

CCCCCCCCCCCCCCCCCCCC

DDDDDDDDDDDDDDDDDDDD

EEEEEEEEEEEEEEEEEEEE

FFFFFFFFFFFFFFFFFFFF

GGGGGGGGGGGGGGGGGGGG

HHHHHHHHHHHHHHHHHHHH

IIIIIIIIIIIIIIIIIIII

JJJJJJJJJJJJJJJJJJJJ

KKKKKKKKKKKKKKKKKKKK

LLLLLLLLLLLLLLLLLLLL

MMMMMMMMMMMMMMMMMMMM

NNNNNNNNNNNNNNNNNNNN

OOOOOOOOOOOOOOOOOOOO

PPPPPPPPPPPPPPPPPPPP

QQQQQQQQQQQQQQQQQQQQ

RRRRRRRRRRRRRRRRRRRR

SSSSSSSSSSSSSSSSSSSS

TTTTTTTTTTTTTTTTTTTT

UUUUUUUUUUUUUUUUUUUU

VVVVVVVVVVVVVVVVVVVV

WWWWWWWWWWWWWWWWWWWW

XXXXXXXXXXXXXXXXXXXX

YYYYYYYYYYYYYYYYYYYY

ZZZZZZZZZZZZZZZZZZZZ

------
cmaxwell
I would probably consider PostgreSQL if I didn't need MySQL on my VPS for
wordpress.

------
floobar
are you paid for talking about postgres via tekpub or otherwise? sometimes
your postgres chatter seems like you are.

~~~
robconery
Other way around - I like Postgres a lot, so I talk about it and do videos.

