Sqlite

• Two SQLite Libraries, One Deleted Password

  Every format in this series so far has been a way of writing a document down. SQLite is the one that asked a different question: what if the application file format were a database?

  It is the best-designed thing I have written about in this whole series. It has a real specification, a versioned header, a page allocator, transactions, and a compatibility promise going back to 2004. The Library of Congress recommends it for long-term preservation. Its own documentation makes the case that a .sqlite file is a better application format than a custom binary blob or an XML tree, and the case is correct.

  And it will still hand a deleted password to anyone who runs strings on the file, or not, depending on which copy of the library you happened to link.

  ---

  The Header Is Legible

  The first hundred bytes of any SQLite database are a fixed-layout, big-endian header. You can read it with xxd:

  $ xxd -g 1 -l 32 notes.db
  00000000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00  SQLite format 3.
  00000010: 10 00 01 01 00 40 20 20 00 00 00 06 00 00 00 02  .....@  ........
  

  Sixteen bytes of magic, and it is the literal string SQLite format 3 with a nul terminator. Then decoding what follows:

  page size        4096
  write version    1  (1 = rollback journal, 2 = WAL)
  read version     1
  reserved/page    0
  change counter   6
  size in pages    2
  freelist head    0
  freelist count   0
  text encoding    1  (1 = UTF-8)
  sqlite version   3047001
  

  The page size is a two-byte field, which creates a small problem: the format allows 65,536-byte pages, and 65,536 does not fit in two bytes. The spec’s answer is a special case, and it is worth reading in the original:

  to specify a 65536-byte page size, the value at offset 16 is 0x00 0x01. This value can be interpreted as a big-endian 1 and thought of as a magic number to represent the 65536 page size.

  That is the whole hack, documented in the spec, with the word “magic number” used by the authors about their own file. Compare that to every other format in this series, where the ambiguities were things nobody wrote down.

  Every page after the header starts with a one-byte type flag:

  page 1: flag 0x0d  leaf table
  page 2: flag 0x0d  leaf table
  

  0x0d is a table leaf, 0x05 a table interior node, 0x0a an index leaf, 0x02 an index interior node. Four values, and from those four you can walk a B-tree without a library. Each page is slotted: a pointer array grows down from the header, cell content grows up from the bottom, and free space is whatever is left in the middle.

  This is a database engine’s internal layout, written down, versioned, and promised to stay readable. There is a reason people keep reaching for it as a document format. The project’s own argument for it is that you get transactions, partial reads, and incremental writes for free, and that for blobs under about 100KB it reads and writes faster than the filesystem does.

  ---

  Two Libraries, Two Answers

  Here is a three-row table. I delete the row with the secret in it, and then look at the file.

  import sqlite3
  c = sqlite3.connect("notes.db")
  c.executescript("""
  CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
  INSERT INTO notes(body) VALUES ('the wifi password is hunter2-CANARY');
  INSERT INTO notes(body) VALUES ('groceries: milk, eggs');
  INSERT INTO notes(body) VALUES ('call the dentist back');
  """)
  c.commit()
  c.execute("DELETE FROM notes WHERE body LIKE '%CANARY%'")
  c.commit()
  

  $ strings notes.db | grep -i canary
  (Sthe wifi password is hunter2-CANARY
  

  The row is gone from every query. SELECT count(*) returns 2. The bytes are still on the page, because deleting a row marks its cell as a freeblock and links it into a chain of free space. Nothing overwrites it. The next insert that happens to fit will land there and clobber it, and until then it sits in the file.

  That is the behavior everyone knows about. If you ran the same three statements through the sqlite3 command-line tool on the same machine, on the same directory, and the string will be gone.

  $ sqlite3 --version
  3.51.0 ...
  
  $ sqlite3 notes.db 'PRAGMA secure_delete;'
  2
  

  $ python3 -c "import sqlite3; print(sqlite3.sqlite_version); \
      print(sqlite3.connect(':memory:').execute('PRAGMA secure_delete').fetchone()[0])"
  3.47.1
  0
  

  secure_delete off means freed space keeps its contents. secure_delete set to 2 is FAST mode, which zeroes freed space inside a page without chasing it into the freelist. macOS ships one build with it on. CPython ships another with it off. Neither reports the difference in PRAGMA compile_options. Nothing in the file records which one wrote it.

  So “does my database keep deleted data” has no answer at the format level. It has an answer per binary, and both binaries are called SQLite, and both are on this laptop.

  VACUUM does resolve it, because it rebuilds the entire file from live rows:

  $ python3 -c "import sqlite3; sqlite3.connect('notes.db').execute('VACUUM')"
  $ strings notes.db | grep -ci canary
  0
  

  Which is the same shape as yesterday’s PDF post. The safe operation and the obvious operation are different operations. DELETE is the obvious one.

  ---

  The Single-File Database Is Three Files

  The pitch for SQLite as an application format leans hard on it being one file you can copy, email, and back up. In the default rollback-journal mode that is true between transactions. Turn on WAL, which is what you want for anything with concurrent readers, and while a connection is open:

  -rw-r--r--  4096 w2.db
  -rw-r--r-- 32768 w2.db-shm
  -rw-r--r-- 12392 w2.db-wal
  

  The write-ahead log and the shared-memory index are each larger than the database. The -wal file starts with its own magic number:

  37 7f 06 82 00 2d e2 18 00 00 10 00
  

  0x377F0682 says little-endian checksums, and the last four bytes are the page size again, 4096, matching the main file.

  Copy just w2.db while that connection is open and you get the database as of the last checkpoint, silently missing every committed transaction still sitting in the WAL. The main file’s header even tells you this is possible: the read and write version bytes both flip from 1 to 2 when WAL is enabled, which is the format’s way of saying “a reader that does not understand WAL must not open me.”

  Both files disappear on a clean close, which is why this bites people at exactly the wrong moment. Your backup script tested fine.

  ---

  What To Do About It

  • Set PRAGMA secure_delete=ON explicitly if the database holds anything sensitive, in your application code, not in a config file you hope got read. Do not assume the default. Check it at startup and log what you got.
  • VACUUM after deleting anything that mattered. It is the only operation that actually reclaims and rewrites. On a large database it is expensive and takes a full-file lock, so schedule it.
  • Back up with .backup or VACUUM INTO, never cp. Both take a consistent snapshot including the WAL. cp on a live WAL database gives you a file that opens fine and is missing data.
  • Check PRAGMA integrity_check in your test suite, not just in production incident response.
  • Read the header yourself when debugging. Page size, page count, encoding, and journal mode are all in the first 100 bytes, and a hex dump will tell you in two seconds whether the file is truncated, whether it is even SQLite, and what wrote it.
  • Use it as an application file format. Seriously. The argument is right. Just know that “it is a database” means it has a database’s habits, including keeping things you told it to delete.

  Two weeks into this series the pattern is uncomfortably consistent. The problem is almost never the specification. CSV had none, JSON had a small one, YAML had a good one that arrived late, XML had one for everything, and SQLite has one that is legible, complete, and honest about its own hacks. It still ships a data-remanence behavior that flips based on a compile-time flag neither binary bothers to report.

  The format is not what you get. The build is what you get.

  Sources

  • SQLite Database File Format — the 100-byte header, the page types, the freeblock chain
  • SQLite as an Application File Format — the project’s own argument, including the claim that it beats the filesystem for blobs under 100KB
  • Write-Ahead Logging and the WAL file format — the -wal and -shm companions, and why cp is not a backup
  • PRAGMA secure_delete — including the FAST mode that macOS ships and CPython does not
  • Library of Congress: SQLite as a recommended preservation format — the format description

  I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].

  Aug 27, 2026 Programming Sqlite Databases security File-formats

  Enjoyed this?

