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.
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=requireto 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
- Some clients may be unable to automatically download our server cert. If this is the case you can do so manually here.
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 spec:
/, so if the connection is failing, you may try replace the
username/database_name with a
. for example to yield
As with all Postgres connections, connections to bit.io will timeout after a period of inactivity (60 seconds in our case). If your client does not handle these automatically it may be necessary to enable some retry or keepalive behavior in order to maintain/reestablish connection.
Old passwords will still work, but, for security, bit.io does not save plaintext passwords and keys. Each page refresh generates a new password.
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
psqlby following along with the psql connection instructions, run the following
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
psqlclient 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
timeshell utility instead is not recommended since this method incurs the overhead of additional client-side
psqloperations and sever-side messages.
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 typically only adds a very small amount of additional latency (1-2ms) when querying on top of existing network 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
psqlby 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.
psqltiming mode enabled for your
psqlsession, 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
Complex or long running queries may be able to be optimized once they are better understood.
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 6 days ago