Tools · SPBoucher
Menu

PostgreSQL

In-depth entry

by PostgreSQL Global Development Group · Database · also known as Postgres

Open-source object-relational database server with a large extension ecosystem (PostGIS, pgvector, TimescaleDB) and broad SQL coverage

PostgreSQL licenseOpen sourceFreeSelf-hostedCloudLocalSelf-hostable
Visit official site Compare

Checked Nov 2, 2026

Overview

PostgreSQL is an open-source object-relational database system maintained by the PostgreSQL Global Development Group and released under the permissive PostgreSQL License. It runs as a server process on Linux, macOS, Windows and the BSDs, and it is the default general-purpose relational store behind a large share of web applications, analytical back ends and research data pipelines.

The engine covers a broad slice of the SQL standard: transactional MVCC storage, window functions, common table expressions, JSON and JSONB documents, array and range types, full-text search and declarative table partitioning. Index types include B-tree, GIN, GiST, BRIN and hash, and the planner can parallelize scans and joins. Streaming and logical replication supply read replicas and change feeds. The extension mechanism is a defining trait: PostGIS adds spatial types, pgvector adds embedding storage and similarity search, TimescaleDB adds time-series partitioning, and foreign data wrappers expose external systems as tables.

In practice PostgreSQL is either self-hosted (packages, Docker) or consumed through managed services such as Neon, Supabase or the large cloud providers. Applications reach it through drivers for nearly every language, ORMs such as Prisma and Drizzle, and the psql command-line client. Analysts often pair it with DuckDB or a BI tool for heavy aggregations.

Limits are mostly operational. A single primary accepts writes, so horizontal write scaling requires sharding tools or a different system; vacuum and table bloat need monitoring on high-churn workloads; and the process-per-connection model usually calls for a pooler such as PgBouncer in front of busy applications.

Capabilities

  • ACID transactions with MVCC and row-level locking
  • JSONB documents with indexing alongside relational tables
  • Window functions, CTEs, lateral joins and declarative partitioning
  • Full-text search, array, range and geometric types
  • Streaming and logical replication for replicas and change capture
  • Extension system (PostGIS, pgvector, TimescaleDB, foreign data wrappers)
  • Role-based access control and row-level security

Typical workflows

  1. Primary store for a web or API application
  2. Research database loaded from CSV or Parquet and queried from Python or R
  3. Vector search for RAG applications through the pgvector extension
  4. Spatial analysis with PostGIS behind QGIS or a web map

Strengths

  • Permissive license with no vendor lock-in
  • Mature planner and wide SQL coverage
  • Extensions let one database cover documents, vectors, time series and geodata
  • Available as a managed service from almost every cloud

Limitations

  • Single-primary write path; sharding is not built in
  • Vacuum and bloat management on high-churn tables
  • Process-per-connection model benefits from an external pooler
  • Major-version upgrades require pg_upgrade or dump and restore

Prerequisites

  • A server or container to run the database, or a managed PostgreSQL service

Who it is for

  • Backend developers
  • Data engineers and analysts
  • Researchers managing structured datasets

Alternatives & comparison

MySQL is the other widely used open-source relational server; PostgreSQL differs in its permissive license, its extension model and its richer type system, while MySQL is dual-licensed by Oracle. SQLite is an embedded single-file engine for local or on-device use rather than a networked server. DuckDB and ClickHouse target analytical scans rather than transactional workloads and are often used next to PostgreSQL. Neon and Supabase are managed services built on PostgreSQL itself.

  • ClickHouse

    Open-source columnar OLAP database server for fast aggregations over billions of rows, with MergeTree storage, SQL and a managed ClickHouse Cloud

    Open sourceOpen source
  • DuckDB

    In-process analytical SQL database that queries Parquet, CSV and Arrow data directly from Python, R, Node or the CLI

    Open sourceOpen source
  • MongoDB

    Document database storing BSON documents with a rich aggregation pipeline, available as a source-available Community Server or the managed Atlas service

    Source-availableFree
  • MySQL

    Oracle's widely deployed open-source relational database server, offered as a GPL community edition and commercial enterprise and cloud editions

    MixedOpen source
  • Neo4j

    Native property-graph database queried with Cypher, offered as a GPL community edition, a commercial enterprise edition and the AuraDB cloud service

    MixedOpen source
  • Neon

    Serverless PostgreSQL service with separated storage and compute, instant database branching and scale-to-zero, now part of Databricks

    MixedFreemium
  • pg-boss

    Node.js job queue that uses PostgreSQL tables and SKIP LOCKED for reliable background jobs, scheduling and retries without a separate broker

    Open sourceOpen source
  • PostGIS

    Spatial extension for PostgreSQL adding geometry, geography and raster types with indexing and hundreds of SQL functions

    Open sourceOpen source
  • Redis

    In-memory key-value data store used as cache, message broker and database, with data structures, Pub/Sub, Streams and, since Redis 8, built-in JSON, search and time-series modules

    MixedOpen source
  • SQLite

    Public-domain embedded SQL database engine that stores a whole database in one file and ships inside most operating systems and browsers

    Open sourceOpen source
  • Supabase

    Open-source backend platform built around a dedicated PostgreSQL database with auto-generated APIs, authentication, file storage, realtime subscriptions and edge functions

    Open sourceOpen source
  • TimescaleDB

    PostgreSQL extension for time-series data adding hypertables, compression and continuous aggregates, from the company now named Tiger Data

    MixedOpen source
Compare PostgreSQL with its alternatives →

Sources & verification

Primary sources consulted for this record. Claims are tied to the fields each source supports; the automated link checker re-verifies every URL on a schedule.

Confidence
High
Reviewed
Nov 1, 2026
Links
Broken · Nov 2, 2026

Spotted an error?

Broken link, outdated license, wrong category, or a tool that no longer exists? Tell us; reports are reviewed by the editor.

pgvector

Andrew Kane (pgvector project) · Vector databases

In-depth entry

PostgreSQL extension adding vector types, distance operators and HNSW/IVFFlat indexes for similarity search inside the database

PostgreSQLOpen sourceFreeSelf-hostedNov 2, 2026

PostGIS

PostGIS Project Steering Committee (OSGeo) · GIS & mapping

In-depth entry

Spatial extension for PostgreSQL adding geometry, geography and raster types with indexing and hundreds of SQL functions

GPL-2.0-or-laterOpen sourceFreeSelf-hostedNov 2, 2026

Prisma ORM

Prisma Data, Inc. · Web frameworks & UI

TypeScript ORM with a declarative schema language, generated type-safe client and migration tooling, from the company that also offers Prisma Postgres and related hosted services

Apache-2.0Open sourceFreeLocalSelf-hostedNov 2, 2026

TimescaleDB

Tiger Data (formerly Timescale) · Relational databases

PostgreSQL extension for time-series data adding hypertables, compression and continuous aggregates, from the company now named Tiger Data

Apache-2.0 AND LicenseRef-Timescale-LicenseOpen sourceFreeSelf-hostedNov 2, 2026

Drizzle ORM

Drizzle Team · Web frameworks & UI

TypeScript ORM with SQL-like query builder, schema defined in code, migrations through Drizzle Kit, and support for PostgreSQL, MySQL and SQLite drivers

Apache-2.0Open sourceFreeLocalSelf-hostedNov 2, 2026