Some Postgres Best Practices

Over my many years of serving as a Developer Relations Advocate, I’ve met and spoken with thousands of developers all over the world about their database configurations, and many have shared with me their lessons learned.

To celebrate the launch of our new managed database product, I’d like to share a few key takeaways you might apply to your new managed PostgreSQL databases.

Use Connection Strings & Environment Variables

The Twelve Factors, a set of best practices for building web applications for the cloud, is very clear about one thing: never store your database credentials in your codebase! Your code should be considered both proprietary and of inherent value, while at the same time being considered inherently insecure. Your data is what’s truly valuable.

So, to provide trusted credentials to trusted code, we recommend exposing the connection string of your Postgres database as an environment variable, named DATABASE_URL. Every common web framework has support for this (e.g., with Django, see dj-database-url).

Rotate Credentials Regularly

It’s a good idea to rotate your database credentials periodically, across your organization. However, during special times, like when offboarding a member of the team, it’s important to rotate the credentials to the Managed Databases and Spaces they had access to.

All it takes is one stray .env file from a couple of years ago to get into the wrong hands, and then your entire infrastructure could be instantly compromised. I recommend having organization-wide quarterly “credential rotation” days, with hard deadlines. This (likely) tedious process will be forced to become streamlined as your team continues to rotate the credentials month after month, encouraging automation. It’s best for everyone.

Use BIGINT or UUID for Primary Keys

A big mistake in a lot of applications is using `INT` instead of `BIGINT` for primary keys (every Django application does this by default, for example).

When you use INT instead of BIGINT, eventually, one day, the value in your database exceeds the “storage capacity” of an INT, and a BIGINT must be migrated to. The migration time to convert a table from INT to BIGINT usually takes around 4 hours, once the problem has been located.

BIGINT is much more appropriate as a default PK, and is not that much more expensive to store or index.

I personally use UUIDs in all of my databases:

-- Enable pgcrypto for UUID support. CREATE EXTENSION pgcrypto; – Table: notes CREATE TABLE notes ( uuid UUID DEFAULT gen_random_uuid(), body text NOT NULL, byline text, CONSTRAINT notes_pk PRIMARY KEY (uuid) );

Learn more about uuid4 / pgcrypto in Postgres.

Use Connection Pooling

When connecting to a high-throughput Postgres database server, it’s considered best practice to configure your clients to use PgBouncer, a lightweight connection pooler for PostgreSQL, instead of connecting to the database server directly.

Connection pooling has many performance advantages, and will make the query performance characteristics of your database much more deterministic.

However, here at DigitalOcean, we take care of that for you! So, there’s no need to run your own instance of PgBouncer when using DigitalOcean Postgres, as it’s already provided and pre-tuned out of the box. Simplicity at scale.

Further Reading

Here are some further resources for upping your Postgres game:

• Postgres Guide
• Managed Databases Connection Pools and PostgreSQL Benchmarking

Happy hacking!

Kenneth Reitz (@kennethreitz)

Developer Relations Advocate