Update and Delete Operations

Databases are not just for reading. You need to update existing rows and delete rows you no longer want. Both look trivial next to what you've already built — a find plus a memcpy — but the strategies behind them are where real engines differ most, so this lesson is as much about why as how.

Update Strategy

Updating a row in place is simple because our rows are fixed-size: the new data always fits exactly where the old data was. Notice what this bought us — in a real database with variable-length rows, updating "Bob" to "Bartholomew" may not fit in the row's slot, forcing the engine to relocate the row within its page, or move it to another page and leave a forwarding pointer behind. Postgres sidesteps in-place updates entirely: an UPDATE writes a whole new row version and marks the old one dead (this is MVCC — multi-version concurrency control — which also lets readers keep seeing the old version mid-transaction). Our version:

int table_update_by_id(Table *t, uint32_t id, const char *name, uint32_t age) {
    for (uint32_t i = 0; i < t->count; i++) {
        if (t->rows[i].id == id) {
            strncpy(t->rows[i].name, name, sizeof(t->rows[i].name) - 1);
            t->rows[i].name[sizeof(t->rows[i].name) - 1] = '\0';
            t->rows[i].age = age;
            return 0;
        }
    }
    return -1; /* not found */
}

One deliberate choice: the ID is not updatable. The ID is the row's identity — it's what indexes point at, what other tables would reference, what the application holds onto between requests. Letting identity change turns every stored reference into a potential dangler.

Delete Strategy

Deleting a row is trickier than it looks: it leaves a hole. Two ways to deal with the hole:

1. Remove and shift: copy every row after the hole one position down, decrement count. The table stays perfectly compact — no wasted memory, scans stay fast — but a delete costs O(n), and every row after the deleted one changes position (remember that consequence; it comes back to bite us in the indexing lesson).

2. Tombstone (soft delete): overwrite the row with a "deleted" marker and leave it there. O(1) delete, positions never change — but the table accumulates ghost rows, every scan pays to skip them, and the space is only reclaimed by a periodic compaction/vacuum pass that rewrites the table without tombstones.

Real engines overwhelmingly choose tombstones, and it's worth understanding why: on disk, "shift everything down" means rewriting the entire file tail on every delete — ruinous. Marking one page's row dead is a single page write. The cost is deferred maintenance: SQLite files grow a free-page list and need VACUUM to shrink; Postgres runs autovacuum continuously; log-structured stores (LevelDB, RocksDB, Cassandra) write tombstone records and reclaim space during compaction. The pattern — make deletion cheap now, clean up in bulk later — is one of the most reused tricks in storage systems.

We'll use remove-and-shift, because our table is in memory (where a memmove is cheap) and compactness keeps everything else simple:

int table_delete_by_id(Table *t, uint32_t id) {
    for (uint32_t i = 0; i < t->count; i++) {
        if (t->rows[i].id == id) {
            /* Shift rows after i one position down */
            for (uint32_t j = i; j < t->count - 1; j++) {
                t->rows[j] = t->rows[j + 1];
            }
            t->count--;
            return 0;
        }
    }
    return -1; /* not found */
}

Why IDs Are Never Reused

Delete row 2 and insert a new row: the new row gets ID 4 (or wherever next_id is), never the vacated 2. This is deliberate. Somewhere out there — in an application variable, a URL, another table, a log line — the old ID may still exist. If ID 2 suddenly names a different row, every one of those stale references silently points at the wrong data. That's not a crash; it's worse — it's wrong answers that look right. Monotonic IDs turn stale references into a detectable "not found" instead. (This is also why next_id is saved in the file header: reload the table and the counter must resume, not restart.)