Postgres best practices I wish every app developer knew

At Bytebase we work with PostgreSQL every day, both inside our own product and across the databases our customers point us at. The same handful of mistakes shows up over and over, and almost none of them are exotic. They are small things an application developer controls and skips anyway.

So this is not a database administration checklist. These are the five things I wish every app developer turned on by default. Each takes minutes, and each one buys you something concrete: a connection you can actually debug, a query that fails fast instead of hanging, a deploy that doesn't lock prod. Do them and your DBA stops paging you at 2am. There's a bonus index trick at the end.

1. Set application_name for Better Debugging#

This is the cheapest win on the list, and the one people skip most. By default every connection from your fleet looks identical. When something melts down and you're staring at a wall of pg_stat_activity rows, all the same user from the same pooler IP, you have no idea which service is the culprit. application_name is a free label. Set it once per service and the noise turns into a name.

How to Implement#

PostgreSQL connection string:

postgresql://user:password@localhost:5432/mydb?application_name=user-service

Node.js with pg:

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  application_name: 'user-service',
});

Python with psycopg2:

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword",
    application_name="user-service"
)

Java with JDBC:

String url = "jdbc:postgresql://localhost:5432/mydb?ApplicationName=user-service";
Connection conn = DriverManager.getConnection(url, "myuser", "mypassword");

Go with lib/pq:

import (
    "database/sql"
    _ "github.com/lib/pq"
)

connStr := "host=localhost dbname=mydb user=myuser password=mypassword application_name=user-service"
db, err := sql.Open("postgres", connStr)

What You Get#

Now pg_stat_activity actually tells you something:

-- See all active connections from your service
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE application_name = 'user-service';

-- Monitor long-running queries from specific services
SELECT application_name, query, now() - query_start as duration
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '30 seconds';

2. Configure PostgreSQL Statement and Lock Timeouts#

Postgres will wait forever if you let it. With no statement timeout, one bad query sits there holding a connection until a human notices. Do that across a pool and you don't get a slow app, you get an outage: every slot taken, nothing left to serve real traffic. Four timeouts decide how patient Postgres is willing to be. Set all four.

The Four Critical Timeouts#

1. statement_timeout. The ceiling on any single statement. This is the one to set first; it kills runaway queries before they take the pool down with them.

2. lock_timeout. How long a statement waits for a lock before giving up, instead of blocking your whole request path behind someone else's transaction.

3. idle_in_transaction_timeout. Reaps a transaction that opened, grabbed locks, and then went quiet because the app forgot to commit.

4. transaction_timeout. The ceiling on an entire transaction, not just one statement (PostgreSQL 17+). Stops a long transaction from sitting on locks the whole time.

How to Configure#

In connection string:

postgresql://user:pass@localhost/db?options=-c%20statement_timeout=30s%20-c%20lock_timeout=10s%20-c%20transaction_timeout=60s

At transaction level:

BEGIN;
SET LOCAL statement_timeout = '30s';
SET LOCAL lock_timeout = '10s';
SET LOCAL transaction_timeout = '60s'; -- PostgreSQL 17+
-- Your transaction operations here
COMMIT;

Recommended Values by Use Case#

The logic is just patience matched to purpose. A user is staring at a spinner, so web requests should fail fast. Background jobs run unattended and can afford to wait a little longer for a lock. Analytics queries legitimately run for hours, so you disable the statement cap there and accept that those sessions are the exception, not the rule.

3. Run Online Schema Migrations Without Downtime#

This is where a one-line migration becomes an incident. A statement that runs in 20ms on your laptop can lock a 50-million-row table for ten minutes in production, and for those ten minutes every read and write behind it queues up. The thing that actually takes prod down isn't the migration. It's the pile of normal traffic stuck waiting on its lock.

The Problem with Standard Migrations#

Traditional migrations acquire exclusive locks that block all reads and writes to your tables:

-- DON'T: This blocks ALL SELECT, INSERT, UPDATE, DELETE on users table
ALTER TABLE users ADD COLUMN email_verified boolean DEFAULT false;

-- DON'T: This blocks ALL writes (INSERT, UPDATE, DELETE) on orders table for hours
CREATE INDEX ON orders (user_id, created_at);

On a small table you'll never notice. On a large one, the same statement blocks every query for minutes, the connection pool drains as traffic backs up behind it, and the stall cascades into every service that touches that table. One ALTER takes down half the stack.

Online Migration Strategies#

1. Use CREATE INDEX CONCURRENTLY

   -- Safe: Builds index without blocking reads/writes
   -- Note: Cannot be run inside a transaction
   CREATE INDEX CONCURRENTLY idx_orders_user_created
   ON orders (user_id, created_at);

   To learn more production tips, please refer Postgres CREATE INDEX CONCURRENTLY.

