EXPLAINER

PostgreSQL, the Relational Database

PostgreSQL (Postgres, often shortened to PG) is an open-source relational database. It stores tables with constraints, speaks SQL, and can add extensions such as pgvector. It is a strong default system of record for applications.

Short answer: PostgreSQL (Postgres, PG) is a relational database: SQL, transactions, and extensions like pgvector. Using it beside an AI feature is covered in Using PostgreSQL in AI Applications. The database itself is this page.

What it is

PostgreSQL, usually called Postgres and sometimes PG, is an open-source relational database. You define tables, constraints and indexes. You query them with SQL. Transactions are real: a group of writes commits together or not at all. It has been developed in the open for decades and is a default choice for new application databases, from small services to large ones.

Postgres is a specific engine, not a synonym for "SQL". MySQL and SQLite are other engines. Using PostgreSQL in AI Applications explains storing chats and optional embeddings next to a model. This page is the database you are choosing when someone asks what Postgres is.

Why and when it is used

Choose Postgres when you need a system of record: users, orders, documents, permissions, billing. Choose it when you want constraints the database enforces (foreign keys, unique emails, check constraints) rather than hoping every service remembers. Choose it when one box, or one managed instance, should hold the data and you want room to grow without a new product.

Add a specialised store beside it when you must: Redis for ephemeral cache and rate limits, a vector database or the pgvector extension for similarity search, object storage for big blobs. Do not start with three databases because a diagram showed them. Start with Postgres until a measurement says a table is the wrong shape.

How it works

A server process accepts connections. Clients speak the Postgres protocol, usually through a driver that uses parameters ($1) rather than pasted strings. The planner chooses how to run each query. Indexes (B-tree by default, others when you know why) keep lookups from scanning the whole table. WAL (the write-ahead log) records changes so a crash can recover. VACUUM reclaims space from old row versions; autovacuum does this for you and still needs watching on very busy tables.

Roles and grants are the permission model. An application should connect as a role that cannot drop the database. Extensions add capabilities inside the same server. pgvector stores embedding vectors and indexes them. That does not replace a relational schema; it adds a column and an index. Managed services (cloud vendors' Postgres) handle backups and failover if you configure them. They do not write your indexes.

Technologies and dependencies

A Postgres server (local install, container, or a managed instance), a client such as psql, and a driver in your language. Migrations live in git. Prisma and other ORMs can target Postgres. Connection pooling (PgBouncer or the platform's pooler) matters once many app instances open connections, because each connection is a process-like cost. Environment variables should hold the URL, not the source tree. SQL versus NoSQL is the neighbouring decision.

How to get started

  1. Run Postgres locally or in Docker. Create a database and a role with a password. Do not use the superuser in the app.
  2. Create two related tables and a foreign key. Insert rows with psql.
  3. Connect from your application with a parameterised query. Print the plan of the one query you will run most (EXPLAIN).
  4. Add a migration tool so the next column is a file, not a click.
  5. Take a backup and restore it once while the stakes are zero.
  6. If you need embeddings later, read Using PostgreSQL in AI Applications before you add pgvector. Often you do not need it on week one.

Write the major version down. Extensions and syntax differ across majors. Upgrade on purpose.

Cautions and trade-offs

A missing index is the usual outage, not the database "being down". Look at slow queries before you buy a larger machine. Long transactions block vacuum and bloat tables. An ORM that loads entire graphs will melt a healthy server.

Postgres is not a queue, a cache, or a search engine, though people stretch it into all three. Stretching works until it does not; know which you are doing. Logical replication and read replicas are operational commitments. And a managed "Postgres compatible" service is mostly Postgres until you hit the feature they omitted. Test the extension and the migration path you actually need, especially pgvector versions.