7 ms·

Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow ((edit: correction) ~10 trans

by ori_b 6y ago

Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow ((edit: correction) ~10 transactions per second) if you aim for integrity, or unsafe if you tune for performance (~50k transactions per second, but if your program or computer dies half way, your file is hosed). So, keeping files up to date in place makes your ui janky. You can work around it by working in memory and writing out a whole new database from in memory structures on save and then do the atomic rename. But if you do that, you are probably better off with json, protobuf, or similar. The libraries around these formats are similarly battle tested, but they fit the needs better, supporting working in ram fully and then saving cleanly and easily.

tzs 6y ago
> Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow (~100 transactions per second) if you aim for integrity, or unsafe if you don't. The kind of application files they are talking about (things like word processor documents, spreadsheets, drawings, source code control system data) would only be writing sporadically. During one of those sporadic writes they might need to update thousands of rows but those could all be done in one transaction.
- ori_b 6y ago
  I made a mistake -- it's an order of magnitude slower. That means a single transaction is within the threshold for human perception. If you block the UI thread on a transaction, you're now dropping 6 frames at 60fps. If you block other operations on the transaction completing, you end up with lag. Reference: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html, question 19. (I've seen similar when testing on SSDs locally).
  - lostmyoldone 6y ago
    If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. It has generally become much better in the last decade or two, but one should still expect most OS's to sometimes pause for excessive amounts of time on disk IO unless the API is specifically guaranteed to never pause. Even then one would be wise to measure/log deviations if it's critical for the application. OS guarantees might also be contingent on driver/subsystem guarantees, and bad drivers might sometimes affect what seems completely unrelated upstream systems.
    - ori_b 6y ago
      > If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. Yes, and other file formats encourage doing things in memory, so you don't have any disk i/o in the common path. Using sqlite as a file format strongly discourages the simple, jank-free until you press save workflow of slurping your content, operating on it in memory, and then outputting it all in one operation as a response to an explicit user action. Instead, your whole application gets small but perceptible delays across all operations and interactions.
      - em500 6y ago
        Your concerns seem pretty theoretical. Several very widely used applications (Whatsapp, iMessage, Apple Notes/Calendar) use sqlite as a file format without much issue.
        ori_b 6y ago
        It's also recommended against by Firefox because it's been causing performance issues: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Ne...
        sdinsn 6y ago
        It's funny you use that as an example, since Firefox itself uses SQLite as the application file format for history and bookmarks...
        ori_b 6y ago
        Yep. if you read it, that page is basically their experience report with that decision.
        6y ago
  - lalaland1125 6y ago
    Why would you block the UI thread on a transaction?
    - ori_b 6y ago
      Because you've got another operation triggered by a UI action, and you need the previous one to finish, for example.
      - yellowapple 6y ago
        Sure, but you shouldn't need to block the thread for this. Most applications that have to deal with this sort of thing would present a loading indicator or something instead while a different thread is busy doing the actual transaction work. This is both possible and encouraged with (to my knowledge) literally every desktop or mobile UI framework written in the last 20 years. And to be clear: this will still be user-visible with literally any other file format. The slow part ain't SQLite itself; it's the disk to which you're writing.
      - sdinsn 6y ago
        That doesn't answer the question. You never should have to block the UI thread. That's just poor application design, independent of the file format; you cannot blame that on SQLite.
      - carlhjerpe 6y ago
        If the user interface depends on synchronous IO something is very wrong.
kbumsik 6y ago
> It's also very slow (~100 transactions per second) > But if you do that, you are probably better off with json, protobuf, or similar. Are you talking about a server application? > or unsafe if you find for performance (you can get it up to, IIRC, ~50k transactions per second, but if your program or computer dies half way, your file is hosed) Never heard that SQLite has unsafe operations. Any source?
- ori_b 6y ago
  > Are you talking about a server application? I'm talking about doing a handful of transactions -- even a single one, now that I looked at the actual numbers that sqlite discusses -- being enough to introduce user-visible jank. > Never heard that SQLite has unsafe operations. Any source? The sqlite docs. You can improve the performance of sqlite by multiple orders of magnitude by messing with things like https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous, at the cost of safe atomic updates.
  - kbumsik 6y ago
    > The sqlite docs. You can improve the performance of sqlite by multiple orders of magnitude by messing with things like https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous, at the cost of safe atomic updates. Thanks for the link, but only when the OS or your computer crashes, not even the application itself. Could you introduce any application file format that is safe in that case? Obviously your JSON file format is much more unsafe than that, not to mention the huge overhead of JSON with binary data.
    - ori_b 6y ago
      > Thanks for the link, but only when the OS or your computer crashes, not even the application itself. Ah, right there are also the journalling knobs that you'd tweak to get to max performance -- either in-memory or no journalling at all make a big impact. There are a bunch of them scattered throughout the docs. > Could you introduce any application file format that is safe in that case? Obviously your JSON file format is much more unsafe than that, not to mention the huge overhead of JSON with binary data. Yes, any format is safe if you write to a temporary file, and then rename(2) it to replace it. This is guaranteed to be atomic, so it works with anything (including sqlite, though at that point all sqlite does is cost you performance and complexity).
SQLite 6y ago
Storing content in SQLite is actually faster than writing the equivalent content directly to disk, in many situations. See https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html for discussion, caveats, and links to source code where you can verify these claims for yourself.
sdinsn 6y ago
> if your program or computer dies half way, your file is hosed Doesn't this apply to writing files in general? It's not unique to SQLite... Edit: Nope, SQLite is designed to be protected against crashes, even OS crashes.
justinclift 6y ago
> It's also very slow ((edit: correction) ~10 transactions per second ... Huh? I was pushing updates to a SQLite file on my laptop a few weeks ago with code that's not at all optimised, and wasn't too fussed with ~400 transactions a second, sustained for a minute or so. What were you doing that only gave 10 transactions a second?

by ori_b 6y ago

tzs 6y ago

> Please no. This is an incredibly complicated file format that is not particularly well suited for file saving. It's also very slow (~100 transactions per second) if you aim for integrity, or unsafe if you don't. The kind of application files they are talking about (things like word processor documents, spreadsheets, drawings, source code control system data) would only be writing sporadically. During one of those sporadic writes they might need to update thousands of rows but those could all be done in one transaction.

ori_b 6y ago
I made a mistake -- it's an order of magnitude slower. That means a single transaction is within the threshold for human perception. If you block the UI thread on a transaction, you're now dropping 6 frames at 60fps. If you block other operations on the transaction completing, you end up with lag. Reference: https://www.sqlite.org/faq.html https://www.sqlite.org/faq.html, question 19. (I've seen similar when testing on SSDs locally).
- lostmyoldone 6y ago
  If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. It has generally become much better in the last decade or two, but one should still expect most OS's to sometimes pause for excessive amounts of time on disk IO unless the API is specifically guaranteed to never pause. Even then one would be wise to measure/log deviations if it's critical for the application. OS guarantees might also be contingent on driver/subsystem guarantees, and bad drivers might sometimes affect what seems completely unrelated upstream systems.
  - ori_b 6y ago
    > If you have an FPS or interactivity target that needs to be guaranteed to be better than about 0.1-1 second - depending on platform - you can't have any disk IO at all on the UI thread, or any thread that's supposed to react at interactive rates at all times. Yes, and other file formats encourage doing things in memory, so you don't have any disk i/o in the common path. Using sqlite as a file format strongly discourages the simple, jank-free until you press save workflow of slurping your content, operating on it in memory, and then outputting it all in one operation as a response to an explicit user action. Instead, your whole application gets small but perceptible delays across all operations and interactions.
    - em500 6y ago
      Your concerns seem pretty theoretical. Several very widely used applications (Whatsapp, iMessage, Apple Notes/Calendar) use sqlite as a file format without much issue.
      - ori_b 6y ago
        It's also recommended against by Firefox because it's been causing performance issues: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Ne...
        sdinsn 6y ago
        It's funny you use that as an example, since Firefox itself uses SQLite as the application file format for history and bookmarks...
        ori_b 6y ago
        Yep. if you read it, that page is basically their experience report with that decision.
        6y ago
- lalaland1125 6y ago
  Why would you block the UI thread on a transaction?
  - ori_b 6y ago
    Because you've got another operation triggered by a UI action, and you need the previous one to finish, for example.
    - yellowapple 6y ago
      Sure, but you shouldn't need to block the thread for this. Most applications that have to deal with this sort of thing would present a loading indicator or something instead while a different thread is busy doing the actual transaction work. This is both possible and encouraged with (to my knowledge) literally every desktop or mobile UI framework written in the last 20 years. And to be clear: this will still be user-visible with literally any other file format. The slow part ain't SQLite itself; it's the disk to which you're writing.
    - sdinsn 6y ago
      That doesn't answer the question. You never should have to block the UI thread. That's just poor application design, independent of the file format; you cannot blame that on SQLite.
    - carlhjerpe 6y ago
      If the user interface depends on synchronous IO something is very wrong.

kbumsik 6y ago

> It's also very slow (~100 transactions per second) > But if you do that, you are probably better off with json, protobuf, or similar. Are you talking about a server application? > or unsafe if you find for performance (you can get it up to, IIRC, ~50k transactions per second, but if your program or computer dies half way, your file is hosed) Never heard that SQLite has unsafe operations. Any source?

ori_b 6y ago
> Are you talking about a server application? I'm talking about doing a handful of transactions -- even a single one, now that I looked at the actual numbers that sqlite discusses -- being enough to introduce user-visible jank. > Never heard that SQLite has unsafe operations. Any source? The sqlite docs. You can improve the performance of sqlite by multiple orders of magnitude by messing with things like https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous, at the cost of safe atomic updates.
- kbumsik 6y ago
  > The sqlite docs. You can improve the performance of sqlite by multiple orders of magnitude by messing with things like https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous, at the cost of safe atomic updates. Thanks for the link, but only when the OS or your computer crashes, not even the application itself. Could you introduce any application file format that is safe in that case? Obviously your JSON file format is much more unsafe than that, not to mention the huge overhead of JSON with binary data.
  - ori_b 6y ago
    > Thanks for the link, but only when the OS or your computer crashes, not even the application itself. Ah, right there are also the journalling knobs that you'd tweak to get to max performance -- either in-memory or no journalling at all make a big impact. There are a bunch of them scattered throughout the docs. > Could you introduce any application file format that is safe in that case? Obviously your JSON file format is much more unsafe than that, not to mention the huge overhead of JSON with binary data. Yes, any format is safe if you write to a temporary file, and then rename(2) it to replace it. This is guaranteed to be atomic, so it works with anything (including sqlite, though at that point all sqlite does is cost you performance and complexity).

SQLite 6y ago

Storing content in SQLite is actually faster than writing the equivalent content directly to disk, in many situations. See https://www.sqlite.org/fasterthanfs.html https://www.sqlite.org/fasterthanfs.html for discussion, caveats, and links to source code where you can verify these claims for yourself.

sdinsn 6y ago

> if your program or computer dies half way, your file is hosed Doesn't this apply to writing files in general? It's not unique to SQLite... Edit: Nope, SQLite is designed to be protected against crashes, even OS crashes.

justinclift 6y ago

> It's also very slow ((edit: correction) ~10 transactions per second ... Huh? I was pushing updates to a SQLite file on my laptop a few weeks ago with code that's not at all optimised, and wasn't too fussed with ~400 transactions a second, sustained for a minute or so. What were you doing that only gave 10 transactions a second?