

Underrated Features of PostgreSQL  - ceyhunkazel
http://ledgersmbdev.blogspot.com/2011/11/10-underrated-features-of-postgresql.html

======
glenngillen
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>

------
famousactress
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 ?

~~~
kijin
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...](http://www.postgresql.org/docs/9.1/static/indexes-partial.html)

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

~~~
techscruggs
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?

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

~~~
jeltz
Also I can highly recommend the #postgresql IRC channel at freenode. The
people there are helpful and polite.

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

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

------
pielud
Where is transactional DDL on this list?

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

~~~
samuel
transactional DDL for granted? Not at all! Oracle doesn't support that AFAIK.
May be you were thinking about transactional DML?

~~~
davidw
Huh, weird. I don't know Oracle much at all... it seems that it does some
things amazingly well, and others abominably. Strange beast.

------
mapgrep
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...](http://stackoverflow.com/questions/1489617/how-do-you-do-phrase-based-
full-text-search-in-postgres-that-takes-advantage-of)).

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.

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

~~~
einhverfr
guess not.

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

~~~
omarqureshi
You can most certainly use matviews

[http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized...](http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views)

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

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

~~~
einhverfr
Clustering is also faster than VACUUM FULL and can be used as a replacement
where that is needed.

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

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

~~~
jpeterson

        #> psql mydb

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

