Tech & AI, Explained

postgres

9 min read

Postgres (PostgreSQL)

Status: 🟩 COMPLETE (🟦 LIVING — Postgres ships major versions annually) Last updated: 2026-06-19 Plain-English tagline: The world’s most powerful open-source relational database. Free, mature (35+ years), feature-rich, scales further than people assume. The default choice for new projects in 2026.

In plain English

PostgreSQL (always called just “Postgres” in conversation, pronounced “post-gres”) is a relational database. It stores tables of rows, you query in SQL, it gives you back results. So far, same as any SQL database.

What makes Postgres special:

  • Free and open source. No license cost, no vendor lock-in. You can run it on your laptop, on a Vercel-managed Postgres, on AWS RDS, on Supabase, anywhere.
  • Old in the best sense. Originated at UC Berkeley in 1986, became Postgres95, then PostgreSQL. Decades of bug-fixing and tuning.
  • Feature-rich. SQL standard compliance, plus huge bonus features: JSON columns, full-text search, geographic data (PostGIS), vector embeddings (pgvector), partial indexes, materialized views, CTEs, window functions, partitioning, replication, foreign data wrappers, and much more.
  • Genuinely solid. Data integrity is taken seriously. ACID compliant. Crash recovery works. Replication works. The community attitude is “if it might lose your data, that’s a bug.”
  • Excellent ecosystem. Tools, libraries, hosting providers, documentation, books — all mature.

In 2026, “use Postgres” is the consensus advice for new webapp projects. The recommendation is so universal it’s borderline meme: “just use Postgres.”

In your stack: Supabase is Postgres-as-a-service. Every Supabase project is a real Postgres database. You can connect to it directly with any Postgres tool (pgAdmin, psql, DBeaver), run any Postgres extension, use any feature. Supabase adds auth, storage, realtime, and APIs on top.

Why it matters

  • You’re using it whether you know it or not. Every Supabase project = Postgres.
  • It handles way more than people assume. Tiny startups → Series A revenue → public companies — many never need to leave a single Postgres instance.
  • The “best” advice is to start with it. Reaching for specialized DBs is a “when you have a specific reason” move.
  • Skills transfer. Postgres skills work at almost every webapp company in 2026.

The lineage and current state

YearMilestone
1986POSTGRES project starts at UC Berkeley
1996Postgres95 → PostgreSQL (SQL support added)
2005PostgreSQL 8.0 (Windows native, savepoints)
2010Adopted by Heroku, gaining mainstream attention
2014JSON-B (binary JSON) — huge feature, narrowed NoSQL gap
2017Logical replication
2020Major Postgres-on-cloud growth (Supabase, Neon, etc. founded around this period)
2023pgvector — first-class vector storage for AI
2024–2025Continued strong growth; widespread “just use Postgres” sentiment
2026Postgres 17 is the recommended major version

A new major version ships roughly every September. Each is backward-compatible with minor maintenance.

What Postgres includes by default

You get an enormous list of features without installing anything extra:

Core SQL

Full standard SQL plus extensions. SELECT, INSERT, UPDATE, DELETE, JOIN, CTE, window functions, transactions, subqueries — all here.

Data types

  • Basic: integer, bigint, smallint, numeric (arbitrary precision), real, double precision
  • Strings: text (unlimited length), varchar(N), char(N)
  • Dates/times: date, time, timestamp, timestamptz (with timezone)
  • Booleans: boolean
  • Binary: bytea
  • UUIDs: uuid (native, indexed, fast)
  • JSON: json (text-based) and jsonb (binary, indexable, much faster — use this)
  • Arrays: any type as text[], integer[], etc.
  • Enums: custom restricted-value types
  • Network: inet, cidr, macaddr
  • Geometric: point, polygon, circle
  • Range types: int4range, tsrange, daterange

Constraints

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, EXCLUDE. Database-level enforcement.

