Comment by adityaathalye

5 hours ago

SQLite emphatically warns against concurrent writes. It is not designed for that.

I'm seeing these numbers on my current scratch benchmark:

- Events append to a 10M+ record table (~4+ GiB database).

- Reads are fetched from a separate computed table, which is trigger-updated from the append-only table.

- WAL-mode ON, Auto-vacuum ON

  {:dbtype "sqlite",
   :auto_vacuum "INCREMENTAL",
   :connectionTestQuery "PRAGMA journal_mode;",
   :preferredTestQuery "PRAGMA journal_mode;",
   :dataSourceProperties
   {:journal_mode "WAL",
    :limit_worker_threads 4,
    :page_size 4096,
    :busy_timeout 5000,
    :enable_load_extension true,
    :foreign_keys "ON",
    :journal_size_limit 0,
    :cache_size 15625,
    :maximumPoolSize 1,
    :synchronous "NORMAL"}},

- 1,600 sequential (in a single process) read-after-write transactions, append-only, no batching.

- With a separate writer process (sequential), and concurrently, two reader processes, I'm seeing 400+ append transactions/second (into the append-only table, no batching), and a total of 41,000 reads per second, doing `select *` on the trigger-updated table.

My schema is (deliberately) poor --- most of it is TEXT.

(edit: add clarifying text)

2 comments

adityaathalye

Reply
TylerE  4 hours ago

Isn’t that schema actually the opposite of poor for SQLite, since it stores everything as text internally?

  • adityaathalye  3 hours ago

    It employs "flexible typing", which does not mean "everything is text". What I am doing is writing fully denormalised text (strings) in most fields, with column type declared as TEXT.

    This is deliberate, to emulate "whoops, if I screw up my types, how bad does it get?".

    However, when written into the DB with some care, each value is stored per the following storage classes:

    https://sqlite.org/datatype3.html

    Quoting...

    ```

    Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

        NULL. The value is a NULL value.

    INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

    REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

    TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

    BLOB. The value is a blob of data, stored exactly as it was input.

    A storage class is more general than a datatype. The INTEGER storage class, for example, includes 7 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.

    Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.

    All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

    ```

    (edits: formatting, clarify what I'm doing v/s what SQLite does)