10 ms·

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere

What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which means if the service had gotten bigger, your chance of moving away from SQLite kind of walks away. So, why not use a safer choice to begin with? Nothing is complicated running MySQL/PostgreSQL unless you've sold yourself to AWS to care for the cost and don't know how to run a DB instance yourself.

Glench 5y ago
> What is the point of using SQLite under a web service? Take a look at the Consider SQLite post I linked. They address your performance questions too. For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.
- iampims 5y ago
  Any tips you can share on how to deploy your saas app without downtime when using sqlite?
  - srcreigh 5y ago
    For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check. As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.
    - iampims 5y ago
      Which backend “owns” the sqlite database? That’s what I haven’t seen mentioned anywhere, if the database is part of the application, how do you switch from one version to another without downtime.
      - srcreigh 5y ago
        The VM owns the database. Multiple backend service versions run on that same VM. Usually just one but 2 while migrating traffic to a new version. I haven't figured out details regarding having 2 processes both writing and reading to the SQLite DB at once. It might just be fine. With a 1 minute request timeout I can just shut down the previous version after 2 minutes (should receive no new requests after 1 minute) and caddy will have sent requests to the new version for a while. Not sure which types of errors I'll be seeing but the client may need to retry requests in some cases. This is all just what I've planned but hopefully kinda makes sense.
lm28469 5y ago
> So, why not use a safer choice to begin with? I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick. People underestimate "simple" tech performance, in 99% of projects by the time your bottleneck is your DB system I can assure you that it'll be the least of your concerns
- mekster 5y ago
  Which part of running MySQL instead SQLite is over engineering?
  - post-it 5y ago
    - Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.
    - mekster 5y ago
      So you trade for some risk for an hour.
      - kyawzazaw 5y ago
        Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.
      - TheRealPomax 5y ago
        Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.
dspillett 5y ago
> What is the point of using SQLite under a web service? … people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. My understanding is that for read performance SQLite is pretty damn good, outperforming MySQL and Postgres in both single and concurrent tests. The key performance issue is the single global write lock. If your data access pattern is massively read biased then SQLite is a good choice performance wise, if you see a lot of write activity then it really isn't. With regard to being correct*, it offers proper ACID transactions and so on. Typing is a big issue for some but far from all. It is significantly more correct than mysql used to be back before InnoDB became the default table type in ~2010, at least as correct as it now (aside from the data types matter depending on which side of that you sit on).
robertlagrant 5y ago
> I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant While "people" complaining is basically meaningless, I don't know why they'd be doing that about SQLite. It's used in most phones, all copies of Windows 10+, and countless other places.
- djbusby 5y ago
  Chrome browser has loads of them in your profile directory.
simonw 5y ago
I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call. So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.
- mekster 5y ago
  Running against a unix socket doesn't seem to make that a selling point for SQLite.
  - simonw 5y ago
    There's still serialization and deserialization overhead there. I would expect SQLite to win in benchmarks against MySQL or PostgreSQL on a Unix socket for basic "select * from table where id = 5" calls, but I've not done the work to prove it myself.
oppositelock 5y ago
SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need multiple backends to handle more load, or the moment you need high availability, you can't do it with SQLite. SQLite has very limited DDL operations, so you also can't evolve your schema over time without downtime. Now, for streaming backups - how do you come back from node failure? You're going to incur downtime downloading your DB. I run many SQL backed production services, and my sweet spot has become a big honking Postgres instance in RDS in AWS, with WAL streaming based replicas for instant failover in case of outage, and read replicas, also via WAL. This system has been up for four years at this point, with no downtime. I love SQLite, and use it regularly, just not for production web services.
- SnorkelTan 5y ago
  SQLite has write ahead logging as well https://sqlite.org/wal.html https://sqlite.org/wal.html
  - oppositelock 5y ago
    It doesn't let you run an active replica from WAL.
