Surprising PostgreSQL Features That Redefine What a Database Can Do

1. Multi‑master asynchronous replication

PostgreSQL can be configured with multiple primary nodes located in different data‑centers. Table‑level changes are replicated asynchronously to the other nodes, enabling active‑active write capability across sites. Because replication is not synchronous, applications must implement their own conflict‑resolution logic when concurrent updates occur on different masters.

2. Greenplum – MPP‑based OLAP data warehouse

Greenplum is an open‑source, massively parallel processing (MPP) platform built on PostgreSQL. It distributes tables across many segment hosts, allowing a single SQL query to run in parallel on dozens to hundreds of servers. This architecture is suited for large‑scale analytical workloads such as data‑warehouse reporting, complex joins, and window functions.

3. pg_shard / FDW‑based data sharding

Horizontal sharding can be achieved with the pg_shard extension or by combining PostgreSQL foreign‑data wrappers (FDW). A single logical table can be partitioned across hundreds of PostgreSQL instances (e.g., 512 nodes). The system tolerates the failure of a few nodes without data loss, providing a PostgreSQL‑native alternative to Hadoop/HDFS for distributed storage.

4. PostGIS – Full OpenGIS‑compliant spatial database

PostGIS extends PostgreSQL with geometry and geography types that fully implement the OpenGIS specifications. It supports 2‑D and 3‑D spatial objects, elevation handling, and irregular ellipsoidal earth models. These capabilities are used in location‑based services such as food‑delivery routing, ride‑hailing, public‑transport planning, and travel navigation.

5. PG‑Strom – GPU‑accelerated SQL execution

PG‑Strom offloads eligible query operators (e.g., scans, joins, aggregates) to modern GPUs. By translating parts of the execution plan into CUDA kernels, it can achieve orders‑of‑magnitude speedups for data‑intensive workloads while using the same SQL interface.

6. PipelineDB – Stream processing with materialized views

PipelineDB implements continuous aggregation by materializing view results as new rows arrive. It is designed for high‑frequency ingestion scenarios, such as collecting 1 KB smart‑meter readings from tens of millions of devices every minute. Example calculations:

Assume 50 million meters, each sending one record per minute → 50 million rows/minute.

Data rate ≈ 50 M × 1 KB ≈ 50 GB per minute (≈ 833 MB/s).

Monthly storage ≈ 50 GB × 60 × 24 × 30 ≈ 2.16 TB.

PipelineDB can keep intermediate aggregates in memory and write only final results to disk, reducing I/O pressure.

7. FDW – Universal foreign‑data wrapper interface

The FDW framework provides a plug‑in mechanism to treat external data sources as regular PostgreSQL tables. Supported back‑ends include Oracle, SQL Server, MySQL, MongoDB, Redis, CSV files, Excel spreadsheets, and many others. FDWs support both read and write operations, enabling heterogeneous data integration within a single SQL query engine.

These extensions illustrate PostgreSQL’s versatility for high‑availability architectures, big‑data analytics, GIS processing, GPU acceleration, streaming aggregation, and cross‑system data federation.