Ryan Smith has wrapped the use of LISTEN/NOTIFY into a really handy queuing library called queue_classic that we've been using internally at Heroku. He does a good write up of it over on his blog: <a href="http://ryandotsmith.heroku.com/2011/09/queue_classic.html" rel="nofollow">http://ryandotsmith.heroku.com/2011/09/queue_classic.html</a>
I used Postgres in a former life.. really liked it, but switched to MySQL because at the time pg didn't have a very palatable solution for running a read-slave. Boy have I been interested in jumping back since v8&9! A question about partial indexes... Can this be used to solve the problem of multi-column unique constraints with null values? That is.. Say I've got columns a, b, and deleted_time. If a null deleted_time means the item hasn't been deleted.. traditionally I can't create a useful unique constraint across them (because NULL != NULL). Does this postgres feature allow me to create a unique constraint on (A,B) where deleted_time is null ?
I've been using PostgreSQL for about 15 years, and it's a rare week when I don't discover a feature that makes my life (and that of my clients) easier and better in some way. The software is rock solid, the feature set both broad and deep, and the community is generous in many different ways. It's really a pleasure to use this database, and to see that a growing number of people are starting to use it.
This is a very well-written article. I really enjoyed the "What it is", "Why I like it", "How I'd use it" format. It is much more helpful than a simple list of features.
I also love Veil¹. A lot of applications I write are LOB apps with a lot of contorted ACLs on rows.<p>For example a user with role "Secretary" can view all calendar entries of her department, but not those of other departments, and a user with role "User" can only see the calendar entries of his office mates.<p>With Veil I can implement those access rules on the database once, and they are applied to all frontends for the data (web apps, desktop apps and mobile apps)<p>¹<a href="http://veil.projects.postgresql.org/curdocs/index.html" rel="nofollow">http://veil.projects.postgresql.org/curdocs/index.html</a>
Using PostgreSQL for full text search is a bad idea. There is no native support for exact phrase searching "like this;" there are some hacky workarounds but you lose stemming and have to do a scan (<a href="http://stackoverflow.com/questions/1489617/how-do-you-do-phrase-based-full-text-search-in-postgres-that-takes-advantage-of" rel="nofollow">http://stackoverflow.com/questions/1489617/how-do-you-do-phr...</a>).<p>High quality, world class text search is a basic prerequisite for a production web app these days (if your app needs search at all). The idea of keeping your database and search engine data in one silo is really beautiful, conceptually, but at the moment it is better for your users if you swallow the complexity of maintaining a parallel, dedicated full text search index (e.g. like Lucene) alongside your regular db. Relying on postgres for full text search is a three quarters solution, and if you believed in three quarter solutions you would not be using postgres in the first place.<p>Just my .02.
On the top of my head, another list of features worth looking at:
- ARRAY support: makes your life easier, especially the array_agg aggregation function
- HSTORE extension: use postgresql like a key/value store
- SQL/MED implementation, foreign data wrappers: allows you to define external data sources as an extension.<p>Things that I miss in postgresql:
- materialized views !!
- a better graphical management tool.
Clustered indexes are really useful, too. They drastically speed up certain types of queries, though there is the overhead of having to periodically run CLUSTER.
The one thing I miss after switching to PostgreSQL from MySQL is Sequel Pro. pgAdmin 3, sorry to say, sucks, and I grow tired of rails dbconsole - but that's my only option, really.<p>Please, someone, make a good osx pg client. Just copy Sequel Pro if you don't know what I mean by that. I would pay a lot of money for a good osx client, I am not kidding, I have to deal with this every day. I cannot be the only one..