PostgreSQL

PostgreSQL is the modern default relational database: open source, rigorously reliable, rich in features, and offered as a managed service by every major cloud provider and a wave of developer-friendly platforms.

Who gets this pick

You'll see PostgreSQL when you're not on shared hosting, your data isn't real-time, and either you expect thousands of users or more, or you're using a managed database service. (The one exception: flexible data plus database experience points to MongoDB.)

Why PostgreSQL fits

It grows with you. The same database handles a side project and serious production traffic, so you won't need a migration when your project takes off.

It protects your data. Constraints, foreign keys, transactions and strict typing mean bad data is rejected at the door instead of discovered months later.

It's more than tables. JSONB columns store and index flexible JSON; full-text search is built in; extensions add geospatial queries (PostGIS) and vector search for AI features (pgvector).

Managed options are everywhere. Neon and Supabase offer hosted Postgres with free tiers and no server to configure; AWS, Google Cloud and Azure all run it at scale.

A first schema

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email TEXT NOT NULL CHECK (customer_email LIKE '%@%'),
  total_pence INTEGER NOT NULL CHECK (total_pence >= 0),
  metadata JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_email_idx ON orders (customer_email);

A few habits are packed in there: identity columns instead of the older SERIAL, CHECK constraints so impossible values can't be stored, money as integer pence rather than floating-point, TIMESTAMPTZ so times are unambiguous, and an index on the column you'll look orders up by.

To use it from an application, follow our Node + Express + PostgreSQL guide, which connects to a free hosted database and deploys the result.

Preparing for real traffic

• Pool connections. Each Postgres connection uses server memory. Use your driver's pool, and a pooler (PgBouncer or your provider's built-in one) if you run many app instances or serverless functions.
• Use EXPLAIN ANALYZE on slow queries; a missing index is the usual culprit.
• Use migrations - versioned schema changes in your repository, via a tool such as Prisma Migrate, Drizzle, node-pg-migrate or Alembic - instead of editing tables by hand.
• Scale vertically first. A bigger instance and good indexes take you a very long way before read replicas or sharding are worth their complexity.

Mistakes to avoid

• A new connection per request, which falls over under load.
• TIMESTAMP without time zone for real-world events, leading to off-by-an-hour bugs twice a year.
• SELECT * everywhere, which breaks when columns change and wastes bandwidth.
• Untested backups. Managed services back up automatically; make sure you know how to restore one before you need to.

Keep going