For most new work, Postgres — richer types (jsonb, arrays, ranges), stronger constraint support (partial and expression indexes, deferrable and EXCLUDE constraints), transactional DDL, and a planner that copes better with complex joins. MySQL with InnoDB is still a sensible answer where a team already runs it well, or where the workload is mostly primary-key reads and writes that benefit from clustered-index locality. Operational familiarity often outweighs the feature gap, and we will say so if that is your situation.

When do we actually need to shard?

Later than most teams assume. The order that works: fix the expensive queries and the indexes, move reporting to a replica, partition the largest tables, scale the instance vertically — managed Postgres goes a long way on that axis — then split by bounded context into separate databases. Sharding, whether Citus or done in the application, comes last, because it costs you cross-shard joins, distributed transactions, and every operational routine you had already made reliable.

Can Postgres replace Redis, Kafka and Elasticsearch so we run one datastore?

For a while, deliberately, yes. SKIP LOCKED is a real queue; LISTEN/NOTIFY covers light fan-out; tsvector covers early search; unlogged tables cover some caching. Each has a visible breaking point: the queue when polling starts competing with application traffic, notify when you need replay or many subscribers, tsvector when someone asks for relevance tuning, the cache when the working set no longer sits comfortably alongside shared_buffers. We start consolidated and split at the breaking point rather than in anticipation of it.

Managed or self-hosted?

Managed unless there is a specific reason not to be. RDS, Cloud SQL and Azure Flexible Server handle patching, backups and failover; the trade is a restricted extension list and no superuser. Self-hosting — usually CloudNativePG or Patroni on Kubernetes — makes sense when a required extension is unavailable or data residency rules force it. HA, backup verification and major-version upgrades then become a standing responsibility, and that should be a decision someone made rather than one that happened.

Our database is slow. What do you look at first?

pg_stat_statements sorted by total time rather than mean, then the plans behind the top few. Connection counts and idle-in-transaction sessions. Dead-tuple ratios and last-autovacuum timestamps on the hot tables. Sequential scans on tables that should never be scanned, and indexes that exist but cannot be used because the predicate wraps the column in a function. Temp file writes that point at work_mem. In practice it is usually a handful of queries, one missing or unusable index, or a vacuum that has not caught up since a bulk delete. Bigger hardware is the last thing we suggest, not the first.

PostgreSQL development, performance and migration

PostgreSQL is our default relational database — the store we start from unless the workload argues otherwise. This page sets out where that default holds, where it clearly does not, and the decisions we make when a Postgres cluster ends up behind a product.

Talk about your project See what we build

What PostgreSQL is

PostgreSQL is an open-source relational database. It stores data in typed tables with enforced keys and constraints, plans queries with a cost-based optimiser, and gives every transaction ACID guarantees using multi-version concurrency control, so readers never block writers. Beyond plain SQL it carries jsonb documents, range and array types, full-text search, geospatial types via PostGIS, logical replication, and an extension mechanism that lets it absorb jobs which would otherwise need a second system.

Prefer the short definition? Read the PostgreSQL glossary entry.

The honest version

When PostgreSQL is the right choice — and when it is not

Choosing a technology because it is popular is how projects end up expensive. Here is where we would pick it, and where we would tell you not to.

Good fit

