Best Practices

PostgreSQL is an open source, object-relational database built for extensibility, data integrity, and speed. Its concurrency support makes it fully ACID-compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

Use VPCs

We recommend you make full use of your DigitalOcean VPC networks to improve security, facilitate connection management, and mitigate trusted source limits. To do this, add the CIDR address of a database’s VPC network to its trusted sources. This functionally adds all other resources you have in the same VPC network to the database’s trusted sources using only one address towards the limit, as long as they connect via the private connection string, instead of the public one. The CIDR address also only counts as one trusted source IP against the 100-IP limit.

First, go to the control panel and click on your database. In its Overview tab, find the VPC Network section, which details the VPC’s name (such as default-nyc3) and its address (such as 10.108.0.0/20). Copy the VPC’s address with its netmask to your clipboard. For example, copy 10.108.0.0/20.

Then, click the Settings tab. Find the Trusted Sources section and click Edit to the right of it. Paste the VPC’s address without the netmask and press Enter. Click Save to confirm your change.

Warning

You currently cannot add IPv6 rules to a database cluster’s trusted sources.

Now, all other resources in that same VPC network can connect to the database by using the private connection string, instead of the public one. You can find the private connection string in the database’s Overview tab. Under Connection Details select VPC Network, then click on the dropdown menu reading Connection Parameters and select Connection String.

For improved security, we recommend you use this private connection string whenever possible and minimize the number of public connections to the database.

To establish secure, centralized connections between resources in different VPCs or platforms, you can use a proxy server, for example PgBouncer or HAProxy.

Terminate Ongoing Queries

Having too many concurrent queries can cause performance issues in your cluster. In order to cancel all ongoing PostgreSQL queries programmatically, connect to your cluster, query for idle connections, and call the following psql function:

pg_terminate_backend(pid);

For more details, see the official PostgreSQL documentation.