5 ms·
Following the PostgreSQL logical replication stream to update a local SQLite database copy is definitely a neat trick, and feels very safe to me (especially sin
by simonw 3y ago
Following the PostgreSQL logical replication stream to update a local SQLite database copy is definitely a neat trick, and feels very safe to me (especially since you track the Log Sequence Number in a postgres_pos table).
The bit that surprised me was that this thing supports writes as well!
It does it by acting as a PostgreSQL proxy. You connect to that proxy with a regular PostgreSQL client, then any read queries you issue run against the local SQLite copy and any writes are forwarded on to "real" PostgreSQL.
The downside is that now your SELECT statements all need to be in the subset of SQL that is supported by both SQLite and PostgreSQL. This can be pretty limiting, mainly because PostgreSQL SQL is a much, much richer dialect than SQLite.
Should work fine for basic SELECT queries though.
I'd find this project useful even without the PostgreSQL connection/write support though.
I worked with a very high-scale feature flag system a while ago - thousands of flag checks a second. This scaled using a local memcached cache of checks on each machine, despite the check logic itself consulting a MySQL database.
I had an idea to improve that system by running a local SQLite cache of the full flag logic on every frontend machine instead. That way flag checks could use full SQL logic, but would still run incredibly fast.
The challenge would be keeping that local SQLite database copy synced with the centralized source-of-truth database. A system like SQLedge could make short work of that problem.
- ericraio 3y agoOne use case I can see this being valuable for is for a client based application and Postgres being a centralized database. The client would just query SQLite and not need to write Postgres SQL.
- DaiPlusPlus 3y ago> I worked with a very high-scale feature flag system a while ago - thousands of flag checks a second. May I ask why the flags are checked that frequently? Couldn't they be cached for at least a minute? > It does it by acting as a PostgreSQL proxy. [...] and any writes are forwarded on to "real" PostgreSQL. What happens if there's a multi-statement transaction with a bunch of writes sent-off to the mothership - which then get returned to the client via logical replication, but then there's a ROLLBACK - how would that situation be handled such that both the SQLite edge DBs and the mothership DB are able to rollback okay - would this impact other clients?
- vasco 3y ago> May I ask why the flags are checked that frequently? Couldn't they be cached for at least a minute? Not in that project but feature flags don't have to be all or nothing. You can apply flags to specific cohorts of your users for example, so if you have a large user base, even if you cache them per-user, it still translates into many checks a second for large systems.
- mikepurvis 3y agoI guess the cost of doing it precisely isn't terribly high, but if the majority of the flags were "off" (eg, subsets of users being opted into a beta or something), I wonder if you cut a bunch of the queries by sending down a bloom filter. So basically you check the filter first, and if that says the feature is enabled, only then do you actually ask the real DB.
- kiitos 3y agoChecking a feature flag for a given cohort/user/demographic/etc. should always be a memory read, not a syscall. (You can't have syscalls in a hot loop!) That means feature flags will always be cached in each application instance from their source of truth. And that means you would never query the source of truth directly from the application, regardless of any property of the flag or its users.
- runeks 3y agoMy limited understanding of logical replication is that writes only happen at COMMIT. Ie. nothing is replicated until it's committed.
- zknill 3y agoThe logical replication protocol sends a series of messages that essentially follow the flow that a database transaction would. i.e. a stream of messages like: "BEGIN", "[the data]", ["COMMIT" or "ROLLBACK"]. So any application that listens to the Postgres replication protocol can handle the transaction in the same way that Postgres does. Concretely you might choose to open a SQLite transaction on BEGIN, apply the statements, and then COMMIT or ROLLBACK based on the next messages received on the stream replication protocol. The data sent on the replication protocol includes the state of the row after the write query has completed. This means you don't need to worry about getting out of sync on queries like "UPDATE field = field + 1" because you have access to the exact resulting value as stored by Postgres. TL;DR - you can follow the same begin/change/commit flow that the original transaction did on the upstream Postgres server, and you have access to the exact underlying data after the write was committed. It's also true (as other commenters have pointed out) that for not-huge transactions (i.e. not streaming transactions, new feature in Postgres 15) the BEGIN message will only be sent if the transaction was committed. It's pretty unlikely that you will ever process a ROLLBACK message from the protocol (although possible).
- aeyes 3y agoHow many flags are we talking here? I implemted a similar system and we just replace the whole sqlite DB file by downloading it from the centralized storage whenever it changes. Even with 1M flags it's still only a few 100 kB compressed. I wouldn't replicate per user flags to the edge to keep size under control.
- hinkley 3y agoDoes it though? If it’s a proxy it can support the SQLite read and the Postgres write syntax. If reads only ever go to SQLite they don’t need to work on Postgres.
- Omnipresent 3y agoHonest question: why is SQLLite needed for local? Why would you not have PG at edge that replicates data with central PG? That way the SQL dialect problem you mentioned wouldn't exist.
- joshuahaglund 3y agoSQLite is much smaller and self-contained than postgres. It's written in ANSI-C and by including one file you have access to a database (which is stored in another single file). It's popular in embedded systems like, I imagine, edge devices
- linsomniac 3y agoYou know what's faster than a local connection to Postgres? Having the database engine directly embedded in your application. No context switch.
- hot_gril 3y agoThe overhead of IPC isn't significant here, unless there's some special use case I'm not thinking of. SQLite might still be faster for small queries for other reasons.
- zbentley 3y agoThere are still context switches with sqlite, even in-memory sqlite (it has to mess with files, which means syscalls). Just not network ops.
- kiitos 3y agoIn-memory SQLite doesn't read or write files, and therefore doesn't generate syscalls, by definition.
- deleted 3y ago[deleted]
- 3y ago
- btown 3y agoA simple version of this is to do a very cheap SELECT * [where tenant = ...] of your feature flag table(s) to a dictionary structure in memory on every single edge/application server, and do this atomically every few seconds or minutes. Statsig [0] and Transifex [1] both use this pattern to great effect, transmitting not only data but logic on permissions and liveness, and you can roll your own versions of all this for your own domain models. I'm of the opinion that every agile project should start with a system like this; it opens up entirely new avenues of real-time configuration deployment to satisfy in-the-moment business/editorial needs, while providing breathing room to the development team to ensure codebase stability. (As long as all you need is eventual consistency, of course, and are fine with these structures changing in the midst of a request or long-running operation, and are fine with not being able to read your writes if you ever change these values! If any of that sounds necessary, you'll need some notion of distributed consensus.) [0] https://docs.statsig.com/server/introduction https://docs.statsig.com/server/introduction [1] https://developers.transifex.com/docs/native https://developers.transifex.com/docs/native
- seedless-sensat 3y agoIt's nice to see another pg proxy using the pgx parser (their src[1]) - I built one using this lib too. However, this implementation is missing a lot of low level features to be considered close to compatible, including: multi-query transactions, auth, TLS, extended query mode, query cancellation. [1]: https://github.com/zknill/sqledge/blob/main/pkg/pgwire/postgres.go https://github.com/zknill/sqledge/blob/main/pkg/pgwire/postg...
- yashap 3y agoI wonder, how does this handle a single transaction that contains both reads and writes? Maybe it just says "within a transaction, all reads and writes go through the Postgres proxy, SQLite is ignored"?
- NicoJuicy 3y agoFf are mostly written centrally and cached to memory.