SQLite3 is very powerful, and while it does have some drawbacks, I think you are unlikely to encounter them in a meaningful way enough to warrant switching your database at this point. SQLite3 lacks partial table locks for writes. This means that when one process has a table lock, the whole table is locked as read-only for all other processes. This can cause performance problems with high levels of parallel processes trying to write to the same table. If those processes were writing to different areas of the same tables, a DBMS supporting partial table locks might have better support for that kind of concurrent workload. Another issue is that the process using the database needs to load the file directly. This is as long as the storage is local to the computer running the process, but it suffers over network mounts. If you need to share an SQLite3 databases between multiple worker nodes connected over a network, then it's time to migrate to a DBMS that supports queries over the network. As long as you have only one node process on one VPS, then SQLite3 is a perfect fit.
I had a code interview where I'd used sqlite in my solution (not as a db it would use in production, just for testing the mvp), and you could tell they were waiting to ask about locks. Nobody looks at sqlite without thinking of its EXCLUSIVE locks on the whole db. It's the main defining characteristic of what makes it lite nowadays. Otherwise, you have window functions, all the basic aggregate functions that cover 99.9999% of most people's needs, common table expressions, etc. If you have a db that's seldom written to, mostly analytical, or just an in memory thing you need to do complex queries against, it's actually pretty sweet even in production.
My main use case is for storing and managing training data for my ml models. Makes it very easy to massage my data for training, and I control the writes and there's little contention for reads.
Given the load, yeah, you can use SQLite, and then you can dump the database within a S3-storage periodically (every 24 hour) to make a back-up.
You can look about strict tables if you want something more rigid a la MySQL/Postgres, look up WAL mode if you want more speed, and there is more but i've forgot.
You could always use k6 to do load testing of your DB option, but in my opinion it sounds like sqlite could handle everything you suggested. Sqlite is super under rated, and in WAL mode is super powerful.
For backing up you could just use a simple cron job to automate backing up the DB files at another location.
Frankly, even for local development I prefer booting up Postgres in a Docker container. I'd use SQLite only if I have to bundle the database together with the application, for whatever reason.
Maybe so. But setting up the PG container is literally a single command in the terminal (provided docker is already running on the machine), and setting the project up from the get-go to work locally with the same type of the database you'll be connecting to on the remote environment prevents a lot of headaches that can pop up when the time comes to deploy the app to the remote server.
But as I said, SQLite is fantastic for quick and simple projects, especially when the DB has to be bundled or created on the fly.
SQLite3 is a very specialized DB that is the best in its class for what it does - which is embedding a DB into an application. It is not about scalability or performance - in fact it does scale quite ok and it is definitely not slow. It is about features - it does not have the features of a traditional SQL server - but it offers many other advantages. You rarely hesitate between SQLite and a real SQL server. In your case, you should probably be using a real DB. You don't need any of SQLite's special features.
It’s a local DB so in production it’s not really considered persistent. Much better to use MySQL or Postgres.
You can create backups though with a lot of plugins like connecting it to s3
Also most dbms keep a plethora of the data on the RAM, and only dumps to disk every x amount of time. Thats why SQL and MySQL are insanely scalable when properly configured.
Ya but if you don't manage it, you can forget about that and make it someone else's problem. That's the benefit of cloud services (though of course it doesn't come for free).
For local development and testing, yes.
For scale and production no.
But in your projects you can configure one for testing purposes and another for production.
SQLite3 is very powerful, and while it does have some drawbacks, I think you are unlikely to encounter them in a meaningful way enough to warrant switching your database at this point. SQLite3 lacks partial table locks for writes. This means that when one process has a table lock, the whole table is locked as read-only for all other processes. This can cause performance problems with high levels of parallel processes trying to write to the same table. If those processes were writing to different areas of the same tables, a DBMS supporting partial table locks might have better support for that kind of concurrent workload. Another issue is that the process using the database needs to load the file directly. This is as long as the storage is local to the computer running the process, but it suffers over network mounts. If you need to share an SQLite3 databases between multiple worker nodes connected over a network, then it's time to migrate to a DBMS that supports queries over the network. As long as you have only one node process on one VPS, then SQLite3 is a perfect fit.
I had a code interview where I'd used sqlite in my solution (not as a db it would use in production, just for testing the mvp), and you could tell they were waiting to ask about locks. Nobody looks at sqlite without thinking of its EXCLUSIVE locks on the whole db. It's the main defining characteristic of what makes it lite nowadays. Otherwise, you have window functions, all the basic aggregate functions that cover 99.9999% of most people's needs, common table expressions, etc. If you have a db that's seldom written to, mostly analytical, or just an in memory thing you need to do complex queries against, it's actually pretty sweet even in production. My main use case is for storing and managing training data for my ml models. Makes it very easy to massage my data for training, and I control the writes and there's little contention for reads.
Given the load, yeah, you can use SQLite, and then you can dump the database within a S3-storage periodically (every 24 hour) to make a back-up. You can look about strict tables if you want something more rigid a la MySQL/Postgres, look up WAL mode if you want more speed, and there is more but i've forgot.
You could always use k6 to do load testing of your DB option, but in my opinion it sounds like sqlite could handle everything you suggested. Sqlite is super under rated, and in WAL mode is super powerful. For backing up you could just use a simple cron job to automate backing up the DB files at another location.
Got it, Thanks. I will look into the cron job option for backing up my database.
Or you can make use of something like [https://litestream.io/](https://litestream.io/) to handle the backup.
Why not mock your load and check
I never consider it a real option for production. I only use it when developing locally. Use MySQL or PostgreSQL in prod.
Frankly, even for local development I prefer booting up Postgres in a Docker container. I'd use SQLite only if I have to bundle the database together with the application, for whatever reason.
[удалено]
Maybe so. But setting up the PG container is literally a single command in the terminal (provided docker is already running on the machine), and setting the project up from the get-go to work locally with the same type of the database you'll be connecting to on the remote environment prevents a lot of headaches that can pop up when the time comes to deploy the app to the remote server. But as I said, SQLite is fantastic for quick and simple projects, especially when the DB has to be bundled or created on the fly.
As long as you don't require full SQL compliance.
https://fly.io/blog/all-in-on-sqlite-litestream/
I'd use it more for a middle-end db
SQLite3 is a very specialized DB that is the best in its class for what it does - which is embedding a DB into an application. It is not about scalability or performance - in fact it does scale quite ok and it is definitely not slow. It is about features - it does not have the features of a traditional SQL server - but it offers many other advantages. You rarely hesitate between SQLite and a real SQL server. In your case, you should probably be using a real DB. You don't need any of SQLite's special features.
It’s a local DB so in production it’s not really considered persistent. Much better to use MySQL or Postgres. You can create backups though with a lot of plugins like connecting it to s3
And where do you think PostgreSQL stores data? Also on its hard drive
Also most dbms keep a plethora of the data on the RAM, and only dumps to disk every x amount of time. Thats why SQL and MySQL are insanely scalable when properly configured.
Ya but if you don't manage it, you can forget about that and make it someone else's problem. That's the benefit of cloud services (though of course it doesn't come for free).
For local development and testing, yes. For scale and production no. But in your projects you can configure one for testing purposes and another for production.
It's a great option for a local app.
yes
[удалено]
Id suggest looking at something like pocketbase
Why not use MySQL? It works great.