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.
Underrated Features of PostgreSQL
41–50 of 51 posts
Re: Underrated Features of PostgreSQL
#42Where is transactional DDL on this list?
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
#43On 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.
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
#44Using 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 y…
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
#45Using 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 y…
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.
guess not.
Re: Underrated Features of PostgreSQL
#46Clustered indexes are really useful, too. They drastically speed up certain types of queries, though there is the overhead of having to periodically run CLUSTER.
Clustering is also faster than VACUUM FULL and can be used as a replacement where that is needed.
Re: Underrated Features of PostgreSQL
#47Using 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 y…
Wondering if you can write sprocs that use Lucene in PL/J?
Re: Underrated Features of PostgreSQL
#48Clustered indexes are really useful, too. They drastically speed up certain types of queries, though there is the overhead of having to periodically run CLUSTER.
Clustering is also faster than VACUUM FULL and can be used as a replacement where that is needed.
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
#49The 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..
DbVis is GREAT and multi-db management tool http://www.dbvis.com/
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
#50Earlier quoted context omitted.
What's wrong with the console ?
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.
People everywhere seem terrified of the console. It has a banner over it saying "Know What You Are Doing" and most would prefer to stab away at tree controls and tabs randomly filling in fields until it does something similar to what they want.