PostgreSQL

Overview

PostgreSQL is an open-source relational database known for strong SQL standards support, reliability and extensibility. It stores data in tables of rows and columns, enforces constraints, supports transactions that follow ACID rules, and offers rich data types including JSON, arrays, ranges and geometric types. Extensions add features such as vector search, time-series handling and geospatial queries. It is often called simply Postgres.

For data teams it is the general-purpose operational database: the place where applications keep their records and where many analysts run their first queries. Because its model is rows and columns, it is a natural source and sink for tabular machine learning, including tabular foundation models. This page explains how PostgreSQL works, shows practical examples and explains its role around models that learn from raw tables. Related pages: Amazon RDS, pgvector, dbt, Apache Spark, Snowflake, HashiCorp Vault and Docker.

What it is

PostgreSQL is a server program. Clients connect over a network or a local socket, send SQL statements and receive results. The server manages storage on disk, memory caches, concurrency, security and recovery.

Core characteristics

• Relational model. Data lives in tables, linked by keys.
• SQL. A rich implementation of the standard, including window functions, common table expressions and many join types.
• ACID transactions. Atomicity, consistency, isolation and durability, so that partial failures do not corrupt data.
• Concurrency without heavy locking. Readers and writers mostly do not block each other thanks to multi-version concurrency control.
• Extensibility. Custom types, functions, operators, index methods and procedural languages.
• Open source. A permissive licence and a large community of contributors.

Data types worth knowing

The JSON story

The jsonb type stores JSON in a binary form that supports indexing and fast lookups. It lets one database serve both strict tables and flexible documents. Many teams use a hybrid: stable fields as columns, variable fields in a jsonb column.

Extensions

An extension is a packaged add-on that you enable per database. Well-known examples include full-text and trigram search helpers, cryptographic functions, foreign data wrappers for querying other systems, time-series and partitioning helpers, geospatial support and vector similarity search. Availability depends on where you run PostgreSQL. Managed services support a curated list.

Where it came from

PostgreSQL grew out of research at a university in California in the 1980s. A project called Ingres led to a successor named Postgres, which explored extensible types, rules and an object-relational model. In the mid-1990s, volunteers added an SQL interpreter and the project became PostgreSQL. Since then, a worldwide community has released new major versions regularly, each adding performance and features.

Milestones in broad strokes

• Early versions focused on correctness and standards.
• Write-ahead logging brought durability and crash recovery.
• Multi-version concurrency control improved concurrent access.
• Replication features arrived over time: streaming physical replication and then logical replication.
• Native partitioning, parallel query and just-in-time compilation for expressions improved large workloads.
• Stored jsonb support made it competitive for document use cases.
• Ongoing improvements to indexing, vacuuming and monitoring continue each release.

Why it became a default

Many developers chose it because it is free, correct, well documented and friendly to extension. Cloud providers offer managed forms, and many analytical and cloud-native systems adopted its wire protocol or SQL dialect for compatibility. As a result, knowing PostgreSQL pays off across many tools.

Governance

The project is run by a community with several core contributors and committers, not by a single company. This lowers the risk of sudden licence changes and has kept the project steady over decades.

How it works

Think of a library with a very careful librarian. Books (rows) are shelved in sections (tables). A card catalog (indexes) helps find books quickly. A logbook (write-ahead log) records every change before it happens, so if the lights go out, the librarian can restore order. Readers are never kept waiting while the librarian reshelves, because old copies of books remain available to them until they leave.

Step 1: Connect and authenticate

A client opens a connection. The server starts a dedicated backend process for it. Authentication uses passwords, certificates or external identity methods. Roles control which objects a user can read or change.

Step 2: Parse, plan and execute

The server parses the SQL, rewrites it where needed and builds a plan. The planner considers different ways to run the query: sequential scan, index scan, hash join, merge join, nested loop. It estimates costs from table statistics and picks the cheapest plan. The executor then runs the plan.

Step 3: Read through shared memory

