back
144 comments
Left out the fact that Schema-changing operations are transactional (big win over MySQL).

[Edit: In migrations, specifically. Also! No mention of the MySQL dual-license JDBC driver malarky... Not that it's a success of Postgres' as much as it's a failure of MySQL's... ]

I'd never heard of Window Functions (not an Oracle user, and been on MySQL for a few years... left Postgres for replication and haven't made it back quite yet).. but I desperately want them. The idea of effectively aggregating over groups in a group by (which is how I understand these Window doohickeys) is something I come up on a few times a year and get really frustrated by.

I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences... The idea that I have a related list of things that I want to keep a sequence column for, and would like the database to understand the concept and help me maintain it.

> I'd never heard of Window Functions

Go ahead and give it a shot. Once you get used to it, you cannot live without it.

Another killer feature in my opinion is the recursive query: http://www.postgresql.org/docs/current/static/queries-with.h...

What's the trouble with PostgreSQL's sequence support? (http://www.postgresql.org/docs/9.1/interactive/sql-createseq...)
> I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences...

I would like some RDBMS to give sharable transactions. What I mean by that is something like this.

1. When I begin a transaction, I can assign it a name.

2. If multiple connections to the database are in transactions with the same name, they can see the changes made by the others, as if none of them were in a transaction. Connections not party to the shared transaction do not see the changes until all of the connections in the shared transaction commit.

3. The connections in the shared transaction can do unnamed transactions, which isolate them from the others within the shared transaction.

The idea here is that sometimes I have some operation that I would like to do atomically, but that involves multiple processes acting on the database. Process A wants to make some changes, then pass responsibility on to process B (which may be on a separate machine) to make more changes, and then process C finishes up, and if any of them encounter an error I want to rollback to the state before A started.

I think the only killer feature I'm left wanting from some RDBMS at this point is managed-sequences...

Excuse me if I didn't completely understand what you wanted but it seemed similar to SQL Server's sequence: http://msdn.microsoft.com/en-us/library/ff878091.aspx

I really disagree with this. The more features you use from your DBMS vendor, the more you shoot yourself in the following departments:

- scalability. Logic and processing in the server is bad as it means you can only scale up and not scale out. Scale up is damn expensive. When you need that 64 core 96Gb machine to run it all on with 6 replicas will see what I mean.

- complexity. The DBMS is a black box which sucks up tight coupling almost instantly. Coupling is bad for maintenance and scalability. SQL always ends up with heavy coupling.

- lock in. Those features only work with postgres so you're stuck with it forever or induce a lot of cost to move away when a better solution comes along.

- schema. Maintaining schema and dependencies is hell on all but a small system.

These facts come from 20 years of using databases.

Stuff that will save you: Use your DBMS as a simple flat store akin to a document store. Use a CQRS based architecture and encapsulate ALL of your logic in code. If you have to go at least partially relational for god's sake use an ORM that supports several databases well. Make a provider independent app level backup/restore facility such as the one with JIRA or TeamCity. NEVER hire a DBA - they exist only to create walled gardens to protect their existence.

My current gig has cocked up on all areas and it's costing them over 40% of their turnover keeping it alive.

Happy databasing :)

Throwing out decades of work on storing data just because it's a hard problem won't help you solve the problem. All you are advocating is that instead of using an existing well tested solution, you should write your own and deal with all the assorted costs of redoing that work, and probably poorly.

Similarly, using an ORM doesn't help you. All that does is hide the details from you. It still uses SQL underneath, but limits what you can do with it.

Let's talk schemas. You have one whether your database understands it or not. Trying to pretend you don't have one doesn't make managing your schema any easier. It doesn't make building indicies on fields any easier.

These are hard problem and pretending they don't exist by rolling your own implementation or hiding them under an ORM won't make them go away.

I've always found doing logic and processing in the database to be much faster and much more efficient than doing it outside. The closer you can process data to the data store, the faster it goes. Using materialized views can get you a long ways.

There's also no reason why you can't use multiple smaller databases. Use dblink. http://www.postgresql.org/docs/9.1/static/contrib-dblink-con...

I've never had a problem managing "schema and dependencies" on larger systems. Not sure what you are referring to there.

-scalability: I/O is often much more expensive than logic. With a well-normalized, well-indexed database, pushing logic off to the server often gives the query optimizer a chance to radically reduce how much data is retrieved. That can translate to less load on the disk controller, memory, controller, CPU, and network, and by extension more scalability. Of course, it might not work out that way, depending on the specific case. As usual, metrics rule all and slavishly following what should be rules of thumb at best is the worst kind of premature optimization.

