201–210 of 274 posts
I've been doing some ETL exploration with opening a sqlite :memory: connection, ingesting small-to-medium data, and then doing "VACUUM INTO somefile.sqlite;" to dump the RAM copy to disk. What a great tool.
Earlier quoted context omitted.
I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.
In Oracle, I can do this: RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE; SQLite does not implement such a feature.
See the -timestamp option here: https://litestream.io/reference/restore/
I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.
Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine. [1]: https://github.com/mholt/timeliner [2]: https://twitter.com/timelinize
Earlier quoted context omitted.
I read that one and agree it feels absurd. Not something I want to depend on.
I’m 100% sure this is (very nearly) suitable for production. It works and works well. It is only a matter of time before browsers implement the proposed sandboxed virtual file system apis with block level access and this would be superseded.
I love to see that more projects are using SQLite as their main database. One thing that I always wondered though: does anyone knows a big project/service that uses Golang and is backed by SQLite? This because SQLite would require CGO and CGO generally adds extra complexities and performance costs. I wonder how big Golang applications fare with this.
arp242 has an excellent post[0] about statically compiling sqlite for Go programs, which may be useful. Isn't there some issue where SQLite basically has to be single-threaded in Golang programs, at least if you use the stdlib SQL library? [0]: https://www.arp242.net/static-go.html
I guess not exactly, but iirc there were some caveats/performance issues around C threads x Go routines. A bit is touched in this post: https://www.cockroachlabs.com/blog/the-cost-and-complexity-o...
But well, it seems arp242 is GoatCounter's developer. Based on the post content, it is powered by SQLite and this is probably a good write-heavy application example. I wonder if there are any blog posts about the SQLite performance for this service.
I use SQLite exclusively on a high performance crypto sniper project - https://bsctrader.app and I could not be happier with it. Performs much better then postgres in terms of query latency which is ultra important for the domain we operate in. I take machine level backups every 2 hours, so in the event of an outage, just boot the disk image on a new vm and it's off. I would never do this on my professional job due t…
> machine level backups I presume that's block level backups? Or some snapshotting? As far as I know, block level filesystem copies can get inconsistent (so we have journalling file systems). But assuming it works well, like a filesystem aware snapshot, can sqlite deal with files snapshotted in the middle of an operation?
Earlier quoted context omitted.
> machine level backups I presume that's block level backups? Or some snapshotting? As far as I know, block level filesystem copies can get inconsistent (so we have journalling file systems). But assuming it works well, like a filesystem aware snapshot, can sqlite deal with files snapshotted in the middle of an operation?
That's a really interesting questions, I have not even considered that. Just using my providers automated backup system. Never had to restore yet
Is SQLite suitable for a small-to-medium CMS (Content Management System), or a blog platform e.g. WordPress (MySQL) or Ghost (MySQL)?
Earlier quoted context omitted.
> How is this setup fault tolerant? It is not. > What happens if there is a hardware failure? The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers. > How do y…
Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can > execute queries and reliably receive results within microseconds What is your product? Who are these…
Earlier quoted context omitted.
> How is this setup fault tolerant? It is not. > What happens if there is a hardware failure? The product would suffer a total outage until manual intervention takes place. A restore of the VM from snapshot would be carried out by the customer. Some loss of the most recent business data would occur (i.e. between latest snapshot and time of failure). All of this is understood and agreed to by our customers. > How do y…
Interesting. For an extremely specific use case and with users who understand and accept the caveats of this approach I'm sure it would work well enough. The most confusing thing to me is that there is apparently an intersection of users who are ok with an outage and data loss with users who want a product which can > execute queries and reliably receive results within microseconds What is your product? Who are these…
None of them display any understanding or empathy whatsoever when you say "Your trade will always be executed after a 1 second delay, even if the price has moved"
Users find occasional downtime awful, but they find consistent lethargy worse.