Data pages are cached in shared buffers. If a page is not in memory, the server reads it from disk. The operating system cache also helps. Good performance often means that the working set fits in memory.

Step 4: Write with write-ahead logging

Before changing a data page, the server writes a description of the change to the write-ahead log (WAL) and flushes it to disk at commit. This means the commit is durable even if the data page has not yet been written. After a crash, the server replays the log to restore a consistent state.

Step 5: Multi-version concurrency control

When a row is updated, PostgreSQL does not overwrite it in place. It writes a new version and marks the old one as no longer current. Each transaction sees a snapshot of the data as of its start. This is why readers do not block writers. The price is that old versions pile up as "dead tuples".

Step 6: Vacuum

A background process called autovacuum removes dead row versions, updates statistics and keeps indexes healthy. Without it, tables bloat and queries slow down. Tuning autovacuum for busy tables is a common task.

Step 7: Indexes

Indexes speed up lookups.

Step 8: Replication and backup

Physical streaming replication copies the WAL to standbys, which can serve read queries. Logical replication copies row changes for chosen tables, which helps migrations and feeding other systems. Backups can be file-level base backups plus archived WAL for point-in-time recovery, or logical dumps for smaller databases.

Step 9: Partitioning

Large tables can be split into partitions by range, list or hash. Queries that filter on the partition key touch only the relevant partitions. Old partitions can be dropped or archived quickly.

An everyday analogy for key ideas

• Transaction: a bank transfer. Either both the debit and the credit happen or neither does.
• Isolation: two cashiers working on separate copies of the ledger that are reconciled safely.
• Index: the index at the back of a book.
• Query plan: choosing a route on a map before you drive.
• Vacuum: a street sweeper that clears old paper from the road.

Key concepts and terms

A worked example

This example creates a small table, queries it, uses jsonb and a window function, and prepares a training set for a model. Names are fake.

Create the table

CREATE TABLE tickets (
    ticket_id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    opened_at        timestamptz NOT NULL DEFAULT now(),
    customer_segment text,
    priority         smallint,
    channel          text,
    details          jsonb,
    resolution_hours numeric(6,2)
);

CREATE INDEX tickets_opened_idx ON tickets (opened_at);
CREATE INDEX tickets_details_gin ON tickets USING gin (details);

Insert and query

INSERT INTO tickets (customer_segment, priority, channel, details, resolution_hours)
VALUES
 ('smb', 2, 'email', '{"product":"alpha","attachments":2}', 5.5),
 ('enterprise', 1, 'phone', '{"product":"beta","attachments":0}', 1.2),
 ('smb', 3, 'chat', '{"product":"alpha"}', NULL);

SELECT customer_segment,
       count(*) AS n,
       avg(resolution_hours) AS avg_hours
FROM tickets
GROUP BY customer_segment;

The avg ignores NULL values, which is the usual SQL behaviour. The third row has no resolution yet, so it does not count toward the average.

Query inside JSON

SELECT ticket_id,
       details->>'product' AS product,
       (details->>'attachments')::int AS attachments
FROM tickets
WHERE details @> '{"product":"alpha"}';

The @> operator uses the GIN index to find rows whose JSON contains the given fragment.

A window function

SELECT ticket_id,
       customer_segment,
       resolution_hours,
       avg(resolution_hours) OVER (PARTITION BY customer_segment) AS segment_avg,
       resolution_hours - avg(resolution_hours) OVER (PARTITION BY customer_segment) AS diff_from_avg
FROM tickets
WHERE resolution_hours IS NOT NULL;

Window functions add the segment average to each row without collapsing rows, which is handy for feature engineering.

Look at the plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tickets
WHERE opened_at >= now() - interval '7 days';

You would look for an index scan or a bitmap scan on tickets_opened_idx, and compare estimated and actual row counts. Large differences suggest stale statistics, and ANALYZE can refresh them.

Prepare a training set

CREATE VIEW ticket_training AS
SELECT priority,
       customer_segment,
       channel,
       coalesce((details->>'attachments')::int, 0) AS attachments,
       resolution_hours
