Skip to content
FLAVIO COPES
flaviocopes.com
2026

Introduction to PostgreSQL

By

An introduction to PostgreSQL, the open source object-relational database: SQL, transactions, concurrency, data types, extensions, and version support.

~~~

PostgreSQL, often called Postgres, is an open source object-relational database management system.

It stores related data in tables and uses SQL for defining, querying, and changing that data. It also extends the SQL model with rich data types, functions, indexes, and an extension system.

PostgreSQL can be used, modified, and distributed for private, commercial, or academic purposes under its permissive license.

Why use PostgreSQL?

PostgreSQL is a general-purpose database suited to applications that need reliable transactions and structured data.

Its core features include:

The official What is PostgreSQL? page summarizes the project’s capabilities.

PostgreSQL is a server

PostgreSQL runs as a database server process.

Applications connect to it over a local socket or network connection, authenticate as a database role, select a database, and send SQL statements.

A connection URL commonly looks like:

postgresql://username:password@localhost:5432/app

Do not commit real database passwords. Store them in environment variables or a secret manager, use TLS for untrusted networks, and grant each application only the permissions it needs.

SQL and PostgreSQL

SQL is the language. PostgreSQL is one database system that implements a large part of the SQL standard and adds PostgreSQL-specific features.

Create a table:

CREATE TABLE posts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  published_at timestamptz
);

Insert a row and return its generated ID:

INSERT INTO posts (title)
VALUES ('Hello PostgreSQL')
RETURNING id;

Query published posts:

SELECT id, title, published_at
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC;

PostgreSQL’s current manual is organized by concepts, SQL commands, administration, client tools, and reference material.

Transactions and concurrency

A transaction groups statements into one unit:

BEGIN;

UPDATE accounts
SET balance = balance - 50
WHERE id = 1;

UPDATE accounts
SET balance = balance + 50
WHERE id = 2;

COMMIT;

If a statement fails or the operation should not continue, use ROLLBACK.

PostgreSQL uses multiversion concurrency control so readers and writers can often work concurrently without blocking each other unnecessarily. Transaction isolation still matters: choose it according to the consistency rules of the application.

Schemas are namespaces

One PostgreSQL database can contain multiple schemas.

The default schema is usually public. A qualified table name includes the schema:

SELECT *
FROM public.posts;

Do not confuse a PostgreSQL schema namespace with an ORM schema file. They are related concepts but not the same thing.

Extensions

Extensions package additional database objects and behavior.

For example, a supported installation can enable an extension with:

CREATE EXTENSION extension_name;

Available extensions depend on the PostgreSQL installation or managed provider. Enabling one changes the database and may affect portability, upgrades, and backups, so review its documentation first.

Version support

PostgreSQL releases a new major version about once a year and supports each major version for five years.

Run a supported major version and keep it on its current minor release for bug and security fixes. Minor upgrades are designed differently from major upgrades; major upgrades require an upgrade procedure such as pg_upgrade or dump and restore.

Check the live PostgreSQL versioning policy instead of copying a version number from an old tutorial.

Continue learning

Tagged: Database · All topics
~~~

Related posts about database: