Search

Still on it. See the STRICT tables thread, I ended up doing that as well.

Welcome to having types.

Did this to my app too. You can't ALTER a table into STRICT so it's new table + copy. Took 20 minutes and found two bugs.

After the "is sqlite enough" thread I went and looked at my own readings db. Found 212 rows where temp_c was the text "nan". SQLite was happy to store that in a REAL column, because type affinity is more of a suggestion. CREATE TABLE readin…

Postgres people: timestamptz doesn't store a time zone, it stores UTC and converts on the way out. That confuses somebody on every team I've ever worked on.

Rowid is still insertion order for me, so that works. Did the conversion with a script that walks in rowid order, and I've kept the old column around in case.

You know the answer. Store UTC. In Python that's datetime.now(timezone.utc), or use integer epoch. Convert when you display. For the old rows, you can't tell the two 01:30s apart from the text alone, but the rows are ordered by rowid, and r…

Export has two sets of readings for 01:00-02:00 on the night the clocks went back. The script writes datetime.now() into the db as text. Pretty sure I know the answer, but what's the right fix for the data that's already there?

Switched the script to .backup, restore test came back with everything. Thanks.

cp on a live WAL database can give you a file with the WAL half-applied. Use sqlite3 readings.db ".backup /mnt/backup/readings.db" or the VACUUM INTO command. Both produce a consistent snapshot.

My backup script just does cp readings.db /mnt/backup/. The app is in WAL mode and writes every few seconds. Restoring from one of these backups gave me a database that was missing the last hour or so. Is cp the wrong tool here?

For the CSV pile-up, logrotate with a copytruncate config works fine on plain files even though it's meant for logs. Or just date the filename in export.py and delete anything older than 30 days.

For whoever picks this up (probably me in a month): - export.py reads readings.db and writes readings.csv, runs from systemd nightly - garage sensor reports every 15 min, porch every 5, the CSV has both - the --since flag works but isn't us…

The same holds in Postgres. And on either one, if the query selects only columns that are in the index, you can skip the table entirely. Bonus speedup that people miss.

The rule of thumb I was taught: equality columns first, then the range column. Anything after a range column in the index doesn't help that query.

Query on 1.4M readings went from 2.8s to 11ms. The index has to be (sensor, ts) not (ts, sensor) for WHERE sensor = ? AND ts > ?. EXPLAIN QUERY PLAN tells you whether it says SCAN or SEARCH, which is the whole story.

That was it. Cron started in my home directory, so ./readings.db was a brand new empty database. Absolute paths and a cd at the top of the script. Marking resolved.

Use absolute paths for the database and output file. Also redirect stderr to a log so you can see if sqlite is opening a different database.

Trying to dump a small sqlite database from cron. Running the script in a terminal works, but the nightly output is a zero-byte file. The crontab line is: 15 2 * * * /home/me/bin/export-readings.sh The script uses ./readings.db. Does cron s…

Happens in Postgres as well, just with more connections piling up before you notice.

Update about ten days in. It's been boring, which is good. Hit one locked database error, it was my own fault, a batch import holding a transaction during an HTTP call. marta_k called that one exactly.

counterpoint, had a corrupted sqlite db on a VPS whose provider had a flaky disk. postgres wouldn't have saved me either but you do feel alone. PRAGMA integrity_check in your backup job, please

Nobody's said it so I will: most of what you want from a database at this size is one file you can copy somewhere and open with a CLI.

If losing a day of writes would hurt, look at Litestream, it ships the WAL to object storage continuously. Haven't needed it but people I trust like it.

It's what I do. Test a restore or you don't have a backup.