Scale to zero Postgres for hobby projects

Fresh Produce
1

Starting with flyctl v0.0.520 you’ll be given the option to have your db scale to zero if you are creating a shared-cpu-1x single node cluster. The idea behind this is that you can have hobby projects scale to zero when you aren’t using them to save you some :money_with_wings: so you can keep building neat stuff.

The way it works for now is after one hour, if there are no open connections, it shuts down and waits until something tries to connect again. If there are open connections it tries again in another hour.

Caveats

  • You’ll have to configure your app to scale to zero as well (e.g this article) otherwise your pg will never scale down
  • This is only offered for new shared-cpu-1x flex databases using flycast
  • The one hour timeout is not yet configurable

We’ll be writing more about how to use this for your hobby projects in the coming weeks but we wanted to get it into folks hands to play with. Go forth and build!

19 Likes
2

Hey @DAlperin , I tried creating a Postgres DB, but the DB stuck with PENDING status. What should I check?

image
image804×94 14 KB

3

Weird. I now tried spinning up another db, it works. Yet the first still pending.

image
image874×96 17.6 KB

4

Oh that is fantastic! Thank you.

Am I right in thinking that when something attempts to connect when it is at 0 it’ll scale to 1 to accept the connection? Roughly what is the latency on this first connection?

1 Like
5

I haven’t tested it but anecdotally under 5 seconds and usually around 2. You might need to configure your pg client with retries though.

2 Likes
6

How to enable/disable this for an already existing DB?

7

I set this up for my Ruby on Rails project, and I’m not sure how to undo it. My app loads too quickly and thus the database doesn’t start fast enough, crashing the app How do we undo picking this option when starting a new app?

8

hi @andydamandy

Here are the steps to remove the scale to zero option:

9

Hi @andie,

I noticed that my Postgres app failed to start even though the app was attempting to connect multiple times over a 24 hour period. I’ve had to remove the FLY_SCALE_TO_ZERO variable to get it working again.

I see that the fly.toml file for the Postgres app contains FLY_SCALE_TO_ZERO, but does not contain

  auto_stop_machines = true
  auto_start_machines = true

Do we need to add that to the config file?

1 Like
10

+1 i had the same issue since roughly a week now

my fly-pg instance also does not start up automatically
after an automatic stop (this used to work before with the same setup)

1 Like
11

update:

I added

  auto_start_machines = true

to my config and it, did the trick.

12

I tried adding auto_start_machines = true but it didn’t work.

13

I’m having trouble with this setting too. When I deployed my app using a Dockerfile about 3 weeks ago, it was set as the default and everything was running smoothly. However, since last week, the app no longer starts up on its own with the FLY_SCALE_TO_ZERO setting. I need to run the machine restart command, otherwise, it doesn’t work. I tried adding auto_start_machines = true , but it didn’t fix the problem. Any suggestions?

1 Like
14

After some investigation, I noticed a setting "disable_machine_autostart": true in the machine running the Postgres App.

I didn’t set this setting, but it went away with the following update

fly machines update <machine_id> --autostart=true --app <app_name>

It seems the app is back to normal and auto-starting without any problem.

15

This is weird. I’m looking into this! Thanks for finding the fix.