5 ms·

I'd say SQLite entered into a new dimension of applicability once local disks got very fast. You can insert a SQLite row in ~40uS on local NVMe storage today, a

I'd say SQLite entered into a new dimension of applicability once local disks got very fast. You can insert a SQLite row in ~40uS on local NVMe storage today, assuming you can be bothered to set 2 pragmas. For certain applications, a millisecond is too long to wait for a trip to the database. I've got some control loops written around SQLite that operate at 1kHz and beyond.

unixhero 4y ago
I tried and failed to read up on what pragma queries are. Would you like to elaborate? This wasn't able to inform me https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html
- carterschonwald 4y ago
  I believe he means build flag cpp pragmas
  - warmwaffles 4y ago
    No there are some PRAGMA calls that people typically don't set or bother to adjust from the defaults that really improve performance. Some are setting the `journal_mode`, `cache_spill`, and `cache_size`.
- bob1029 4y ago
  These are the specific pragmas I am referring to: https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_journal_mode I set them to "NORMAL" and "WAL" respectively.
  - gpderetta 4y ago
    > A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash That's a pretty big failure to preserve durability.
    - zepolen 4y ago
      Yes and the speedup is not that great, I hear piping changes to /dev/null is even faster.
      - Filligree 4y ago
        My computer rolling back a couple seconds on crashes is, actually, perfectly fine! Furthermore, I think most personal computer owners would agree. The near impossibility of achieving ACI semantics on modern filesystems (other than ZFS) is one of my biggest pet peeves with how data is being handled. Servers may need ACID, but practically no home computers do.
    - pantsforbirds 4y ago
      I mean if you read the full quote: > When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode. It seems extremely reasonable to use WAL with synchronous=NORMAL
      - gpderetta 4y ago
        You might roll back committed transactions in case of a system crash. It might be acceptable for some applications, but I wouldn't certainly call it extremely reasonable. If you do not care about system crashes, why sync at all?
        wongarsu 4y ago
        I've written plenty systems that would be fine with a valid, consistent but maybe slightly older state (for example when the db describes both work to be done and work results, or the db otherwise implicitly records where to resume work). On the other hand there are obvious examples where this is unacceptable, say recording the transactions of a cash register.
  - password4321 4y ago
    A bit more discussion yesterday: https://news.ycombinator.com/item?id=34247738#34250043 https://news.ycombinator.com/item?id=34247738#34250043
ragebol 4y ago
Control loops with SQLite no just in the loop but written around it, could you please elaborate? Sounds interesting.
- bob1029 4y ago
  Application defined functions are exposed to SQL. At the beginning of each tick an entry point query is executed. Everything flows from there. Makes integrating computation and data very easy for domain experts.

unixhero 4y ago

I tried and failed to read up on what pragma queries are. Would you like to elaborate? This wasn't able to inform me https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html

carterschonwald 4y ago
I believe he means build flag cpp pragmas
- warmwaffles 4y ago
  No there are some PRAGMA calls that people typically don't set or bother to adjust from the defaults that really improve performance. Some are setting the `journal_mode`, `cache_spill`, and `cache_size`.
bob1029 4y ago
These are the specific pragmas I am referring to: https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_journal_mode I set them to "NORMAL" and "WAL" respectively.
- gpderetta 4y ago
  > A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash That's a pretty big failure to preserve durability.
  - zepolen 4y ago
    Yes and the speedup is not that great, I hear piping changes to /dev/null is even faster.
    - Filligree 4y ago
      My computer rolling back a couple seconds on crashes is, actually, perfectly fine! Furthermore, I think most personal computer owners would agree. The near impossibility of achieving ACI semantics on modern filesystems (other than ZFS) is one of my biggest pet peeves with how data is being handled. Servers may need ACID, but practically no home computers do.
  - pantsforbirds 4y ago
    I mean if you read the full quote: > When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode. It seems extremely reasonable to use WAL with synchronous=NORMAL
    - gpderetta 4y ago
      You might roll back committed transactions in case of a system crash. It might be acceptable for some applications, but I wouldn't certainly call it extremely reasonable. If you do not care about system crashes, why sync at all?
      - wongarsu 4y ago
        I've written plenty systems that would be fine with a valid, consistent but maybe slightly older state (for example when the db describes both work to be done and work results, or the db otherwise implicitly records where to resume work). On the other hand there are obvious examples where this is unacceptable, say recording the transactions of a cash register.
- password4321 4y ago
  A bit more discussion yesterday: https://news.ycombinator.com/item?id=34247738#34250043 https://news.ycombinator.com/item?id=34247738#34250043

ragebol 4y ago

Control loops with SQLite no just in the loop but written around it, could you please elaborate? Sounds interesting.

bob1029 4y ago
Application defined functions are exposed to SQL. At the beginning of each tick an entry point query is executed. Everything flows from there. Makes integrating computation and data very easy for domain experts.