9 ms·

I'm curious if we will see similar improvements for full-text search. If PostgreSQL continues to improve at this rate, there will be little reason to use many o

I'm curious if we will see similar improvements for full-text search. If PostgreSQL continues to improve at this rate, there will be little reason to use many of alternatives... Personally, I already view PostgreSQL comparable (for many use cases) to paid options[1]. Interesting, for queries such as: EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM sort1 ORDER BY 1) AS a LIMIT 5 ; You'll see an order of magnitude speed improvement (page 47 of [2]). [1] https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... [2] https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains...

anarazel 8y ago
I doubt it unfortunately - the FTS operators are too heavyweight to be inlined (or benefit from inlining), so JIT isn't likely to help a ton. Parallelism can help however, depending on what your bottleneck is (if the query produces a bitmap indexscan the heap access part can be parallelized).
SigmundA 8y ago
Seriously if PG gets better text search to put it on par with Lucene (Rum indexes, BM25 and TF*IDF) I don't see much reason to use anything else either.
- sandGorgon 8y ago
  exactly this. I wish they would update the relevance algorithms to use TFIDF and BM25. We've been praying for this for a long time.. and not really sure why they havent been implemented.
  - fgonzag 8y ago
    My guess would be because no developer with the skillet necessary has either been interested or been paid to do it. It is an open source project at the end of the day, which means the features that get added are those that are wanted by either the developers or the sponsors, not the the general user base
    - qaq 8y ago
      Actually this might be a good idea for kickstarter if anyone with relevant experience is around :) I bet it would not be a problem to raise 200-300K for this
      - sandGorgon 8y ago
        i would definitely contribute. BTW - elastic.co is just going for an IPO. There's a lot of money in search!
  - Alex3917 8y ago
    Yeah for whatever advantages Elastic Search might have, I'd rather not read a 500 page book and then have to deal with dozens of potential security issues. I'd gladly have slightly less relevant search results in exchange for getting to use the same permissions system I use for all my other queries.
- allan_s 8y ago
  honest and naive question: What would be the difficulties to have these in PG, outside of "getting developer interested in implementing it" (i.e is there some current architecture decision in PG that prevent it, like it's the case for "why vanilla PG is not suitable as a column store")
  - SigmundA 8y ago
    My understanding is gin/gist indexes do not store enough information to use better scoring and RUM indexes could change that: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- um_ya 8y ago
  You can use Elasticsearch within Postgres by using zombodb.
- b9b10eb736 8y ago
  From the top of my head, I can list at least four reasons to actually use anything else: - Not distributable - Poor resiliency - Hard to upgrade - Schemas remains at postgres core Every solution to address any of these points in PG are hacks (bucardo, londist, slony, pgbouncer, things relying on triggers or proxies). Quite honestly, PG contributors are doing an impressive job. PG is arguably the best relational database in the market as of today and still improving very fast. However it's also one of the most (if not the most) bloated. And despite all these features nobody needs and after all these years, it's still lacking some very basic yet essential items anyone would ask for in any modern application. It feels like things haven't changed that much since the 90's. DBAs have to hack around things and root issues never gets addressed. Implementing JIT optimizations looks like fun. It will undoubtedly improve some queries by 2x, 4x or even 10x, but it won't be a game changer anyways. It'll just increase the postgres bloat and the overall complexity of the system. Meanwhile, we'll still be lacking essential things that would make PG suitable for pretty much any use-case.
  - minxomat 8y ago
    > And despite all these features nobody needs Please do speak for yourself.
    - b9b10eb736 8y ago
      "nobody needs" is to take in the unix philosophy sense here. If you take it first degree, you're part of the reason why PG has been missing these essential points for so long (IMHO). In comparison to the items I've listed, those features are nice to have but definitely not used by the majority of users. And I do use these advanced features (nobody needs) in PostgreSQL. I use them extensively and really love them. It's just that I could workaround them easily and properly. That's just not the case for the things I've listed.
      - scrollaway 8y ago
        Yes, the famous "People can work around our lack of features" unix philosophy. What?

anarazel 8y ago

I doubt it unfortunately - the FTS operators are too heavyweight to be inlined (or benefit from inlining), so JIT isn't likely to help a ton. Parallelism can help however, depending on what your bottleneck is (if the query produces a bitmap indexscan the heap access part can be parallelized).

SigmundA 8y ago

Seriously if PG gets better text search to put it on par with Lucene (Rum indexes, BM25 and TF*IDF) I don't see much reason to use anything else either.

sandGorgon 8y ago
exactly this. I wish they would update the relevance algorithms to use TFIDF and BM25. We've been praying for this for a long time.. and not really sure why they havent been implemented.
- fgonzag 8y ago
  My guess would be because no developer with the skillet necessary has either been interested or been paid to do it. It is an open source project at the end of the day, which means the features that get added are those that are wanted by either the developers or the sponsors, not the the general user base
  - qaq 8y ago
    Actually this might be a good idea for kickstarter if anyone with relevant experience is around :) I bet it would not be a problem to raise 200-300K for this
    - sandGorgon 8y ago
      i would definitely contribute. BTW - elastic.co is just going for an IPO. There's a lot of money in search!
- Alex3917 8y ago
  Yeah for whatever advantages Elastic Search might have, I'd rather not read a 500 page book and then have to deal with dozens of potential security issues. I'd gladly have slightly less relevant search results in exchange for getting to use the same permissions system I use for all my other queries.
allan_s 8y ago
honest and naive question: What would be the difficulties to have these in PG, outside of "getting developer interested in implementing it" (i.e is there some current architecture decision in PG that prevent it, like it's the case for "why vanilla PG is not suitable as a column store")
- SigmundA 8y ago
  My understanding is gin/gist indexes do not store enough information to use better scoring and RUM indexes could change that: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
um_ya 8y ago
You can use Elasticsearch within Postgres by using zombodb.
b9b10eb736 8y ago
From the top of my head, I can list at least four reasons to actually use anything else: - Not distributable - Poor resiliency - Hard to upgrade - Schemas remains at postgres core Every solution to address any of these points in PG are hacks (bucardo, londist, slony, pgbouncer, things relying on triggers or proxies). Quite honestly, PG contributors are doing an impressive job. PG is arguably the best relational database in the market as of today and still improving very fast. However it's also one of the most (if not the most) bloated. And despite all these features nobody needs and after all these years, it's still lacking some very basic yet essential items anyone would ask for in any modern application. It feels like things haven't changed that much since the 90's. DBAs have to hack around things and root issues never gets addressed. Implementing JIT optimizations looks like fun. It will undoubtedly improve some queries by 2x, 4x or even 10x, but it won't be a game changer anyways. It'll just increase the postgres bloat and the overall complexity of the system. Meanwhile, we'll still be lacking essential things that would make PG suitable for pretty much any use-case.
- minxomat 8y ago
  > And despite all these features nobody needs Please do speak for yourself.
  - b9b10eb736 8y ago
    "nobody needs" is to take in the unix philosophy sense here. If you take it first degree, you're part of the reason why PG has been missing these essential points for so long (IMHO). In comparison to the items I've listed, those features are nice to have but definitely not used by the majority of users. And I do use these advanced features (nobody needs) in PostgreSQL. I use them extensively and really love them. It's just that I could workaround them easily and properly. That's just not the case for the things I've listed.
    - scrollaway 8y ago
      Yes, the famous "People can work around our lack of features" unix philosophy. What?