Ledgers, payments, bookings — anything where two concurrent requests must not both win: Real transactions with row-level locking, SERIALIZABLE isolation with retry on serialisation failure where a balance is being mutated, unique constraints that make idempotency keys enforceable rather than advisory, and EXCLUDE constraints over tstzrange that make a double-booked slot impossible at the storage layer instead of hopefully impossible in application code. Transactional DDL also means a failed migration rolls back rather than leaving half a schema behind.
Multi-tenant SaaS where tenant isolation has to be provable: Row-level security puts the tenant predicate in the database, so a forgotten WHERE clause in one endpoint is not a data breach. Partitioning by tenant_id stops the largest tenants from dragging query plans for everyone, and logical replication gives you a per-tenant export or migration path when an enterprise customer asks for their data in their own region.
Products still finding their shape, where one store doing several jobs is the right trade: jsonb absorbs the parts of the schema you have not settled yet, SELECT … FOR UPDATE SKIP LOCKED is a genuinely good job queue, pg_trgm covers search until relevance tuning starts, PostGIS covers geo. One backup story, one failover story, one connection string — until traffic actually justifies splitting a concern out.
Regulated data with audit and recovery obligations: WAL archiving gives point-in-time recovery to a chosen second rather than to last night's dump; logical decoding produces an append-only change stream you can retain as an audit trail; pgaudit, column-level grants and encryption at rest through managed storage cover the controls an assessor asks about. All of it is inspectable, which counts for more in an audit than a vendor claim.
Moving off Oracle or SQL Server without a big-bang cutover: PL/pgSQL covers the bulk of stored-procedure logic, and foreign data wrappers let the legacy database and Postgres run side by side so traffic moves table by table. The licence saving is usually what starts the conversation; the reason it finishes is that the schema becomes something a normal engineering team can change.

We would choose something else

Ingest pipelines sustaining hundreds of thousands of writes per second: Every write funnels through WAL on a single primary — core Postgres has no multi-master — and MVCC means each UPDATE writes a whole new row version plus index entries that autovacuum has to reclaim afterwards. Past a certain rate the vacuum backlog, WAL volume and full-page writes dominate, and the fix is not a bigger instance. A wide-column store such as Cassandra or ScyllaDB, or a Kafka buffer in front of batched Postgres writes, matches that shape far better.
True document workloads — records whose schema genuinely varies and that are read and written whole: jsonb is an excellent sidecar and a poor primary model at scale. Updating one key rewrites the entire row and re-TOASTs the document; GIN indexes over jsonb are large and expensive to maintain on write; and the planner's selectivity estimates for containment operators are weak, so plans degrade precisely as the table grows. When the document is the unit of both read and write, MongoDB's storage and index model fits the access pattern better than bending Postgres to it.
Time-series and telemetry at scale — metrics, IoT streams, tick data with long retention: Vanilla Postgres stores time-ordered rows at full row width with no columnar compression, and expiring old data with DELETE leaves bloat instead of returning disk. You end up hand-rolling partition rollover and still paying several times the storage. TimescaleDB fixes much of this with compression and continuous aggregates, but only where the platform permits the extension; for pure metrics Prometheus or VictoriaMetrics, and for analytical scans over billions of rows ClickHouse, are cheaper to run by a wide margin.
Search and discovery — typo tolerance, per-field relevance tuning, facets: tsvector with a GIN index is fine for keyword lookup over a modest corpus. It does not give you per-field BM25 weighting you can tune, language analysers beyond what is compiled in, fuzzy matching without pg_trgm workarounds, or faceted aggregations that stay fast as the corpus grows — and rebuilding a large tsvector index is a maintenance event, not a background task. The right answer is Elasticsearch or OpenSearch fed by change data capture, with Postgres still the source of truth.
Serverless fleets that fan out to thousands of concurrent function instances: Postgres forks an OS process per connection, each with its own memory footprint. A few thousand cold-starting functions each grabbing a connection will exhaust max_connections and push the host into swap long before query load is the issue. PgBouncer or RDS Proxy in transaction mode helps, but transaction pooling strips session state — session-level advisory locks, LISTEN/NOTIFY and session prepared statements stop behaving — so either the runtime changes or the datastore does. A store with an HTTP-native connection model is the honest alternative for that architecture.

In practice

What we build with PostgreSQL

Data models designed before application code: keys, constraints and indexes chosen against the queries that will actually run, plus a written migration path for the parts of the schema we expect to move.
Performance work on databases that have already slowed down — pg_stat_statements ranked by total time rather than worst single case, auto_explain for plan capture, index review (missing, redundant, and the ones a function call in the predicate makes unusable), autovacuum and work_mem tuning, connection pooling.
Migrations: MySQL, SQL Server or Oracle onto Postgres; one overloaded instance onto a partitioned schema with read replicas; on-premise onto managed cloud — usually via logical replication so the cutover window is minutes rather than a weekend.
High availability and recovery you can demonstrate: streaming replicas, WAL archiving with point-in-time recovery, an agreed RTO and RPO, and a restore rehearsed against real backups instead of assumed to work.
Postgres-backed application patterns — the transactional outbox, SKIP LOCKED job queues, row-level security for tenant isolation, and CDC feeds into Kafka, a cache, or a search index.