- js2 5y ago
  > my sweet spot has become a big honking Postgres instance in RDS Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.) [1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price. https://aws.amazon.com/blogs/database/is-amazon-rds-for-postgresql-or-amazon-aurora-postgresql-a-better-choice-for-me/ https://aws.amazon.com/blogs/database/is-amazon-rds-for-post...
deleted 5y ago
[deleted]

Glench 5y ago

> What is the point of using SQLite under a web service? Take a look at the Consider SQLite post I linked. They address your performance questions too. For me, SQLite was a nice way to simplify launching and running my SaaS business and has had no downsides.

iampims 5y ago
Any tips you can share on how to deploy your saas app without downtime when using sqlite?
- srcreigh 5y ago
  For my personal project I'm planning to use a VM with an SSD. Manually I'll use caddy to switch over to a new running backend service with readiness check. As for scaling if I need it I can increase disk space for the app server or scale out horizontally/vertically. Don't need that yet so I'm waiting for more details in the future to decide how to handle that.
  - iampims 5y ago
    Which backend “owns” the sqlite database? That’s what I haven’t seen mentioned anywhere, if the database is part of the application, how do you switch from one version to another without downtime.
    - srcreigh 5y ago
      The VM owns the database. Multiple backend service versions run on that same VM. Usually just one but 2 while migrating traffic to a new version. I haven't figured out details regarding having 2 processes both writing and reading to the SQLite DB at once. It might just be fine. With a 1 minute request timeout I can just shut down the previous version after 2 minutes (should receive no new requests after 1 minute) and caddy will have sent requests to the new version for a while. Not sure which types of errors I'll be seeing but the client may need to retry requests in some cases. This is all just what I've planned but hopefully kinda makes sense.

lm28469 5y ago

> So, why not use a safer choice to begin with? I know several people who build projects like that. It take them months to get a working product, just to discover it doesn't interest people or doesn't work like they expected. If for every piece of tooling you go for the "safe" and most performant one you gain bloat and complexity real quick. People underestimate "simple" tech performance, in 99% of projects by the time your bottleneck is your DB system I can assure you that it'll be the least of your concerns

mekster 5y ago
Which part of running MySQL instead SQLite is over engineering?
- post-it 5y ago
  - Setting up a MySQL server on both your dev machine and server, and making sure they're the same version (extra fun if they're on different OS versions) - Setting up an out-of-repo config file on the server with your MySQL credentials - Setting up a backup script for your server data It's only about an hour of work total, but it's an hour of work that I hate doing.
  - mekster 5y ago
    So you trade for some risk for an hour.
    - kyawzazaw 5y ago
      Pieter Levels has been using SQLite for nomadlist and I think it's been going well for him.
    - TheRealPomax 5y ago
      Please do explain what risk you're thinking of, as anyone smart enough to write their own SaaS would not put resources in the web server's file system tree? You stick your db file in an normal secure location outside the server's root, chmodded appropriately so that it suffers the exact same risks as any other file on the OS. It's no more or less risky than /etc/shadow, while being considerably easier to work with (and less failure-prone for light db work) than an independently running database service.

dspillett 5y ago

> What is the point of using SQLite under a web service? … people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. My understanding is that for read performance SQLite is pretty damn good, outperforming MySQL and Postgres in both single and concurrent tests. The key performance issue is the single global write lock. If your data access pattern is massively read biased then SQLite is a good choice performance wise, if you see a lot of write activity then it really isn't. With regard to being correct*, it offers proper ACID transactions and so on. Typing is a big issue for some but far from all. It is significantly more correct than mysql used to be back before InnoDB became the default table type in ~2010, at least as correct as it now (aside from the data types matter depending on which side of that you sit on).

robertlagrant 5y ago

> I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant While "people" complaining is basically meaningless, I don't know why they'd be doing that about SQLite. It's used in most phones, all copies of Windows 10+, and countless other places.

djbusby 5y ago
Chrome browser has loads of them in your profile directory.

simonw 5y ago

I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call. So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.

mekster 5y ago
Running against a unix socket doesn't seem to make that a selling point for SQLite.
- simonw 5y ago
  There's still serialization and deserialization overhead there. I would expect SQLite to win in benchmarks against MySQL or PostgreSQL on a Unix socket for basic "select * from table where id = 5" calls, but I've not done the work to prove it myself.

oppositelock 5y ago

SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need multiple backends to handle more load, or the moment you need high availability, you can't do it with SQLite. SQLite has very limited DDL operations, so you also can't evolve your schema over time without downtime. Now, for streaming backups - how do you come back from node failure? You're going to incur downtime downloading your DB. I run many SQL backed production services, and my sweet spot has become a big honking Postgres instance in RDS in AWS, with WAL streaming based replicas for instant failover in case of outage, and read replicas, also via WAL. This system has been up for four years at this point, with no downtime. I love SQLite, and use it regularly, just not for production web services.

SnorkelTan 5y ago
SQLite has write ahead logging as well https://sqlite.org/wal.html https://sqlite.org/wal.html
- oppositelock 5y ago
  It doesn't let you run an active replica from WAL.
js2 5y ago
> my sweet spot has become a big honking Postgres instance in RDS Why do you prefer PostgreSQL RDS over Aurora RDS? Aurora seems better in every way but price[1]. (I know it also had some growing pains at launch.) [1]: Amazon RDS for PostgreSQL is ideal when you have a small-to-medium intense workload. It works best when you have limited concurrent connections to your database. If you’re moving from commercial database engines such as Oracle or Microsoft SQL Server, Aurora PostgreSQL is a better choice because it provides matching performance with a lower price. https://aws.amazon.com/blogs/database/is-amazon-rds-for-postgresql-or-amazon-aurora-postgresql-a-better-choice-for-me/ https://aws.amazon.com/blogs/database/is-amazon-rds-for-post...

deleted 5y ago

[deleted]