Underrated Features of PostgreSQL
ledgersmbdev.blogspot.com
ledgersmbdev.blogspot.com
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...
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.
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)
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.
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.
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.
Things that I miss in postgresql: - materialized views !! - a better graphical management tool.
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.
SQL/MED is also worthy of mention, but again kind of fell outside my focus.
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.
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..
#> psql mydbI just learned SP has bundles, like TextMate. Nifty.
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."
On Windows, I find that I quite like pgadmin3 as compared to MySQL workbench but it's likely just a matter of taste.
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.