- Compelxity, lock in: The DBMS is like any other software component: If you're actually using it to its capabilities, then it's trivially obvious that it should be difficult to replace, because there are a lot of capabilities you have to find substitutes for. True, you could avoid being coupled to your dependencies by choosing to reinvent wheels whenever possible instead. That would obviously reduce coupling. But bloating your codebase is generally not the best way to make your code more maintainable.

- schema: Pretty much any programming task is hell for people who aren't skilled at it. If you find that you're not very good at using the standard tools of a carpenter, that does not mean that the tools are fundamentally misdesigned. Much more likely, it means that you should be hiring someone else to do your carpentry for you. It's worth pointing out here that programmers shouldn't need to touch the schema in a well-designed system: They should be touching views and stored procedures that abstract away the implementation details.

You seem to be coming a very specific viewpoint. Not all things you build will require scaling out. Not all systems will become as complex as you seem to be envisioning. Not all systems will be around in 20 years, much less require the types of changes in 20 years that necessitate changing the datastore.

I view it as an optimization problem. Based upon what you're building, the aforementioned issues are more or less likely to occur. Optimize for what is likely.

If I'm building an official site for a popular product, I'd bet on all the above being true.

Some of what I build, even if 100% of people who could use the product would use it, will run just fine with a single database server that costs in the area of $8k. Planning for scale out is a complete waste of time here.

Most of what I build starts out simple. Most of what I build starts out with 0 users. Will it be popular, will it last, will it need to scale? We have no clue. For these, we keep it simple: modularize our code based upon features, use our datastores to their fullest within features, and keep in mind how we might scale things out (which may ultimately be incorrect) - if we have to. Usually we don't have to. I believe it to be a fair balance in this scenario.

Also, given your constraints, I don't quite understand how you could find a future datastore that is a better solution. How can you exploit better solutions and avoid lockin at the same time? If a better solution comes along in the future, it seems like you would be using features that would lock your datastore to that newer, better solution. However, you cannot do this because you need to avoid lockin.

Scale up is damn expensive. When you need that 64 core 96Gb machine to run it all on with 6 replicas will see what I mean.

Damn expensive, eh?

As it happens I bought a couple boxes that size just recently. Except with 256G RAM instead of 96G. They cost $8500 per shot. That's not even 2 months of salary for a single developer.

Don't underestimate Moore's Law.

If you are paying for your RDBMS (e.g. Oracle, SQL Server) and not using its features you are throwing your money away. Even if you are using a "free" database, if you're just tossing aside things like data types, permissions, referential integrity and check constraints, natural declarative handling of sets, and writing all that in your client application what happens when you want to put another front end on your data? You have to do all that again. Where's the hell then?

What happens when someone has to do a SQL update manually? Referential integrity can save you from shooting yourself in the foot there. SQL injection? Much less likely if you're using stored procedures with parameters as your client interface.

You have lock-in all over the place regardless. Languages. Frameworks. ORMs. Operating systems. All are decisions that are painful to change when something "better" comes along. You're better off making those decisions and then leveraging them for all you can get. RDBMSs have been around longer than many languages and most modern frameworks, and are still a very good, probably the best, general purpose solution for data storage and management.

"The DBMS is a black box"

The DBA you refuse to hire would likely disagree.

KV stores are painful to use. Only huge applications that are DB-bound have the problem of scaling database and they usually have a lot of money. Also you don't need your replicas to be as strong as your master db server.
Don't confuse the "RDBMS uber alles!" for all storage is equivalent to "RDBMS is always a fail." Certainly over the last decade or so there was a mistaken belief that all data should be in an RDBMS, even though they were flattened or required no relational algebra, but that doesn't discount the benefit of a tool that can handle such relations when necessary.

Redis solves a certain set of problems for me, Memcached as well. ElasticSearch solves another set, and RDBMS yet another. Square pegs for square holes, round pegs for round holes.

Huh? Your worried about complexity and lock in, so you want devs to replicate tried and true database features?

I can understand wanting to avoid Oracle lockin.... But Postgres?

I agree with you but I often question when people list "lock in" as an issue. How often do you see projects where they decide to move from one db to another? Even in cases where an ORM is used, making the migration process painless in theory, I doubt many projects take the risk and exercise their freedom of not being locked in.
On the other hand, SQL lets you write concise and efficient queries that run without lots of round-trips between the db and whatever machine you're accessing it from. ORMs force you to write less expressive code that is less efficient, hardly a winning combination.
- schema. Maintaining schema and dependencies is hell on all but a small system

When you have the tools you have the power.

Being "locked in" to an opensource database doesn't strike me as some kind of unbearable death trap.
I disagree with everything you say here. I seriously doubt you've been using databases for 20 years. Which databases have you used? How many hours experience do you have with each of them? Just because some people do it wrong doesn't mean you should throw out the baby with the bath water! In every stack I've used the database has been the most robust and amazing piece of technology and it's been an absolute dream come true for every project I've been involved in. (SQL Server / PostgreSQL - 10,000 hours experience)
The one of the biggest advantages of PostgreSQL is GiST (Generalized Search Tree) which is based on the theory of indexability.

