Usage as a browser-based SQL db

[vjpr]

There are many queries I run in the backend that could run on the frontend.

For offline support and optimistic frontend UI updates, it's nice to work with an SQL db on the frontend.

pg-mem's use case focuses on unit testing, but it provides a nice JS interface to a PSQL-compatible DB which could be a nice building block for an in-memory frontend SQL database.

Interested in your thoughts on what would be required to extend pg-mem or extract pieces of it to build an in-memory db for browser usage.

[oguimbal]

Hi,

I actually already gave some thought about it.
Technically speaking this could be doable.

The data stored in pg-mem tables is only an immutable dictionary of raw data, so it could very much be serialized to indexedDb or even localStorage (see here). So that part is trivial.

The only thing that would require some work is due to the fact that pg-mem schema is not stored as data.
This was a way for me to simplify its implementation, but it has some tradeoffs. This has been discussed here

However, I see some drawbacks:

• pg-mem includes a full-featured parser, and is therefore not lightweight (we're speaking hundreds of kilobytes)
• pg-mem has some optimizations (basic indices implementations, etc), but it might misbehave on large datasets, especially on complex join requests, where it can quickly fallback to requential scans (leading to O(card(table1) * card(table2)) execution times).
• Given that pg-mem does not support concurrent transactions, that could be an issue in environments where transacted requests could executed in parallel.

[oguimbal]

BTW...
I know its not pgsql, wouldnt it be more appropriate to to look into things like js-store or alasql ? (never tested them myself)

[vjpr]

Thanks. I've been meaning to dive deeper into alasql - it does seem like the most mature browser-based sql engine.