Skip to content

Data

PostgreSQL

The world’s most capable open-source database, designed and run by engineers who tune it in production — not just install it.

Overview

PostgreSQL is a relational database with a reputation earned over thirty-five years: fully ACID-compliant, ferociously correct about your data, and extensible in ways no other open-source database matches. It is our default choice for the store of record in almost every system we build. Not because it is fashionable, but because it is the option that punishes you least as a project grows — you very rarely regret starting with Postgres, and you frequently regret not starting there.

What sets it apart is range. Postgres speaks rich, standards-compliant SQL — window functions, common table expressions, recursive queries, materialised views — and it holds relational data with genuine integrity: foreign keys, check constraints, and transactional DDL that lets a migration either fully apply or fully roll back. But it is also a document store when you need one. Its JSONB type gives you binary, indexed, queryable JSON in the same table as your relational columns, so you can model the structured parts of your domain as tables and the genuinely unstructured parts as documents, in one engine, under one transaction. That combination removes the usual reason teams reach for a second database.

The extension ecosystem is where Postgres becomes something closer to a platform. PostGIS turns it into a first-class geospatial database. pgvector adds vector similarity search for AI embeddings and retrieval. TimescaleDB makes it competent at time-series. Built-in full-text search handles a great deal that teams reach for Elasticsearch to do. We use these deliberately: a great many systems that people assume need three or four specialised data stores actually need one well-configured Postgres and the discipline to keep it that way until the evidence says otherwise.

Best for — The transactional store of record for almost any serious application — relational data that must stay correct, with room for JSON documents, geospatial, and vector search in the same engine — operated by a team that knows how to tune it.

Why teams choose PostgreSQL

  • One database instead of four

    Relational tables, JSONB documents, full-text search, geospatial, and vector similarity all live in one Postgres. That is one system to back up, secure, monitor, and reason about transactionally — instead of a fragile constellation of specialised stores that have to be kept consistent with each other.

  • Data integrity you can trust

    Foreign keys, check constraints, unique constraints, and transactional DDL mean the database enforces your rules rather than hoping the application does. Bad data is rejected at the door, and a botched migration rolls back cleanly instead of leaving your schema half-changed.

  • No licence, no lock-in

    Postgres is under a permissive open-source licence with no per-core fees and no vendor holding your data hostage. It runs the same on your laptop, on a cheap VM, and on managed offerings from every major cloud — so your options stay open and your bill stays predictable.

Why businesses choose PostgreSQL

  • You want a store of record whose correctness you can rely on for years, not a database you will be migrating away from in eighteen months because it could not hold your invariants.
  • You would rather run one well-understood database than assemble a zoo of specialised stores, and you want Postgres’s extensions to absorb geospatial, search, and vector work before you add new infrastructure.
  • You value an open licence and portability — no per-core fees, no lock-in, and the freedom to run anywhere from a single VM to a managed cloud service.
  • You want the operational side done right: sensible connection pooling, tuned autovacuum, thought-through indexing, and a plan for scaling reads and, if you ever truly need it, writes — rather than discovering those the hard way in an incident.

What we build with PostgreSQL