> One advantage of GiST is that it allows the development of custom data types with the appropriate access methods, by an expert in the domain of the data type, rather than a database expert.

http://www.postgresql.org/docs/9.1/static/gist-intro.html

> Traditionally, implementing a new index access method meant a lot of difficult work. It was necessary to understand the inner workings of the database, such as the lock manager and Write-Ahead Log. The GiST interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GiST layer itself takes care of concurrency, logging and searching the tree structure.

> [...]

> So if you index, say, an image collection with a PostgreSQL B-tree, you can only issue queries such as "is imagex equal to imagey", "is imagex less than imagey" and "is imagex greater than imagey". Depending on how you define "equals", "less than" and "greater than" in this context, this could be useful. However, by using a GiST based index, you could create ways to ask domain-specific questions, perhaps "find all images of horses" or "find all over-exposed images".

http://www.postgresql.org/docs/9.1/static/gist-extensibility...

If you are already using PostgreSQL though have not known about this fact, I highly recommend you to learn about GiST. It is the most powerful feature of PostgreSQL as I know.

If you are doing anything serious with GeoSpatial, you should be using PostgreSQL with PostGIS.
Beat me to it, so upvotes :)

Let me pile on a big one: Stored procedures in multiple languages:

* python * perl * PHP (though I could not get it to work on 9.1)

Both Postgres and MySQL are great. In history, Postgres emphasized more on feature development instead of performance, while MySQL took the opposite approach. It depends on your engineering and operation requirements to choose which one to deploy. Most of OP's points, however, need to have further consideration, IMHO.

- "While replication is indeed very important, are users actually setting up replication each time with MySQL or is it to only have the option later?"

Replication is not an option. It is a must-have for any serious products, both in scaling and in operation.

- Windows functions: They are wonderful and I love them. But it is not an important factor at all.

- Flexible datatypes: True in certain scenarios. It allows to create certain types to map a business object with unusual requirements. Otherwise, the requirements have to be implemented in application logic. But data type of Array? IMHO, use of Array data type in relational database usually means there are some issues in data modeling and design. The performance is another issue. If you have to use array, consider NoSQL options.

- Functions. Although I am a database architect, I use database functions only for simple poking around. For any serious work, I prefer to write application code. It is easier to maintain, to test, and to extend. I don't want to have a big muddy ball. I prefer to have structured, well-decoupled application code.

- Custom Language. Same as above. Why not just write application code?

- Extensions: Geospatial is awesome. It is better than that in MySQL. But for many extensions, I prefer not to do the computation in database. Databases are usually the most expensive resources (in operation costs) and are usually the performance bottle neck. It would be better to have the computation in app servers. It is easier to scale and it is cheaper.

- Custom Functions: see above.

- Common Table Expressions: Recursive queries are awesome. I t is nice to have for ad-hoc data inspection, but I wouldn't encourage its usage in application code. The risk to use it wrong vs the benefits are high if used in application code.

> - Windows functions: They are wonderful and I love them. But it is not an important factor at all.

I disagree or would choose to qualify a number of the things you wrote, but I'll choose this one because I think it's the most interesting:

While window functions are seldom (but not never) used by common application workloads, but I find them pretty important for lowering the barrier of what one-off queries I can write (hence, more questions are economic to answer) to figure out some statistics that assist doing business or solving strange problems. The alternative is to be forced to make a greater number of decisions with less supporting data.

This can be applied to any labor-saving device when it comes to reporting, but I think window functions make the cut for me overall, and it's great they are there.

PostgreSQL is awesome (I really love it), now let's do some complaining:

* I'm not fan of doing backups with pg_dump. I think it would be a lot more awesome if I could tell the database to back itself up in-band. Even better, I would like to be able to ask the database to schedule its own backups, instead of having to set up my own cron jobs (or equivalents). Commercial databases can do that.

