4 ms·

There are some important things that SQLite does not do. It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options

• lelanthran 5y ago
  > It is not client/server; a process must be able to fopen() the database file. The one time I actually wanted to do that, I wrote the server that `accept`ed incoming connections and used the single `fopen`ed SQLite DB. It can be very flexible that way, TBH, but if you really need that type of thing a more traditional DB is better.
• mushufasa 5y ago
  Note -- the single process write at any one time is a killer for most web apps, where for example within SaaS you have many users doing things at the same time.
  • js4ever 5y ago
    It's not really an issue if you have 1 db per customer
    • rguillebert 5y ago
      If a customer has 1000 employees all using your app, it is.
      • chasil 5y ago
        If the app is designed correctly, then the thousand employees would write to their own temporary databases, and a background job would pull their changes into the main database sequentially. If the app is not specifically designed to do this, then SQLite would not be an option.
        • pc86 5y ago
          Serious question, is this just a "how do I get SQLite to work in this scenario?" thing, or is there actually some other benefit to having this sort of data architecture?
          • chasil 5y ago
            This can actually relate to SMTP servers using mbox or maildir formats. Maildir is more resistant to corruption, and doesn't rely (as much) on file locks. https://serverfault.com/questions/564456/what-are-the-practical-differences-between-maildir-and-mbox https://serverfault.com/questions/564456/what-are-the-practi...
        • rguillebert 5y ago
          What's the benefit over just using the main database?
      • zbentley 5y ago
        Not always. In fact, I'd strengthen that and say that this is not a problem in general/in most cases with thousands of concurrent users. See my other comment one level up.
  • blacksmith_tb 5y ago
    Sure, though that's if they're all doing CRUD at the same time. I maintain our company blog on a self-hosted install of Ghost backed by sqlite, and it's a been great (since the only inserting or updating is likely to be one person editing a post, and the frontend is mostly cached).
  • benbjohnson 5y ago
    Most writes only take tens to hundreds of microseconds and you can perform those writes from different processes serially. As long as you set WAL mode & a busy timeout then you should be fine: PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 30000;
  • dahart 5y ago
    Can you elaborate? I’m imagining that the DB web server process would have no problem opening a thread per user and multiple threads can write to a SQLite store. Or, optionally you could buffer multi-threaded requests into a single queue for DB I/O. I’m not seeing why the single process constraint is a major impediment, or maybe I guess I’m not sure I understand why multiple separate processes might be required. Am I misunderstanding your comment?
  • pc86 5y ago
    Does an event sourcing architecture help with this? I'm thinking a bunch of client piping events into a queue with a single writer working through that queue. Might be stretching the eventual part of eventual consistency if the latency gets too bad, but I don't think SQLite is top of mind for most SaaS solutions anyway so more of a thought exercise.
  • zbentley 5y ago
    Even in write-heavy environments, I've used SQLite effectively with multiple thousands of concurrent (as in logged in and changing things at the speed of human site interactions, not as in simultaneous web requests at the same instant) users. In this context, SQLite is effectively a write linearizer. With appropriate pragmas and database handles stored persistently between requests in server processes, the rate (in bytes of data affected by INSERT/UPDATE/DELETE) at which users could make DB changes got pretty darn close to the rate at which a file could be sequentially written on the filesystem, which is more than enough aggregate throughput for 1000s of concurrent non-bulk changes in most web applications, I'd imagine.
• lrem 5y ago
  I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.
  • cperciva 5y ago
    Be careful! If you copy a database file while it is being modified, you may end up with an inconsistent view of the database depending on the order in which different parts of the file are modified. There are ways around this (filesystem snapshots and potentially file locking) but it's not trivial and failing to handle this correctly is a very good way to build a system which passes tests under low load (when the file-copying operation will win all the relevant races) but fail catastrophically under heavy load.
    • chasil 5y ago
      The sqlite3 utility can be used to create a transaction-consistent backup of a live database. This interface is the only way that a backup should be taken, either from the utility or the C API. .backup ?DB? FILE Backup DB (default "main") to FILE https://sqlite.org/cli.html https://sqlite.org/cli.html
  • chasil 5y ago
    In Oracle, I can do this: RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE; SQLite does not implement such a feature.
    • srcreigh 5y ago
      There is a diff tool [0], and an online backup tool [1]. That kinda thing should be pretty easy to cobble together. You'll just need ~3x disk space to store 2 backups at an interval, diff them and discard the older backup, then apply backward diffs on your most recent backup to achieve a specific backup time. EDIT: or maybe the Session Extension [2] [0]: https://www.sqlite.org/sqldiff.html https://www.sqlite.org/sqldiff.html [1]: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html [2]: https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
