Woohoo, look who's back on What Got Done!
---
RT @czue
Lately, I've been more motivated to work on @saaspegasus
than I have been in ages.

Here's what got done this week:

https://whatgotdone.com/czue/2022-05-27
https://twitter.com/czue/status/1530202225468907522

Here's what I got done this week:
* Published a blog post about my new NAS build
* Led TinyPilot's monthly dev meeting
* Ripped ~50 new BluRay discs for home streaming
https://whatgotdone.com/michael/2022-05-27

After several years with an off-the-shelf Synology NAS, this year I decided to build my own storage server from scratch.

Behold! My budget homelab TrueNAS server with 22 TB of usable storage and 32 GB of RAM. #truenas #homelab https://mtlynch.io/budget-nas/

Here's what I got done this week:
* Started the ball rolling to offer TinyPilot on Amazon
* Deployed a nicer shopping cart design
* Started rewriting a Python web service in Go
https://whatgotdone.com/michael/2022-05-20

Here's what I got done this week:
* Hired a marketing agency
* Published my April retrospective
* Published new TinyPilot landing page
https://whatgotdone.com/michael/2022-05-13

If I'm making $58k/month in revenue, how can I be losing $3k/month overall? Come with me to my April retrospective, where I explore what's holding TinyPilot back from profitability. https://mtlynch.io/retrospectives/2022/05/

The Morning Show is exactly how I imagine it would be if Aaron Sorkin wrote a series exploring life behind the scenes of a struggling TV show.

I'm so excited about this!

For the past year I've been building all of my apps using Fly and Litestream, so it's awesome to see them come together as a single company.
---
RT @flydotio
Breaking: Litestream is now part of http://Fly.io!

Do you like SQLite? Do you like streaming replication? No? Read @benbjohnson’s new post on the http://Fly.io blog anyway: https://fly.io/blog/all-in-on-sqlite-litestream/
https://twitter.com/flydotio/status/1523743433109692416

Creating the index is a one-line fix, and it doesn't have the drawback my original PR did of storing a redundant copy of the file size. https://github.com/mtlynch/picoshare/pull/230/files

Update! @wmertens and @dholth showed me a simpler way to achieve the same performance without storing the file size redundantly. Creating an index achieves the same thing.

Enjoyed this post about disagreeing constructively. I like Cory's distinction between "range of opinions I personally hold," and "range of opinions I find it reasonable for others to hold," and how they shouldn't be identical most of the time.
---
RT @czue
After a heated disagreement with a friend went way better than expected, I figured out what saved it, and packaged it up into a long-ass essay.

Maybe it will help everyone on here be just …
https://twitter.com/czue/status/1520045738893398017

Hope you enjoyed following along! Here's the full PR with the fix: https://github.com/mtlynch/picoshare/pull/221

The last tricky part was writing a SQL migration to populate the sizes of files that were uploaded and stored in the DB before this change. I've never written an update that derives from other data in the DB before, but it wasn't too hard.

I still didn't love the idea of storing the redundant file size, and I considered alternatively creating a virtual SQLite table.

But we'd still have to populate the virtual table still at app load time, which would take ~10s per GB of data
https://www.sqlite.org/vtab.html

And we have a winner! For the same 1.1 GB file, latency dropped from 9s to 9ms, a 100x speedup.

Next, I tried storing the file size along with the file metadata

This surprised me, and I still don't have a good explanation for it. It's 3,708 rows, so it doesn't seem like it should take SQLite *that* long to calculate the SUM of 3708 values.

I'm guessing the large blob in each row slows down the query even though we don't read it.

I tried running the SQL on its own, and it took 743 ms

Storing the chunk size worked, and it brought the latency down from 9s to 839ms, a 10x performance boost.

But 839ms to calculate the size of a single file was still pretty slow...

But based on the 9s latency, calculating sizes on the fly wasn't going to work.

My first thought was to store the chunk size alongside the blob in the table containing file data. That had the advantage of keeping size close to the data it described.