News for hackers. Stuff that matters.
Re:Underrated Features of PostgreSQL by glenngillen on Wednesday November 02, 2011 @11:06AM (#3187072)
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: http://ryandotsmith.heroku.com/2011/09/queue_classic.html
Re:Underrated Features of PostgreSQL by famousactress on Wednesday November 02, 2011 @11:41AM (#3187287)
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 ?
Re:Underrated Features of PostgreSQL by kijin on Wednesday November 02, 2011 @12:02PM (#3187407)
According to the manual [1], this is one of the standard use cases for partial indexes. See the last section.

CREATE UNIQUE INDEX index_name ON table_name (a, b) WHERE deleted_time IS NULL;

[1] http://www.postgresql.org/docs/9.1/static/indexes-partial.ht...

Re:Underrated Features of PostgreSQL by reuven on Wednesday November 02, 2011 @11:36AM (#3187260)
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.
Re:Underrated Features of PostgreSQL by re_todd on Wednesday November 02, 2011 @12:49PM (#3187675)
I used it about 2 years before realizing it had regular expressions, which have made some of my queries so much easier. http://www.postgresql.org/docs/9.0/static/functions-matching...
Re:Underrated Features of PostgreSQL by techscruggs on Wednesday November 02, 2011 @01:53PM (#3188039)
Wow, I have to ask, what do you think are some of the best resources to pull on for postgres knowledge are? and do you blog/tweet about your postgres work? if so, where?
Re:Underrated Features of PostgreSQL by reuven on Wednesday November 02, 2011 @02:24PM (#3188208)
Do I blog or tweet about it? Oh, I really, really should. (And I enjoy doing so when I do.) I used to blog a bit, but consulting + family + my (oh-so-dragged-out) PhD work tend to eat into my time. But yeah, I really should. Maybe your comment will push me back into occasional blogging on technical subjects.

Fortunately, there are some amazing resources out there. The pgsql-general e-mail list has oodles of helpful people, and great questions (and answers!). Get it in digest format, or prepare to have your inbox inundated with PostgreSQL goodness. But I've learned a ton just from reading messages on that list.

There are some great PostgreSQL-related blogs, as well. Many are on the "Planet PostgreSQL" aggregator, at http://planet.postgresql.org/ . Between those blogs and the e-mail list, you'll have lots of good stuff to read.

Finally, Packt published two books about PostgreSQL 9.x in the last year, both of which are excellent. One is an administrative cookbook, and the other is a high-performance guide. Definitely worth reading (and re-reading).

I'm in Israel, where MySQL is the open-source database king, and where people have barely heard of PostgreSQL. But I get about a call a month from someone who needs help with their PostgreSQL usage or configuration, and besides the nice feeling of having become the local go-to guy on the subject, it's also fascinating for me to see just how many serious places are using PostgreSQL -- but under the radar, without fanfare. They know that it's a great database, but no one else knows that they know.

Re:Underrated Features of PostgreSQL by jeltz on Wednesday November 02, 2011 @07:52PM (#3189469)
Also I can highly recommend the #postgresql IRC channel at freenode. The people there are helpful and polite.
Re:Underrated Features of PostgreSQL by techscruggs on Wednesday November 02, 2011 @07:15PM (#3189369)
Thanks! I have and love the two Packt books. I'll check out the rest. If end up blogging, let me know. I'm @techscruggs
Re:Underrated Features of PostgreSQL by mjijackson on Wednesday November 02, 2011 @12:19PM (#3187502)
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.
Re:Underrated Features of PostgreSQL by y0ghur7_xxx on Wednesday November 02, 2011 @01:59PM (#3188079)
I also love Veil¹. A lot of applications I write are LOB apps with a lot of contorted ACLs on rows.

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.

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)

¹http://veil.projects.postgresql.org/curdocs/index.html

Re:Underrated Features of PostgreSQL by pielud on Wednesday November 02, 2011 @10:35AM (#3186874)
Where is transactional DDL on this list?
Re:Underrated Features of PostgreSQL by davidw on Wednesday November 02, 2011 @10:57AM (#3187015)
I think that's the sort of thing most people take for granted. It really shocked me to see that Mysql didn't have that.
Re:Underrated Features of PostgreSQL by samuel on Wednesday November 02, 2011 @11:15AM (#3187134)
transactional DDL for granted? Not at all! Oracle doesn't support that AFAIK. May be you were thinking about transactional DML?
Re:Underrated Features of PostgreSQL by davidw on Wednesday November 02, 2011 @05:06PM (#3188931)
Huh, weird. I don't know Oracle much at all... it seems that it does some things amazingly well, and others abominably. Strange beast.
Re:Underrated Features of PostgreSQL by jpitz on Wednesday November 02, 2011 @05:15PM (#3188967)
Oracle silently issues an implicit commit after each DDL statement. I think it kills a kitten each time.
Re:Underrated Features of PostgreSQL by treenyc on Wednesday November 02, 2011 @11:12AM (#3187112)
Web development is like a fashion industry. Just because it is popular it doesn't mean it has the functionalities.
Re:Underrated Features of PostgreSQL by ebiester on Wednesday November 02, 2011 @01:40PM (#3187971)
The functionality for fashion is "makes you look great."
Re:Underrated Features of PostgreSQL by michaelbuckbee on Wednesday November 02, 2011 @01:30PM (#3187920)
Transactional DDL is awesome with Rails migrations, it's not a huge thing, but being able to cleanly roll them forward and back when they fail or you screw something up in development is very nice (and certainly beats having to comment out half of your migration and re-run it like you need to do with MySQL).
Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @09:30PM (#3189771)
I mentioned it in the "this could be included but it didn't meet my criteria and here's why" section in the last couple paragraphs. Yes, it's highly underrated, but my focus was just a little narrow perhaps.
Re:Underrated Features of PostgreSQL by mapgrep on Wednesday November 02, 2011 @01:13PM (#3187836)
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 (http://stackoverflow.com/questions/1489617/how-do-you-do-phr...).

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.

Just my .02.

Re:Underrated Features of PostgreSQL by techscruggs on Wednesday November 02, 2011 @01:33PM (#3187939)
"High quality, world class text search is a basic prerequisite for a production web app these days"

That is a pretty bold statement, that I can't agree with. The right tool for the job can vary dependent on need. Perhaps you are building your MVP or you have a small ops team or search is an admin function or etc ...

If search is one of the main components of my site, then no, I'd probably not use Postgres for that. On the other hand, out right dismissing it sounds like a recipe for shaving yaks.

Re:Underrated Features of PostgreSQL by michaelbuckbee on Wednesday November 02, 2011 @01:27PM (#3187899)
I'm not sure it is a bad idea so much as not the one size fits all solution.

It's fast, easy administratively to setup, works on Heroku with no addons, and greatly reduced the gap between data entering our system and data being indexed for search results (an important consideration for our problem).

It's not perfect, but it is a definite improvement over our previous separate db + ft engine solution.

Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @10:03PM (#3189854)
Wondering about using the pg_trgm option for this sort of search. Might not be perfect but it might help a lot and as a side effect might suggest where words are misspelled too.
Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @11:06PM (#3190020)
guess not.
Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @11:55PM (#3190175)
Wondering if you can write sprocs that use Lucene in PL/J?
Re:Underrated Features of PostgreSQL by rbranson on Wednesday November 02, 2011 @04:36PM (#3188821)
Eh, it's good for basic stuff and getting to MVP, but I tend to agree that if search is a core feature, ElasticSearch or Solr are the way to go.
Re:Underrated Features of PostgreSQL by rdunklau on Wednesday November 02, 2011 @01:11PM (#3187819)
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.

Things that I miss in postgresql: - materialized views !! - a better graphical management tool.

Re:Underrated Features of PostgreSQL by omarqureshi on Wednesday November 02, 2011 @01:28PM (#3187907)
You can most certainly use matviews

http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized...

it's not part of pg - but, it is something that you can write yourself due to the awesomeness of plpgsql.

Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @09:52PM (#3189832)
I mentioned ARRAY support in the context of complex types, but it could definitely get its own entry. I haven't played much with hstore, but there are a few places LedgerSMB might be able to use it in the future.

SQL/MED is also worthy of mention, but again kind of fell outside my focus.

Re:Underrated Features of PostgreSQL by apinstein on Wednesday November 02, 2011 @08:25PM (#3189563)
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.
Re:Underrated Features of PostgreSQL by einhverfr on Wednesday November 02, 2011 @11:09PM (#3190034)
Clustering is also faster than VACUUM FULL and can be used as a replacement where that is needed.
Re:Underrated Features of PostgreSQL by jeffdavis on Thursday November 03, 2011 @12:48AM (#3190341)
As of 9.0, the old version of VACUUM FULL has been replaced with a new implementation closely resembling CLUSTER (except without anything to do with an index, of course).

So, if you need to reclaim unused space in a table (usually only useful after a large delete or update), VACUUM FULL is a reasonable way to do so.

But, if you happen to have a useful index order, you might as well use CLUSTER so you get that benefit at the same time.

Re:Underrated Features of PostgreSQL by saturn on Wednesday November 02, 2011 @11:26AM (#3187190)
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.

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

Re:Underrated Features of PostgreSQL by jpeterson on Wednesday November 02, 2011 @12:12PM (#3187452)

    #> psql mydb
Re:Underrated Features of PostgreSQL by jpitz on Wednesday November 02, 2011 @05:12PM (#3188950)
Seriously. The command line console for postgres really is that good. I'd rather spend a day in it than any vendor-provided or third party GUI for any database I have ever touched. This includes things like dbVisualizer, SquirrelSQL, pgAdmin3, SQL Server Management Studio, and the various flavors of Toad. A screen session with psql in one display and emacs in the other is heaven.
Re:Underrated Features of PostgreSQL by ryanbraganza on Wednesday November 02, 2011 @03:52PM (#3188649)
rails dbconsole is the same thing. (it runs psql for you with args as specified in your database config)
Re:Underrated Features of PostgreSQL by willcodeforfoo on Wednesday November 02, 2011 @12:57PM (#3187730)
Sequel Pro is awesome... I think they are working toward offering Postgres support: http://code.google.com/p/sequel-pro/issues/detail?id=362 but haven't seen anything concrete yet.

I just learned SP has bundles, like TextMate. Nifty.

Re:Underrated Features of PostgreSQL by Pewpewarrows on Wednesday November 02, 2011 @11:30AM (#3187220)
Is Navicat not a good option?
Re:Underrated Features of PostgreSQL by megaman821 on Wednesday November 02, 2011 @12:27PM (#3187537)
It is not free, but neither is my time. I find that Navicat is awesome.
Re:Underrated Features of PostgreSQL by michaelbuckbee on Wednesday November 02, 2011 @01:21PM (#3187871)
The Navicat Lite option is free and pretty adequate (on par feature wise with pgAdmin 3).
Re:Underrated Features of PostgreSQL by a2tech on Wednesday November 02, 2011 @06:45PM (#3189282)
DbVis is GREAT and multi-db management tool http://www.dbvis.com/
Re:Underrated Features of PostgreSQL by pak on Thursday November 03, 2011 @01:33AM (#3190462)
As soon as I see "available on all major operating systems" I groan, because of course it seems to be a Java app. The user experience for Cocoa apps on OS X blows away that of a Java app. The reason Sequel Pro is great is because it feels like a first-rate Mac app and seamlessly fits in with everything else.

You can make a Java app feel almost native on OS X, like with Cyberduck, but it is really, really hard and you have to prioritize that target in your design. Then what happens, as seen with Cyberduck, is the Mac version coming out pretty good and the Windows version feeling a little "off."

Re:Underrated Features of PostgreSQL by bsg75 on Thursday November 03, 2011 @03:42PM (#3193363)
I share some of your sentiments about x-plat Java UIs, but DbVisualizer does an admirable job. I now use on Windows for SQL Server for most tasks even when the native GUI is available. Its worth a look.
Re:Underrated Features of PostgreSQL by bockris on Wednesday November 02, 2011 @12:49PM (#3187676)
Just curious, is it a pgadmin3 on OSX that is the problem for you or pgadmin3 in general?

On Windows, I find that I quite like pgadmin3 as compared to MySQL workbench but it's likely just a matter of taste.

Re:Underrated Features of PostgreSQL by izak30 on Wednesday November 02, 2011 @12:44PM (#3187638)
You could do alright (and very customizable) with the django admin and introspection. "Somebody" should probably write up a guide on doing that.
Re:Underrated Features of PostgreSQL by caf on Wednesday November 02, 2011 @07:38PM (#3189440)
Have you tried Aqua Data Studio?
Re:Underrated Features of PostgreSQL by dramaticus3 on Wednesday November 02, 2011 @12:57PM (#3187732)
What's wrong with the console ?
Re:Underrated Features of PostgreSQL by reuven on Wednesday November 02, 2011 @03:46PM (#3188618)
Good question; I'm not sure. That said, none (literally none) of my PostgreSQL-using clients use psql, and they're somewhere between amazed and horrified that I prefer it for working with the database.

I'm not against GUIs, but it seems to me that psql has all of the advantages of the GUI administration tools, and then some.