Closed simonw closed 1 year ago
Options:
raw.githubusercontent.com
VACUUM
?I downloaded https://s3.amazonaws.com/til.simonwillison.net/tils.db - it was 34MB before a vacuum and 32MB after.
https://til.simonwillison.net/-/databases says this:
[
{
"name": "tils",
"route": "tils",
"path": "tils.db",
"size": 36118528,
"is_mutable": false,
"is_memory": false,
"hash": "70479f24850dd7d41026f856c43244cd5d189da8729d13afcfc7cb8347e2513a"
},
{
"name": "fixtures",
"route": "fixtures",
"path": "fixtures.db",
"size": 262144,
"is_mutable": false,
"is_memory": false,
"hash": "40882a89c8b0eb57f500131bdfccb64a6f2e53c670659f6d5d282f916e0cd909"
}
]
Not sure why fixtures.db
is there, I should drop that (probably so I could test something in it on Vercel).
This will buy me a bit of time. I think the best longer-term solution is going to be moving the screenshots to that https://s3.amazonaws.com/til.simonwillison.net/
S3 bucket.
No, even that wasn't enough of a reduction in size: https://github.com/simonw/til/actions/runs/4823200296/jobs/8591387148
Error: The Serverless Function "index.py" is 52.41mb which exceeds the maximum size limit of 50mb.
Dropping that shots
column with the binary screenshots in it will fix the problem:
/tmp % sqlite-utils transform tils.db til --drop shot
/tmp % ls -lah tils.db
-rw-r--r-- 1 simon wheel 36M Apr 27 11:38 tils.db
/tmp % sqlite-utils vacuum tils.db
/tmp % ls -lah tils.db
-rw-r--r-- 1 simon wheel 5.0M Apr 27 11:39 tils.db
I can keep the shot_hash
column and use it to detect if the S3 image needs to be re-generated.
To get a deploy out I'm going to reset the images for every piece of content older than a year.
sqlite-utils tils.db "update til set shot = x'89504E470D0A1A0A0000000D494844520000010000000100010300000066BC3A2500000003504C5445B5D0D0630416EA0000001F494441546881EDC1010D000000C2A0F74F6D0E37A00000000000000000BE0D210000019A60E1D50000000049454E44AE426082'
where created <= '2023'"
sqlite-utils vacuum tils.db
Results in a 9.9MB file.
That hex value is the first image on https://www.mjt.me.uk/posts/smallest-png/ converted to hex. I did that like so:
sqlite-utils insert-files tils.db images tiny.png
Then:
select hex(content) from images
https://github.com/simonw/til/actions/runs/4821511162/jobs/8587478486