Underrated Features of PostgreSQL • Hacker News Board

Re: Underrated Features of PostgreSQL

#21

Post by mapgrep » Wed, Nov 02, 2011, 5:13 PM UTC

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

#22

Post by michaelbuckbee » Wed, Nov 02, 2011, 5:21 PM UTC

megaman821 wrote: post #14

Pewpewarrows wrote: post #8

Earlier quoted context omitted.

Is Navicat not a good option?

It is not free, but neither is my time. I find that Navicat is awesome.

The Navicat Lite option is free and pretty adequate (on par feature wise with pgAdmin 3).

Re: Underrated Features of PostgreSQL

#23

Post by michaelbuckbee » Wed, Nov 02, 2011, 5:27 PM UTC

mapgrep wrote: post #21

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 y…

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

#24

Post by omarqureshi » Wed, Nov 02, 2011, 5:28 PM UTC

rdunklau wrote: post #20

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.

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

#25

Post by michaelbuckbee » Wed, Nov 02, 2011, 5:30 PM UTC

pielud wrote: post #2

Where is transactional DDL on this list?

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

#26

Post by techscruggs » Wed, Nov 02, 2011, 5:33 PM UTC

mapgrep wrote: post #21

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 y…

"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

#27

Post by ebiester » Wed, Nov 02, 2011, 5:40 PM UTC

treenyc wrote: post #5

davidw wrote: post #3

Earlier quoted context omitted.

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.

Web development is like a fashion industry. Just because it is popular it doesn't mean it has the functionalities.

The functionality for fashion is "makes you look great."

Re: Underrated Features of PostgreSQL

#28

Post by techscruggs » Wed, Nov 02, 2011, 5:53 PM UTC

reuven wrote: post #9

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.

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

#29

Post by y0ghur7_xxx » Wed, Nov 02, 2011, 5:59 PM UTC

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

#30

Post by reuven » Wed, Nov 02, 2011, 6:24 PM UTC

techscruggs wrote: post #28

reuven wrote: post #9

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.

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?

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.