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.
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
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) );
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.
Here are some further resources for upping your Postgres game:
Kenneth Reitz (@kennethreitz)
Developer Relations Advocate