Realtime search capability

[ind-igo]

Hello all,

Big fan of Supabase and its philosophy! I wanted to throw an idea out there that might be of use.

Most full-text search implemented across various web apps is currently done with Elasticsearch. However, for many simple to moderately-complex use cases this is overkill, and another dependency to learn and maintain for developers. Many don't know that Postgres has powerful full-text search capability on its own with tsvector and other features like trigram indexes. What really got me interested in using Postgres for search was this great article:
https://devrain.io/ecto/phoenix/elixir/postgresql/2020/05/14/postgres-and-phoenix-search-without-triggers.html

Here is a relevant quote about the motivation:

Searches were fast but some pages turned out to be slow. This was because the materialized views were recreated by every trigger (create, update, delete) of one entity and building the materialized view could take up to 3 seconds.

...Since the triggers, and thus the refreshes, are happening in the same transaction as the statement, all create/update/delete actions on one table had to wait for each other. This could become very slow and batch operations also needed to ensure that the triggers were off before firing.

To improve performance we needed a way of asynchronously refreshing our views. This is a difficult task for PostgreSQL but for Elixir this fits like a glove. We removed the database triggers and replaced them with a GenServer.

I know that Realtime is built on top of Phoenix and Elixir, and thought that maybe the same stack could be used for creating a service for realtime full-text search as an additional feature for Supabase. Please let me know what you all think, and if this might be a good feature to add. I am fairly new to Elixir and am learning Postgres, but would love to contribute to something like this. Thanks!

[kiwicopple]

Hey @sali0 - thanks for this, it's fascinating. I'd love it if they had the code available, but from what I can glean from the article:

1. All insert/update/delete operations to a table goes through an Elixir server
2. The operation then checks if there are any views which involve the table being operated on
3. For any views returned, it calls REFRESH MATERIALIZED VIEW CONCURRENTLY

This is quite cool. We could actually swap 1 with our realtime server (you wouldn't need to update "through" our server, you could just update the database from anywhere).

Also, REFRESH MATERIALIZED VIEW CONCURRENTLY, will refresh the entire view. The holy grail is to do incremental refreshes - only updating the information that's relevant. I've been hitting my head against the wall trying to figure out how to do this, while simultaneously preserving Row Level Security.

This article gave me a could of ideas though. I couldn't find the code - do you know if they published it?

[fatboypunk]

Hi I'm the author of the related article. What kind of code are you are you missing in the article, I think I've put in pretty much all code that we have to realize this.

One remark about the following.

1. All insert/update/delete `operations` to a table goes through an Elixir server

It's not really a separate server, it's a GenServer which is basically a process in Elixir that can handle messages, execute code and keep state..

Let me know if I could help with anything.

[kiwicopple]

Hey @fatboypunk , thanks for dropping in

What kind of code are you are you missing

I was looking for a standalone repo where I could try it out. I guess i could build it myself from the code in the article but I didn't have the time.

1. All insert/update/delete operations to a table goes through an Elixir server

What i mean here is this: if I connected to my Postgres database directly via psql and ran an update on the underlying tables, the Materialized View wouldn't be updated, correct? The MV is now dependent on the Elixir server - it wont refresh unless the Elixir server receives an insert/update/delete command.

Still this seems like you're onto something - I would love to see how far this can be taken to see if it can do something like Differential Dataflow (like Noria)

[fatboypunk]

I was looking for a standalone repo where I could try it out. I guess i could build it myself from the code in the article but I didn't have the time.

Ah yes that would be nice, I will look into that once once i have the time for it.

What i mean here is this: if I connected to my Postgres database directly via psql and ran an update on the underlying tables, the Materialized View wouldn't be updated, correct? The MV is now dependent on the Elixir server - it wont refresh unless the Elixir server receives an insert/update/delete command.

You're right in psql you'd have to run the refresh command by hand as well.

I would love to see how far this can be taken to see if it can do something like Differential Dataflow (like Noria)

That would be interesting