I love this approach. I've ran sqlite in production for a variety of products over the years and for the most part it's great. One "back up" solution I did was dump tables to text - commit the diff in git. Taking file copies while transactions are in process can lead to corrupted db's.
Consider SQLite
161–170 of 274 posts
Re: Consider SQLite
#162Earlier quoted context omitted.
I'm pretty sure you do need FB and Azure level infra for you recipe app. I've read a couple blog posts, watched a video on YouTube and copied the code from Stack Overflow, so I'm pretty much an expert on this, so trust me.
My recipe application is going to be written in a purely functional language with CQRS and a message bus that provides "guaranteed once" determinism. I should be able to spin up no more than a half dozen containers on two VM's to handle this architecture. The database will probably be redundant MSSQL and Oracle (just in case one technology is fundamentally broken). Database writes will be proxied by a Redis instance.…
It's never gonna scale. Get on the NoSQL train now to future proof and woo the VCs.
Re: Consider SQLite
#163Earlier quoted context omitted.
> Can't you run your queries in a copy of the data (eg.: a backup)? That's essentially what we do: copy the file locally if we need to inspect it. It's slightly more cumbersome though. > I think that'd be advisable even if you were running postgresql. Connecting to live prod servers is definitely not a 10/10 on the "best practices" scale, but it works well for our business (trading), where there are small developer t…
With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data. I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very ni…
> With postgres you could set up a read-only replica or something. At least for me, the small effort is well worth not having to worry about accidentally deleting production data.
We do do this, and probably should be more disciplined about connecting to it when only reading. Of course that doesn't help if we need to run an update in production, but that isn't that often.
> I feel like having an easy mechanism to clone the production database somewhere you can play with is well worth the effort. You can even use those clones to run backtests and other integration/regression tests against, which is also a very nice to have.
We do actually do this as well (nightly), and it is a huge productivity boost for testing and development. I would recommend to anyone that writes software dealing with persisted data to invest in an easy mechanism to clone from production.
Re: Consider SQLite
#164We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
Interesting. So I would ordinarily want to put a foreign key constraint on the user_id column of a UserSessions table (or similar). In general, presumably you have relationships across the tables that are in those discrete databases. Do you just enforce these constraints/do joins in code? It seems like splitting related tables across multiple databases loses some (possibly a lot?) of the benefits of relational DBs, s…
SQLite has ATTACH (https://sqlite.org/lang_attach.html) for cross database operations. I've never tried it for foreign key constraints across databases, but I think it would work?
Re: Consider SQLite
#165We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
> So, we would have databases like Users.db, UserSessions.db, Settings.db, etc. How do you do joins?
I would guess they do parallelized calls to the microservices to collect the data.
Re: Consider SQLite
#166We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
How do you join?
Presumably ATTACH: https://www.sqlite.org/lang_attach.html
Re: Consider SQLite
#167We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
> So, we would have databases like Users.db, UserSessions.db, Settings.db, etc. How do you do joins?
Not the OP, but you can attach and join tables from multiple sqlite databases in the same process.
Re: Consider SQLite
#168Am I the only one who thinks SQLite is still too complicated for many programs? Maybe it's just the particular type of software I normally work on, which tends towards small, self-hosted networking services[0] that would often have a single user, or maybe federated with Now obviously if I wanted to scale up, at some point you would have too many users to fit in memory. But do programs at that scale actually need to e…
Look up "Prevayler" and the "system prevalence" pattern. It introduces occasional data journaling, and so it exists somewhere in between "serialize and persist the whole thing at once" and a full blown DBMS. But I've experimented with system prevalence in the past and decided that just "serializing and persisting the whole thing at once" like you've mentioned was simply a better solution.
Re: Consider SQLite
#169Earlier quoted context omitted.
I'm pretty sure you do need FB and Azure level infra for you recipe app. I've read a couple blog posts, watched a video on YouTube and copied the code from Stack Overflow, so I'm pretty much an expert on this, so trust me.
My recipe application is going to be written in a purely functional language with CQRS and a message bus that provides "guaranteed once" determinism. I should be able to spin up no more than a half dozen containers on two VM's to handle this architecture. The database will probably be redundant MSSQL and Oracle (just in case one technology is fundamentally broken). Database writes will be proxied by a Redis instance.…
You can't do any serious neural stuff on 8GB of VRAM. I'd say save yourself some pains and buy the $3000 professional models with ECC so you can train transformers.
Re: Consider SQLite
#170We've been using SQLite in production as our exclusive means for getting bytes to/from disk for going on 6 years now. To this day, not one production incident can be attributed to our choice of database or how we use it. We aren't using SQLite exactly as intended either. We have databases in the 100-1000 gigabyte range that are concurrently utilized by potentially hundreds or thousands of simultaneous users. Performa…
[deleted]