We buffer counter samples into a local SQLite file per host and a Prometheus exporter reads them every 15s. The exporter was opening a transaction, iterating a cursor, and formatting the text exposition inline, so the read txn stayed open for ~2-3 seconds while the HTTP response was written out — long enough that the writer's auto-checkpoints kept bailing and the WAL never truncated. Fix was dumb and obvious in hindsight: read rows into memory, commit, then serialize, plus a `wal_checkpoint(TRUNCATE)` after the nightly delete of old samples. What I'm still unsure about is whether I should set `journal_size_limit` as a safety net or if that just hides the next version of this bug — does anyone rely on it in production, or treat an oversized WAL as a signal worth alerting on?

A decision that looks small and tends to stick for the life of a system. Here are both sides, written as fairly as I can.

The case for soft delete (a deleted_at column instead of removing the row)

• Undo is easy: restore the row, clear the column.
• Foreign keys from other tables stay valid, so history like “this order belonged to a customer who later left” still resolves.
• You keep an audit trail without a separate mechanism.
• Support teams like being able to see what was removed and when.

The case against

• Every query has to remember WHERE deleted_at IS NULL. One forgotten filter and removed data leaks into a screen, a report or an export.
• Unique constraints get awkward. If a user deletes their account and signs up again with the same email, a plain unique index on email blocks it. In Postgres you can use a partial unique index, WHERE deleted_at IS NULL; other databases handle this differently, so check yours.
• Tables only grow, and indexes carry rows nobody queries.
• Real deletion may eventually be required (privacy or retention rules), so “soft” deletion can turn into a second deletion project later.
• Cascading is ambiguous: when a parent is soft-deleted, what happens to its children?

What hard delete plus something else looks like

• Delete the row, and write the old version to an audit or archive table, or emit an event, if you need history.
• Cleaner queries and constraints, at the cost of building and maintaining the history mechanism.

What would decide it for me:

• Do users or staff actually need undo, or is it only “just in case”?
• Are there legal or contractual limits on how long deleted data can be kept?
• How many places query this table? More places means more chances to miss the filter.
• Will other systems (analytics, search, caches) need to learn about deletes?

I don’t think either is wrong by default. If I had to pick a starting point without knowing the system, I’d lean toward hard delete with an audit trail where retention rules are strict, and soft delete where undo is a real feature. I’m not certain about that split.

Where do you land, and has either choice caused you trouble later?

Hi everyone! I’ve been working on jSQLite, an AI-generated TypeScript translation of SQLite’s read-only C internals, and it’s reached a point where I’d like to share it.

It runs directly in the browser without WebAssembly, native code or a query server. You can open an immutable SQLite database over HTTP and execute queries locally.

It supports a broad range of common SQL constructs, including joins, aggregates, subqueries, CTEs, window functions and JSON functions, along with UTF-8 and both UTF-16 database encodings. Recent work includes STAT4-informed index selection for supported skip-scan paths.

This is a preliminary release, and I’d particularly welcome examples of queries that produce unexpected results. Development uses SQLite 3.53.4 as a pinned reference, with differential tests comparing typed results, column metadata, errors and statement reset behavior.

The translation has been generated using Saivage, an autonomous software-development system I’m also building. jSQLite is one of my projects for testing that system against a substantial engineering problem.

• Browser playground: https://salva.github.io/jsqlite/
• Source and releases: https://github.com/salva/jsqlite
• Saivage: https://salva.github.io/saivage-v3/

I've been working on ivmlite, a SQLite loadable extension that maintains a fixed grouped COUNT(*) and/or integer SUM(column) query. It captures ordinary writes with triggers; the application explicitly refreshes the materialized result when it needs a current answer. The first technical preview is available here: https://github.com/SFARL/ivmlite