The capabilities this technology is genuinely strong at — and what we most often build with it.

  • JSONB — relational and document in one table

    JSONB stores JSON in a binary, indexed form you can query and filter as fast as columns, with GIN indexes over document keys and values. We use it for the genuinely unstructured parts of a schema — flexible metadata, event payloads, per-tenant fields — while keeping the structured core in proper relational columns, all under one transaction. It is not an excuse to abandon schema design; it is a precise tool for the parts that resist it.

  • PostGIS for geospatial

    PostGIS turns Postgres into a serious geospatial database: geometry and geography types, spatial indexes, and hundreds of functions for distance, containment, and intersection. Nearest-store queries, delivery zones, catchment analysis, and map layers run natively against your operational data instead of in a separate GIS system that has to be kept in sync.

  • pgvector for AI and embeddings

    pgvector adds vector columns and similarity search (cosine, L2, inner product) with HNSW and IVFFlat indexes. For most retrieval-augmented generation and semantic-search workloads, it means your embeddings live next to the rows they describe, in the database you already run — so you can filter by ordinary SQL and rank by vector distance in one query, and skip standing up a dedicated vector store until scale genuinely demands it.

  • Built-in full-text search

    Postgres has real full-text search — tsvector and tsquery, stemming, ranking, and GIN-indexed lookups — that handles a large share of what teams reach for Elasticsearch to do. For product catalogues, help centres, and in-app search, keeping search in Postgres avoids a whole second system and the consistency problems of shipping data to it.

  • Window functions, CTEs, and analytical SQL

    Rich standards-compliant SQL lets us express genuinely hard queries in the database: running totals and rankings with window functions, hierarchical and graph-like traversals with recursive CTEs, and materialised views for expensive aggregates. Correct, set-based SQL routinely replaces slow application loops that fetch and process rows one at a time.

  • Logical replication and change data capture

    Logical replication streams row-level changes to replicas, other Postgres versions, or downstream systems — the foundation for near-zero-downtime major upgrades, blue-green cutovers, and feeding a data warehouse or search index without dual writes. We use it to move and upgrade production databases without the maintenance windows teams assume are unavoidable.

Use cases

  • Transactional application backbone

    The core store of record behind a web or mobile product — users, accounts, orders, payments, inventory — where every write must be atomic and consistent, and the constraint system enforces business rules the application must never be allowed to violate.

  • Multi-tenant SaaS platforms

    Tenanted data with strict isolation, using row-level security and schema design to keep tenants apart, JSONB for per-tenant custom fields, and read replicas to absorb reporting load without touching the transactional primary.

  • AI retrieval and semantic search

    A knowledge base or product catalogue with pgvector embeddings alongside the source rows, so a single query filters by ordinary predicates and ranks by semantic similarity — the retrieval layer for a RAG system without a separate vector database to operate.

  • Location-aware and time-series systems

    Logistics, field-service, and IoT workloads that combine PostGIS spatial queries with time-ordered event data — nearest-asset lookups, geofencing, and (with TimescaleDB) efficient time-series storage and roll-ups, all in one database.

When PostgreSQL is the right choice

  • You are building the store of record for an application — orders, users, accounts, inventory, anything where correctness matters and losing or corrupting a row is unacceptable. This is Postgres’s home ground, and its ACID guarantees and constraint system are exactly what you want holding that data.
  • Your data is mostly relational but has genuinely unstructured corners — flexible metadata, per-tenant custom fields, event payloads. JSONB lets you keep both shapes in one database with one transaction boundary, rather than running a separate document store and reconciling two sources of truth.
  • You need a capability that lives in an extension — geospatial queries via PostGIS, vector similarity for AI retrieval via pgvector, time-series via TimescaleDB, or full-text search. Reaching for Postgres first often collapses a multi-database architecture into a single, operable one.
  • Wrong for: pure caching and ephemeral high-churn data — session stores, rate-limit counters, leaderboards. That is Redis’s job. Using a Postgres table as a cache creates write pressure and vacuum work for data you were going to throw away anyway.
  • Wrong for: petabyte-scale analytics and columnar reporting over the whole history of your business. Postgres is a superb transactional (OLTP) database; it is not a data warehouse. Heavy analytical scans belong in a columnar warehouse such as BigQuery, Snowflake, or ClickHouse, fed from Postgres — not run against your production primary.

PostgreSQL: pros and cons

Strengths

  • Full ACID compliance and a genuinely strong constraint system, so the database itself guarantees correctness — the single most valuable property a store of record can have.
  • Exceptional range in one engine: rich SQL with window functions and CTEs, JSONB for document data, and extensions (PostGIS, pgvector, TimescaleDB, full-text search) that replace whole categories of separate infrastructure.
  • MVCC concurrency means readers never block writers and writers never block readers, so a busy transactional workload stays responsive without the coarse locking that plagues some databases.
  • A permissive licence, no vendor lock-in, and universal managed support (RDS, Cloud SQL, Azure, and more) — you are never trapped, and you can move your data and your workload where you like.

