Intermittent Connection Drops with QuestDB using Python psycopg

Community
,
1

I’m working on a Proof of Concept with QuestDB, and we’re occasionally experiencing connection drops when querying the database via Python using psycopg. The error seems to originate from within the psycopg library, suggesting that the connection to QuestDB might be dropping unexpectedly. The QuestDB log is quite verbose, but I’m looking for specific keywords that could provide clues about these disconnection issues. Below is an anonymized example of the log entries when a disconnection occurred:

YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID' 
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID' 
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID' 
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I pg-server disconnected [ip=IP_ADDRESS, fd=FILE_DESCRIPTOR, 
src=idle]

This was preceded by the server opening a partition for a query:

YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.TableReader open partition 
/var/lib/questdb/db/table_name~ID/YYYY-MM-DD.ID

Any suggestions on how to diagnose or resolve this issue?

2

Searching for “disconnect” in the logs can help identify relevant entries. If your queries are particularly long-running, consider the possibility that this is related to the query.timeout.sec configuration property, which is set to 60 seconds by default. You might need to increase this setting to prevent premature disconnection.

To adjust the query.timeout.sec setting, locate it in the db_root_dir/conf/server.conf file. Also, think about whether your Python application might be pausing while processing the queried data, as this could trigger an idle connection timeout, leading to the disconnection.

It’s also wise to examine the pg.net.connection.timeout setting, defaulting to 300,000 milliseconds (5 minutes). Consider increasing this to 900,000 milliseconds (15 minutes) to accommodate longer-running queries.

Ensure your server recognizes the new values by checking the server logs or executing the SHOW PARAMETERS; SQL command. This command should list all current configuration settings, including those set via environment variables.

Adjusting these settings should be carefully balanced with your specific workload and operational environment to avoid unintended side effects, such as excessively prolonged query execution or resource strain.