Positions

Decisions we have already made

Defaults we start from. They are arguable — but they are argued, not assumed, and we will change them for a good reason.

Managed Postgres by default; Aurora only when the read pattern pays for it: RDS, Cloud SQL or Azure Flexible Server take patching, backup and failover off your team, and that is worth more than superuser access on most engagements. Aurora earns its price when you need several low-lag read replicas and fast failover — but it is not vanilla Postgres: storage behaves differently, some extensions and parameters are unavailable, and its I/O-based billing can surprise you on a write-heavy or vacuum-heavy table. We self-host (CloudNativePG or Patroni) only when a required extension is not offered or data residency forces it, and we say plainly that HA, backup verification and version upgrades then become a recurring job for someone.
An ORM for CRUD, hand-written SQL for anything that reports — and migrations as plain versioned SQL: We prefer tools that keep the generated SQL inspectable (Drizzle, SQLAlchemy Core, jOOQ, Ecto) over ones that hide it, because the query you cannot see is the query you cannot fix. Schema auto-sync against a production database is off the table. Migrations are numbered SQL files applied forward under a lock, and anything touching a large table is written to run online: CREATE INDEX CONCURRENTLY, backfills in batches, NOT NULL added through a validated CHECK constraint rather than a full-table rewrite.
PgBouncer in transaction mode in front of every service, with small application pools: Process-per-connection means an app fleet opening twenty connections per pod exhausts the database long before it exhausts CPU, so we size the application pool from the database's capacity rather than accepting the framework default. The consequence has to be designed for, not discovered: under transaction pooling no session state survives between statements, so SET LOCAL inside a transaction replaces SET, advisory locks move to their transaction-scoped variants, LISTEN/NOTIFY gets its own direct connection, and prepared statements only work where the pooler version supports them.
bigint identity or UUIDv7 primary keys — not random UUIDv4 on tables that will grow: UUIDv4 is random, so inserts scatter across the B-tree: page splits, inflated full-page writes in WAL, larger indexes and collapsing cache locality, all arriving exactly when the table gets big. UUIDv7 is time-ordered, so it inserts like a sequence while staying opaque enough to expose publicly. Where a random external identifier is genuinely mandated, we keep a bigint primary key for the internal relationships and index the UUID separately rather than letting it cluster everything.

Architecture

Things worth getting right early

Partition before you shard: Declarative range or list partitioning on time or tenant, with pg_partman handling rollover. Retention becomes DROP PARTITION — instant, and it actually returns the disk — rather than a DELETE of a hundred million rows that leaves autovacuum a backlog and gives back nothing. The cost is discipline: the partition key has to appear in query predicates for pruning to happen, and unique constraints must include it. Done early, this buys years before a distributed store or application-level sharding is on the agenda.
Replicas are for read scale, not for read-your-writes: Streaming replication is asynchronous by default, so a user who just submitted a form and then reads from a replica can see the state before their change. We route by intent rather than by load: writes and the reads immediately following them go to the primary; reporting, exports and search-index rebuilds go to replicas. Where a path cannot tolerate lag but must stay off the primary, we track the commit LSN and wait for the replica to reach it instead of hoping.
Transactional outbox instead of dual writes: Writing a row to Postgres and then publishing an event to a broker is two operations that can fail independently, and eventually will. We write the row and the event in the same transaction, then publish from the outbox using logical decoding. The database's commit order becomes the event order, which is also what keeps a search index, a cache and downstream consumers converging on the same truth rather than drifting apart quietly.
Vacuum, bloat and transaction-ID wraparound are operational concerns, not automatic ones: MVCC keeps old row versions until vacuum reclaims them, and the shipped autovacuum defaults are too relaxed for hot tables. One long-running transaction — an idle-in-transaction connection, an analyst's query left running on the primary — pins the xmin horizon and stops vacuum reclaiming anything anywhere in the cluster. We tune autovacuum per table, alert on dead-tuple ratio and datfrozenxid age, set statement_timeout and idle_in_transaction_session_timeout as a matter of course, and schedule REINDEX CONCURRENTLY rather than waiting for index bloat to surface as latency.