Trade-offs

  • Scaling writes horizontally is genuinely hard. Postgres scales up beautifully and scales reads via replicas, but it has no built-in sharding — spreading writes across many nodes needs an extension such as Citus or application-level sharding, and that is real engineering, not a config flag.
  • It needs competent operation to run well. Autovacuum tuning, connection limits, and the fact that each connection is a full backend process (so a busy app needs PgBouncer in front) are all things that bite teams who treat Postgres as fire-and-forget.
  • It is the wrong tool for pure caching and for petabyte analytics. Use it as a cache and you pay in write and vacuum pressure; run warehouse-scale scans on it and you will starve your transactional traffic. Those jobs belong to Redis and a columnar warehouse respectively.
  • Major-version upgrades and some schema changes on very large tables require planning. The tooling is good — logical replication makes near-zero-downtime upgrades achievable — but a naive ALTER on a billion-row table under load can lock you up if nobody thought it through.

Architecture

We treat the schema as the most important design artefact in the system, because it is the hardest thing to change once data and code have grown around it. That means modelling the relational core properly — normalised where correctness demands it, with real foreign keys and check constraints — and reserving JSONB for the parts that are genuinely unstructured rather than using it to dodge schema design. Indexes are chosen against actual query patterns: B-tree for equality and ranges, GIN for JSONB and full-text, GiST for geospatial, and partial and expression indexes where they earn their maintenance cost.

Around the database we put the pieces Postgres needs to run well at scale but does not bundle. Connection pooling via PgBouncer sits in front, because each Postgres connection is a full backend process and an application that opens hundreds directly will exhaust the server long before it exhausts the hardware. Read replicas take reporting and read-heavy traffic off the primary. Where a workload is heavily analytical, we separate concerns rather than overload one instance — feeding a warehouse for the big scans and keeping the primary lean for transactions. And we design migrations to be safe under load from the start, so schema changes on large tables never become an outage.

Performance

Most Postgres performance problems are not the database being slow; they are missing or wrong indexes, queries written as application loops instead of set-based SQL, and configuration left at conservative defaults. We tune from evidence: EXPLAIN ANALYZE on the queries that actually matter, pg_stat_statements to find the ones that dominate load, and index and query changes measured against real data volumes rather than a near-empty dev database. A single well-placed index or one rewritten N+1 query routinely does more than any amount of hardware.

Configuration matters and is workload-specific. Shared buffers, work_mem, effective_cache_size, and the autovacuum thresholds all need setting for your data and access pattern, not left at the packaged defaults. Autovacuum in particular is not optional housekeeping: on a write-heavy table, under-tuned vacuum lets dead tuples and bloat accumulate until queries slow and, in the worst case, transaction-ID wraparound threatens the database. We size and monitor it deliberately, because the teams who ignore it are the ones who get surprised.

Security

Postgres has a mature, granular security model, and we use it rather than leaning on the application alone. Role-based access with least privilege, so services connect as roles that can do only what they need; row-level security to enforce tenant and record isolation in the database itself, where a bug in the application layer cannot bypass it; and TLS for connections plus encryption at rest on the storage. Parameterised queries — always — close off SQL injection at the source rather than filtering for it.

Operational security is the other half. We keep credentials out of code and in a secrets manager, restrict network access so the database is never exposed to the open internet, and keep Postgres patched, because it is a well-studied target and known issues get fixed promptly for those who apply the updates. Backups are encrypted, access-controlled, and — the part teams skip — actually restore-tested, because a backup you have never restored is a hope, not a recovery plan. Audit logging captures who did what where the compliance context calls for it.

Scalability

The honest scaling story for Postgres is: it scales up and it scales reads superbly, and scaling writes horizontally is genuinely hard. A single primary on modern hardware handles far more than most teams expect — tens of thousands of transactions per second is well within reach with good schema and tuning — and read replicas let you fan read-heavy and reporting traffic across many nodes. For the large majority of systems, this is the entire scaling plan, and it works for years. We reach for connection pooling, partitioning of very large tables, and replica routing long before anything more exotic.

