Databases

• 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?

• `updated_at` Is Not a Conflict-Resolution Strategy

  In the last post we talked about the problems with a distributed system, and touched on the fact that timestamps are not as reliable as you think they are.

  If you have two updated_at fields and you compare them, how do you decide which side is the correct one?

  The updated_at field only tells you that a write happened. It doesn’t tell you the meaning, or if it was intentional. Conflict resolution is fundamentally a question about causality. Did side A intend for this change to happen? A wall clock timestamp can’t tell you the answer to that.

  Two independent clocks can drift, and will drift. Yes, it will get corrected by NTP occasionally. But you can’t always rely on their NTP service working. Timestamps are a fine signal that something occurred, and they’re a reasonable way for a human to sort a list and answer roughly when we think a change occurred. But if you use them as a foundation to decide what data to keep, you’re gonna end up destroying and losing data.

  Things That Actually Work

  The good news is the alternatives are not exotic, and you don’t need all of them.

  Content hashes. Hash the meaningful content and compare hashes instead of times. This kills the metadata-edit problem outright: if the hash matches, nothing changed, no matter what the timestamp claims. It’s the highest-value change on this list and usually the easiest, because it’s a pure function of data you already have.

  Version counters. A monotonic integer per record, incremented on every meaningful write. Immune to clock skew entirely, because it isn’t a clock. The cost is that somebody has to own the increment, which is straightforward with a single authority and gets harder without one.

  Sync checkpoints. Record what was confirmed at the last successful sync, not just when it happened. Then the question becomes “has this changed since the last agreed state,” which is answerable, instead of “is this newer,” which is a guess.

  Operation logs. Store what happened rather than only the result. Heavier, but it’s the only option that lets you reconstruct intent after the fact, and it turns “which one wins” into a question you can actually audit.

  You can get most of the benefit from the first one. Hash the content, and let the timestamp go back to being a display field.

  When Last-Write-Wins Is Fine

  I’m not gonna lie, last write wins is often the correct engineering choice, and replacing it with something more complicated can be its own mistake. Sometimes it’s fine. If a write gets lost and the data is recoverable, that’s a trade you can live with.

  If it’s a simple tool without a ton of users, adding a lot of complexity is not the way to go.

  If the data is just a cache or a projection, then who cares? You can rebuild it from the authoritative source anyway.

  What I’d Actually Do

  Keep updated_at. It’s useful. Sort by it, display it, log it.

  Just stop letting it decide things. Add a content hash and check that first, so a no-op edit stays a no-op. If a field can be written from two sides independently, give it a version counter or an explicit authority rule, and write the rule down somewhere the next person will find it.

  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 5, 2026 Databases Software-development Distributed-systems Local-first Data-modeling

  Enjoyed this?

• Your Local File Should Not Have to Argue With Your Database

  Sync bugs usually all start the same way. Two copies of something, both of them mostly right, and no written rule about which one wins.

  The problems occur when you don’t notice The bug. When the file says one thing and the database says another. It’s not a problem until it is. And then you have to spend time figuring the why and when’s of the drift.

  So let’s talk about authority. Not storage, not sync, not “where does the data live.” Authority. Which copy is allowed to be right when two copies disagree.

  The Question Nobody Writes Down

  Most systems that hold the same data in two places never actually decide this. The decision gets made accidentally, by whichever code path happened to run last, and then it gets re-made differently by the next feature.

  The failure is duplication without a stated rule.

  Here’s a concrete version. I run a content pipeline for this blog. Posts are Markdown files with YAML frontmatter sitting in a directory. There’s also a Turso database holding metadata about those same posts. Two copies of what looks like the same information.

  Ask the naive question, “which one is the source of truth,” and you get a bad answer, because the honest answer is neither, and both, depending on the field.

  Split Authority by Field, Not by Store

  You might try to pick an authoritative source based on store. Files win, or the database wins. But the useful granularity is usually the field.

  In my pipeline it breaks down like this:

  • Post content and tags: the Markdown file wins. The frontmatter is authoritative. If the database has a different tag list, the database is wrong, and it gets rebuilt from the file.
  • Scheduling: the database wins. What time a post goes out, what slot it holds, whether it’s been claimed. The file does not get a vote.

  Those are different answers for the same post, and that’s fine, because each one is written down and each one has a reason.

  The content lives in the file because content is the thing I edit by hand, in an editor, with Git history behind it. I want git log to be the real record of what changed. Putting that in a database would mean my writing history lives somewhere that is harder to access.

  The schedule lives in the database because scheduling is a coordination problem. It needs uniqueness constraints, it needs to answer “what’s in the 10am slot on Tuesday,” and it needs to do that without me parsing 241 files. A database is genuinely better at that. It just isn’t better at holding prose.

  A Database Can Be Useful Without Being Authoritative

  I think there’s a reflex where adding a database feels like promoting the data into it. You put the posts in Postgres and now Postgres is where posts are.

  It doesn’t have to work that way. A database can be a query layer over data that lives somewhere else, and that’s a completely respectable job. Indexes, joins, counts, “show me every post tagged local-first published before June.” All of that is worth having, and none of it requires the database to be the authority.

  The test I use: if I deleted the database right now, what would I lose forever?

  For me, it would be the scheduling state because that’s what I put in the database. The important thing is I wouldn’t lose a single word that I’ve written. Every post would still be in a directory. This choice is deliberate.

  It’s easy for the database to become a Cache and not an authoritative source.

  What Should Happen When They Disagree

  If you have documented your authoritative source, then the disagreements stops becoming a crisis, and it just is a routine. Resolution event

  You should be able to rebuild it. There should be nothing to decide. The decision is documented and how you resolve conflicts. Just depends on. Which authoritative source owns Which s segment of your data?

  In my case, there’s actually a third authoritative source, and that’s the remote blog system that hands back an ID every time I schedule a new post.

  So, this is totally fine if you pick the authority at the field level and Document that decision to prevent trip-ups in the future.

  Your files and your database shouldn’t be arguing, all it requires is a bit of planning.

  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 3, 2026 Databases Software-development Architecture Local-first Data-modeling

  Enjoyed this?