Around it

What PostgreSQL usually sits next to

Redis
Sits in front for caching, rate limiting and idempotency-key lookups, so read-heavy paths never reach a Postgres backend at all.
Apache Kafka
Consumes the outbox via logical decoding, giving downstream services a replayable, ordered stream without any of them querying the primary.
Elasticsearch
Takes the search workload once relevance tuning, fuzzy matching or facets start — Postgres stays the source of truth and feeds it by CDC.
ACID
The property that makes Postgres the default for money and state transitions: the guarantee you would otherwise have to rebuild by hand in application code.
Partitioning
The scale path we take before sharding — cheap retention, smaller indexes, and pruning that keeps plans stable as the table grows.
RTO & RPO
WAL archiving and replicas only mean something once the recovery targets are written down and the restore has actually been rehearsed.

Questions

Common questions about PostgreSQL

PostgreSQL or MySQL?
For most new work, Postgres — richer types (jsonb, arrays, ranges), stronger constraint support (partial and expression indexes, deferrable and EXCLUDE constraints), transactional DDL, and a planner that copes better with complex joins. MySQL with InnoDB is still a sensible answer where a team already runs it well, or where the workload is mostly primary-key reads and writes that benefit from clustered-index locality. Operational familiarity often outweighs the feature gap, and we will say so if that is your situation.
When do we actually need to shard?
Later than most teams assume. The order that works: fix the expensive queries and the indexes, move reporting to a replica, partition the largest tables, scale the instance vertically — managed Postgres goes a long way on that axis — then split by bounded context into separate databases. Sharding, whether Citus or done in the application, comes last, because it costs you cross-shard joins, distributed transactions, and every operational routine you had already made reliable.
Can Postgres replace Redis, Kafka and Elasticsearch so we run one datastore?
For a while, deliberately, yes. SKIP LOCKED is a real queue; LISTEN/NOTIFY covers light fan-out; tsvector covers early search; unlogged tables cover some caching. Each has a visible breaking point: the queue when polling starts competing with application traffic, notify when you need replay or many subscribers, tsvector when someone asks for relevance tuning, the cache when the working set no longer sits comfortably alongside shared_buffers. We start consolidated and split at the breaking point rather than in anticipation of it.
Managed or self-hosted?
Managed unless there is a specific reason not to be. RDS, Cloud SQL and Azure Flexible Server handle patching, backups and failover; the trade is a restricted extension list and no superuser. Self-hosting — usually CloudNativePG or Patroni on Kubernetes — makes sense when a required extension is unavailable or data residency rules force it. HA, backup verification and major-version upgrades then become a standing responsibility, and that should be a decision someone made rather than one that happened.
Our database is slow. What do you look at first?
pg_stat_statements sorted by total time rather than mean, then the plans behind the top few. Connection counts and idle-in-transaction sessions. Dead-tuple ratios and last-autovacuum timestamps on the hot tables. Sequential scans on tables that should never be scanned, and indexes that exist but cannot be used because the predicate wraps the column in a function. Temp file writes that point at work_mem. In practice it is usually a handful of queries, one missing or unusable index, or a vacuum that has not caught up since a bulk delete. Bigger hardware is the last thing we suggest, not the first.

Services that use PostgreSQL

Where it lands

Get a second pair of eyes on your database

Send the schema, the top twenty from pg_stat_statements, and what the system has to do a year from now. You get back a written read on the data model, the indexes and the scale path — including a direct answer if part of that workload belongs in something other than Postgres.

Talk to TantraDev All technologies