PostgreSQL

The world's most advanced open-source relational database.

Database Cluster-ready #sql#relational

PostgreSQL is a battle-tested, fully ACID-compliant SQL database with first-class JSON support, full-text search, geospatial indexes, and an enormous extension ecosystem. Pier deploys the official Docker image with automated backups, point-in-time recovery, and one-click replicas.

Deploy with Pier

1 Open the Pier dashboard and click Add service.
2 Pick PostgreSQL from the template list.
3 Choose the version, set a service name, and Pier provisions the container, storage, and ports automatically.
4 Attach a domain if you want HTTPS. Traefik auto-provisions the Let's Encrypt certificate.

What is PostgreSQL?

PostgreSQL (“Postgres”) is an open-source object-relational database that has been in active development for over 30 years. It’s the default choice for new application backends across industries — from one-person startups to Apple, Instagram, Reddit, and most of the European banking sector.

What sets it apart from MySQL and other “easy” databases is the depth of its type system, query planner, and extension model. JSONB lets it act like a document database. PostGIS turns it into a geospatial server. pgvector makes it a vector search engine. TimescaleDB adds time-series. You rarely outgrow Postgres — you grow extensions on top of it.

How Pier deploys it

Pier uses the official postgres Docker image, mounting /var/lib/postgresql as the data volume. The default version is latest (currently PostgreSQL 18); versions 18-alpine, 17-alpine, and 16-alpine are also available.

For single-node deployments, Pier provisions a strong password, creates the container, exposes port 5432 internally, and (optionally) publishes via Traefik. For clustered deployments, Pier switches to the bitnami/postgresql image which supports streaming replication out of the box — 1 primary + N read replicas, configurable from 2 to 5 nodes total.

Backups run on schedule (per-database or cluster-wide) using pg_dump custom format. Backups can be uploaded to any S3-compatible storage configured in Pier’s S3 Storages settings.

When NOT to use PostgreSQL

For embedded, single-process applications, SQLite is simpler and lighter. For pure key-value workloads with no relations and sub-millisecond latency targets, Redis or Valkey are better fits. For petabyte-scale OLAP with no transactional requirements, ClickHouse wins on raw throughput.

For almost everything else — Postgres.

Key features

Full ACID & MVCC

True serializable isolation, foreign keys, triggers, stored procedures (in plpgsql, Python, JS, Rust). Multi-version concurrency without read locks.

Rich type system

Native JSONB with indexes and operators, arrays, ranges, UUID, geometric types, custom types, enums. JSONB is fast enough that Postgres often replaces document databases.

Streaming replication & WAL

Synchronous or async physical replicas, logical replication for cross-version migrations and CDC, point-in-time recovery via WAL archiving.

Performance at any scale

Cost-based query planner with parallel scans, BRIN indexes for billion-row tables, partitioning by range/list/hash, partial and expression indexes.

Extension ecosystem

PostGIS (geospatial), TimescaleDB (time-series), pgvector (AI embeddings), pg_partman, pg_stat_statements, pg_repack, foreign data wrappers — Postgres is a database you grow into.

Cluster-ready on Pier

1 primary + N read replicas with streaming replication via the bitnami/postgresql image. Pier handles healthchecks and connection routing.

Use cases

SaaS application backend

The default choice for new application backends. Works with Prisma, Sequelize, SQLAlchemy, Hibernate, GORM, ActiveRecord, Diesel — every mainstream ORM.

Multi-tenant data with row-level security

Native row-level security policies enforce tenant isolation at the database, not the application — leak-proof multi-tenancy.

Geospatial workloads (with PostGIS)

Maps, routing, geofencing, fleet tracking — PostGIS adds 1000+ spatial functions, R-tree indexes, projections, and topology.

AI / vector search (with pgvector)

Store and query 1536-dim embeddings with HNSW or IVFFlat indexes. Skip the dedicated vector DB if Postgres already holds your other data.

Analytics & BI source

Connect Metabase, Superset, Grafana, Tableau directly. Materialized views and partitioning handle hundreds of millions of rows comfortably.

Code examples

Create a table with constraints and indexes sql

CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT UNIQUE NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  profile     JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX users_created_idx ON users (created_at DESC);
CREATE INDEX users_profile_gin ON users USING gin (profile);

JSONB query with operator sql

SELECT id, email
FROM users
WHERE profile @> '{"plan": "pro"}'
  AND (profile->>'signups')::int > 5
ORDER BY created_at DESC
LIMIT 50;

Row-level security for multi-tenancy sql

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation
ON invoices
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Per-request:
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Range partitioning by month sql

CREATE TABLE events (
  id BIGSERIAL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_05 PARTITION OF events
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

How it compares

vs MySQL / MariaDB	MySQL is simpler and still fast for the basics, but Postgres wins on JSON, full-text search, geospatial, partial indexes, CTEs, and SQL standard compliance. Pick Postgres for anything non-trivial.
vs MongoDB	Postgres's JSONB matches Mongo's flexibility while keeping joins, transactions, and SQL. Most teams that picked Mongo for "schema flexibility" eventually migrate back.
vs SQLite	SQLite is unbeatable for embedded use and single-writer apps. Postgres takes over when you need concurrent writes, network access, or replication.
vs Managed Postgres (RDS, Cloud SQL, Supabase)	Self-hosting on Pier gives you the same Postgres at a fraction of the cost — typical savings 10–30× for small/medium workloads. You give up the SaaS UI but gain control and portability.

Frequently asked questions

Which PostgreSQL version does Pier deploy by default?

The default is `latest` (PostgreSQL 18 at the time of writing). The version selector also includes 18-alpine, 17-alpine, and 16-alpine. Pin to a specific major version in production so future image updates don't trigger an unexpected major upgrade.

Are backups automatic?

Pier ships scheduled per-database and cluster-wide backups (pg_dump-custom format) with optional S3 destinations. Point-in-time recovery via WAL archiving is on the roadmap.

Can I run replicas?

Yes — the PostgreSQL template supports clusters of 2 to 5 nodes (1 primary + N read replicas) using bitnami/postgresql's streaming replication.

How do I add extensions like PostGIS or pgvector?

For PostGIS use the dedicated PostGIS template (PostgreSQL + extension bundled). For pgvector and others, exec into the container and CREATE EXTENSION manually, or install via a custom image.

Does it support row-level security?

Yes — full RLS via CREATE POLICY. Often used to enforce tenant isolation in multi-tenant SaaS without app-side filtering.

What ports are exposed?

5432/tcp by default. Pier routes traffic over the internal network; only expose externally via Traefik if you must.

Can my existing ORM (Prisma, Sequelize, Diesel) connect?

Yes, every mainstream ORM has first-class Postgres support. Connect via the standard `postgresql://user:pass@host:5432/db` URI Pier displays on the service page.

Related services

Deploy on your VPS

Deploy this service →