8 ms·
In the later part of the slides, I learned that Postgres 12 now supports case and accent insensitive collations. https://www.postgresql.org/docs/current/collati
by idoubtit 7y ago
In the later part of the slides, I learned that Postgres 12 now supports case and accent insensitive collations.
https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC https://www.postgresql.org/docs/current/collation.html#COLLA...
For the many simple cases where a grep-like search is enough, my little experience with pg was far from satisfying. MySQL is simple to use for this thanks to the awfully named `utf8_mb4_unicode_ci`. With Postgres, searching for "étranger" won't find "Etranger" and "Étranger" unless you put some hard work into your database search system.
Unfortunately, it seems that PG12 won't change much in that aspect. From the link above, you have to create the collation (which is already a heavy restriction) and then "certain operations are not possible with nondeterministic collations, such as pattern matching operations." Looks like uncharted territory.
On a side note, I've always seen Solr, SphinxSearch and such specialized tools used instead of the DB's FTS. The main drawback is keeping the data in sync, but it has many pros.
- SigmundA 7y agoPG could really do most of it if they got serious about https://github.com/postgrespro/rum https://github.com/postgrespro/rum with TF/IDF ranking.
- irrational 7y agoCREATE EXTENSION unaccent; begin; CREATE OR REPLACE FUNCTION public.insensitive_query(text) RETURNS text AS $func$ SELECT lower(public.unaccent('public.unaccent', CAST($1 AS text))) $func$ LANGUAGE sql IMMUTABLE; ALTER FUNCTION public.insensitive_query(text) OWNER TO [your user that has create rights on the database]; commit; To use in a query: WHERE insensitive_query(some_table.name) LIKE insensitive_query('%Étranger%')
- rakoo 7y agoIf you use a function like this, it will need to be run for every entry; doesn't that invalidate the whole point of having indexes ? What you can do is storing the unaccented, simplified version of a word. Which I'm sure pg already does in a way.
- cies 7y ago> If you use a function like this, [each query] it will need to be run for every entry
- rzwitserloot 7y agoNo; if you create an index for the item: `insensitiveQuery(some_table.name)` and you then attempt to run: `SELECT * FROM some_table WHERE insensitiveQuery(name) LIKE insensitiveQuery('Étranger');`, the engine would run the `insensitiveQuery` function on the string `'Étranger'`, and then look up that result in the index directly, which is O(nlogn) or faster. If you have a million tables, that'd run the function only once (on `Étranger`) and do about 20 lookups in the index before finding you the qualifying row(s).
- cies 7y agoCheer. I did not get the index-on-a-derived-value bit.
- Mister_Snuggles 7y agoYou can also create an index on the result of the function[0]. [0] https://www.postgresql.org/docs/12/indexes-expressional.html https://www.postgresql.org/docs/12/indexes-expressional.html
- irrational 7y agoI forgot to include the index and it is too late to edit my comment. CREATE INDEX index_name ON some_table (public.insensitive_query(NAME));
- jrumbut 7y agoThis is a lot more graceful than some of the other workarounds I've seen/tried before. Thank you and I feel a little sheepish I didn't do it first!
- stereolambda 7y agoI'm getting the impression that recreating functionality from Lucene on top of RDBMS abstractions is certainly possible, but requires a lot of effort from devs and you still need to understand stuff like tokenization, stemming, inverted indexes etc. if you want to use it. At this point one may as well just learn Solr or Elasticsearch. And these are still better at searching at the low level, although of course you sacrifice consistency guarantees. This is a legitimately different problem arena, not NoSQL for the sake of it.
- mistrial9 7y ago> recreating functionality from Lucene on top of RDBMS strange mix of assumptions there, since searching text strings is Job One of computer search since 'forever' .. if Lucene has done some (search) things well, there is no reason that others do not have a chance to also do those things well. In fact, encourage it, foster it, engage with the process that brings new software capacities ..
- stereolambda 7y agoSure, I was meaning all the structures and algorithmic stuff we do to limit as much as possible straight up scanning strings. I came to appreciate much the work accumulated, layer by layer, in Lucene. Of course you can redo it elsewhere and it's a worthy programming enterprise but often not directly related to what you want to accomplish with your application.
- galacticdessert 7y agoI see your point, but I also see why RDBMS are appealing. We just built a documentation portal for our customers, and we initially used ElasticSearch for as-you-type full text search. I ended up redoing the implementation with SQLite (using fts5) and a simple express api later on top of it. The resource consumption is 3 orders of magnitude lower, the speed is the same and the quality of the results comparable. Elastic was crashing on us due to excessive heap memory consumption, even if 2gb of ram was assigned to it and we had two nodes. I thought the resource consumption was crazy for a light search use case, and found out that SQLite can do the same with 20 mbs of ram.
- blaisio 7y agoFor MySQL, most of the time it is better to not use a real collation at all and just do utf8mb4_bin. This disabled weird features like case insensitivity. You do need to make sure you properly encode strings, especially if you are working with combining code points. But it is much simpler and a little more efficient.
- bmn__ 7y agohttp://www.public-software-group.org/pg_collkey http://www.public-software-group.org/pg_collkey exists, first release in 2006 for PostgreSQL 8. Those who wanted Unicode-compliant collation support had already installed it years ago.