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

PostgreSQL 9.6 Released

postgresql.org

Post reply on HN
Print view View original

131–136 of 136 posts

Re: PostgreSQL 9.6 Released

#131

Post by joshberkus »

ngrilly wrote: post #37
fabian2k wrote: post #17

Just from reading the documentation, the full text search features on Postgres already look pretty powerful. And it is encouraging that they are actively being worked on. I'm wondering how this compares to a dedicated search engine like Solr or Elasticsearch. Are there huge differences in performance, features or search quality? At which scale does using Postgres for full text search still make sense?

We use Xapian to search over millions of documents. We are thinking of switching to PostgreSQL built-in FTS to simplify our system. We ran an internal benchmark which showed that PostgresSQL can be competitive with Xapian, except when you need to rank results (in that case the performance is bad).

You'll be interested in ongoing work in this area, then. Oleg & Teodor are working on a new index type (RUM indexes, no less) which will speed up ranking operations conserably.

https://lwn.net/Articles/689387/

Re: PostgreSQL 9.6 Released

#132

Post by joshberkus »

greggyb wrote: post #5
jbkkd wrote: post #4

Congratulations to the PostgreSQL Global Development Group on a much-anticipated release. Curious about this: > parallelism can speed up big data queries by as much as 32 times faster Why would it be only 32 times faster? The sky's the limit if there aren't major bottlenecks on the way.

No one has tested a query that got more than 32x faster, so they don't want to promise something they can't prove.

That's pretty much the case, yes.

Re: PostgreSQL 9.6 Released

#133

Post by ngrilly »

joshberkus wrote: post #131
ngrilly wrote: post #37

Earlier quoted context omitted.

We use Xapian to search over millions of documents. We are thinking of switching to PostgreSQL built-in FTS to simplify our system. We ran an internal benchmark which showed that PostgresSQL can be competitive with Xapian, except when you need to rank results (in that case the performance is bad).

You'll be interested in ongoing work in this area, then. Oleg & Teodor are working on a new index type (RUM indexes, no less) which will speed up ranking operations conserably. https://lwn.net/Articles/689387/

Hi Josh. Yes, I'm aware of this new type of index. Oleg was even kind enough to answer my email asking a few question about it :-)

Re: PostgreSQL 9.6 Released

#134

Post by brightball »

nugator wrote: post #98
brightball wrote: post #23

Earlier quoted context omitted.

Having used all 3, Postgres search is my go to for most use case simply because I don't have to deal with managing deltas to an outside system and keeping things in sync. The search features are powerful and fast and PG's ability to combine multiple indexes in search results make it trivially easy to include a bit of full text search in a query right next to geographic distance filters or other conditions. You can al…

What about when having different PG database instances that has data you want to join on? Would you still use PG as an aggregated read-only copy of the databases or would you use for example ES?

It's situational. When you need a search across multiple data sources, standing up a dedicated search engine makes a lot more sense. Then again...PG Foreign Data wrappers would make that scenario pretty simple without the need for an aggregate.

I can't speak to performance in that situation though.

Re: PostgreSQL 9.6 Released

#135

Post by joshberkus »

rwultsch wrote: post #63
ngrilly wrote: post #41

Earlier quoted context omitted.

Yes, you can, but it doesn't change the fact that you still have a clustered index (an index organized table), which is great for PK lookups, but bad if you do a secondary indexes lookup (because you need to lookup through 2 B-trees instead of 1). There is real, and well-known, tradeoff here.

You missed the point of my post. You are going to have one of the two issues, either looking through two index or indexes including the a large PK. At least with InnoDB you can make the choice. The strategy I suggested gets you the desired outcome of not including a large PK in all secondary indexes.

The other part of the tradeoff is that inserts and many other write operations are less expensive in heap tables. A Big Table in InnoDB, measured in "when do I start having to spend a lot of time troubleshooting this table's performance" is about 1% the size of a Big Table in Postgres. TokuDB was introduced for MySQL for a reason.

Heap vs. Index organization is a classic tradeoff of database design.

Now, if you're saying "it would be really nice if Postgres had the option of index-organized tables" I'd agree with you. I'd love to have that, as an option.

Re: PostgreSQL 9.6 Released

#136

Post by zombodb »

chishaku wrote: post #64
fabian2k wrote: post #17

Just from reading the documentation, the full text search features on Postgres already look pretty powerful. And it is encouraging that they are actively being worked on. I'm wondering how this compares to a dedicated search engine like Solr or Elasticsearch. Are there huge differences in performance, features or search quality? At which scale does using Postgres for full text search still make sense?

Does anyone have experience with ZomboDB? "ZomboDB is a Postgres extension that enables efficient full-text searching via the use of indexes backed by Elasticsearch. In order to achieve this, ZomboDB implements Postgres' Access Method API. In practical terms, a ZomboDB index appears to Postgres as no different than a standard btree index. As such, standard SQL commands are fully supported, including SELECT, BEGIN, CO…

As the developer, I do!

Feel free to email the mailing list (zombodb@googlegroups.com). I'd be happy to help answer any questions you might have

Post reply on HN