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.
> 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?
Feature flag systems are usually based on a set of rules that could be serialized and evaluated locally (this is how pretty much every open source feature flag system and feature flag SaaS works). Usually it's based on some kind of UUID being hashed with a per-flag seed and bucketed after some set of targeting rules are applied to other properties passed in for that user. There are added features where you can stores large cohorts to do specific targeting and usually there's some kind of local cache added to make that look-up faster for recent users.
I'm not sure what the original commenter was doing but it sounds like they had some kind of targeting that was almost entirely based on cohorts or maybe they needed to have stability over time which would require a database. We did something similar recently except we just store a "session ID" with a blob for look-up and the evaluation only happens on the first request for a given session ID.
> 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.
They were being cached for at least a minute (maybe even more than that, I can't remember the details) - that's what the local memcached instance was for.
This was problematic though because changing a feature flag and then waiting for a minute plus to see if the change actually worked can be frustrating, especially if it relates to an outage of some sort.
The 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).
Only per feature+per user. (Though 1000s per second does seem high unless your scale is gigantic.)
> 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
Nothing makes it into the replication stream until it is committed.
Honest 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.
That is a much safer way to go for most use cases. Well actually, most use cases don't need edge compute at all, but for those that do, this setup is indeed common, and fine for most apps:
- Say we do edge compute in San Francisco, Montreal, London and Singapore
- Set up a PG master in one place (like San Francisco), and read replicas in every place (San Francisco, Montreal, London and Singapore)
- Have your app query the read replica when possible, only going to the master for writes
In rare cases, maybe any network latency is not OK, you really need an embedded DB for ultimate read performance - then this is pretty interesting. But a backend server truly needing an embedded DB is certainly a rare case. I would imagine this approach would come with some very major downsides, like having to replicate the entire DB to each app instance, as well as the inherent complexity/sketchiness of this setup, when you generally want your DB layer to be rock solid.
This is probably upvoted so high on HN because it's pretty cool/wild, and HN loves SQLite, vs. it being something many ppl should use.
SQLite 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
A 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.)
Does 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.
How 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.
It'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.
I 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"?
One 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.
This is pretty neat! One question, if all your queries have to be SQLite-compatible, doesn't that defeat the purpose of using PG in the first place? Maybe SQLite supports more PG features than I thought, but if for example your app uses pgvector or pgcrypto you might have issues here.
Yes, absolutely, and this is going to be one of the hardest tech challenges to solve. I've thought a little about it, and it's probably unrealistic to think that we can translate every single PG statement into a SQLite one, especially when PG has extensions. So we're probably destined to use the local SQLite database for queries we can parse and understand, and forwarding all the others (both reads and writes) to the upstream PG server.
This slightly breaks model of having a local copy serve data faster, but if only the minority of queries use a format that we don't understand in SQLite then only that minority of queries will suffer from the full latency to the main PG server.
> doesn't that defeat the purpose of using PG in the first place
PG can scale higher than SQLite, especially considering concurrent writers. So even without the PG syntax and extensions, it's still useful. Also, maybe you can use PG syntax for complex INSERT (SELECT)s?
I'm super excited for this -- it seems like it's perfect as an app-local cache of things that can be a drop-in replacement for some high-cost queries.
Are there any plans to support which tables get copied over? The main postgres database is too big to replicate everywhere, but some key "summary" tables would be really nice to have locally.
How "edgy" can real PostgreSQL be? Seems to me that this is all in lieu of using real PostgreSQL replication, on the basis that real Postgres is too heavy / complex to run on the edge. Can a true PostgreSQL replica be configured in a light weight way to serve a similar purpose?
I have the same question. There have been demos of local Postgres-on-web using wasm, which would not solve this issue (browser is way heavier than SQLite), but maybe it demonstrates how portable Postgres can be with some effort.
Does anyone know of a tool that will export a Postgres database to a SQLite database file? Seems like a handy way of exporting and passing around smallish DBs. I feel like this tool must exist, but I haven’t found it yet. (Supporting imports and data transformations would be even better!)
We considered tailing binlogs directly but there's so much cruft and complexity involved trying to translate between types and such at that end, once you even just get passed properly parsing the binlogs and maintaining the replication connection. Then you have to deal with schema management across both systems too. Similar sets of problems using PostgreSQL as a source of truth.
In the end we decided just to wrap the whole thing up and abstract away the schema with a common set of types and a limited set of read APIs. Biggest missing piece I regret not getting in was support for secondary indexes.
If you're asking about secondary indexes, it was just seen as a "later" feature we'd implement as a follow-up. It was definitely asked for, just never prioritized before I moved off the project.
Readit News
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.
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?
I'm not sure what the original commenter was doing but it sounds like they had some kind of targeting that was almost entirely based on cohorts or maybe they needed to have stability over time which would require a database. We did something similar recently except we just store a "session ID" with a blob for look-up and the evaluation only happens on the first request for a given session ID.
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.
Not the previous poster, but it appears in the scenario, the SQLite database is the cache.
This was problematic though because changing a feature flag and then waiting for a minute plus to see if the change actually worked can be frustrating, especially if it relates to an outage of some sort.
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).
Only per feature+per user. (Though 1000s per second does seem high unless your scale is gigantic.)
> 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
Nothing makes it into the replication stream until it is committed.
- Say we do edge compute in San Francisco, Montreal, London and Singapore
- Set up a PG master in one place (like San Francisco), and read replicas in every place (San Francisco, Montreal, London and Singapore)
- Have your app query the read replica when possible, only going to the master for writes
In rare cases, maybe any network latency is not OK, you really need an embedded DB for ultimate read performance - then this is pretty interesting. But a backend server truly needing an embedded DB is certainly a rare case. I would imagine this approach would come with some very major downsides, like having to replicate the entire DB to each app instance, as well as the inherent complexity/sketchiness of this setup, when you generally want your DB layer to be rock solid.
This is probably upvoted so high on HN because it's pretty cool/wild, and HN loves SQLite, vs. it being something many ppl should use.
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
[1] https://developers.transifex.com/docs/native
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.
[1]: https://github.com/zknill/sqledge/blob/main/pkg/pgwire/postg...
This slightly breaks model of having a local copy serve data faster, but if only the minority of queries use a format that we don't understand in SQLite then only that minority of queries will suffer from the full latency to the main PG server.
PG can scale higher than SQLite, especially considering concurrent writers. So even without the PG syntax and extensions, it's still useful. Also, maybe you can use PG syntax for complex INSERT (SELECT)s?
Are there any plans to support which tables get copied over? The main postgres database is too big to replicate everywhere, but some key "summary" tables would be really nice to have locally.
edit
> The writes via SQLedge are sync, that is we wait for the write to be processed on the upstream Postgres server
OK, so, it's a SQLite read replica of a Postgres primary DB.
Of course, this does mean that it's possible for clients to fail the read-your-writes consistency check.
Dead Comment
You can see an example of it in use here: https://github.com/simonw/simonwillisonblog-backup/blob/main...
We considered tailing binlogs directly but there's so much cruft and complexity involved trying to translate between types and such at that end, once you even just get passed properly parsing the binlogs and maintaining the replication connection. Then you have to deal with schema management across both systems too. Similar sets of problems using PostgreSQL as a source of truth.
In the end we decided just to wrap the whole thing up and abstract away the schema with a common set of types and a limited set of read APIs. Biggest missing piece I regret not getting in was support for secondary indexes.