Indexes

  • B-tree (default; most common)
  • Hash
  • GIN (generalized inverted — good for JSON, arrays, full-text search)
  • GiST (geometric, full-text, custom)
  • BRIN (block-range, for huge sequential tables)
  • Partial (only index rows matching a condition)
  • Expression (index on computed value)
  • Multi-column

Procedural languages

  • PL/pgSQL — Postgres’s procedural extension to SQL (the default)
  • PL/Python, PL/Perl, PL/Tcl, PL/v8 (JavaScript) — write stored functions in your favorite language

Advanced features

  • Full-text search — tsvector, tsquery, ranking, stemming
  • Materialized views — pre-computed query results, refreshed periodically
  • Triggers — code that runs before/after DML operations
  • LISTEN / NOTIFY — pub/sub messaging built in
  • Logical replication — replicate specific tables to other DBs
  • Foreign data wrappers — query other databases as if they were Postgres tables
  • Row-Level Security (RLS) — per-row access policies (Supabase’s killer feature)
  • Partitioning — split huge tables into smaller ones, transparently

That’s all in stock Postgres. Then extensions add more.

Extensions — the killer ecosystem

Postgres has a powerful extension system. Some standout ones:

ExtensionWhat it adds
pgvectorVector type for AI embeddings + similarity search
PostGISGeographic / spatial data (the gold standard for GIS)
TimescaleDBTime-series superpowers — automatic partitioning, downsampling, compression
pg_trgmTrigram similarity — fuzzy text search
uuid-osspUUID generation utilities
pg_cronSchedule SQL jobs inside the DB
hstoreKey-value type (mostly superseded by jsonb)
pgcryptoCryptographic functions (hashing, encryption)
citusDistributed Postgres — sharding for massive scale
pglogicalLogical replication enhancements
pg_stat_statementsQuery statistics — essential for tuning

Supabase enables many of these by default. Check your project’s “Extensions” panel.

This is why “just use Postgres” works even for specialized needs. AI embeddings? pgvector. Geographic queries? PostGIS. Time-series at scale? TimescaleDB. All inside one familiar Postgres.

A concrete example: a few Postgres-specific features

Full-text search

-- Create a tsvector index
CREATE INDEX posts_search_idx ON posts USING GIN(to_tsvector('english', title || ' ' || body));
 
-- Search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'react & next.js');

Stems, ranks, handles language nuances. Not as powerful as Elasticsearch but good enough for many apps.

JSON queries

-- Find users whose preferences contain a specific theme
SELECT * FROM users
WHERE preferences->>'theme' = 'dark';
 
-- Update nested JSON
UPDATE users
SET preferences = jsonb_set(preferences, '{theme}', '"light"')
WHERE id = '...';
 
-- Index a JSON path
CREATE INDEX users_theme_idx ON users((preferences->>'theme'));

Vectors (with pgvector)

CREATE EXTENSION vector;
CREATE TABLE documents (
  id uuid PRIMARY KEY,
  content text,
  embedding vector(1536)
);
 
-- Find most similar documents
SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

Same database that holds your users now does AI similarity search. Powerful.

LISTEN / NOTIFY

-- Listener (in app code):
LISTEN new_user;
 
-- Trigger that fires on insert:
CREATE OR REPLACE FUNCTION notify_new_user() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('new_user', NEW.id::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER user_created
AFTER INSERT ON users
FOR EACH ROW EXECUTE FUNCTION notify_new_user();

Now any process listening on new_user gets notified instantly when a user is created. Built-in pub/sub.

Partial indexes

-- Only index published posts
CREATE INDEX posts_published_idx ON posts(published_at)
  WHERE published_at IS NOT NULL;

Smaller index, faster, ignores irrelevant rows.

Hosting Postgres

You can run Postgres anywhere, but for webapps, managed options dominate:

ProviderNotes
SupabasePostgres + Auth + Storage + APIs. Best free tier. Sydney + others.
NeonServerless Postgres with branching. Cheap; pause when idle.
Vercel PostgresPowered by Neon under the hood. Tight Vercel integration.
PlanetScale PostgresStrong horizontal scaling.
AWS RDS / AuroraBattle-tested enterprise option. Pricey but bulletproof.
GCP Cloud SQLGoogle’s managed Postgres.
DigitalOcean / Render / Fly.ioMid-tier managed Postgres.
Self-hostedOn a VPS or Kubernetes. Full control, more ops work.

For solo developers in 2026: Supabase is the no-brainer choice. Free tier, great DX, integrated auth.

Common Postgres-specific idioms

Generating UUIDs

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),  -- built-in
  ...
);

