When you scale something to 0, indeed there are issues with cold starts. On the Neon free tier, you will get a cold start if your database is inactive for 5 minutes (you can configure this value on paid plans).

As an exercise, I decided to try for myself what my cold start time is + query round trip, on Neon's free tier. Note that I am in Austin connecting to us-east-2.

    $ time psql -c 'SELECT 1' 'postgresql://neondb_owner:<password>@<endpoint>.us-east-2.aws.neon.tech/neondb?sslmode=require'
    Null display is "(null)".
     ?column?
    ----------
            1
    (1 row)


    real    0m1.156s
    user    0m0.036s
    sys     0m0.020s

You can't make a trend out of 1 data point, but thought I would provide it nonetheless.