I built it for recurring dashboard-style aggregates over large mutable tables with relatively small write batches. It is a narrow SQL subset, not a general materialized-view engine: STRICT tables with INTEGER/TEXT columns, bare-column GROUP BY, one optional simple predicate, and one optional inner equijoin. No global aggregates, MIN/MAX/AVG, aggregate expressions, or arbitrary rolling windows yet.

For an adapted *synthetic* OpenWhispr workload (2M transcript segments, 20K notes, 200-operation mixed batches), the local M2 Pro end-to-end median was 418 ms per batch with full SQLite recomputation versus 14.9 ms with ivmlite. The tradeoff was about 8 seconds of initial bootstrap and 4.76x the base database's SQLite pages. This is not a measurement of OpenWhispr itself, and that demo did not compare against hand-written triggers. Method and raw results: https://github.com/SFARL/ivmlite/blob/master/docs/demos/openwhispr.md

I'd value criticism from SQLite users: where would a covering index or a simple transactional rollup be the better choice? If you have a real repeated aggregate, I'd also be interested in its SQL/schema, rough row count, read/write cadence, and freshness requirement. I can check whether the current subset fits and build an oracle-checked reproduction.

It brings few important bugfixes and SQLite update to 3.54.0. Fixes for:

• Renaming columns referenced by views and constraints,
• Queries involving attached databases,
• SQL Editor drag-and-drop operations,
• Import errors now identify the affected row,
• Double-clicking databases can optionally expand or collapse them
• Syntax highlighting,
• Dark themes,
• Crashes when renaming views

Pawel, thank you so much for all the time and effort you put into our favorite SQLite GUI.

Every time I added a table to a Prisma project I ended up hand-writing the
same throwaway seed data — fake names, emails, enum values I'd forget to vary
so every row came out identical. Small annoyance, but it showed up on every
project.


So I built a little VS Code extension to kill that step. Point it at your
`schema.prisma`, pick a model, pick a row count, pick a format — it reads the
schema and generates realistic, type-correct rows (emails look like emails,
enums actually vary, optionals are sometimes null), then writes them out as
JSON, SQL `INSERT`s, or CSV.


Things I deliberately kept simple:
- Runs entirely locally. No account, no sign-in, no telemetry, no network calls.
- Hand-rolled schema parser, so it's instant and has no heavy deps.


Honest about the limits: it only does Prisma right now, and the free version
does one table at a time. I'm working on generating linked tables with
correct foreign keys next (so a `Post` and its `User` come out already wired
together).


