Troubleshooting Common Connection Issues

Suggest Edits

bit.io works with a lot of different Postgres-compatible tools, but many of those tools have different connection requirements. Connection errors most likely mean that you need to tweak a setting or two, not that the client is incompatible with bit.io. First, check if we have documentation for the tool you're using (in the "Connect to bit.io" section)—we cover specific connection details there.

Otherwise, here are the top three issues with connecting to various clients and how to resolve them.

SSL Settings

Incorrectly configured SSL settings are one of the most common reasons for failed bit.io connections.

  • Ensure that SSL is enabled on your client
  • For some clients, adding ?sslmode=require to the connection string may be necessary to enable SSL. Connections using the JDBC driver (and tools that use it) may require the ssl property to be set to true.
  • Some clients may be unable to automatically download our server cert. If this is the case you can do so manually here.

Database Name Separator

By default, database names are on bit.io are formatted username/database_name. Some clients do not support the default separator '/' that bit.io uses. All of the following separators are valid according to the Postgres identifier spec: .,~,|,>, / and bit.io supports any of these as a separator. If the connection is failing, try replacing the / in username/database_name with a . for example to yield username.database_name.

Connection Timeouts

As with all Postgres connections, connections to bit.io will time out after a period of inactivity (60 seconds with no connections or five minutes with idle connections in our case). If your client does not handle these automatically it may be necessary to enable some retry or error catching behavior in order to maintain/reestablish connection. See https://docs.bit.io/docs/the-connection-lifecycle for more information.

Password Keeps Changing

Old passwords will still work, but, for security, bit.io does not save plaintext passwords and keys. Each page refresh generates a new password.

Connecting to bit.io Seems Slow

Infrequently used databases may be temporarily "spun down" to preserve compute resources. The first time connecting to a database after a period of inactivity may be slightly slower than usual as the database is being brought back online.

However, once your database is actively "online" and you have successfully connected at least once, opening successive connections should be fast and performant. Typically, the majority of connection latency can be attributed to network latency. bit.io's internal infrastructure typically only adds a very small amount of additional latency (1-2ms) when connecting on top of existing network latency.

📘

How to measure connection latency (advanced users)

You can quickly measure your connection latency to bit.io using psql. Once you've confirmed you can connect to your bit.io database via psql by following along with the psql connection instructions, run the following psql command:

psql postgresql://my-username:[email protected]/my-username/my-database \
-c "\! date +'%T.%3N'" -c "\connect" -c "\! date +'%T.%3N'"

This command will output two timestamps with millisecond precision. The difference in the two timestamps represents total connection latency given that the above command forces the psql client to reconnect.

Note: This method of measuring connection latency may me slightly inflated due to shell command initialization time in addition to terminal printing time. Wrapping psql in the time shell utility instead is not recommended since this method incurs the overhead of additional client-side psql operations and sever-side messages.

Running Queries Against bit.io Seems Slow

Once you have successfully connected to bit.io, running individual queries should be performant. Query execution time scales with the complexity of the query and the number of rows scanned by the query, so complex queries scanning many rows might take some time to complete. In most cases, the majority of any observable query latency can likely be attributed to network latency. bit.io's internal infrastructure adds negligible increases in latency.

📘

How to measure query latency (advanced users)

You can quickly measure your query latency to bit.io using psql. Once you've connected to your bit.io database via psql by following along with the psql connection instructions, enable psql's timing mode via the following:

psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1), server 14.5)
Type "help" for help.

foo/bar =# \timing
Timing is on.

With the psql timing mode enabled for your psql session, all client-side query round trip times will be displayed:

foo/bar =# select 1;
 ?column?
----------
        1
(1 row)

Time: 12.331 ms

More information on psql's timing mode can be found in the official psql documentation.

Complex or long running queries may be able to be optimized once they are better understood.
Postgres's built-in EXPLAIN ANALYZE functionality may highlight potential optimization points or query mistakes by breaking down the overall "query plan" and providing an execution time estimate. Postgres also provides an official performance documentation category that may be useful in these situations.

Updated 2 months ago