<jfred>
Postgres as a backend with a Mongo protocol implementation is a neat idea
<ill_logic>
What's the interest in leaving Mongo? I know scaling comes up as an issue (at least in older versions of Mongo).
<ill_logic>
Scaling isn't really an issue for SS instances.
<ocdtrekkie>
Well, some people do not like that Mongo is now SSPL. But I guess there are also frustrating issues with upgrades/migrations.
<ocdtrekkie>
My understanding is we use Mongo in the first place less because we like Mongo, and more because Meteor originally couldn't be used any other way.
<isd>
Also, the version we're on supports neither joins nor transactions. So an upgrade of some kind would be nice.
<isd>
And when I looked into doing that it migrating a database automatically looked painful, and if we're going to go through the trouble I'd like to get off the treadmill. IIRC postgres tries a bit harder to make upgrading easier. But something like SQLite would be more ideal, as it would just become a non issue.
<ocdtrekkie>
I like SQLite too, though my understanding is if you start asking it to do harder things, you start pushing on the limits of what it can do as a database.
<xet7>
harder things like what?
<xet7>
SQLite can also do multi-user access, when coded correctly
<isd>
For us I think the fiddly things with SQLite are two fold, both solved by postgres: (1) meteor really wants a reactive data store, i.e. updates in the database should get pushed to applications so they can update the UI in response. postres has a NOTIFY construct that can be used for this, sqlite has nothing afaik. and (2) we have structured data in our current database; to switch to SQLite we'd have to flatten all of that into columns of strings,
<isd>
numbers, etc. Whereas postres just has a 'json' column type.
<isd>
So migration would be a lot easier.
<xet7>
Some have ported from PostgreSQL to SQLite because managing PostgreSQL servers is too expensive. With SQLite it's easier to self-host and have per-customer databases.
<xet7>
SQLite is more crash-proof than PostgreSQL
<xet7>
Most likely I need to hand-code vanilla JS or HTML4 to get only visible data to WeKan multiverse. Meteor is just too much.
<Corbin>
You can have a "json" column type in SQLite, but I think an extension is required in order to do queries into it.
<Corbin>
The "recommended" pattern that I'd give as a professional opinion would be to wrap SQLite with a daemon which owns its concurrency and handles notifications. At that point, Pg really does have an advantage architecturally; there's fewer moving parts overall.
<xet7>
I prefer changing JSON data to just normal tables, and maybe blobs. And doing normal SQL queries.
<isd>
xet7: in prinicple yes; the reason I say this is an advantage is from a migration perspective; this would let us switch over without having to do that all at once.
<isd>
Corbin: yeah, I think I'd seen an extension like that.
<isd>
But the thing linked above would be putting another daemon in front of postgres regardless, so so much for fewer moving parts.