Hey @mrchrisadams! Fly Postgres is open source, and you can find the scale-to-zero implementation here.
It uses the same method described in the Elixir blog post that you linked. When scale-to-zero is enabled (via an environment variable), the supervisor process (start) periodically checks whether there are any open Postgres client connections. If not, then it stops Postgres and exits. When the Machine’s main process (start) exits, the Machine shuts down.
On the flyctl side, when the Postgres Machine is created, it’s configured not to auto-restart unless it fails. Therefore, when the Machine shuts itself down, it stays down.
(It’s also worth noting that since scale-to-zero for PG was introduced, we added support for using our edge proxy to auto-stop machines, so depending on the use-case you may not need to program this logic into the app itself anymore.)
Hope this helps!