FROM tickets
WHERE resolution_hours IS NOT NULL
  AND opened_at >= now() - interval '180 days';

Pull it into Python

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine("postgresql+psycopg://reader@db.example.internal:5432/support")
df = pd.read_sql("SELECT * FROM ticket_training", engine)
print(df.dtypes)
print(df.isna().mean())

The last line prints the share of missing values per column, a quick quality check before any modelling.

A numeric walk-through of index benefit

Suppose a table has 10 million rows. A sequential scan reads every row, so a lookup by id may touch millions of rows. A B-tree index over 10 million keys has a height of only about three or four levels, so a lookup touches a handful of pages. That is the difference between milliseconds and seconds, and the gap widens as the table grows.

A transaction example

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the second update fails, you issue ROLLBACK and neither change is kept.

Strengths and limitations

Strengths

• Correctness and standards. Strong constraints, transactions and SQL features.
• Extensibility. Types, indexes and extensions let it adapt to new needs.
• JSON and relational together. Flexible and strict data in one system.
• Mature tooling. Backup, monitoring, migration and client libraries are widely available.
• Open source with no lock-in. Run it anywhere: laptop, container, virtual machine, managed service.
• Strong community. Documentation and answers are easy to find.
• Good analytical SQL. Window functions, CTEs, lateral joins and grouping sets.

Limitations

• Vertical write scaling. A single primary handles writes. Horizontal sharding needs extra tools or design.
• Vacuum and bloat. Heavy update workloads need tuning and monitoring.
• Connection model. Each connection uses a process, so thousands of direct connections need a pooler.
• Large analytical scans. Row storage is not ideal for scanning billions of rows across many columns. Columnar systems do better.
• Operational skill needed. Self-managed clusters require knowledge of backups, replication and tuning.
• Major upgrades need planning. They require tools and testing.
• Planner surprises. Poor statistics or odd data skew can produce slow plans.

How it compares with alternatives

A practical reading: use PostgreSQL for transactions and moderate analytics, copy data to a columnar system for large-scale analysis, and use both in the same design.

Role in tabular foundation models

Tabular foundation models learn from raw tables: rows, columns, mixed types, missing values and, often, no useful column names. They are pre-trained on synthetic or real tables and use in-context learning, where a few labelled rows given at prediction time guide the output without retraining. See /model/architecture and /model/research. PostgreSQL connects to this in several practical ways.

The same shape of data

A PostgreSQL table is the data structure these models are designed for. Columns have declared types, which makes it easy to tell numeric from categorical fields. NULL represents missing values, and many tabular models treat missingness as information. Constraints and keys document how tables relate.

Building the training and context tables

Models usually want one flat table. In PostgreSQL, you build it with joins, aggregates and window functions, then expose it as a view or a materialized view. Tools like dbt can manage these transformations as versioned code. For in-context learning, a query selects a small, relevant set of labelled rows, for example the most recent labelled cases for the same customer segment.

Imputation

When values are missing, SQL can do simple fills such as coalesce or group means via window functions. A tabular model can do better by predicting the missing cell from the rest of the row. Store the model's imputed values in a separate column or table, and keep the original NULL so you can always see what was observed.

Anomaly detection

Transaction tables, audit tables and log tables are ideal for anomaly scoring. A scheduled job reads new rows, scores them with a model and writes an anomaly score back. PostgreSQL's own statistics views, which are tables too, can be scored to find unusual query or connection patterns.

Sector and semantic classification

Text or code columns, such as company descriptions or product titles, can be classified into sectors or semantic categories. Results go into a lookup or results table keyed by the entity id. Trigram and full-text indexes help find candidates, and embeddings stored with pgvector help find near neighbours.

Continual learning

Models that are updated over time need to know which rows are new. Add an updated_at timestamp with an index, or use logical replication to stream changes to a lake. Store a watermark of the last processed change. This lets each update see each row once.

Feature storage and serving

Small feature stores can be built in PostgreSQL: a table keyed by entity id with the latest feature values, refreshed by jobs. For low-latency serving at moderate scale this is often enough. For large scale, a dedicated store or cache may be needed.