Sharding writes across nodes is where Postgres asks for real work, because there is no built-in horizontal write scaling — you add it with an extension such as Citus or with application-level sharding, and both carry lasting complexity in cross-shard queries, transactions, and operations. We are blunt about this: most teams who think they need sharding actually need better indexing, a read-replica strategy, and to move their analytics off the primary. We only take on sharding when the numbers genuinely demand it, and we tell you plainly when they do not — because inflicting distributed-database complexity on a workload that does not need it is a mistake we would rather help you avoid.

PostgreSQL integrations & ecosystem

The technologies we most often pair with it — each links to how we work with it.

How we work

We start with the data model, because it is load-bearing for everything above it. That means understanding the domain properly, deciding what is relational and what is genuinely a document, defining the constraints that must always hold, and planning indexes and access patterns before the first feature is built on top. Getting this right early is cheap; unpicking a wrong schema after a year of code and data has accreted onto it is not. Migrations are version-controlled and written to run safely under production load from day one.

The engineers who design your schema are the ones who tune it and keep it running, which is what we mean by operating what we build. That shapes the choices — sensible connection pooling, autovacuum tuned for the workload, indexes measured against real data, and restore-tested backups — because we are the people who would be on the other end of the incident if those were done carelessly. We would rather spend the effort up front than firefight it later.

The service behind it

Delivered throughData Engineering

What we build with PostgreSQL

The disciplines this technology most often shows up in — from a first build to taking over and stabilising an existing one.

How we deliver

  1. 01

    Discover

    We map the system, the constraints and the business it serves — including the parts nobody documented.

    Architecture brief

  2. 02

    Architect

    Decisions get made, written down and defended before a line of production code exists.

    Decision records

  3. 03

    Build

    Short cycles against working software. You see progress in the product, not in a status deck.

    Shipping increments

  4. 04

    Operate

    Monitoring, incident response and iteration. The system is alive, so the engagement is too.

    Runbooks & SLOs

Industries we use PostgreSQL in

Domain knowledge changes what gets built. A few of the sectors we know before the first meeting.

Also in Data

Related terms

Why teams choose us for PostgreSQL

  • Senior engineers only

    Database design is where a project’s worst, most expensive mistakes get made — and they surface years later, under load, when they are hardest to fix. The people modelling your schema and tuning your queries have done it many times before. There are no juniors learning normalisation and indexing on your production data.

  • We operate what we build

    We run the databases we design, so we optimise for the on-call reality rather than the demo: pooled connections, tuned vacuum, indexes that hold up at real volume, and backups that have actually been restored. You get a database that stays fast and correct under real traffic, not just in a benchmark.

  • One database, not a zoo

    We use Postgres’s range — JSONB, PostGIS, pgvector, full-text search — to collapse the multi-store architectures teams accumulate by default. Fewer systems to secure, back up, and reason about means fewer places for data to drift and fewer things to wake up to at night.

  • Honest about the limits

    We will tell you when your caching belongs in Redis, when your analytics belong in a warehouse, and when — rarely — you genuinely need sharding versus when you just need better indexing and a read replica. We would rather talk you out of complexity you do not need than sell it to you.

Typical timeline

  1. 01

    Discovery and data modelling

    One to two weeks understanding the domain and workload, designing the schema and constraints, and deciding what is relational, what is JSONB, and which extensions (if any) earn their place — so the foundations are settled before code grows on top of them.

  2. 02

    Build and instrumentation

    Implementing the schema with version-controlled migrations, wiring in connection pooling and the data access layer, and putting monitoring and slow-query visibility in place from the start rather than after the first incident.

  3. 03

    Tuning and load validation

    Indexing and configuration tuned against representative data volumes, queries profiled with EXPLAIN ANALYZE and pg_stat_statements, and autovacuum sized for the real write pattern — validated under load, not assumed.

  4. 04

    Hardening and handover

    Security review, restore-tested backups, a documented scaling plan (replicas, partitioning, and where the line to sharding actually sits), and documentation so your team can own and extend the database.

