Postgres dropping connections

[boiserunner]

We’re running a fairly simple series of UPDATEs, but after committing just 5 or 6, Fly Postgres will drop the connection.

  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1115, in _rollback_impl
    self.engine.dialect.do_rollback(self.connection)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 691, in do_rollback
    dbapi_connection.rollback()
psycopg2.OperationalError: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

This is a very light workload. Does anyone have any recommendations? Or, if this is an issue with Fly’s network, can anyone suggest a managed provider that allows secure connections from the Fly network? (We’d previously considered RDS, but there didn’t seem to be a way to peer securely.)

[greg]

Hi,

I would guess there is the question of how intensive those UPDATES are. If they involve changing a lot of rows, not using an index etc they could be putting load on the database. You would need to check on its metrics. You could try temporarily giving it more CPU/RAM, run the same load, and see if you get the same issue.

However you say it is a light workload . In which case you could maybe experiment with keep-alive settings. Keep the connection alive when idle e.g

conn = psycopg2.connect(
host=hostname,
dbname=database,
user=username,
password=password,
port=port_id,
keepalives=1,
keepalives_idle=30,
keepalives_interval=10,
keepalives_count=5)

(or whatever values are supported to change their defaults to ones that suit your usage better).

Else, yes, there is the option of using a managed database. The slight problem there is you can’t get a fixed outgoing IP from Fly to whitelist in a security group. That would point you to another service like https://neon.tech/ or https://planetscale.com/ (that’s MySQL though).