Governance and privacy

Row-level security policies, column privileges and views allow fine-grained control over what a training job can read. Pair these with de-identification jobs such as Spark UDF de-identification, short-lived credentials from HashiCorp Vault and encryption key control as covered under Thales.

Local development

Docker makes it easy to run a throwaway PostgreSQL for experiments, tests and demos, so model code can be tested against a real database.

A common architecture

1. Applications write to PostgreSQL.
2. Change data flows to object storage or a lakehouse for heavy analytics.
3. Feature tables are built by SQL jobs.
4. A tabular model scores or adapts using rows from these tables.
5. Predictions are written back to PostgreSQL results tables.

This is a general pattern, not a claim of any built-in integration. For benchmark context, see /model/benchmarks.

Practical guidance and best practices

Model your data carefully

Use proper types: timestamptz for times, numeric for exact values, boolean for flags, text for strings. Add primary keys and foreign keys. Constraints catch errors early.

Index for real queries

Look at slow queries with EXPLAIN. Add indexes that match filters and joins. Use partial indexes for hot subsets and covering indexes to avoid table lookups. Remove unused indexes, since they slow writes.

Keep statistics fresh

Autovacuum normally runs ANALYZE. After large loads, run it yourself. Good statistics lead to good plans.

Tune autovacuum for busy tables

Lower thresholds for hot tables so cleanup happens often and in small amounts. Monitor dead tuple counts and table bloat.

Use connection pooling

A pooler in front of the database keeps connection counts low and stable. This matters for serverless functions and notebooks.

Bound your queries

Use LIMIT, date filters and pagination. For large extracts to a model, use keyset pagination or server-side cursors, not huge offsets.

Choose the right JSON strategy

Keep frequently filtered fields as real columns. Use jsonb for variable parts. Add GIN indexes for containment queries only when needed.

Partition very large tables

Partition by time for event data. Drop or archive old partitions instead of deleting millions of rows.

Secure the instance

Use strong authentication, TLS, least-privilege roles and row-level security where useful. Do not expose the port to the internet.

Back up and test restores

Use base backups plus WAL archiving, or a managed service that does it for you. Test recovery on a schedule.

Use read replicas for heavy reads

Point reports and training extracts at a replica, and remember that replicas can lag.

Version your schema

Use migrations stored in version control. Apply them in order in every environment.

Monitor

Track connections, cache hit ratio, slow queries, replication lag, disk space, dead tuples and locks. Alert on trends before they become outages.

Common mistakes

• Using float for money. Use numeric to avoid rounding surprises.
• Storing times without a zone. Prefer timestamptz and store in UTC.
• Missing indexes on foreign keys. Joins and cascading deletes become slow.
• Too many indexes. Each one adds write cost.
• Ignoring vacuum. Bloat leads to slow queries and wasted space.
• Long-running transactions. They hold back vacuum and can cause bloat and lock trouble.
• SELECT * in application code. It transfers needless data and breaks when columns change.
• Treating jsonb as an excuse for no schema. Unstructured data makes queries and quality harder.
• Direct connections from thousands of clients. Use a pooler.
• Large OFFSET pagination. It rescans skipped rows; use keyset pagination.
• Running heavy analytics on the primary. Use replicas or a warehouse.
• Overwriting source facts with predictions. Store model outputs separately with version and time.
• Skipping backup tests. Untested backups may fail when needed.
• Superuser everywhere. Give applications limited roles.
• Forgetting about NULL logic. Comparisons with NULL yield unknown, not true or false.

Frequently asked questions

What is PostgreSQL used for?

PostgreSQL is used as a general-purpose relational database for web applications, internal systems, analytics on moderate data volumes, geospatial work and as a backend for data pipelines. Its features, such as transactions, JSON support and extensions, let it serve many roles in one system.

Is PostgreSQL the same as Postgres?

Yes. Postgres is a common short name for PostgreSQL. The project name is PostgreSQL, and many tools and clients use the word postgres in commands and connection strings.

