undefined | Better HN

0 pointsnine_k4y ago0 comments

I do understand the point of running SQLite in-process to speed up reads.

I do not understand why SQLite must also handle intense write load with HA, failover, etc.

I would rather have the best of both worlds: a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node.

(My ideal case would be some kind of natural sharding where each node keeps its own updates, or just a highly available data browsing app, with data in SQLite files updated as entire files, like a deploymen.)

0 comments

6 comments · 3 top-level

jrochkind14y ago· 2 in thread

> a proper DB server (say, Postgres) replicated to super-fast and simple read replicas in SQLite on every node.

Having your replicas be different database software than your master seems to me like asking for, at least, weird edge case bugs.

Is this something anyone does? Interested to hear experiences if so!

JohnBooty4y ago

I've not done this but it's intriguing; potentially a best-of-all-worlds solution.

I think "proper" automatic replication is not possible given the mismatch between Postgres and SQLite - not everything in Postgres maps to a thing that is possible in SQLite.

That said, there are a variety of ways to get data out of Postgres, and a variety of ways to get things into SQLite.

You could periodically export CSVs or whatever from Postgres and periodically import them into SQLite.

Or you could do a more realtime sync by using a Postgres foreign data wrapper like this one: https://github.com/pgspider/sqlite_fdw which would let you write directly to SQLite. Combine that with database triggers on the Postgres side of things and you've got something pretty close to realtime replication.

Those sorts of solutions wouldn't be as robust as "real" replication (specifically, what happens when one of the SQLite replicas is unavailable? do you catch things up later?) but could be very useful for a lot of scenarios. You could have Postgres importing gobs of data, and "replicating" it over to your read-only reporting server which uses SQLite as a data source.

nine_kOP4y ago

My idea was to either use triggers or a process that reads Postgres's WAL and replay transactions to SQLite, by sending updates, rebuilding files entirely, or anything else.

Such replicator processes can be horizontally scaled, and made into an HA configuration.

Of course you should be careful to use on the Postgres side only the SQL features that will map well on SQLite. The range of such features is wide enough for practical applications though.

My real-life example would be a realty site, where changes are infrequent, but the desire to slice and dice the current large(ish) dataset in interesting ways is very high. The latter could be done using a per-node SQLite DB.

1 more reply

coredog644y ago· 1 in thread

One of the ideas I tried to sell at my last company was to bake SQLite into the lambda images used to run the app. It wouldn’t have been for transactional data, but for the mess of other data you wind up needing (country codes, currency codes, customer name to ID, etc.). It was all stuff that changed infrequently if ever. Unfortunately nobody wanted to do it. I think they wanted to have DynamoDB on their resume.

manigandham4y ago

This is a solid approach; used several times in my previous adtech companies to distribute a large amount of core data to every server instance.

jethro_tell4y ago

I've worked on an 'eventually consistent' system with read/write SQLite dbs on each host with a background worker that replayed the log into a central source of truth db and workers that made updates across each host instance of SQLite.

It could have been made a lot faster, I think the replication sla for the service was 10 minutes usually done in seconds. But our specific workflow only progressed in one direction, so replaying a step wasn't a huge issue now and again though that was quite rare. If you were to put a little more effort than we did into replication layer and tuning your master db, it could be a really effective setup.

One of the best parts is that when instances are stopped or isolated, they were also almost isolated from everything that used the service so if you go into a black box with your clients, you work as normal and when connection or other hosts are brought back up they replay the db before accepting connections. We could take entire availability zones offline and the workers and clients would keep humming and update neighbors later.

j / k navigate · click thread line to collapse