PostgreSQL Rising

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.

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

>Depending on your application and how you're taking advantage of your database, you could drop-in replace a database.

If you're doing something simple, sure, maybe. But beyond trivial things like DDL syntax and query syntax, there's query optimization (even when only going in via ORM, because schema design can affect this), tuning, backups, HA, monitoring, and a dozen other things.

You're already making a huge non-portable investment in using a complex tool like a database. In comparison to this, introducing dependence on its non-standard features is pretty small change, so you might as well stop worrying (considering how seldom people actually migrate), raise a glass to YAGNI, and learn to love your database.

Maybe in another five years your or some successor will end up cursing the day you made that decision, but if you could really use non-standard feature X and it's sitting right there in front of you, it's silly to shun it "just in case."

As for "show tables", there's a big difference here.

"show tables;" is a server-side command. \dt is a command you give psql to say "ask the server what tables there are and list them." \d stands for describe. If you use a graphical client, it will have to query the system catalogs itself which you can do if you want.

As for replication, the real challenge is that replication is not a one-size-fits-all thing. Slony, Bucardo, and the built-in streaming replication have different limitations. For example you can't replication from 9.0 to 9.1 with streaming replication but you can with Slony and I would expect you could with Bucardo too. So with the out of tree replication systems you can actually have a zero-downtime upgrade as long as you block writes during the upgrade of the master. With streaming replication, assuming pg_upgrade is supported for your upgrade, you only have a short downtime window, but it is downtime.

Also streaming replication is all or nothing. With Slony you can replicate different pieces of your database. On the other hand, with Slony, you can (accidentally) replicate only a piece of your database.

So the differences give you a lot of flexibility there, but you need to have a clear idea of what you need and why you need it before choosing a replication solution. Slony is perfect for what Affilias uses it for, while streaming replication wouldn't meat their needs.

Also I don't think that PostgreSQL suffers from a lack of mindshare. Ever since I have been building apps on open source db's, PostgreSQL has been regarded as the go-to database for complex business apps. The sorts of things people do with the database are different. There is consequently a huge "dark community" if you will (in the sense that you don't see them).

This was driven home to me when I went to the Malaysian Government Open Source Software convention last year. There were two booths (Oracle and one other) which had the MySQL logo, and a few more that were offering MySQL services, while I counted at least 5 that were using the PostgreSQl logo. Most of those were reselling EnterpriseDB's PostgreSQLPlus but it when I looked at the level of interest and the number of government deployments I was asked about, it was clear which people were usually choosing for complex work.

As for user-friendliness, MySQL used to be much more user-friendly than PostgreSQL but I don't think that's true anymore. The command-line tools are more full-featured and the in-app help is better on PostgreSQL. \? brings up a list of psql client-side commands and what they do for example, and \h [command] will give you a summary of the syntax of an SQL command. I use \h quite a bit when I am trying to remember the specific syntax of a new feature, or when I am doing something I rarely do (like ALTER TABLE). I think that since about PostgreSQL 7.3, PostgreSQL has been at least as easy to use as MySQL, and quite frankly far more robust.

Finally, the PostgreSQL planner is awesome. I have not had to rewrite a query to get around planner limitations since 8.1.