• umvi 5y ago
  > Only a single process can write to the database at any time; it does not support concurrent writers. I think you can do concurrent writing now with Write-Ahead Logging (WAL): https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html I've never tried it though, so I don't know how suitable it is for web apps that might potentially have multiple processes trying to write to the DB at the same time.
  • srcreigh 5y ago
    There is no concurrent writing with WAL mode
  • teraflop 5y ago
    Nope. In the default rollback-journal mode, SQLite supports either multiple readers or a single writer at a time. WAL mode improves this to multiple readers and at most one writer. But transactions in SQLite are often fast enough that this could still be acceptable for a lot of purposes, especially on an SSD.
• teraflop 5y ago
  > NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network. On a single machine, file locking is generally reliable (mediated by the kernel). Multiple clients can access the same database simultaneously, with readers and writers taking turns, and all that happens is you pay the performance penalty of serialized access. But if your client thinks it has a lock on a remotely-stored database file, but the server-side lock has actually expired and another client is writing to the database, your data is likely to get hosed. https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
  • chasil 5y ago
    Databases on remote filesystems should be limited to SQLITE3_OPEN_READONLY access, agreed.
    • ridiculous_fish 5y ago
      I'm experimenting with using SQLite to store users' history in fish shell, but the remote filesystems problem seems likely to be a showstopper. What can be done about it?
      • chasil 5y ago
        I'm just reading this, and learning a few new things: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html SQLite has an alternate lock mode with dotfiles that seems to prevent database corruption over NFS. It is important that all SQLite accesses from all connected processes use the same lock mode. "2.3. Two processes using different locking protocols "The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative sqlite3_vfs using the sqlite3_open_v2() interface, an application can make use of other locking protocols that might be more appropriate to certain filesystems. For example, dot-file locking might be select for use in an application that has to run on an NFS filesystem that does not support POSIX advisory locking. "It is important that all connections to the same database file use the same locking protocol. If one application is using POSIX advisory locks and another application is using dot-file locking, then the two applications will not see each other's locks and will not be able to coordinate database access, possibly leading to database corruption."
• srcreigh 5y ago
  In WAL mode, a write can mean writing some data in memory on the local machine. With Postgres/MySQL/etc, it has to go over the network. I can't parse [0] right now about the overhead of TCP and round trips with the database server, but it's basically a question of whether one SQLite write is 10x, 100x, 1000x, or more faster than a database server write. That should make a lot of difference. [0] https://gist.github.com/jboner/2841832 https://gist.github.com/jboner/2841832
  • chousuke 5y ago
    If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). In terms of latency it'll still be difficult to beat a database that lives in the same process as your application, but it won't be as bad as going over the network might be.
    • lelanthran 5y ago
      > If you have PostgreSQL or MySQL on the same machine as your application, you can use UNIX sockets; they won't have much latency at all (I think Linux also optimizes TCP on localhost to skip all the pointless protocol overhead). Sounds like a nice topic for a study; I'd expect the latency using local sockets to connect to a DB server to be about 3x as much, minimum, as the latency to simply write to a file. Writing to a file: a single context-switch from process to kernel (CS). Writing to a socket: CS on write to socket, CS when receiving process writes the data to file, CS when receiving process sends ACK to sender via a socket. The worst thing you can do in your program is to cause a context switch by passing data to kernel or recieving data from a kernel. That being said, a trial of the two approaches is not a bad idea for an easily-publishable paper :-)
      • cma 5y ago
        The context switch buys you safety from many cases of corruption due to uninitialized memory mistakes etc. though, if using an unmanaged language. In C-type code with sqlite embedded, you might write garbage over its internal datastructures. A program error might still make you issue the wrong command or write corrupt data, but you shouldn't corrupt the internals with process isolation.
• boring_twenties 5y ago
  None of those matter to me, but I'm being forced to switch from SQLite simply because of its lack of uint64 support.
  • zbentley 5y ago
    Being forced as in you can't make things work without that support? If so, is parsing/processing blobs infeasible for some reason?
    • boring_twenties 5y ago
      I want to use arithmetic functions like sum(). Sure, I could just sum them myself, but not only would that still leave me SOL with ad-hoc, interactive queries, but actually be more work than just firing up postgres.
• eternalban 5y ago
  > Only a single process can write to the database at any time; it does not support concurrent writers. Potential plus in cloud/container deployments. > It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good. There are other possibilities given a bit of additional wrappers (over the db process) and federating nodes with some (consensus) protocol. It may actually be a good fit as the 'node-unit' for very large scale distributed metadata store that would benefit from relational DB semantics instead of basic K/V.