IMO all of the downsides listed at the end override most of the upsides. SQLite is a fine upgrade over a CSV, but honestly it's almost as easy to get postgres up and running with equivalent...
IMO all of the downsides listed at the end override most of the upsides. SQLite is a fine upgrade over a CSV, but honestly it's almost as easy to get postgres up and running with equivalent resiliancy, like 4 commands on almost any distro, and it has none of those downsides.
If your app is only ever going to be a single-user, single device, its a fine standalone option. But if its server-side, only offering SQLite is shooting yourself in the foot due to aforementioned downsides.
Personally I find the convenience advantage isn't the ease of setup – it's that SQLite provides for substantially faster response times (because you don't have to make a network round trip) and...
Personally I find the convenience advantage isn't the ease of setup – it's that SQLite provides for substantially faster response times (because you don't have to make a network round trip) and you can make almost as many queries as you want in SQLite (rather than in Postgres where each query is relatively expensive, so you tend to have to do a bit of gymnastics to reduce the response time).
Of course it's pretty straightforward to migrate from SQLite to Postgres (with some rewriting for performance problems) so you can always switch later!
If you're running sqlite locally, presumably you'd be running postgres locally, and that wouldn't be a concern. On the other hand, sqlite absolutely gets HAMMERED if you are doing a write heavy...
If you're running sqlite locally, presumably you'd be running postgres locally, and that wouldn't be a concern. On the other hand, sqlite absolutely gets HAMMERED if you are doing a write heavy load. Like, it was a couple of orders of magnitude lower write rate running the same app on the same hardware and going to sqlite vs postgresql.
SQLite will generally be faster than Postgres even if the database is on the same machine because SQLite runs in-process, whereas Postgres runs out of process. In terms of writes you can get...
SQLite will generally be faster than Postgres even if the database is on the same machine because SQLite runs in-process, whereas Postgres runs out of process. In terms of writes you can get pretty good SQLite performance (of course you have to enable WAL).
I question why you'd pick Postgres over SQLite if you're running something on a single machine though? SQLite is an amazingly well-tested piece of software with maintainers who have an explicit goal of keeping it usable through to 2050.
We may be thinking different application scales, I think. The general way I've seen it work is that sqlite is faster... until you hit a certain performance requirement threshold. The problem is,...
We may be thinking different application scales, I think. The general way I've seen it work is that sqlite is faster... until you hit a certain performance requirement threshold. The problem is, getting faster past that requires fundamentally restructuring your application, and even then there's limits you'll never get past. If you are working on a little user facing app or simpler project, sure. But when you scale to the point of doing serious DB work, it just doesn't have what it needs to keep up.
In my case, it's a application at my job that does just run locally... but you're talking about a locally run program that's distributing work across multiple high load C++ processes to keep up with performance demands. We actually did deploy with sqlite for a while for robustness and ease of deployment (it's in an environment where maintenance accessibility is low), but it required a bunch of workarounds to accommodate sqlite's limitations, and still got a fraction of the performance of even just swapping out sqlite for postgres without even changing the architecture to remove the workarounds. Even more performance once we quit having to coddle sqlite to get it to work.
Note, I'm not saying sqlite is bad. It's still my go to for personal projects. It just has very stark limitations that one needs to be cognizant of.
It's obviously hard to say without knowing anything about your application but this sounds like you're using the database as a concurrency control mechanism with a write-heavy workload? In this...
it's a application at my job that does just run locally... but you're talking about a locally run program that's distributing work across multiple high load C++ processes to keep up with performance demands
It's obviously hard to say without knowing anything about your application but this sounds like you're using the database as a concurrency control mechanism with a write-heavy workload? In this case I can totally see why SQLite is not a good fit (e.g. it doesn't have row-level locking), but the cases in which the article advocates for people using SQL are really ones where the workloads are pretty read-heavy (as in the case of websites).
I generally find it's relatively straightforward to migrate applications to Postgres (which is almost but not quite a superset of SQLite in terms of compatibility) and then just run a number of instances of the application behind a load balancer.
There's some synchronization, primarily to bring a process up to current if a process is taken down and has to spool back up, although the heavy write load is because we're required to be able to...
There's some synchronization, primarily to bring a process up to current if a process is taken down and has to spool back up, although the heavy write load is because we're required to be able to forensically replay system state for later analysis, so extremely detailed high rate logging.
Yeah, if it was more read heavy, it wouldn't be an issue. This kind of write level load where you're bottlenecked by sqlite locking for the writes is the main case I've seen where there's problems.
The thing I’ve run into with SQLite is if you have a lot of writes (I was using SQLite to store logs, so hundreds to thousands of small writes every second) is you need to put them all in a...
The thing I’ve run into with SQLite is if you have a lot of writes (I was using SQLite to store logs, so hundreds to thousands of small writes every second) is you need to put them all in a transaction that commits every second or so.
Doing that got good enough performance in my case.
Sorry to snag those out of context and outline an idea that isn't directly SQLite, but I actually have had a much fonder experience setting up RQLite (which is SQLite + distributed and/or...
it's almost as easy to get postgres up and running with equivalent resiliancy, [...] But if its server-side, only offering SQLite is shooting yourself in the foot due to aforementioned downsides.
Sorry to snag those out of context and outline an idea that isn't directly SQLite, but I actually have had a much fonder experience setting up RQLite (which is SQLite + distributed and/or segmented replication) to be a much better and performant option than Postgres. Not only that, but it's so small, easy to install, and it's performant beyond imagination out of the box. Postgres, while certainly better than say MySQL, has always kind of been a performance nightmare and even more of a nightmare to properly setup and install (and insanely easy to fubar). I've been reaching for RQ a lot over the last two or so years. There are a few other options (Litestream, et al2), just haven't really tried the others.
I find distributed SQL to be practically the dream, especially in my field (and most I would imagine), where a device has their own internal database and instead of "replicating" (ie, pushing and pulling/polling) by item or table, and then collate them, you can replicate entire databases into the 'main/master' database alongside others, or you can allow for automatic (or manual, if you so wish) conflict-resolution replication and duplication between all the nodes for data symmetry.
On SQLite and it's downsides, I find the 'plugin' extension system far better than postgres to fix the issues that may come up with the core of SQLite, if you are designing a system, you should expect to use and develop modules for your system, SQLite just gets rid of the cruft and puts in a single binary - the absolute dream for databases. Postgres tries to be a virtual machine with its own built in programming languages for applications, SQLite tries to just be a file that you can apply extensions on top of in your own applications.1
I'll use both, but always prefer working with SQLite than anything PG. You tell me the system has SQLite, I am excited to dig in. You tell me the system runs on PG, and I start worrying because there is going to be an entire custom ecosystem I am going to have to learn before I can start messing with the data,
*. I didn't even realize I had these feelings about the Postgres vs SQLite debate, weird. I might need to think more about that, my decisions in life, and go touch grass and stare at the clouds for a few millenias hours.
1. As an example: building E2EE in SQLite is an unexpected phenomenal experience because of this. Postgres is a nightmare in my experience.
2. Alternatives to RQ that have piqued my interest. There are quite a few, because SQLite is just great at being extended while keeping the core SQLite database untouched:
Where I list some additional types and my interest in them
I have been fascinated with LiteFS, and will likely shift to reaching for it over RQ as it seems more specific to my use cases.
LiteFS is a distributed file system that transparently replicates SQLite databases. You can run your application like it’s running against a local on-disk SQLite database but behind the scenes the database is replicated to all the nodes in your cluster. With LiteFS, you can run your database right next to your application on the edge. You can run LiteFS anywhere!
Or even, Marmot, which I just found and sounds fascinating.
Marmot is a distributed SQLite replicator with leaderless, and eventual consistency. It allows you to build a robust replication between your nodes by building on top of fault-tolerant NATS JetStream.
So if you are running a read heavy website based on SQLite, you should be easily able to scale it out by adding more SQLite replicated nodes. SQLite is probably the most ubiquitous DB that exists almost everywhere, Marmot aims to make it even more ubiquitous for server side applications by building a replication layer on top.
There is also DQLite, supported (and pushed) by Canonical that is tailored for IOT and Edge Devices, but can be used for larger. I particularly like their addition of official support for the IBM z/OS Mainframes, never worked on them, but love their systems design.
Edit: Added ^2 instead of as an additional comment.
Could you elaborate on the "insanely easy to fubar" part of your comment? I've used Postgres in dozens of projects under heavy load. I've never run into an issue that I could consider "fubaring"...
Could you elaborate on the "insanely easy to fubar" part of your comment? I've used Postgres in dozens of projects under heavy load. I've never run into an issue that I could consider "fubaring" the database and now I'm concerned your comment has jinxed me for an upcoming release.
We run multiple medium-use apps off of a single unified postgres server, total database size is about 200GB with about 80GB of WAL churn a day. The extent of server setup was: Harden distro...
We run multiple medium-use apps off of a single unified postgres server, total database size is about 200GB with about 80GB of WAL churn a day. The extent of server setup was:
Harden distro
Install postgres through package manager
Setup backup jobs in cron
Tune WAL checkpoints (ie two lines in a config)
Create users and lock down access as appopriate.
Repeat steps 1-3 on second server, then run a command to start a asynchronous replica instead.
Then we basically didn't need to touch it for 5 years unless an app fucked up. If performance is bad, its probably a lack of indexing or questionable architecture design rather than anything wrong with postgres itself.
I'll leave that for others more focused on Postgres to talk on, as I am sure it's partially a failure due to my adherence, but this perfectly exemplifies my feelings, not particularly on...
I'll leave that for others more focused on Postgres to talk on, as I am sure it's partially a failure due to my adherence, but this perfectly exemplifies my feelings, not particularly on destroying the data within, you should be backing that up 123 style, but more on performance and the Postgres engine itself.
As for Postgres, I have enormous respect for it and its engineering and capabilities, but, for me, it’s just too damn operationally scary. In my experience it’s much worse than MySQL for operational footguns and performance cliffs, where using it slightly wrong can utterly tank your performance or availability. … Postgres is a fine choice, especially if you already have expertise using it on your team, but I’ve personally been burned too many times.
Correct. My company's old software was based on SQLite for some instances and it was a nightmare. The software can have multiple users writing to the database frequently. Supporting it was awful....
If your app is only ever going to be a single-user, single device, its a fine standalone option. But if its server-side, only offering SQLite is shooting yourself in the foot due to aforementioned downsides.
Correct. My company's old software was based on SQLite for some instances and it was a nightmare. The software can have multiple users writing to the database frequently. Supporting it was awful. We've moved on, but the memory of it stays with me
I think SQLite is great for set it and forget it type uses. I dunno that I'd choose it specifically in my web stack, but for many situations it's good enough and with good caching and a CDN you...
I think SQLite is great for set it and forget it type uses. I dunno that I'd choose it specifically in my web stack, but for many situations it's good enough and with good caching and a CDN you can do a lot even with a large spike in traffic. But I wouldn't entertain it for a large enterprise platform roll out.
Now, systems applications that have local user driven workloads I'd package it in for tons of situations.
I've had a lot of issues the last few years with sqlite databases (in several different contexts) getting irrecoverably corrupted. Plus the whole no multithreading thing. I wouldn't recommend...
I've had a lot of issues the last few years with sqlite databases (in several different contexts) getting irrecoverably corrupted. Plus the whole no multithreading thing. I wouldn't recommend sqlite to anyone anymore.
Which is a shame, because a single-file db is a nice bit of kit.
This is really surprising to me because I have found SQLite to be amazingly robust (the file format hasn't changed in basically forever, I don't know of any open source databases with a more...
This is really surprising to me because I have found SQLite to be amazingly robust (the file format hasn't changed in basically forever, I don't know of any open source databases with a more sophisticated testing regime). SQLite is even recommended as an archival format by the Library of Congress.
Home Assistant uses it for the default history logger. Maybe they configured it wrong, but I've had that thing get corrupt many times for one reason or another.
Home Assistant uses it for the default history logger. Maybe they configured it wrong, but I've had that thing get corrupt many times for one reason or another.
My only REAL pet peeve with SQLite when I've had to use it for something as a sysadmin is the dates handling, I'm stupidly spoiled by Postgres' great date/time/datetime management and SQLite just...
My only REAL pet peeve with SQLite when I've had to use it for something as a sysadmin is the dates handling, I'm stupidly spoiled by Postgres' great date/time/datetime management and SQLite just doesn't compare. Yeah, there's important issues described in the blogpost, but this is the one that always has left me annoyed when I've worked with it.
IMO all of the downsides listed at the end override most of the upsides. SQLite is a fine upgrade over a CSV, but honestly it's almost as easy to get postgres up and running with equivalent resiliancy, like 4 commands on almost any distro, and it has none of those downsides.
If your app is only ever going to be a single-user, single device, its a fine standalone option. But if its server-side, only offering SQLite is shooting yourself in the foot due to aforementioned downsides.
Personally I find the convenience advantage isn't the ease of setup – it's that SQLite provides for substantially faster response times (because you don't have to make a network round trip) and you can make almost as many queries as you want in SQLite (rather than in Postgres where each query is relatively expensive, so you tend to have to do a bit of gymnastics to reduce the response time).
Of course it's pretty straightforward to migrate from SQLite to Postgres (with some rewriting for performance problems) so you can always switch later!
If you're running sqlite locally, presumably you'd be running postgres locally, and that wouldn't be a concern. On the other hand, sqlite absolutely gets HAMMERED if you are doing a write heavy load. Like, it was a couple of orders of magnitude lower write rate running the same app on the same hardware and going to sqlite vs postgresql.
SQLite will generally be faster than Postgres even if the database is on the same machine because SQLite runs in-process, whereas Postgres runs out of process. In terms of writes you can get pretty good SQLite performance (of course you have to enable WAL).
I question why you'd pick Postgres over SQLite if you're running something on a single machine though? SQLite is an amazingly well-tested piece of software with maintainers who have an explicit goal of keeping it usable through to 2050.
We may be thinking different application scales, I think. The general way I've seen it work is that sqlite is faster... until you hit a certain performance requirement threshold. The problem is, getting faster past that requires fundamentally restructuring your application, and even then there's limits you'll never get past. If you are working on a little user facing app or simpler project, sure. But when you scale to the point of doing serious DB work, it just doesn't have what it needs to keep up.
In my case, it's a application at my job that does just run locally... but you're talking about a locally run program that's distributing work across multiple high load C++ processes to keep up with performance demands. We actually did deploy with sqlite for a while for robustness and ease of deployment (it's in an environment where maintenance accessibility is low), but it required a bunch of workarounds to accommodate sqlite's limitations, and still got a fraction of the performance of even just swapping out sqlite for postgres without even changing the architecture to remove the workarounds. Even more performance once we quit having to coddle sqlite to get it to work.
Note, I'm not saying sqlite is bad. It's still my go to for personal projects. It just has very stark limitations that one needs to be cognizant of.
It's obviously hard to say without knowing anything about your application but this sounds like you're using the database as a concurrency control mechanism with a write-heavy workload? In this case I can totally see why SQLite is not a good fit (e.g. it doesn't have row-level locking), but the cases in which the article advocates for people using SQL are really ones where the workloads are pretty read-heavy (as in the case of websites).
I generally find it's relatively straightforward to migrate applications to Postgres (which is almost but not quite a superset of SQLite in terms of compatibility) and then just run a number of instances of the application behind a load balancer.
There's some synchronization, primarily to bring a process up to current if a process is taken down and has to spool back up, although the heavy write load is because we're required to be able to forensically replay system state for later analysis, so extremely detailed high rate logging.
Yeah, if it was more read heavy, it wouldn't be an issue. This kind of write level load where you're bottlenecked by sqlite locking for the writes is the main case I've seen where there's problems.
The thing I’ve run into with SQLite is if you have a lot of writes (I was using SQLite to store logs, so hundreds to thousands of small writes every second) is you need to put them all in a transaction that commits every second or so.
Doing that got good enough performance in my case.
Sorry to snag those out of context and outline an idea that isn't directly SQLite, but I actually have had a much fonder experience setting up RQLite (which is SQLite + distributed and/or segmented replication) to be a much better and performant option than Postgres. Not only that, but it's so small, easy to install, and it's performant beyond imagination out of the box. Postgres, while certainly better than say MySQL, has always kind of been a performance nightmare and even more of a nightmare to properly setup and install (and insanely easy to fubar). I've been reaching for RQ a lot over the last two or so years. There are a few other options (Litestream, et al2), just haven't really tried the others.
I find distributed SQL to be practically the dream, especially in my field (and most I would imagine), where a device has their own internal database and instead of "replicating" (ie, pushing and pulling/polling) by item or table, and then collate them, you can replicate entire databases into the 'main/master' database alongside others, or you can allow for automatic (or manual, if you so wish) conflict-resolution replication and duplication between all the nodes for data symmetry.
On SQLite and it's downsides, I find the 'plugin' extension system far better than postgres to fix the issues that may come up with the core of SQLite, if you are designing a system, you should expect to use and develop modules for your system, SQLite just gets rid of the cruft and puts in a single binary - the absolute dream for databases. Postgres tries to be a virtual machine with its own built in programming languages for applications, SQLite tries to just be a file that you can apply extensions on top of in your own applications.1
I'll use both, but always prefer working with SQLite than anything PG. You tell me the system has SQLite, I am excited to dig in. You tell me the system runs on PG, and I start worrying because there is going to be an entire custom ecosystem I am going to have to learn before I can start messing with the data,
*. I didn't even realize I had these feelings about the Postgres vs SQLite debate, weird. I might need to think more about that, my decisions in life, and go touch grass and stare at the clouds for a few
milleniashours.1. As an example: building E2EE in SQLite is an unexpected phenomenal experience because of this. Postgres is a nightmare in my experience.
2. Alternatives to RQ that have piqued my interest. There are quite a few, because SQLite is just great at being extended while keeping the core SQLite database untouched:
Where I list some additional types and my interest in them
I have been fascinated with LiteFS, and will likely shift to reaching for it over RQ as it seems more specific to my use cases.
Or even, Marmot, which I just found and sounds fascinating.
There is also DQLite, supported (and pushed) by Canonical that is tailored for IOT and Edge Devices, but can be used for larger. I particularly like their addition of official support for the IBM z/OS Mainframes, never worked on them, but love their systems design.
Edit: Added ^2 instead of as an additional comment.
Could you elaborate on the "insanely easy to fubar" part of your comment? I've used Postgres in dozens of projects under heavy load. I've never run into an issue that I could consider "fubaring" the database and now I'm concerned your comment has jinxed me for an upcoming release.
We run multiple medium-use apps off of a single unified postgres server, total database size is about 200GB with about 80GB of WAL churn a day. The extent of server setup was:
Then we basically didn't need to touch it for 5 years unless an app fucked up. If performance is bad, its probably a lack of indexing or questionable architecture design rather than anything wrong with postgres itself.
I'll leave that for others more focused on Postgres to talk on, as I am sure it's partially a failure due to my adherence, but this perfectly exemplifies my feelings, not particularly on destroying the data within, you should be backing that up 123 style, but more on performance and the Postgres engine itself.
Correct. My company's old software was based on SQLite for some instances and it was a nightmare. The software can have multiple users writing to the database frequently. Supporting it was awful. We've moved on, but the memory of it stays with me
I've used sqlite quite often as a testing database. I always end up having to use something else because of the lack of migration support.
I think SQLite is great for set it and forget it type uses. I dunno that I'd choose it specifically in my web stack, but for many situations it's good enough and with good caching and a CDN you can do a lot even with a large spike in traffic. But I wouldn't entertain it for a large enterprise platform roll out.
Now, systems applications that have local user driven workloads I'd package it in for tons of situations.
I've had a lot of issues the last few years with sqlite databases (in several different contexts) getting irrecoverably corrupted. Plus the whole no multithreading thing. I wouldn't recommend sqlite to anyone anymore.
Which is a shame, because a single-file db is a nice bit of kit.
This is really surprising to me because I have found SQLite to be amazingly robust (the file format hasn't changed in basically forever, I don't know of any open source databases with a more sophisticated testing regime). SQLite is even recommended as an archival format by the Library of Congress.
Home Assistant uses it for the default history logger. Maybe they configured it wrong, but I've had that thing get corrupt many times for one reason or another.
My only REAL pet peeve with SQLite when I've had to use it for something as a sysadmin is the dates handling, I'm stupidly spoiled by Postgres' great date/time/datetime management and SQLite just doesn't compare. Yeah, there's important issues described in the blogpost, but this is the one that always has left me annoyed when I've worked with it.