Auto-updated updated_at

CREATE TRIGGER posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION moddatetime(updated_at);

(Or via extensions in Supabase.)

Upserts

INSERT INTO settings (user_id, key, value)
VALUES ('...', 'theme', 'dark')
ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value;

Bulk inserts

INSERT INTO posts (title, author_id)
SELECT 'Post #' || g, '...'
FROM generate_series(1, 1000) g;

Date math

SELECT created_at + interval '30 days' FROM users;
SELECT now() - interval '1 hour';
SELECT date_trunc('month', created_at) FROM users;

Common gotchas

  • Connection limits. Postgres has a max_connections (default 100). Webapps that open a connection per request exhaust quickly. Use a connection pooler (PgBouncer, Supabase’s pooler).

  • SELECT * in production. Wasteful and brittle. List columns.

  • Forgetting timestamptz. Use timezone-aware timestamps always. timestamp (no tz) is dangerous.

  • VACUUM not running. Postgres needs periodic VACUUM to reclaim space and prevent transaction ID wraparound. Autovacuum handles this; if disabled or misconfigured, you’ll have problems.

  • Long-running transactions. Block VACUUM, hold locks. Keep transactions short.

  • Missing indexes. A query on 10M rows without an index = 5+ seconds. Always EXPLAIN slow queries.

  • Over-indexing. Each index slows writes and uses disk. Index thoughtfully.

  • Storing files as bytea. Possible but usually wrong. Store in object storage; put URLs in DB.

  • Default schema search_path. Postgres has schemas (namespaces); the default public is searched first. Confusion can arise with named schemas (Supabase uses auth, storage, etc.).

  • Major version upgrades. Going from 15 to 16, etc., requires a planned upgrade (logical or pg_upgrade). Not as scary as it sounds; Supabase handles this for you.

  • Replication lag. Read replicas can lag behind primary. “I just inserted and the read query doesn’t see it” might be replication, not a bug.

  • pg_dump / pg_restore. The standard backup tools. Use them; verify backups by restoring.

  • JSON vs JSONB. Use jsonb (binary, indexable, faster). json (text) is rarely the right choice.

  • SELECT count(*) is slow on large tables. Postgres can’t use indexes for COUNT without filters. For approximate counts, use pg_class.reltuples or maintain a counter table.

  • Index-only scans require visibility map. Postgres can sometimes answer queries from index alone — but only if the visibility map is up to date (VACUUM matters).

  • Migration scripts that lock tables. ALTER TABLE on a huge table can lock for minutes. Plan migrations carefully (zero-downtime patterns exist).

  • Triggers with side effects. A trigger that does a complex query on every UPDATE can devastate performance. Profile.

  • Floating-point arithmetic. Use numeric for money. 0.1 + 0.2 in real/double precision is approximately 0.3 but not exactly.

  • String matching with LIKE '%foo%'. Can’t use a standard index. Use pg_trgm or full-text search for these patterns.

  • Connection settings (statement_timeout). Without a timeout, a runaway query can block connections forever. Set sensible timeouts.

  • Encoding issues. Postgres defaults to UTF-8; some legacy installations don’t. Verify on setup.

  • PgBouncer transaction pooling and prepared statements. Don’t mix without understanding — broken in subtle ways.

See also

Sources

Graph View

Backlinks