• pgvector vs Pinecone: You Probably Don't Need a Separate Vector Database

  Every time someone starts building a RAG pipeline, the same question will come up: do I need a “real” vector database like Pinecone, or can I just use pgvector with the Postgres I already have?

  I can imagine teams agonizing over this decision for weeks. So maybe this will save you some time?

  The Case for Staying Put

  If you already have a PostgreSQL instance in your stack, adding pgvector is almost always the right first move.

  You manage one stateful service instead of two. Your existing backup strategy, monitoring, and security all stay the same. Your vector embeddings live next to your metadata, so you get ACID compliance and standard SQL joins. No syncing between two data stores. No eventual consistency headaches.

  Performance? From what I found, for datasets under a few million vectors, pgvector with HNSW indexes is fast. Really fast. It satisfies the latency requirements of most applications without breaking a sweat.

  And you’re not paying for another SaaS subscription…

  When Pinecone Actually Makes Sense

  Pinecone is a purpose-built vector database designed for high-dimensional data at massive scale. It’s serverless and fully managed.

  If you’re dealing with hundreds of millions or billions of vectors, a specialized engine handles memory and disk I/O for similarity searches more efficiently than Postgres can. Pinecone also gives you native namespace support, metadata filtering optimized for vector search, and live index updates that are faster than re-indexing a large Postgres table.

  Those are real advantages. At a certain scale.

  The Decision Is Simpler Than You Think

  Stay with Postgres + pgvector if:

  • You want to minimize infra sprawl and moving parts
  • Your vector dataset is under 5 to 10 million records
  • You rely on relational joins between vectors and other business data
  • You have existing observability and DBA expertise for Postgres

  Consider Pinecone if:

  • Your Postgres instance needs massive, expensive vertical scaling just to keep the vector index in memory
  • You don’t want to tune HNSW parameters, mmap settings, or vacuuming schedules for large vector tables
  • You need sub-millisecond similarity search at a scale where Postgres starts to struggle

  That is what I would use to make that decision.

  Most teams are probably nowhere near the scale where Pinecone becomes necessary. They have a few hundred thousand vectors, maybe a million or two. Postgres handles that without flinching. Adding a separate managed vector database at that point is just adding operational complexity for no measurable benefit.

  The trap is thinking you need to “plan ahead” for scale you don’t have yet. You can always migrate later if you actually hit the ceiling. Moving from pgvector to Pinecone is a well-documented path. But moving from two services back to one because you overengineered your stack? That’s a conversation nobody wants to have.

  Start with what you have. Add complexity when the numbers force you to, not when a vendor’s marketing page makes you nervous.

  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 25, 2026 DevOps AI Programming Databases

  Enjoyed this?

• Everything Is Eventually a Database Problem

  I think there’s a saying that goes something like “code is ephemeral, but data is forever.” That’s never been more true than right now. Code is easier than ever to create, anyone can spin up a working app with an AI agent and minimal experience. But your data structure? That’s the thing that sticks around and haunts you.

  Data modeling is one of those topics that doesn’t get enough attention, especially given how critical it is. You need to understand how your data is stored, how to structure it, and what tradeoffs you’re making in how you access it. Get it right early, and your code stays elegant and straightforward. Get it wrong, and your codebase becomes a forever series of workarounds…

  Microservices Won’t Save You

  For teams moving from a monolith to microservices, if the data stays tightly coupled, you don’t really have microservices; you have a distributed monolith with extra network hops.

  Yes, data can be coupled just like code can be coupled. If all your different services are still hitting the same database with the same schema, you have a problem. You need separate data structures for your services, not a monolithic architecture hiding behind a microservices facade.

  The Caching Trap

  So what happens when you have a lot of data and your queries get slow? You’ve done all the easy stuff; optimized queries, added indexes, followed best practices. But things are still slow.

  Every senior engineer’s first instinct is the same: “Let’s add Redis in front of it.” Or “more read replicas.” And sure, that works, but you have just added complexity and now you have to deal with cache invalidation.

  What happens when you have stale data? How do you recache current data, and when does that happen?

  Are you caching on the browser side too? Understanding where data can be cached and how to invalidate it is another genuinely difficult problem to solve. You’re just trading one set of problems for a different set of problems.

  You Can’t Predict Every Future Question

  If you’re selling things on the internet, chances are, you will care about event sourcing at some point. A lot of interesting business problems don’t care about the current state of a user, they care about the intent and history. So how you store intent and history is probably different from your ACID-compliant Postgres table that you’ve worked hard to normalize.

  You can get your data structure perfect for displaying products and processing sales, then run into a completely new set of requirements that changes everything about how your data needs to be structured.

  It’s genuinely hard to foresee all the potential questions you’ll need to answer in the future.

  Why This Matters Now

  Everything you do on a computer stores data somewhere, it’s just a matter of persistence.

  Which is why; everything software-related is eventually a database problem.

  Data modeling isn’t glamorous, but getting it right is the difference between a system that scales gracefully and one that fights you every step of the way.

  Mar 16, 2026 Programming Databases Architecture

  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