How pricing works

  • Fixed-scope engagements for well-defined work — a schema design and build for a new system, a performance audit, or a migration onto or between Postgres versions — quoted once we understand the data, the workload, and the constraints.
  • Monthly senior engagement for ongoing product work where the database evolves with the application and you want continuity of the people who know your schema, rather than a fixed deliverable.
  • Focused audits and rescues for struggling Postgres deployments — slow queries, vacuum and bloat problems, connection exhaustion, or a scaling decision you need to get right — priced by the assessment.

Hire PostgreSQL engineers

Need PostgreSQL capacity on your own team? We embed named senior engineers into your existing team — reporting to your leads, working in your rituals — so you add capacity without a hiring cycle.

Hire PostgreSQL engineers

Common questions

PostgreSQL or MySQL — which should we use?

Both are excellent, mature relational databases, and either will serve most projects well. We default to Postgres because its feature set is broader and its correctness stricter: richer SQL (window functions, CTEs, sophisticated indexing), true JSONB, a deep extension ecosystem (PostGIS, pgvector), and transactional DDL so a failed migration rolls back cleanly. MySQL is a fine choice too, particularly where you have existing expertise or a stack built around it, and its replication tooling is very well trodden. The gap between them has narrowed over the years; we reach for Postgres when we want the extensibility and the strictest data integrity, which is most of the time.

When would you use MongoDB instead of PostgreSQL’s JSONB?

Less often than people assume. The usual reason teams pick MongoDB is flexible, document-shaped data — and Postgres’s JSONB already gives you binary, indexed, queryable documents inside a database that also does proper relational integrity and transactions. So for most applications, JSONB removes the need for a separate document store. MongoDB earns its place when the workload is genuinely document-first at large scale and its horizontal sharding and write-scaling model fit the shape of the problem better than a single-primary relational database. We would rather start with one Postgres and add specialised stores only when the evidence demands it, than run two databases from day one out of habit.

Can PostgreSQL really handle our scale?

Almost certainly, and further than most teams expect. A single well-tuned primary on modern hardware comfortably handles tens of thousands of transactions per second, and read replicas fan out read-heavy and reporting traffic across many nodes. The overwhelming majority of systems never need more than that. The honest caveat is writes: Postgres has no built-in horizontal write sharding, so scaling writes across nodes needs Citus or application-level sharding, which is real complexity. But most teams who worry about this need better indexing, a read-replica strategy, and their analytics moved off the primary — not sharding. We will tell you which situation you are actually in.

Do we need a separate vector database for our AI features?

For most workloads, no — pgvector is enough, and keeping embeddings in Postgres is a real simplification. Your vectors live next to the rows they describe, so a single query can filter by ordinary SQL predicates and rank by vector similarity, and you have one database to operate rather than two systems to keep in sync. Dedicated vector databases start to make sense at very large embedding counts or with specialised indexing and filtering needs that outgrow what pgvector’s HNSW and IVFFlat indexes do well. We would begin with pgvector and only add a specialised store when your scale and query patterns genuinely justify the extra infrastructure.

Why does PostgreSQL need connection pooling and vacuum tuning — isn’t it fire-and-forget?

It is superb software, but it is not fire-and-forget, and the teams who treat it that way get bitten in predictable ways. Each Postgres connection is a full backend process, so an application that opens hundreds directly will exhaust the server’s memory and process limits long before it exhausts the hardware — which is why we put PgBouncer in front to pool connections. Autovacuum is the other one: on write-heavy tables it must be tuned to reclaim dead tuples, or bloat accumulates, queries slow, and in the worst case transaction-ID wraparound threatens the whole database. Neither is difficult once you know to do it; both are things we set up correctly from the start rather than discover in an incident.

Building on PostgreSQL?

A technical conversation with the engineers who would do the work. If we are not the right fit, we will say so on the call.

Prefer to book a call?Or email [email protected]

Two fields required. We reply to real enquiries — no list, no sequence.