2. Add columns and constraints safely

   -- Step 1: Add constraint without validation (fast)
   ALTER TABLE users ADD CONSTRAINT email_verified_not_null
   CHECK (email_verified IS NOT NULL) NOT VALID;
   
   -- Step 2: Validate constraint (can be done later, non-blocking)
   ALTER TABLE users VALIDATE CONSTRAINT email_verified_not_null;
   
   -- Step 3: Convert to NOT NULL constraint
   ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

4. Split PostgreSQL Roles for Different Operations#

Most apps connect to Postgres as one user that can do everything, because that's what the quickstart told you to do. It works right up until a bug in your code runs a DROP TABLE it should never have been allowed to run. Least privilege isn't bureaucracy here. It's the difference between a bug that returns a permission error and a bug that drops prod by accident.

The Problem with Single "Super User" Approach#

Many applications use a single database user with broad permissions:

-- DON'T: Single user with excessive privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

You inherit four problems at once. The app carries DDL rights it never uses, so a single bug can reshape the schema. Every connection is the same identity, so in pg_stat_activity you can't tell a runaway report apart from your write path. You can't tune connection settings per workload. And your audit trail says "the app did it", which tells you nothing. One identity, every risk.

The Three-Role Strategy#

Three roles cover almost every app. Think three keys, not one master key:

1. Read-Only Role - For analytics, reporting, read replicas
2. Application Role - For normal CRUD operations
3. Migration Role - For schema changes and DDL operations

Implementation Example#

Create the roles:

-- 1. Read-only role for analytics/reporting
CREATE ROLE app_reader;
GRANT CONNECT ON DATABASE myapp TO app_reader;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
-- Automatically grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_reader;

-- 2. Application role for normal operations
CREATE ROLE app_writer;
GRANT CONNECT ON DATABASE myapp TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_writer;
-- Auto-grant on future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

-- 3. Migration role for schema changes
CREATE ROLE app_migrator;
GRANT CONNECT ON DATABASE myapp TO app_migrator;
GRANT ALL PRIVILEGES ON SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_migrator;

Create actual users:

-- Create users and assign roles
CREATE USER analytics_user PASSWORD 'secure_password';
GRANT app_reader TO analytics_user;

CREATE USER application_user PASSWORD 'secure_password';
GRANT app_writer TO application_user;

CREATE USER migration_user PASSWORD 'secure_password';
GRANT app_migrator TO migration_user;

5. Use UPSERT for Idempotent Writes#

Networks drop, clients retry, two requests race. In production this isn't an edge case, it's Tuesday. UPSERT (INSERT ... ON CONFLICT) lets you write code that doesn't care whether the row already exists. A retry can't create a duplicate, a race resolves itself, and the "create or update" branching you'd otherwise write in the app disappears. The database settles the conflict atomically, which is exactly where you want that decision made.

Ignore Duplicates#

-- Pattern 1: Ignore duplicates
INSERT INTO users (email, name, created_at)
VALUES ('john@example.com', 'John Doe', NOW())
ON CONFLICT (email) DO NOTHING;

Update on Conflict#

-- Pattern 2: Update on conflict
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John Smith', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

Conditional Update#

-- Pattern 3: Conditional update
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES ($1, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
  login_count = user_stats.login_count + 1,
  last_login = EXCLUDED.last_login
WHERE user_stats.last_login < EXCLUDED.last_login;

Bonus: Implement "Invisible" Indexes#

PostgreSQL doesn't ship invisible indexes the way MySQL 8 does. But you can fake it by flipping the indisvalid flag in the pg_index catalog, which takes an index out of the planner's consideration without dropping it. Handy when you suspect an index isn't earning its keep and want to confirm that before you actually remove it. Just know you're poking a system catalog directly, so keep it to staging or a maintenance window.

Making an Index "Invisible"#

-- Disable an existing index (make it invisible to the query planner)
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_orders_status'::regclass;

Making the Index "Visible" Again#

-- Re-enable the index (make it visible to the query planner)
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = 'idx_orders_status'::regclass;

Checking Index Status#

-- See which indexes are currently disabled
SELECT
  schemaname,
  tablename,
  indexname,
  indisvalid AS is_valid
FROM pg_indexes
JOIN pg_index ON pg_indexes.indexname = pg_class.relname
JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE NOT indisvalid;

Note: PostgreSQL itself uses the indisvalid flag internally. When you run CREATE INDEX CONCURRENTLY, PostgreSQL automatically sets indisvalid = false until the index is fully built.

Conclusion#

None of this is database administration. It's five things an application developer controls and usually leaves on the floor: name your connections, set your timeouts, migrate online, split your roles, write idempotent inserts. Start with application_name and timeouts. They take ten minutes and pay you back the first time something breaks at 2am.

None of these will win you praise in a design review. They just quietly keep you out of the incident channel, which is the whole point.