* The sad state of libpq (the C API). Libpq is where PostgreSQL really shows its age. It's very cumbersome to use and can't do (or can't do well) a bunch of things the raw protocol is capable of. Since almost all PostgreSQL bindings are built on libpq, none of the bindings support these features either.

* It would be cool if it was possible to kill sessions in-band (other database servers can do that). With PostgreSQL, the only way to shut down misbehaving sessions is sshing in to the database server and doing a `kill` on the process of the session.

* The infamous COUNT(*) fiasco.

There is no fiasco in count(*), it is THE general behavior of any MVCC database. Try the same with MySQL InnoDB, you get the same result (or worse). MySQL MyISAM DB gets the result fast because it is non-trasactional. To get approximate count very quickly on Pg, you can use -- select reltuples from pg_class where relname='<table_name>';
* Check out wal-e (https://github.com/heroku/wal-e), we wrote it and we manage 250k postgres databases with it. :)

* libpq does basically suck, but go has a pretty great native implementation. suggestions are welcome for improvement, patches are even better.

* pg_cancel_backend() plus select-fu on the `pg_locks` table joined with `pg_stat_activity` = joy

* fast count(*) is possible via index-only scans in 9.2

Depending on how badly the session is misbehaving: http://www.postgresql.org/docs/9.1/static/libpq-cancel.html or pg_cancel_backend(pid int) or pg_terminate_backend(pid int)
As others have pointed out, there is no such "fiasco". However, if you want to simply count the entire table and don't care if the number is exact within the current transaction, you can cheat and do:

    select n_live_tup from pg_stat_user_tables
    where relname = 'your_table_name'
This will access the internal statistics about how many tuples are in the table. It ignores the transaction's isolation level since it's a global value, and it may be a few milliseconds out of date, but it's otherwise correct.
List of reasons is so short...

For example since 9.1 Postgres support foreign data tables - other data sources is available for user as regular table; there are many drivers: MySQL, Oracle, external pg server, and even plain files.

Since 9.0 there is support for per-column triggers.

I know this isn't a MySQL versus PostgreSQL thread, but I was bitten by MySQL today...

The issue: I ran a SELECT with a WHERE against a column that is an "int(11)", but I meant to run it against another column that is "varchar(255)". Something like: SELECT * FROM table WHERE wrong_column = "1abcdefghijk";

Somehow MySQL cast a "varchar(255)" to an "int(11)" with a value "1" without telling me. WTF? My result set was approximately 1M rows. I expected 1 row, so knew the result was wrong... But what if I had expected more than 1 row? Then the bizarre results from MySQL would have appeared to make sense.

Maybe there's a deeper reason PHP and MySQL are so closely linked.
This is my least favorite MySQL misfeature, and it's something that users migrating FROM MySQL often rely on in their application and get surprised/angry when they encounter errors rather than silent successes.
Great points. I'd love to see someone tackle how to get a corporate environment to consider Postgres over MS-SQL. We weren't able to, due to MS-SQL's great amount of "out of the box" functionality regarding automatic failover, mirroring, clustering, etc. PG of course can do all of this but to convince the DBAs to throw away their point-and-click interfaces so that we could start writing custom scripts and listeners from scratch wasn't really feasible.
You left out one of my faves. Parent / child tables (ie tabular inheritance). Makes time series stuff so much nicer as pruning over time can be performed with truncate as opposed to "delete from".
ActiveRecord 4.0 will support postgresql custom types, by the way. https://github.com/rails/rails/pull/4775 Thanks @tenderlove!
Call me lazy, but this is why I don't use Postgres: Amazon RDS is MySQL

Its that simple. I can spend a lot of time setting up and monitoring a Postgres cluster, or I can spend about 5 minutes configuring a MultiA-Z RDS instance. However, the moment Amazon offers Postgres on RDS instances, I'll make plans to switch.

Also: fast (having a properly smart query planner) and, in my experience, fantastically reliable.
Is there a software that balances databases on different servers just like mongodb sharding works, by range? So for example i have a SaaS and each customer has 1 database. And the databases are migrated from one server to another automatically? If not is there something similar? Thanks
Two I particularly like:

- CREATE DOMAIN

- ON UPDATE CASCADE

Oh, and yeah, a BOOLEAN datatype. That's one in the eye to Larry Ellison.

What would make it absolutely the bee's knees would be a decent data language on top of that lovely data engine. Something closed over composition, with a simple, orthogonal syntax. NoSQL in the truest form — not a blind rejection of Codd's algebra, but something LESS DUMB STUPID THAN THE WRETCHEDNESS THAT IS SQL.

Is there a good inexpensive hosting option for postgres aside from just running it yourself? Sort of like RDS on Amazon? Alternatively a DBaaS host for postgres?
Two points:

- using pg'a arrays is using SQL against its grain. If you need multiple phone records per user just create a user_data table with three columns (user, key, value)

- the number one reason to prefer PostgreSQL over MySQL is just correctness. Every second query I write in MySQL get a wrong answer.

I'm pretty impressed that something of this complexity can be implemented entirely in C. People complain about the lack of tools for building higher level abstractions in C but this shows that with the right code discipline it can scale quite high.
Great article.

As someone coming from a Maths background - instead of pure CS - I'd never heard of some of the features you highlighted. If you consider expanding the article, please could you put in a line or 2 explaining what a feature is?

I love that there are so many options for writing stored procedures. There is the traditional pl/pgsql (matches quite closely to Oracle's plsql) but also Tcl, Perl, Python, Ruby, Lua, and others.