postgresql

PostgreSQL

PostgreSQL (Postgres) is a powerful, open-source object-relational database system. It is known for its reliability, feature robustness (JSONB, GIS, Full Text Search), and performance.

When to Use

• The Default Advanced Relational Database: The standard, most versatile open-source relational database for modern software engineering.
• Semi-Structured Document Storage (JSONB): Storing, indexing, and querying JSON documents with GIN indexes and JSONPath expressions.
• AI & Vector Embeddings (pgvector): Storing high-dimensional vector embeddings and executing cosine/L2 semantic similarity search.
• Geospatial Applications (PostGIS): Analyzing spatial geometries, geographic boundaries, and GPS coordinates with industry-standard PostGIS.

Quick Start

-- JSONB column usage
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES ('{"type": "login", "user": "alice"}');

-- Query JSON directly (indexing supported)
SELECT * FROM events WHERE data->>'user' = 'alice';

Core Concepts

Advanced JSONB & Generalized Inverted Indexes (GIN)

JSONB stores binary decomposed JSON with full indexing support:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    profile JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create GIN Index over entire JSONB structure
CREATE INDEX idx_users_profile ON users USING GIN (profile);

-- Fast sub-millisecond containment search
SELECT * FROM users
WHERE profile @> '{"role": "admin", "settings": {"notifications": true}}';

pgvector Semantic Similarity Search

Integrates AI vector embeddings directly alongside relational tables:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536) -- OpenAI embedding dimension
);

-- Create HNSW index for sub-5ms approximate nearest neighbor search
CREATE INDEX idx_embeddings_hnsw ON document_embeddings
USING hnsw (embedding vector_cosine_ops);

-- Find top 5 most semantically similar documents
SELECT id, content, 1 - (embedding <=> '[0.012, -0.045, ...]') AS similarity
FROM document_embeddings
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 5;