Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL is Engine Yard's New Default

engineyard.com

Post reply on HN
Print view View original

11–20 of 21 posts

Re: PostgreSQL is Engine Yard's New Default

#11

Post by saurik »

buremba wrote: post #9
trustfundbaby wrote: post #3

Does anybody know how Postgres' fulltext search capabilities/performance compare to using something like ElasticSearch?

Postgresql cannot be fast as Lucene because of its not a "search engine". Postgresql full-text is fast enough for most cases but it doesn't have support for facet queries which is important for a search engine.

Actually, it does, which is why tools like PostGIS are built for it. In the most recent versions of PostgreSQL (I believe 9.1) they even added k-nearest-neighbor multi-dimensional index support to GiST.

Re: PostgreSQL is Engine Yard's New Default

#12

Post by einhverfr »

heretohelp wrote: post #5
trustfundbaby wrote: post #3

Does anybody know how Postgres' fulltext search capabilities/performance compare to using something like ElasticSearch?

Postgresql is nowhere, NOWHERE near as powerful or as fast, but it'll work for the cases where you're doing simple search-interface bolt-ons to an existing product already using PGSQL. Postgresql fulltext and ElasticSearch are ultimately solving two very different sorts of problems. The only case I can think of where you would prefer to use PGSQL is if you some weird blending of normal relational data with the docume…

As you say they solve two very different sorts of problems.

If you want to do full text searching of documents, probably PostgreSQL is not the right answer. If you want to pull all customers in the UK with notes searched full-text-wise for "did not pay" then PostgreSQL is much better just because it all integrates into the same filter.

Re: PostgreSQL is Engine Yard's New Default

#13

Post by astrodust »

elchief wrote: post #8

After like an entire year (duh) I figured out how to have InnoDB transactions with MyISAM full-text search... InnoDB master. MyISAM slaves.

The other way is to have an InnoDB master table with MyISAM derivatives kept in sync by triggers. Ugly but effective.

It's been way too long for InnoDB to be without that type of index.

Re: PostgreSQL is Engine Yard's New Default

#14

Post by heretohelp »

einhverfr wrote: post #12
heretohelp wrote: post #5

Earlier quoted context omitted.

Postgresql is nowhere, NOWHERE near as powerful or as fast, but it'll work for the cases where you're doing simple search-interface bolt-ons to an existing product already using PGSQL. Postgresql fulltext and ElasticSearch are ultimately solving two very different sorts of problems. The only case I can think of where you would prefer to use PGSQL is if you some weird blending of normal relational data with the docume…

As you say they solve two very different sorts of problems. If you want to do full text searching of documents, probably PostgreSQL is not the right answer. If you want to pull all customers in the UK with notes searched full-text-wise for "did not pay" then PostgreSQL is much better just because it all integrates into the same filter.

Fully agreed.

Re: PostgreSQL is Engine Yard's New Default

#15

Post by elchief »

astrodust wrote: post #13
elchief wrote: post #8

After like an entire year (duh) I figured out how to have InnoDB transactions with MyISAM full-text search... InnoDB master. MyISAM slaves.

The other way is to have an InnoDB master table with MyISAM derivatives kept in sync by triggers. Ugly but effective. It's been way too long for InnoDB to be without that type of index.

5.6 baby!

Re: PostgreSQL is Engine Yard's New Default

#16

Post by fdr »

trustfundbaby wrote: post #3

Does anybody know how Postgres' fulltext search capabilities/performance compare to using something like ElasticSearch?

I can't give you a comparison, but for what it's worth, it is used on the Postgres documentation and mailing list archives. I mention this because you can play with it and get an intuitive sense of its performance and subjective usefulness.

http://archives.postgresql.org/

http://www.postgresql.org/search/?u=%2Fdocs%2F9.1%2F&q=c...

It's also handy because you can combine it with relational data. For example, if you search from a manual page of a particular version of Postgres, it'll only return results from that version.

The main performance limitation is probably update/insert rate, and it doesn't has as many features as a dedicated IR-type system, but it's frequently good-enough and already present.

Re: PostgreSQL is Engine Yard's New Default

#17

Post by taligent »

elchief wrote: post #7
trustfundbaby wrote: post #3

Does anybody know how Postgres' fulltext search capabilities/performance compare to using something like ElasticSearch?

The main problem is that it's a major PITA to sync a db and an external search service. So it's nice to have FTS built-in

Depends on the database.

I know CouchDB and MongoDB both make it pretty easy to add Solr/ElasticSearch into the equation.

Re: PostgreSQL is Engine Yard's New Default

#18

Post by jeltz »

einhverfr wrote: post #2

Awesome. Given that their services look Ruby-centric, maybe they could take over PL/Ruby and point out that this would allow people to write their stored procs in Ruby too.

Yeah, PL/Ruby looks quite dead to me. Would be nice if someone got development of it up to speed again.

Re: PostgreSQL is Engine Yard's New Default

#19

Post by astrodust »

elchief wrote: post #15
astrodust wrote: post #13

Earlier quoted context omitted.

The other way is to have an InnoDB master table with MyISAM derivatives kept in sync by triggers. Ugly but effective. It's been way too long for InnoDB to be without that type of index.

5.6 baby!

Can't wait for it.

Re: PostgreSQL is Engine Yard's New Default

#20

Post by jeltz »

One important feature for PostgreSQL which they should have mentioned is transactional DDL. It makes applying migrations much less risky since you know if a statement in the migration fails all of it will be rolled back.

After having used PostgreSQL in production for some years I do not know how I could go back to a database without transactional DDL.

Post reply on HN