• pgvector vs sqlite-vec: You Probably Don't Need Postgres

  Whenever I start looking into vector search, I always end up finding information on pgvector. It’s totally worth considering, especially if you already have Postgres. But there are situations where it might be overkill. And in those situations, sqlite-vec (the successor to sqlite-vss) is quietly becoming the better answer.

  When SQLite Wins

  Local-first apps and CLI tools. If your thing runs entirely on a user’s machine, a personal knowledge base plugin, a developer CLI, a desktop app, running a full Postgres instance is a huge ask. sqlite-vec gives you a vector store as a single .db file sitting next to your markdown or code. Zero configuration. No background daemon. No port management. It’s just there.

  Edge computing. On Cloudflare Workers or Vercel Edge Functions, cold starts matter. Establishing a connection to a remote Postgres database, even with a connection pooler, adds latency you feel. SQLite can be bundled with your app or mounted as a local read-only resource. Near-instant access to embeddings.

  Testing. Spinning up a Postgres container just to verify your vector search logic adds seconds to every test run. With sqlite-vec, you initialize an in-memory database, run your tests, and discard it in milliseconds. If you care about fast inner loops, might be the right solution.

  Single-user scenarios. Building a personal RAG system for your own research? A private publishing pipeline? The complexity of managing Postgres users, permissions, and backups is unnecessary. A single file on disk is easier to back up (just copy it) and easier to reason about.

  The Actual Trade-offs

  There are real differences and here is what I found.

  Feature	sqlite-vec	pgvector
  Deployment	Library (embedded)	Server (process/container)
  Configuration	Zero	High
  Portability	Single file	Database dump/restore
  Concurrency	One writer	Multi-user
  Ecosystem	Focused vector ops	Full relational SQL

  pgvector has better memory management, using the Postgres buffer cache and background workers. sqlite-vec runs in-process, so a large vector index competes directly with your application’s memory. And Postgres has decades of refinement on indexing strategies, JSONB joins, and all the relational features you’d expect.

  If you’re not using those features, you’re paying for complexity you don’t need. If you prefer lightweight tools over heavy infrastructure, sqlite-vec just gets out of the way. There’s no docker compose file required to start your day. The database is just there when your binary runs.

  Pick the Right One

  The decision is probably easier than you would think.

  If you have multiple users writing concurrently, need complex relational queries alongside your vectors, or are already running Postgres, use pgvector. It’s battle-tested and the ecosystem is deep.

  If you’re building something local-first, single-user, edge-deployed, or just want fast tests without container overhead, reach for sqlite-vec. You’ll spend less time on infrastructure and more time on the actual problem.

  Not every vector search needs a database server.

  I’d appreciate a follow. You can subscribe with your email below. The emails go out once a week, or you can find me on Mastodon at @[email protected].

  Apr 26, 2026 Sqlite Development Postgres Vector-search Pgvector

  Enjoyed this?

• Just discovered DB Pro, a new desktop app for SQLite and LibSQL databases. Looks pretty promising. Meanwhile, I’m still waiting for DataGrip to get proper LibSQL support. Come on JetBrains, just give me Turso already!

  Jan 6, 2026 Sqlite Databases Tools