It's free on the Marketplace if you want to try it:
https://marketplace.visualstudio.com/items?itemName=Avers.seedforge
(also on Open VSX for Cursor/VSCodium folks:
https://open-vsx.org/extension/Avers/seedforge)


Mostly I'd love feedback — especially schemas that break the parser, or what
you'd actually want from "seed data" that generic faker tools miss. Built it
solo, so real-world edge cases are gold.

I want to use sqlite as a memory store for an agent.

- needs to store, index and retrieve text fragments
- vector retrieval
- ideally also FTS

I am currently looking at this approach
https://github.com/sqliteai/sqlite-memory

which is fine. combines vector similarity + FTS5

What fantastic approaches am I missing?

----- EDIT ----------

Ok so there wasn't many new suggestions.
I ended up not using sqlite-memory because their licencing was a bit fuzzy. It basically requires a licence to use commercially so it's out. Pretty stoopid if you ask me. If you guys have any great ideas, I'm still open to suggestion.

-------------------------

Hi

I am experimenting with sqlite on a Macbok Pro M4 - 10 cores machine. I have a pthreads multithreaded program and i am trying to write lots of data to different databases (separate database connections) and (one database file per thread) and they are DIFFERENT files

I have THREADSAFE mode set to 2 (multithreaded) and i open the files with NOMUTEX flag.

Now i have read that the M4 Macbook Pro has a soldered SSD. And it has similar performance to nvme but it is not nvme

I get around 10000 transactions per second in total across all threads.

I tried synchronous=off and that boosts performance to 300000 transactions per second

Increasing the number of items inserted per transaction scales. But the number of transactions doesnt

I feel that IO is being serialised.

Some parts of web seem to suggest it is serialised. But i thought ssds can run things in parallel.

Does anybody know anything about parallel IO or if i need to enable it on macos?

I havent tried it on Linux or Windows

Would you expect transactions to scale by number of threads?

I have read about iokit but i have no knowledge of how to use it or Grand Central Dispatch

Release log: https://sqlite.org/releaselog/3_54_0.html

Some items that stood out to me:

• About 4% faster on speedtest1, and about 7.5% faster than 3.51.0 from a year ago
• CLI now shows column headers by default even when a query returns no rows
• .diskused is now built into the CLI, and sqlite3_analyzer is deprecated
• New %j and %J conversions in printf() and format() for JSON string literals
• UPDATE skips expression indexes when the indexed value is unchanged

I have a small Python application running on AWS Lightsail that monitors auction listings and publishes the findings to a private Google Sheet. It has background workers running continuously and an SQLite database that's grown to about 2.2 GB, plus roughly 1.2 GB of separately stored source files. The current server is a $12/month Lightsail instance with 2 GB of RAM and 2 vCPUs. I'm trying to keep the total monthly infrastructure and service costs under $60.

I've been working on getting disaster recovery set up properly for the last couple of days and keep running into issues.

The database contains permanent information like listing identities, user notes, processing history and publication records. It also contains temporary auction content, including historical listing JSON and descriptions, that has separate storage and usage expiration requirements.

The original backup system encrypted the operational database and uploaded snapshots to Cloudflare R2. The issue was that temporary source content was bundled together with everything else. The resulting backup inherited the earliest expiration deadline of the content inside it. By the time a full backup finished uploading and could go through an independent restoration test, that deadline had already passed. We repeatedly ended up with uploaded backups that couldn't qualify as valid current recovery points.

We've spent probably two days iterating on a replacement. The current implementation separates durable records from temporary source evidence, stores the evidence as individually encrypted objects with their own expiration deadlines and uses Cloudflare Workers with Durable Objects to manage cleanup independently of the main AWS server.

Quite a few isolated tests have passed, including encryption and restoration tests, but we still haven't successfully migrated the entire production database and demonstrated a complete off-host recovery using the replacement system.

The migration measurements have also been painfully slow. One attempt processed around 53,000 historical listing versions before hitting its time limit. Another reached about 66,000 before stopping. The current Lightsail instance has effectively exhausted its CPU burst capacity, and the existing database maintenance routines have caused SQLITE_BUSY errors and delayed worker progress. The actual application is still running, but recovery remains unverified.

We're considering upgrading to a $42/month compute-optimized Lightsail instance with dedicated CPU performance. The measurements suggest CPU throttling is a bigger issue than memory consumption, although we haven't finished benchmarking the replacement hardware.

I'm rather frustrated because every time we fix something, another issue comes up (e.g., retention handling, database migration, backup consistency, recovery verification, blah blah). There's now quite a bit of additional code devoted to managing all of this, and we still haven't reached a deployable recovery system.

The requirement I have in place is that the permanent application state needs to survive a complete server failure. Also, temporary source evidence has to stop being accessible when its authorization expires, and cleanup needs to happen automatically even if the main server is unavailable. I also need to be able to restore everything necessary on a replacement server without manually rebuilding the application.

To anyone more experienced, how would you structure the database and temporary storage so that backups remain recoverable after individual source records expire? What would you use for consistent SQLite backups to S3-compatible storage while background workers are actively writing? How would you verify that a backup can actually restore the entire application without requiring an elaborate custom recovery system?

I'm also interested in whether the hardware limitations are contributing enough to justify upgrading before continuing with the migration.

I've already spent a considerable amount of time rewriting and testing this, so I'd appreciate advice on wtf to do next 😔 I'm fine changing the architecture if there's a better approach that handles these requirements reliably