What does MVCC mean and why does it matter?

MVCC stands for multi-version concurrency control. PostgreSQL keeps multiple versions of a row so that each transaction sees a consistent snapshot, and readers do not block writers. The trade-off is that old versions must be cleaned up by vacuum, so busy tables need healthy autovacuum settings.

Should I store JSON in PostgreSQL?

Yes, when parts of your data vary or are naturally nested. Use the jsonb type, which supports indexing and efficient queries. Keep important, frequently filtered fields as normal columns, and avoid putting everything into one JSON blob.

How does PostgreSQL differ from a data warehouse?

PostgreSQL stores rows and is optimized for transactions and quick lookups, while a warehouse stores columns and is optimized for scanning large volumes for analytics. PostgreSQL handles moderate analytics well, but at very large scale a columnar system is usually a better fit.

Can I use PostgreSQL for machine learning data?

Yes. It is often the source of training data, and with SQL you can build features using joins and window functions. For very large datasets, copy data to a columnar store or lake. Model outputs such as predictions and imputed values can be written back to separate tables.

How do tabular foundation models relate to PostgreSQL?

These models learn from rows and columns, which is the structure of a PostgreSQL table. A query can select the rows used for training or for in-context examples, NULL values map to missing data and predictions are written back as new columns or tables. PostgreSQL does not include the model; it supplies and stores the data around it.

What is pgvector and do I need it?

It is an extension that adds a vector type and similarity search to PostgreSQL, useful for embeddings. You need it only if you store embeddings, for example for semantic search on text columns. See pgvector for details. For plain tabular work you do not need it.

How do I make PostgreSQL highly available?

Use streaming replication with a standby and an automated failover mechanism, or use a managed service that provides it. Test failover, and make sure clients reconnect with retries. Keep backups and WAL archives as well, since replication alone does not protect against accidental deletes.

How do I upgrade major versions?

Plan the upgrade: read release notes, test on a restored copy, and choose a method such as dump and restore, in-place upgrade tooling or logical replication for low downtime. Keep a rollback plan and check extension compatibility before you start.

Glossary

• ACID: Atomicity, consistency, isolation, durability.
• Autovacuum: Background process that cleans dead rows and updates statistics.
• B-tree: The default ordered index type.
• CTE: Common table expression, a named subquery.
• Dead tuple: An old row version no longer visible to any transaction.
• Extension: A packaged add-on.
• Foreign key: A constraint linking rows in two tables.
• GIN: An index type for multi-valued data such as arrays and jsonb.
• Index: A structure that speeds up lookups.
• jsonb: Binary JSON type with indexing support.
• Keyset pagination: Paging by the last seen key instead of an offset.
• Logical replication: Replication of row changes for selected tables.
• Materialized view: A stored query result.
• MVCC: Multi-version concurrency control.
• Partition: A piece of a large table.
• Planner: The component that chooses a query plan.
• Primary key: The unique identifier of a row.
• Role: A user or group with privileges.
• Row-level security: Policies that limit which rows a role can see.
• Schema: A namespace inside a database.
• Vacuum: Cleanup of dead rows.
• WAL: Write-ahead log.
• Window function: A calculation across related rows without collapsing them.

Summary

PostgreSQL is a mature, open-source relational database with strong SQL, ACID transactions, MVCC, rich data types, jsonb and a large extension ecosystem. It works by planning queries over indexed row storage, protecting changes with a write-ahead log and cleaning old row versions with vacuum. It is the usual general-purpose operational database, available self-managed or through services such as Amazon RDS. For tabular foundation models, it provides the row-and-column data that these models learn from and score, supplies labelled rows for in-context learning, keeps NULLs as missing-value signals and stores predictions, imputed values and anomaly scores in separate versioned tables. Good practice means sound types and keys, indexes tuned with EXPLAIN, healthy vacuum, pooling, replicas for heavy reads, tested backups and careful privacy controls. For the model side, see /model/research, /model/architecture and /model/benchmarks.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: