| Myth | Reality |
|---|---|
| Postgres is only good for transactional, row-by-row application data. | Extended Postgres runs vector similarity search, geospatial queries, time-series rollups, and sharded distributed workloads inside the same engine. |
| Extensions are risky, unofficial hacks bolted onto the database. | pgvector, PostGIS, TimescaleDB, and Citus are mature C-language extensions with active maintainers, and several have commercial backers offering support contracts. |
| A dedicated, purpose-built database will always outperform an extension. | At moderate to large scale a well-tuned extension often matches dedicated stores because it avoids network hops, dual writes, and cross-system consistency bugs. |
| Consolidating onto Postgres is a stopgap, not a long-term architecture. | Many engineering organisations treat consolidation as a deliberate choice that shrinks operational surface area and the number of systems that need patching, monitoring, and backup. |
What “Postgres as a default” actually means
Every growing product eventually collects a pile of specialised data stores: a vector database for embeddings, a geospatial engine for location queries, a time-series database for metrics, and a sharding layer for scale. Each one solves a real problem, and each one also adds a connection pool to manage, a backup job to schedule, a permissions model to reason about, and a failure mode to debug at 2 a.m.
“Postgres as a default” is the practice of reaching for PostgreSQL first and only introducing a new specialised system when an extension genuinely cannot do the job. It is not a rejection of dedicated databases. It is a sequencing decision: start with the extension, measure it against your real traffic, and only migrate off Postgres when the numbers force the issue.
The reason this works in 2026 is that the extension ecosystem has matured. Teams building AI products increasingly default to Postgres plus pgvector rather than standing up a separate vector store on day one, and the same pattern shows up in geospatial, time-series, and distributed workloads.
How Postgres extensions work under the hood
The extension framework
PostgreSQL was built to be extended. The CREATE EXTENSION command loads a packaged bundle of SQL objects, functions, operators, index access methods, and often a compiled shared library, directly into a database. Once loaded, an extension’s functions and types behave exactly like built-in ones: they show up in the catalog, they participate in the query planner’s cost estimates, and they can be indexed like any native column type.
This is different from bolting a plugin onto an application. The extension runs inside the same process as the database engine, so it can hook into the planner, the storage layer, and the write-ahead log. That is what lets pgvector add a genuinely new index type, or TimescaleDB rewrite how a table is partitioned, without either one becoming a separate service.
Background workers and custom index access methods
The other piece of the framework is the background worker API, which lets an extension run its own maintenance processes, such as Citus rebalancing shards or TimescaleDB compressing old chunks, on a schedule, inside the same instance. Combined with custom index access methods (the same mechanism that gives Postgres B-tree, GiST, and GIN indexes), this is why an extension can introduce an entirely new capability, like approximate nearest-neighbour search, without forking the database.
Figure 1: Where each extension sits in the stack
Application layer to ORM or SQL driver to PostgreSQL core (planner, storage, WAL) to extension layer: pgvector (vector index and distance operators), PostGIS (geometry types and spatial index), TimescaleDB (hypertables and compression), Citus (distributed planner and shard rebalancer).
All four extensions plug into the same core engine rather than running as separate services, which is why a single connection string can serve all of them at once.
pgvector: turning Postgres into a vector database
pgvector adds a vector data type plus distance operators for L2, cosine, and inner product similarity, along with IVFFlat and HNSW index types for approximate nearest-neighbour search. In practice this means a table storing product descriptions, support tickets, or document chunks can carry both the structured columns (price, status, owner) and an embedding column in the same row, queried with a single SQL statement instead of a join across two systems.
The appeal is consistency: filtering by tenant_id and ranking by embedding distance in one query removes an entire class of bugs where a dedicated vector store and the relational database drift out of sync. This is the same trade-off explored in discussions of AI backend architecture, where the choice between a bolted-on vector database and a vector column inside the primary store shapes the rest of the stack, and it is directly relevant to agent systems that rely on retrieval-augmented generation, where deleting a user’s data from one system instead of two is a real compliance advantage.
| Factor | pgvector in Postgres | Dedicated vector store |
|---|---|---|
| Data consistency | Single source of truth, no dual writes | Separate write path, possible drift |
| Query flexibility | Full SQL joins and filters alongside similarity search | Usually limited to vector plus metadata filters |
| Scale ceiling | Very large collections with HNSW tuning and hardware | Built for extreme scale from the start |
| Operational overhead | One system to patch, back up, monitor | An additional service, credentials, and network hop |
PostGIS: geospatial analysis without a separate GIS stack
PostGIS adds geometry and geography types, spatial indexes, and hundreds of functions for distance, intersection, containment, and coordinate transformation. It is the extension with the longest track record on this list and underpins a large share of the open-source mapping world. A delivery platform can store addresses, route polygons, and driver positions in the same schema as orders and invoices, and answer “which drivers are within two kilometres of this address” with a spatial index lookup instead of a call to an external mapping service.
Because PostGIS functions are ordinary SQL functions, they compose with everything else in the query: joins against customer tables, window functions for ranking, and transactions that update inventory and location in the same commit.
TimescaleDB: time-series workloads on core Postgres
TimescaleDB turns a normal table into a hypertable, which is automatically partitioned into time-based chunks behind the scenes. Writes still look like ordinary INSERT statements, but the engine only scans the chunks relevant to a query’s time range, and it can compress older chunks into a columnar format that shrinks storage by a large margin while keeping the data queryable. Continuous aggregates let a dashboard query a pre-computed rollup instead of scanning raw rows on every refresh.
This matters for monitoring, IoT telemetry, and financial tick data, where the write volume is high but most queries only touch recent data. Instead of running a metrics database next to the application database, both live in the same Postgres instance, which simplifies the queries that join operational metrics against business data, something a managed relational database comparison often glosses over when it treats “the database” as a single monolithic choice rather than a base engine plus extensions.
Figure 2: A hypertable’s chunk layout over time
Hypertable “sensor_readings” split into chunks: chunk_2026_w1 (compressed, columnar), chunk_2026_w2 (compressed, columnar), chunk_2026_w3 (current week, uncompressed, row-based, actively written).
Recent chunks stay uncompressed for fast writes, while older chunks are compressed automatically, so storage cost drops without a separate archival system.
Citus: horizontal scaling and distributed Postgres
Citus turns a single logical Postgres database into a distributed cluster by sharding tables across worker nodes and rewriting incoming queries to run in parallel across those shards. Unlike a full migration to a distributed SQL engine, applications keep talking to what looks like one Postgres connection; Citus’s coordinator node handles the routing and result merging. This is the extension most teams reach for only after outgrowing a single machine, since the operational complexity of a distributed system, rebalancing, colocation planning, cross-shard transactions, is real and should not be taken on before it is needed.
A decision framework: stay on Postgres or adopt a dedicated system
The decision rarely comes down to “can it technically do this.” It comes down to where the workload sits on a handful of practical axes.
| Criterion | Favors staying on Postgres and extensions | Favors a dedicated system |
|---|---|---|
| Data volume relative to a single node’s memory and disk | Fits comfortably with headroom for growth | Exceeds what one well-specced node can hold |
| Query pattern | Needs joins across vector, spatial, time-series, and relational data | Single-purpose, extremely high query-per-second workload |
| Team expertise | Strong SQL and Postgres operations skills already in-house | Existing deep expertise in the dedicated system |
| Consistency requirements | Needs the new data type to be transactionally consistent with core business data | Eventual consistency with the primary store is acceptable |
| Operational budget | Limited platform team, wants fewer systems to run | Dedicated platform or SRE capacity for a specialised system |
Common mistakes when consolidating on Postgres
Mistake
Enabling pgvector’s HNSW index on millions of rows without tuning m and ef_construction, then concluding “Postgres cannot do vector search” when the real issue is default index parameters.
Mistake
Running Citus in production without first testing cross-shard transactions and colocation rules against the application’s actual query patterns, which surfaces query rewrites the planner cannot parallelise well.
Mistake
Leaving TimescaleDB’s compression and retention policies at their defaults, so old chunks never compress and storage costs climb the way they would in an unmanaged relational table.
Mistake
Treating every extension as free once installed, and skipping load testing before a launch, when in practice each one adds its own tuning knobs and failure modes that need real traffic to validate.
How experienced teams evaluate and roll out an extension
Practice
Start with a read replica or staging database and load a realistic sample of production-sized data before touching the primary instance, since extension behaviour under ten thousand rows tells you almost nothing about behaviour under ten million.
Practice
Benchmark the specific query shapes the application actually runs, not generic extension demos, because a similarity search combined with a tenant filter behaves very differently from an unfiltered nearest-neighbour scan.
Practice
Set explicit alerts on extension-specific metrics, such as compression ratio for TimescaleDB or shard rebalance duration for Citus, rather than relying only on generic CPU and memory dashboards.
Practice
Keep a documented exit path: know what a migration to a dedicated system would require before you need it, so a scaling wall does not turn into an emergency rewrite.
Glossary
- Extension
- A packaged bundle of SQL objects and optionally compiled code that PostgreSQL loads directly into a database via CREATE EXTENSION, extending its native capabilities.
- HNSW index
- Hierarchical Navigable Small World, a graph-based index structure used by pgvector for fast approximate nearest-neighbour search over embeddings.
- Hypertable
- TimescaleDB’s abstraction over a normal Postgres table, automatically partitioned into time-based chunks for faster writes and queries.
- Shard
- A horizontal partition of a table’s data, distributed across worker nodes by Citus so queries can run in parallel.
- Continuous aggregate
- A TimescaleDB materialized view that incrementally refreshes rollups over time-series data instead of recomputing from raw rows on every query.
- Coordinator node
- The Citus node that receives application queries, plans how to distribute them across worker shards, and merges the results.
- Postgres extensions let one engine absorb vector search, geospatial analysis, time-series storage, and horizontal scaling without four separate systems.
- pgvector adds a native vector type and approximate nearest-neighbour indexes, keeping embeddings transactionally consistent with relational data.
- PostGIS is the most mature extension on this list and answers spatial questions using the same SQL and transactions as the rest of the schema.
- TimescaleDB’s hypertables and compression cut storage costs on high-volume time-series data while keeping writes looking like normal inserts.
- Citus should be adopted only after a single node genuinely cannot hold the workload, since distributed operations add real complexity.
- The decision to stay on Postgres or move to a dedicated system should be driven by data volume, query pattern, team skills, and consistency needs, not by default assumptions.
- Load testing with production-sized data and documenting an exit path are what separate a successful consolidation from a painful migration later.
FAQs
Is pgvector as fast as a dedicated vector database?
For most workloads under a few tens of millions of vectors, a well-tuned pgvector HNSW index performs competitively with dedicated vector stores, especially once you account for the network hop and consistency overhead a separate service adds. Extremely large or extremely high-throughput similarity search workloads may still favour a purpose-built system.
Do I need a different Postgres version to use these extensions?
Most current extensions support recent PostgreSQL major versions, but each one publishes its own compatibility matrix, and cloud providers often gate which versions are supported on managed offerings. Always check the extension’s release notes before planning an upgrade.
Can I run pgvector, PostGIS, TimescaleDB, and Citus together in one database?
Yes, extensions can generally coexist, though Citus’s distributed model changes how tables are created and how other extensions’ background workers behave, so combining Citus with TimescaleDB or pgvector requires reading each extension’s documentation on distributed compatibility.
What happens if an extension is not available on my managed database provider?
Managed Postgres providers maintain an allow-list of supported extensions. If one you need is missing, options include requesting it, switching to a provider that supports it, or self-hosting Postgres, which trades operational convenience for full extension freedom.
Does adding extensions make Postgres harder to back up or upgrade?
Extensions are included in standard pg_dump and physical backups, but major version upgrades require checking that each installed extension has a compatible release for the target version before upgrading, which adds a step to the usual upgrade checklist.
How do I decide between IVFFlat and HNSW in pgvector?
HNSW generally gives better query speed and recall at the cost of slower index builds and higher memory use, while IVFFlat builds faster and uses less memory but needs careful tuning of its list count. Most new deployments default to HNSW unless index build time is a hard constraint.
Is Citus a good fit for a startup’s first database?
Usually not. Citus adds real operational complexity that only pays off once a single Postgres node cannot hold the data or handle the write volume. Most teams should start on plain Postgres, add pgvector, PostGIS, or TimescaleDB as needed, and only introduce Citus once a genuine single-node ceiling is hit.
Do these extensions work the same way on every cloud provider?
Core behaviour is consistent because the extension code itself is the same, but managed offerings differ in which versions they support, how they expose configuration parameters, and whether background workers required by extensions like Citus are permitted at all, so provider documentation should be checked before committing to an architecture.
