Cartodb: As an engine user, I'd like really fast text-based searches.

Created on 13 Feb 2018 · 13Comments · Source: CartoDB/cartodb

In making sites and apps on carto.js, I'd like to get really fast text-based search results from a few of the columns in my data. This is important because people use our site for a lot of searching over lots of data and I'd like to make their experience better.

From experience, I'd need to get them down to the tens of milliseconds by adding pgtrgm indices to specific columns. But if there's a way we can get these really-fast LIKE queries to work using an already built-in index, I'd love to explore that as well.

@inigomedina @rochoa

engine enhancement

Source

zingbot

👍3

Most helpful comment

Worth noting that extensions like fuzzystrmatch and pg_trgrm are available on RDS. That kind of means "AWS has vetted these for local security issues and is fine w/ it"

pramsey on 13 Feb 2018

👍2

All 13 comments

Worth noting that extensions like fuzzystrmatch and pg_trgrm are available on RDS. That kind of means "AWS has vetted these for local security issues and is fine w/ it"

pramsey on 13 Feb 2018

👍2

I'm surprised we haven't had a little push from folks wanting pg_crypto as well, for app-building stuff. Encrypted-at-rest is a pretty useful standard to have for a PaaS

pramsey on 13 Feb 2018

This will be useful for us because we have a few typeahead search features that require pg_trgrm

allthesignals on 13 Feb 2018

It is unclear to me what's being requested here. Is this a feature? or just a request to enable a bunch of PG extensions?

If it is a feature, it will require some definition work: UX/UI, limits and the like.

rafatower on 2 Mar 2018

I think it’s just the latter, a request to enable the pg_trgrm Postgres extension on Carto accounts.

allthesignals on 2 Mar 2018

Yes, the request is

for the extensions to be built and installed (so an easy packaging task) and
for (some of) them to be enabled by default (pg_trgrm, fuzzystrmatch) or there be some way to enable them optionally (a properly privileged magic CDB_Extension() function maybe)

pramsey on 2 Mar 2018

@pramsey is this something the RT can handle (max of 2-day work by a backender)?

ramiroaznar on 8 Mar 2018

The PL/PGSQL piece in cartodb-postgres is very easy, no problem for RT. The piece of making sure that the extension is in fact built and installed in all the instances, RT can at least investigate and see what the state-of-play is, but if the answer is "no, it's not built and installed" then changing that could take a while.

pramsey on 8 Mar 2018

Roger that. @Algunenano can you have a quick look?

ramiroaznar on 8 Mar 2018

Both pg_trgrm and fuzzystrmatch seems to be built with our 9.5 release:

Same for the 10.1 servers:

@pramsey Do you know if there is any drawback to enabling it for all users or is it preferable to do it on demand (CDB_Enable_Extension with a whitelist)?

Algunenano on 8 Mar 2018

Well, it'll be installed per-database. Once we start letting people enable it, we're basically locked into supporting it forever, as database dumps will reference it. I think enabling it per-database is fine, it makes sense that a whole org should be able to use it, once it's turned on. Probably for orgs, we want only the org admin to be able to run the enabling function.

pramsey on 8 Mar 2018

Moving this to ENGINE kanban @dgaubert and summoning @inigomedina because this is also a product decision. Feel free to open a new issue to start an internal discussion.

ramiroaznar on 9 Mar 2018

👍1

Stale issue. Closing 👋