8 ms·
SQLedge: Replicate Postgres to SQLite on the Edge
- luckystarr 3y agoHow would mutually incompatible upstream changes from multiple SQLite edge instances be resolved? You'd need user input for that, right?
- zffr 3y ago> SQLedge serves reads from it's local sqlite database, and forwards writes to the upstream postgres server that it's replicating from. I don't think SQLite data is ever written back to the postgres DB so this shouldn't be an issue
- simonw 3y agoI don't think that's a problem here, because the write queries aren't handled by the local SQLite databases - instead, the proxy forwards them directly to the PostgreSQL instance, so presumably the resulting changes show up in SQLite when they are replicated back down again.
- zknill 3y agoThe writes via SQLedge are sync, that is we wait for the write to be processed on the upstream Postgres server. So it operates as if SQLedge wasn't in the request path from application to Postgres. The writes to Postgres are only reflected in SQLite when the data is received back from the Postgres server on the replication slot. This means writes are eventually consistent, currently, but I intend to include a feature that allows waiting for that write to be reflected back in SQLite which would satisfy the 'read your own writes' property. SQLedge will never be in a situation where the SQLite database thinks it has a write, but that write is yet to be applied to the upstream Postgres server. Basically, the Postgres server 'owns' the writes, and can handle them just like it would if SQLedge didn't exist.
- luckystarr 3y agoSo, the advertised performance is just for the read-part, not the write-part? As far as I understand: writing would still exhibit the same characteristics as before, while reads would never be affected by other users.
- kiitos 3y agoWhat about migrations?
- maxfurman 3y agoThis 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.
- zknill 3y agoYes, 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.
- hot_gril 3y ago> 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?
- simonw 3y agoFollowing 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?
- hrdwdmrbl 3y agoThis space is starting to get crowded. Can anyone compare this with some of the other solutions coming out recently?
- DANmode 3y agoList a couple?
- arve0 3y agoA few I know: rqlite, dqlite, SQLite wasm, litestream.
- otoolep 3y agorqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
- lazyant 3y ago(I guess I can test or ask in the rqlite Slack etc) Can I try to use a UNIX socket to TCP socket so I can use rqlite as a drop-in sqlite replacement (where a client expects a file)? for ex: socat TCP-LISTEN:4000,reuseaddr,fork UNIX-CLIENT:/path/to/sqlite
- otoolep 3y agoNo, rqlite doesn't support that kind of access. No reason it couldn't, but it currently only exposes a HTTP API. https://rqlite.io/docs/faq/#is-it-a-drop-in-replacement-for-sqlite https://rqlite.io/docs/faq/#is-it-a-drop-in-replacement-for-...
- westurner 3y ago#. SQLite WAL mode From https://www.sqlite.org/isolation.html https://www.sqlite.org/isolation.html https://news.ycombinator.com/item?id=32247085 https://news.ycombinator.com/item?id=32247085 : > [sqlite] WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go into the separate write-ahead log file. That means that readers can continue to read the old, original, unaltered content from the original database file at the same time that the writer is appending to the write-ahead log #. superfly/litefs: a FUSE-based file system for replicating SQLite https://github.com/superfly/litefs https://github.com/superfly/litefs #. sqldiff: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html https://news.ycombinator.com/item?id=31265005 https://news.ycombinator.com/item?id=31265005 #. dolthub/dolt: https://github.com/dolthub/dolt https://github.com/dolthub/dolt : > Dolt is a SQL database that you can fork, clone, branch, merge, push and pull just like a Git repository. [...] > Dolt can be set up as a replica of your existing MySQL or MariaDB database using standard MySQL binlog replication. Every write becomes a Dolt commit. This is a great way to get the version control benefits of Dolt and keep an existing MySQL or MariaDB database. #. github/gh-ost: https://github.com/github/gh-ost https://github.com/github/gh-ost : > Instead, gh-ost uses the binary log stream to capture table changes, and asynchronously applies them onto the ghost table. gh-ost takes upon itself some tasks that other tools leave for the database to perform. As result, gh-ost has greater control over the migration process; can truly suspend it; can truly decouple the migration's write load from the master's workload. #. vlcn-io/cr-sqlite: https://github.com/vlcn-io/cr-sqlite https://github.com/vlcn-io/cr-sqlite : > Convergent, Replicated SQLite. Multi-writer and CRDT support for SQLite > CR-SQLite is a run-time loadable extension for SQLite and libSQL. It allows merging different SQLite databases together that have taken independent writes. > In other words, you can write to your SQLite database while offline. I can write to mine while offline. We can then both come online and merge our databases together, without conflict. > In technical terms: cr-sqlite adds multi-master replication and partition tolerance to SQLite via conflict free replicated data types (CRDTs) and/or causally ordered event logs. yjs also does CRDTs (Jupyter RTC,) #. pganalyze/libpg_query: https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query : > C library for accessing the PostgreSQL parser outside of the server environment #. Ibis + Substrait [ + DuckDB ] https://ibis-project.org/blog/ibis_substrait_to_duckdb/ https://ibis-project.org/blog/ibis_substrait_to_duckdb/ : > ibis strives to provide a consistent interface for interacting with a multitude of different analytical execution engines, most of which (but not all) speak some dialect of SQL. > Today, Ibis accomplishes this with a lot of help from `sqlalchemy` and `sqlglot` to handle differences in dialect, or we interact directly with available Python bindings (for instance with the pandas, datafusion, and polars backends). > [...] `Substrait` is a new cross-language serialization format for communicating (among other things) query plans. It's still in its early days, but there is already nascent support for Substrait in Apache Arrow, DuckDB, and Velox. #. ibis-project/ibis-substrait: https://github.com/ibis-project/ibis-substrait https://github.com/ibis-project/ibis-substrait #. tobymao/sqlglot: https://github.com/tobymao/sqlglot https://github.com/tobymao/sqlglot : > SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine. It can be used to format SQL or translate between 19 different dialects like DuckDB, Presto, Spark, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects. > It is a very comprehensive generic SQL parser with a robust test suite. It is also quite performant, while being written purely in Python. > You can easily customize the parser, analyze queries, traverse expression trees, and programmatically build SQL. > Syntax errors are highlighted and dialect incompatibilities can warn or raise depending on configurations. However, it should be noted that SQL validation is not SQLGlot’s goal, so some syntax errors may go unnoticed. #. benbjohnson/postlite: https://github.com/benbjohnson/postlite https://github.com/benbjohnson/postlite : > postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier. > The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax. > Note: This software is in alpha. Please report bugs. Postlite doesn't alter your database unless you issue INSERT, UPDATE, DELETE commands so it's probably safe. If anything, the Postlite process may die but it shouldn't affect your database. #. > "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766 https://news.ycombinator.com/item?id=28021766 #. >> - bittorrent/sqltorrent https://github.com/bittorrent/sqltorrent https://github.com/bittorrent/sqltorrent >> Sqltorrent is a custom VFS for sqlite which allows applications to query an sqlite database contained within a torrent. Queries can be processed immediately after the database has been opened, even though the database file is still being downloaded. Pieces of the file which are required to complete a query are prioritized so that queries complete reasonably quickly even if only a small fraction of the whole database has been downloaded. #. simonw/datasette-lite: https://github.com/simonw/datasette-lite https://github.com/simonw/datasette-lite datasette, *-to-sqlite, dogsheep "Loading SQLite databases" [w/ datasette] https://github.com/simonw/datasette-lite#loading-sqlite-databases https://github.com/simonw/datasette-lite#loading-sqlite-data... #. awesome-db-tools: https://github.com/mgramin/awesome-db-tools https://github.com/mgramin/awesome-db-tools Lots of neat SQLite/vtable/pg/replication things
- kiitos 3y agoAnd how do you manage conflicts? 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.
- durkie 3y agoI'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.
- rbranson 3y agoWe replicated our MySQL database to a SQLite edge at Segment in ctlstore: https://github.com/segmentio/ctlstore https://github.com/segmentio/ctlstore 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.
- karakanb 3y agothis seems neat, I'll definitely give it a look. this seems like a very suitable trade-off for a lot of applications I have worked on. does anyone know if there is a postgres-postgres version of this that is easy to run in ephemeral environments? ideally I'd like to be able to run Postgres sidecars along my application containers and eliminate the network roundtrip using the sidecar as a read replica, but haven't seen this being done anywhere. maybe it wouldn't be fast enough to run in such scenarios?
- djbusby 3y agoIn PG only one can use logical or streaming replication. Then all reads to the replica and writes to main. App needs two connections - one for read, one for write.
- packetlost 3y agoWhy SQLite instead of a standard hub and spoke replication? What benefit does this provide? Being able to run your database on the client? That seems like it would be risky
- tptacek 3y agoBecause it’s running in the app’s address space, SQLite is obscenely fast for reads, so much so that you can often just write N+1 queries with it.
- hamandcheese 3y agoIn this case though it appears that SQLEdge is running as its own process, distinct from the app, and the app sends all queries to the SQLEdge proxy.
- tptacek 3y agoThat makes less sense to me then, though the IPC cost for those reads is still much lower than a typical n-tier Postgres setup.
- sgt 3y agoA commercial offering (although also open source): https://www.powersync.co/ https://www.powersync.co/
- kobieps 3y ago[dead]
- e12e 3y agoThis looks neat. Any support for postgres schemas? Would be cool if this supported each SQLite chard to write to separate schema - giving each "share" a single tenant "view" while giving access to all data in the postgres instance? Or is this only public or all schemas?
- maxpert 3y agoVery interesting! I have question ( out of my experience in https://github.com/maxpert/marmot https://github.com/maxpert/marmot ) how do get around the boot time, specially when a change log of table is pretty large in Postgres? I've implemented snapshotting mechanism in Marmot as part of quickly getting up to speed. At some level I wonder if we can just feed this PG replication log into NATS cluster and Marmot can just replicate it across the board.
- markhalonen 3y agoWe're currently grappling with trying to build a system similar to https://stripe.com/sigma https://stripe.com/sigma with a single multi-tenant Postgres db as the source and an SQLite read replica per tenant. Currently we re-generate the world every night. We need fine-grained control over the SQLite schema (it's a public api we let users write sql against). Any related projects would be great to point me towards!
- kobieps 3y ago[dead]
- hinkley 3y agoWe need a standardized WAL format. Too many people trying to write software to simulate it.
- social_quotient 3y agoWAL = write-ahead logging?
- hinkley 3y agoYep, and the world is now full of apps that read one WAL format and copy the data somewhere else. This app, Kafka, “data lake” replication to heterogeneous databases, several other flavors of database replication, on and on. And every bit of it is custom to the WAL format of the source database.
- samanator 3y agoHow does this work with custom postgres types?
- mvaliente2001 3y agoFor one moment I thought this was the equivalent of SQLite for EdgeDB and the idea made me very happy.
- hamandcheese 3y agoI'm a bit confused by this. Since SQLEdge is a proxy, you lose all the IO benefits of running an embedded in-process DB. At that point, why not replicate to a real Postgres on the edge? Maybe the expectation is that the application also opens the SQLite file directly? (But in that case, what is the point of the SQLEdge proxy?)
- singingwolfboy 3y agoDoes 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!)
- simonw 3y agoI wrote a tool to do that: https://datasette.io/tools/db-to-sqlite https://datasette.io/tools/db-to-sqlite You can see an example of it in use here: https://github.com/simonw/simonwillisonblog-backup/blob/main/.github/workflows/backup.yml https://github.com/simonw/simonwillisonblog-backup/blob/main...
- zmmmmm 3y agoHow "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?
- hot_gril 3y agoI 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.
- hot_gril 3y agoVery cool. I've always found it hard to write any slightly complex SQL (i.e. not just a simple join) in a way that's fast on multiple DBMSes. Even if both support the same syntax you need, subtle things can affect the performance a lot. But there are probably use cases where this isn't an issue and you care a lot more about using something compatible with limited edge devices, like SQLite.
- grodes 3y agoI am sick of reading "on the edge"
- jonhohle 3y agoSomewhat orthogonal, but I was just thinking of looking for or writing a MySQL compatible frontend for SQLite. There’s a third party app that has removed SQLite support in its latest version and I really don’t want a MySQL database for something SQLite is more than capable of for my use case.
- _xnmw 3y agoI’ve been thinking a lot about this problem of distributed databases, and so far the easiest and most innovative solution I’ve seen is PolyScale[1], basically a CDN cache at the SQL layer with smart automatic invalidation. I just don’t want to deal with the hairiness of logical replication and now sqlite<>postgres incompatibility. [1] Not associated with PolyScale in any way, just an interested potential customer.
- jtk12s 3y agoIf I was convinced of the Edge I would use this. I tried to challenge this concept because I don't like the Edge hype. But I must say, this looks like one of the best solutions in terms of cost, simplicity and maturity (PG, SQlite). The major downsides are the limits in data size and the SQL features. Overall I think it's really well thought out and done.
- febed 3y agoI can’t be the only person who doesn’t know what ‘at the edge’ means in this context. Any useful links to educate myself?
- hot_gril 3y ago"At the edge" means closer to the end user. Maybe you have a central server with the master database in Virginia, USA and edge servers with read-only follower databases near various big cities where your customers live. The point of edges is to provide lower-latency access due to their proximity. They also reduce load on the master server, but you don't need edges to do that.
- zyngaro 3y agoWhat does on the edge mean in this context? What is the use case ?