# Fly Postgres Connections to Replicas Slow

**URL:** <https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104>\
**Category:** Questions / Help\
**Tags:** postgres, django, login-only\
**Created:** [December 2, 2023, 11:48pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104 "2023-12-02T23:48:33Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 2, 2023, 11:48pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/1 "2023-12-02T23:48:33Z")

</div>

Kinda the same issue as a thread I made a while back, but this time I think the cause is different.

> [@Fly Postgres Connections Extremely Slow from Django](https://community.fly.io/t/fly-postgres-connections-extremely-slow-from-django/14325):
>
> Hi, kinda a bit confused right now. Am using Fly Postgres along with a Django app and it’s been working perfectly for about a month. A few hours ago it started being really slow. The last deploy was several hours before that started and changed nothing related to db stuff, and rolling back that deploy also didn’t fix it. If I look in Sentry at my performance things I see stuff like this. Sometimes the connect will only be a few seconds, or sometimes it’s well over a minute. Assuming I…

Around November 19th, I added a new Postgres replica and app server in `ams`. Before this I was only in `iad`. When looking at the connect span in Sentry there is an immediate jump in connection times at this point.

 ![image](https://global.discourse-cdn.com/fly1/original/3X/1/4/14a1e4f89b2149aea80e3ee470f926b859931c52.png)  
When looking into it further, I found that the connection span took a very long time on instances in `ams`.

 ![image](https://global.discourse-cdn.com/fly1/original/3X/4/3/434e8a2d2d75160119c1753bc4c33a1d6af7f553.png)  
 ![image](https://global.discourse-cdn.com/fly1/original/3X/4/2/42c5186bd43308cb180641469ef9dc68e1b973b5.png)

This is just one sample, but I’ve looked at dozens of them and seen similar results with connect taking at least several hundred milliseconds. Also note that the port used is `5433`, which is expected. The stuff for determining the port to use is at [splashcat/splashcat/settings.py at 67763575255fa41fd6f53a56205f4b6ceec343ad · splashcat-ink/splashcat · GitHub](https://github.com/splashcat-ink/splashcat/blob/67763575255fa41fd6f53a56205f4b6ceec343ad/splashcat/settings.py#L154)

 ![image](https://global.discourse-cdn.com/fly1/original/3X/8/7/87e421f8019b28af450cd6204e7b0d296ddd0096.png)  
 ![image](https://global.discourse-cdn.com/fly1/original/3X/8/d/8d2d686b51d3b433d5621a56e42b5af7e30fed57.png)

A similar request that happened in `iad` used the port 5432 as expected and had a much more reasonable connect time of about 50ms.

I’m not sure what to do really. I’ve tried destroying and recreating both the app server and Postgres replica in `ams` thinking that maybe one of the hosts was having issues, but that didn’t help at all. The machine IDs are `e2860e0a563d28` for the app server (might change with deployments because of bluegreen? idk) and `e82d921f743508 ` for the Postgres replica.

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 12:42am UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/2 "2023-12-03T00:42:49Z")

</div>

Well I tried updating Postgres with `fly image update` in hopes that maybe it’d help or something. That was a mistake. I now don’t have a primary and I have no idea what I’m doing.

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 1:10am UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/3 "2023-12-03T01:10:25Z")

</div>

Well I think I fixed Postgres exploding (but don’t think I fixed the slow connecting). Maybe. That was a fun experience.

---

<div class="post-metadata">

**Author:** ![smorimoto](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/smorimoto/32/16097_2.png) [@smorimoto](https://community.fly.io/u/smorimoto)\
**Post date:** [December 3, 2023, 7:58am UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/4 "2023-12-03T07:58:28Z")

</div>

Is the connection from the application server to the database in the private network?

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 8:06am UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/5 "2023-12-03T08:06:34Z")

</div>

Yeah, is using a flycast address which was what flyctl gave me when I made the cluster.

---

<div class="post-metadata">

**Author:** ![mayailurus](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mayailurus/32/17593_2.png) [@mayailurus](https://community.fly.io/u/mayailurus)\
**Post date:** [December 3, 2023, 3:14pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/6 "2023-12-03T15:14:34Z")

</div>

> [@catgirl](#):
>
> Well I think I fixed Postgres exploding (but don’t think I fixed the slow connecting). Maybe. That was a fun experience.

Ouch… It looks like there still is at least an 800ms difference for European users…

```plaintext
$ fly console --region ams
# time curl -i 'https://splashcat.fly.dev/battles/7011/' | fgrep -i fly-
fly-region: ams
fly-request-id: 01HGR0J32E5Q53KW2YZS4SR22C-ams

real 0m1.292s

# time curl -i -H 'fly-prefer-region: iad' 'https://splashcat.fly.dev/battles/7013/' | fgrep -i fly-
fly-region: iad
fly-request-id: 01HGR0NQCT4H01PV8YZBBVDV59-ams

real 0m0.445s

```

Compared to…

```plaintext
$ fly console --region iad
# time curl -i 'https://splashcat.fly.dev/battles/7017/' | fgrep -i fly-
fly-region: iad
fly-request-id: 01HGR0VDY5AQE0P4G2YN3X129S-iad

real 0m0.301s

```

And (somewhat less reliably)…

```plaintext
$ fly curl 'https://splashcat.fly.dev/battles/7019/'
REGION STATUS DNS CONNECT TLS TTFB TOTAL    
ams 200 3.9ms 4.2ms 430.6ms 1812.2ms 1813.6ms
dfw 200 7.6ms 7.8ms 180.1ms 793.2ms 921.5ms 
ewr 200 4.3ms 4.6ms 92.9ms 411.5ms 431.2ms 
fra 200 5.4ms 5.5ms 405.2ms 1860.9ms 1884.6ms
iad 200 2.8ms 3 ms 63 ms 557.1ms 559.2ms 
lax 200 6.7ms 6.9ms 271.4ms 919.2ms 1159.7ms
lhr 200 80.3ms 80.8ms 546.9ms 1855 ms 1874.6ms
mia 200 2.4ms 2.5ms 197.5ms 891.6ms 1076.1ms
nrt 200 4.6ms 4.8ms 724.5ms 1075.6ms 1689.6ms
ord 200 5.3ms 5.4ms 222.9ms 773.5ms 869.1ms 
sjc 200 3.4ms 3.6ms 328.3ms 921.3ms 1162.9ms

```

If you haven’t tried it already, it might make sense to SSH in to the replica and see how a direct connection to its unix-domain socket might work out:

```plaintext
$ fly ssh console -a splashcat-db --region ams
# echo "$FLY_REGION" # double-check
# su postgres
# time psql -p 5433 -c "select state, count(*) from pg_stat_activity group by state"

```

If even that takes hundreds of milliseconds, then it may be that your [tweaks from July](https://community.fly.io/t/fly-postgres-connections-extremely-slow-from-django/14325/7) didn’t end up carrying over to the new node…

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 4:31pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/7 "2023-12-03T16:31:25Z")

</div>

> [@mayailurus](#):
>
> If you haven’t tried it already, it might make sense to SSH in to the replica and see how a direct connection to its unix-domain socket might work out:
> 
> ```auto
> $ fly ssh console -a splashcat-db --region ams
> # echo "$FLY_REGION" # double-check
> # su postgres
> # time psql -p 5433 -c "select state, count(*) from pg_stat_activity group by state"
> 
> ```
> 
> If even that takes hundreds of milliseconds, then it may be that your [tweaks from July](https://community.fly.io/t/fly-postgres-connections-extremely-slow-from-django/14325/7) didn’t end up carrying over to the new node…

Didn’t know this was a thing (my Postgres knowledge is very limited). When running it I get a much more reasonable time, so I think it’d have to be something with either my app or the network between the 2 hosts in `ams`.

```auto
% fly ssh console -a splashcat-db --region ams
Connecting to fdaa:2:4f68:a7b:13b:a04e:cabe:2... complete
root@e82d921f743508:/# su postgres
postgres@e82d921f743508:/$ time psql -p 5433 -c "select state, count(*) from pg_stat_activity group by state"
 state | count 
--------+-------
        | 4
 active | 1
 idle | 6
(3 rows)

real	0m0.046s
user	0m0.025s
sys	0m0.009s
postgres@e82d921f743508:/$ echo $FLY_REGION
ams

```

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 4:39pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/8 "2023-12-03T16:39:17Z")

</div>

Tried running a similar command from an app server in `ams` and it has an 800ms delay.

```auto
root@e2860e0a563d28:/code# export PGPASSWORD=(meow)
root@e2860e0a563d28:/code# time psql -h "splashcat-db.flycast" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
 state | count 
--------+-------
        | 4
 active | 1
 idle | 6
(3 rows)

real	0m0.800s
user	0m0.035s
sys	0m0.009s
root@e2860e0a563d28:/code# echo $FLY_REGION
ams

```

Also out of curiosity, I ran this same command but through the .internal host instead so flycast doesn’t get involved and that seems to help.

```auto
root@e2860e0a563d28:/code# time psql -h "splashcat-db.internal" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
 state | count 
--------+-------
        | 4
 active | 1
 idle | 8
(3 rows)

real	0m0.264s
user	0m0.027s
sys	0m0.011s

```

I don’t know if I can eliminate flycast though as tmk it was created for Postgres?

Also ran the same few commands in `iad`. I tried both ports 5432 and 5433 for this but only included 5433 below as the results were practically the same.

```auto
root@6e8245ddc49987:/code# time psql -h "splashcat-db.flycast" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
 state | count 
--------+-------
        | 4
 active | 1
 idle | 4
(3 rows)

real	0m0.065s
user	0m0.036s
sys	0m0.005s
root@6e8245ddc49987:/code# time psql -h "splashcat-db.internal" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
 state | count 
--------+-------
        | 4
 active | 1
 idle | 4
(3 rows)

real	0m0.040s
user	0m0.022s
sys	0m0.013s
root@6e8245ddc49987:/code# echo $FLY_REGION
iad

```

---

<div class="post-metadata">

**Author:** ![mayailurus](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mayailurus/32/17593_2.png) [@mayailurus](https://community.fly.io/u/mayailurus)\
**Post date:** [December 3, 2023, 6:04pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/9 "2023-12-03T18:04:15Z")

</div>

> [@catgirl](#):
>
> I ran this same command but through the .internal host instead so flycast doesn’t get involved and that seems to help.

These were really good things to try, 🐈‍⬛… I’m wondering what they could actually be resolving to now, though…

How do the following compare, when run on an app server in `ams`?

```plaintext
# dig +short AAAA splashcat-db.internal # should have multiple results
# dig +short AAAA top1.nearest.of.splashcat-db.internal
# dig +short AAAA iad.splashcat-db.internal
# dig +short AAAA ams.splashcat-db.internal

```

(On Debian, `dig` is in the `dnsutils` package.)

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 6:10pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/10 "2023-12-03T18:10:44Z")

</div>

```auto
root@e2860e0a563d28:/code# dig +short AAAA splashcat-db.internal 
fdaa:2:4f68:a7b:13b:a04e:cabe:2
fdaa:2:4f68:a7b:1db:726c:5eae:2
fdaa:2:4f68:a7b:1dc:6a63:49ed:2
fdaa:2:4f68:a7b:22c:581d:9e34:2
root@e2860e0a563d28:/code# dig +short AAAA top1.nearest.of.splashcat-db.internal
fdaa:2:4f68:a7b:13b:a04e:cabe:2
root@e2860e0a563d28:/code# dig +short AAAA iad.splashcat-db.internal
fdaa:2:4f68:a7b:1db:726c:5eae:2
fdaa:2:4f68:a7b:1dc:6a63:49ed:2
fdaa:2:4f68:a7b:22c:581d:9e34:2
root@e2860e0a563d28:/code# dig +short AAAA ams.splashcat-db.internal
fdaa:2:4f68:a7b:13b:a04e:cabe:2

```

The IP `fdaa:2:4f68:a7b:1db:726c:5eae:2` in `iad` is running barman.

---

<div class="post-metadata">

**Author:** ![mayailurus](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mayailurus/32/17593_2.png) [@mayailurus](https://community.fly.io/u/mayailurus)\
**Post date:** [December 3, 2023, 7:59pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/11 "2023-12-03T19:59:42Z")

</div>

This part is correct, at least. (It noticed that Amsterdam is closest to Amsterdam, for example.)

Flycast really should be helping you, though, instead of adding 600ms…

How about the following, from an `ams` web-app machine again?

```plaintext
psql -h "splashcat-db.flycast" -p 5432 -c "select inet_server_addr()" -U postgres

```

(I get an `fdaa:*` that’s on the `.internal` list, in the analogous scenario over here.)

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 8:07pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/12 "2023-12-03T20:07:17Z")

</div>

```auto
root@e2860e0a563d28:/code# echo $FLY_REGION
ams
root@e2860e0a563d28:/code# psql -h "splashcat-db.flycast" -p 5432 -c "select inet_server_addr()" -U postgres
Password for user postgres: 
        inet_server_addr         
---------------------------------
 fdaa:2:4f68:a7b:1dc:6a63:49ed:2
(1 row)

```

The ip here does match with the primary’s ip, which is expected. I decided to also run it with port 5433 out of curiosity and got an IPv4 address for some reason??

```auto
root@e2860e0a563d28:/code# psql -h "splashcat-db.flycast" -p 5433 -c "select inet_server_addr()" -U postgres
Password for user postgres: 
 inet_server_addr 
------------------
 172.19.160.66
(1 row)

```

---

<div class="post-metadata">

**Author:** ![mayailurus](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mayailurus/32/17593_2.png) [@mayailurus](https://community.fly.io/u/mayailurus)\
**Post date:** [December 3, 2023, 9:11pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/13 "2023-12-03T21:11:37Z")

</div>

Oops, you are right, I really did want that 5433 there… I don’t yet know why it gives an IPv4 address in this situation, but I do see that same effect.

Overall, the idea is to try to confirm that it’s connecting to the Amsterdam replica, as opposed to getting sent all the way across the Atlantic. So, an IPv4 address might turn out to be just as useful.

If you SSH in to the various Postgres machines and `ip -4 addr show eth0` inside, then perhaps you can distinguish the two continents that way?

(On Debian, `ip` is in the `iproute2` package, and the man page for this sub-command is `man ip-address`.)

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 3, 2023, 9:46pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/14 "2023-12-03T21:46:42Z")

</div>

Seems like it’s being sent to the correct machine.

```auto
root@e82d921f743508:/# ip -4 addr show eth0 
3: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1420 qdisc pfifo_fast state UP group default qlen 1000
    inet 172.19.160.66/29 brd 172.19.160.71 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.19.160.67/29 brd 172.19.160.71 scope global secondary eth0
       valid_lft forever preferred_lft forever
root@e82d921f743508:/# echo $FLY_REGION
ams

```

I did run the same command on every machine in the cluster just to make sure that the IPv4 address wasn’t like, something that’s always the same or something. Was worried that maybe it was something weird inside the container because of [Fly.io](http://Fly.io) stuff usually being IPv6.

I feel like at this point it has to be something with [Fly.io](http://Fly.io)’s networking in `ams`, especially with flycast. ~~But even using the `.internal` address above in `ams` still had a higher latency compared to using `.internal` in `iad`.~~ Nevermind, I just reran this and the response time is the same as in `iad`. So it’s just something with flycast in `ams`.

```auto
root@e2860e0a563d28:/code# time psql -h "splashcat-db.internal" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
 state | count 
--------+-------
        | 4
 active | 1
 idle | 8
(3 rows)

real	0m0.048s
user	0m0.031s
sys	0m0.005s
root@e2860e0a563d28:/code# echo $FLY_REGION
ams

```

> [@catgirl](#):
>
> Also out of curiosity, I ran this same command but through the .internal host instead so flycast doesn’t get involved and that seems to help.
> 
> > **Output**
> >
> > ```auto
> > root@e2860e0a563d28:/code# time psql -h "splashcat-db.internal" -p 5433 -c "select state, count(*) from pg_stat_activity group by state" -U postgres
> > state | count 
> > --------+-------
> > | 4
> > active | 1
> > idle | 8
> > (3 rows)
> > 
> > real	0m0.264s
> > user	0m0.027s
> > sys	0m0.011s
> > 
> > ```

I’m guessing the try in the quoted post being slow was either just random luck or it routed it to `iad` for some reason. Rerunning it several times in a row with `ams.splashcat-db.internal` is pretty consistently around 0.05s.

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 5, 2023, 7:10am UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/15 "2023-12-05T07:10:05Z")

</div>

Can anyone from [Fly.io](http://Fly.io) provide some insight on this? I’m pretty sure this is an issue with flycast and is seemingly outside my control.

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 9, 2023, 4:44pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/16 "2023-12-09T16:44:14Z")

</div>

Once again bumping this hoping to get some insight from someone at Fly.

---

<div class="post-metadata">

**Author:** ![mayailurus](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/mayailurus/32/17593_2.png) [@mayailurus](https://community.fly.io/u/mayailurus)\
**Post date:** [December 9, 2023, 4:47pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/17 "2023-12-09T16:47:52Z")

</div>

This may have escaped notice way down here…

🍃 🍃 🍃

I think a [top-level re-post](https://community.fly.io/new-topic?title=Tips%20for%20debugging%20800ms%20intra-region%20Flycast%20latency%20(Amsterdam)&tags=distributed,help-me-help-you&body=Does%20anyone%20have%20any%20good%20tips%20for%20%5Bdiplomatic%20phrasing%20here%5D) would be fair, emphasizing the Flycast aspect.

(Odds are you’re not the only one affected.)

---

<div class="post-metadata">

**Author:** ![catgirl](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/catgirl/32/42134_2.png) [@catgirl](https://community.fly.io/u/catgirl)\
**Post date:** [December 14, 2023, 1:27pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/18 "2023-12-14T13:27:12Z")

</div>

Made a thread 🙂 [800ms intra-region Flycast latency (Amsterdam)](https://community.fly.io/t/800ms-intra-region-flycast-latency-amsterdam/17246)

Thanks for all the help with this!!

> **dumb rambling that i wrote for some reason**
>
> Sorry for not really saying thanks sooner \>.\< I feel like I struggle a lot with knowing when to say it exactly and like, I worry a lot that it’d be weird if I said thanks in like every single post. yeah i don’t know why i wrote this but i did \>.\< but like really thanks so much for the help with this!!

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/fly1/original/3X/6/e/6e8955d00cb218e186ebbe3a6ab06fefdce15290.svg) [@system](https://community.fly.io/u/system)\
**Post date:** [December 21, 2023, 1:27pm UTC](https://community.fly.io/t/fly-postgres-connections-to-replicas-slow/17104/19 "2023-12-21T13:27:41Z")

</div>

Closed on December 21, 2023, 1:27pm UTC

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.