6 ms·

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. B

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

voxic11 6y ago
Also if you really need HA there are nice projects like https://dqlite.io/ https://dqlite.io/
- SahAssar 6y ago
  Looking at https://dqlite.io/docs/protocol https://dqlite.io/docs/protocol it seems like it does not allow for embedded usage though. Doesn't this turn it into more of a traditional client-server RDBMS?
  - yawaramin 6y ago
    From the home page: > Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.
  - rstat1 6y ago
    Looking at the archtecture page [0] on that same website the second heading is "Embedding". [0] https://dqlite.io/docs/architecture https://dqlite.io/docs/architecture
    - SahAssar 6y ago
      Ah, right, I should have looked deeper!
gwd 6y ago
I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.
- jakoblorz 6y ago
  https://litestream.io/ https://litestream.io/ That's exactly what litestream accomplishes.
  - benbjohnson 6y ago
    That's close. Litestream takes a snapshot of the database and then continuously replicates out WAL frames. On restore, it grabs the snapshot and replays all those WAL frames (or all the frames up to a certain time if you want point-in-time recovery).
zokier 6y ago
I see availability as orthogonal issue to scale/size. You might be processing one transaction per day but it can still be super important that the service is available for that one transaction.
tmountain 6y ago
How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?
- sangnoir 6y ago
  It supports multiple readers, and writes are atomic - no reader should ever get corrupt data just because there is a write happening in parallel. You probably should not use the OS to copy the live db file, rather, have an in-process thread that reads the db and writes to a backup location periodically.
  - mrweasel 6y ago
    Exactly, many forget the little detail that they should use the backup feature, rather than just copy the database file.
- nijave 6y ago
  You can also combine file system snapshots to get versioning which would allow you to pull the db file out of the latest snapshot (right after taking it) and send it somewhere
crazygringo 6y ago
So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?
- emodendroket 6y ago
  If writes are infrequent I think it might be fine. Otherwise I’d be wary.
- dbattaglia 6y ago
  I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.
  - danenania 6y ago
    Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.
    - benbjohnson 6y ago
      It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.
      - TheCoelacanth 6y ago
        I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.
mikesabbagh 6y ago
I ran it once on an NFS disk. There must be some file locking happening I think. But I could read from multiple nodes, but for some reason the delete was failing (could not delete a database). Did not test the write as my app at that time was writing from one location only. Anyone tested such HA scenario?

voxic11 6y ago

Also if you really need HA there are nice projects like https://dqlite.io/ https://dqlite.io/

SahAssar 6y ago
Looking at https://dqlite.io/docs/protocol https://dqlite.io/docs/protocol it seems like it does not allow for embedded usage though. Doesn't this turn it into more of a traditional client-server RDBMS?
- yawaramin 6y ago
  From the home page: > Dqlite is a fast, embedded, persistent SQL database with Raft consensus that is perfect for fault-tolerant IoT and Edge devices.
- rstat1 6y ago
  Looking at the archtecture page [0] on that same website the second heading is "Embedding". [0] https://dqlite.io/docs/architecture https://dqlite.io/docs/architecture
  - SahAssar 6y ago
    Ah, right, I should have looked deeper!

gwd 6y ago

I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.

jakoblorz 6y ago
https://litestream.io/ https://litestream.io/ That's exactly what litestream accomplishes.
- benbjohnson 6y ago
  That's close. Litestream takes a snapshot of the database and then continuously replicates out WAL frames. On restore, it grabs the snapshot and replays all those WAL frames (or all the frames up to a certain time if you want point-in-time recovery).

zokier 6y ago

I see availability as orthogonal issue to scale/size. You might be processing one transaction per day but it can still be super important that the service is available for that one transaction.

tmountain 6y ago

How do backups work? Is there a locking mechanism to prevent file corruption if the file is copied during writing?

sangnoir 6y ago
It supports multiple readers, and writes are atomic - no reader should ever get corrupt data just because there is a write happening in parallel. You probably should not use the OS to copy the live db file, rather, have an in-process thread that reads the db and writes to a backup location periodically.
- mrweasel 6y ago
  Exactly, many forget the little detail that they should use the backup feature, rather than just copy the database file.
nijave 6y ago
You can also combine file system snapshots to get versioning which would allow you to pull the db file out of the latest snapshot (right after taking it) and send it somewhere

crazygringo 6y ago

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wrong, or when it's right?

emodendroket 6y ago
If writes are infrequent I think it might be fine. Otherwise I’d be wary.
dbattaglia 6y ago
I think one valid fear is that a web application might, for multiple reasons, scale beyond a single process on a single server. This is common enough that using an embedded database can be problematic, compared to a separate RDBMS process that can be shared between processes. Some web applications are just never going to scale out for any reason, and for those SQLite might be appropriate.
- danenania 6y ago
  Agreed, but it’s not only a question of scaling. Any app with high uptime requirements will need more than a single process from day 1.
  - benbjohnson 6y ago
    It depends on what you consider high uptime. You can achieve 99.95% uptime with 4h of downtime a year. A lot of downtime occurs because of overly complicated systems so running a single process on a single server can give you relatively high uptime.
    - TheCoelacanth 6y ago
      I just want to be able to deploy updates during business hours without downtime. Hard to do with a single process.

mikesabbagh 6y ago

I ran it once on an NFS disk. There must be some file locking happening I think. But I could read from multiple nodes, but for some reason the delete was failing (could not delete a database). Did not test the write as my app at that time was writing from one location only. Anyone tested such HA scenario?