# Introduction to PostgreSQL

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

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2019-12-29 | Updated: 2026-07-18 | Topics: [Database](https://flaviocopes.com/tags/database/) | Canonical: https://flaviocopes.com/postgres-introduction/

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:

- transactions with atomic commit and rollback
- foreign keys and constraints
- joins, subqueries, common table expressions, and window functions
- multiversion concurrency control
- views, triggers, and stored functions
- indexes for common and specialized query patterns
- JSON and JSONB alongside relational columns
- full-text search
- user-defined types, functions, operators, and extensions

The official [What is PostgreSQL?](https://www.postgresql.org/docs/current/intro-whatis.html) 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:

```text
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:

```sql
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:

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

Query published posts:

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

PostgreSQL's [current manual](https://www.postgresql.org/docs/current/) is organized by concepts, SQL commands, administration, client tools, and reference material.

## Transactions and concurrency

A transaction groups statements into one unit:

```sql
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:

```sql
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:

```sql
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](https://www.postgresql.org/support/versioning/) instead of copying a version number from an old tutorial.

## Continue learning

- [How to install PostgreSQL](https://flaviocopes.com/postgres-how-to-install/)
- [PostgreSQL user permissions](https://flaviocopes.com/postgres-user-permissions/)
- [How to list all users in PostgreSQL](https://flaviocopes.com/postgres-how-to-list-all-users/)
- [How to list all databases using PostgreSQL](https://flaviocopes.com/postgres-how-to-list-all-databases/)
- [How to switch databases using PostgreSQL](https://flaviocopes.com/postgres-how-to-switch-database/)
- [How to create a PostgreSQL database](https://flaviocopes.com/postgres-create-database/)
- [Introduction to SQL](https://flaviocopes.com/sql-introduction/)