PostgreSQL Tutorial

PostgreSQL, usually called Postgres, is a free and open-source object-relational database. It is known for strict data correctness, full ACID transactions, standards-compliant SQL and an unusual degree of extensibility: you can add your own data types, functions, index methods and even whole features through extensions.

This tutorial explains how PostgreSQL works and how to use it well. It covers the server architecture, how MVCC lets readers and writers work without blocking each other, data types (including JSONB), constraints, indexes, transactions, query tuning with EXPLAIN, replication, and connecting from Java. Every section includes small, runnable examples.

1. What Is PostgreSQL?

PostgreSQL is a relational database management system (RDBMS). It stores data in tables of rows and columns, links tables with keys and lets you query them with SQL. It is called object-relational because it also supports ideas from object-oriented programming, such as custom data types, table inheritance and functions that can be written in several languages.

What makes PostgreSQL stand out is its combination of reliability and flexibility:

• Correctness first: every change happens inside a transaction, constraints are always enforced, and committed data survives crashes.
• Rich SQL: window functions, common table expressions, MERGE, lateral joins and JSON querying are all built in.
• Extensible: extensions such as PostGIS (maps and geography), pgvector (AI embeddings) and pg_stat_statements (query statistics) add major capabilities without changing the core server.
• Truly open: it is developed by an independent global community and released under the permissive PostgreSQL License, so no single company controls it.

Because of this, PostgreSQL is used for everything from small web applications to banking systems, analytics platforms and geographic information systems.

2. A Short History of PostgreSQL

PostgreSQL grew out of the POSTGRES research project, started in 1986 at the University of California, Berkeley, by Professor Michael Stonebraker. The name meant "post-Ingres", because it followed his earlier Ingres database.

A new major version is released every year, usually in September or October, and each major version receives bug and security fixes for five years.

3. Key Features of PostgreSQL

4. PostgreSQL Architecture

PostgreSQL uses a process-per-connection model. Understanding its main parts helps you configure and troubleshoot it.

4.1 Processes

• Postmaster: the main server process. It listens on port 5432 and starts a new backend process for each client connection.
• Backend processes: each one parses, plans and executes the SQL of a single connection.
• Background processes: the checkpointer and background writer flush changed pages to disk, the WAL writer writes the transaction log, autovacuum cleans up old row versions, and WAL senders stream changes to replicas.

Because every connection is a separate operating-system process, thousands of idle connections waste memory. Applications therefore use a connection pool, such as HikariCP inside the application or PgBouncer in front of the database.

4.2 Memory

• shared_buffers: the shared cache of table and index pages (8 KB each). A common starting value is about 25% of the server's RAM.
• WAL buffers: hold transaction log records until they are written to disk.
• work_mem: memory per sort or hash operation in a query. Each query may use several multiples of it, so raise it carefully.
• maintenance_work_mem: memory for maintenance tasks such as VACUUM and CREATE INDEX.

4.3 The write-ahead log (WAL)

Before a change to a data page is written to disk, a description of the change is first written to the write-ahead log. When a transaction commits, PostgreSQL only has to make sure its WAL records are safely on disk, which is a fast sequential write. The changed data pages can be written later. If the server crashes, it replays the WAL from the last checkpoint to restore every committed change. The same WAL is used for replication and point-in-time recovery.

5. MVCC and VACUUM

PostgreSQL handles concurrent users with Multi-Version Concurrency Control (MVCC). Instead of changing a row in place, an UPDATE writes a new version of the row and marks the old one as expired. Each row version records which transaction created it (xmin) and which transaction deleted or replaced it (xmax).

Every transaction works with a snapshot: a record of which transactions were committed when it started. It then sees only the row versions that were valid for that snapshot. As a result:

• A long report can read consistent data while other users keep updating the same rows.
• Readers do not need to lock rows, so they never wait for writers.
• Writers only wait for other writers changing the same row.

5.1 Why VACUUM matters

Expired row versions, called dead tuples, stay in the table until VACUUM removes them and makes the space reusable. VACUUM also updates planner statistics (with ANALYZE) and protects against transaction ID wraparound, a limit that would otherwise stop the database from accepting writes.

The autovacuum daemon does this automatically, and it should almost never be turned off. Tables with very heavy updates may need more aggressive autovacuum settings so that they do not grow "bloated" with dead tuples.

-- Tables with the most dead row versions and when they were last vacuumed
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Clean up one table manually and refresh its statistics
VACUUM (ANALYZE, VERBOSE) orders;

6. Running PostgreSQL Locally with Docker

The fastest way to get a working server is the official Docker image:

# Start PostgreSQL 18 on port 5432 with a password for the 'postgres' superuser
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:18

# Open the interactive psql shell inside the container
docker exec -it pg psql -U postgres

# Or connect from your own machine if psql is installed
psql -h localhost -U postgres

For real projects, mount a volume so your data survives when the container is removed, and check the image documentation for the correct volume path for your PostgreSQL version.

7. Databases, Schemas, Roles and psql

A PostgreSQL server (often called a cluster) is organised in layers:

-- Run as the postgres superuser
CREATE ROLE shop_app WITH LOGIN PASSWORD 'change-me';
CREATE DATABASE shop OWNER shop_app;

\c shop shop_app          -- psql: connect to the new database as the new role

CREATE SCHEMA sales;
SET search_path TO sales, public;   -- look in 'sales' first for unqualified names

Useful psql meta-commands, which start with a backslash:

• \l lists databases and \c dbname connects to one.
• \dn lists schemas, \dt lists tables and \d tablename describes a table.
• \du lists roles.
• \x toggles expanded output, which is handy for wide rows.
• \timing shows how long each query takes.
• \i file.sql runs a script and \q quits.

8. Data Types Worth Knowing

9. SQL Basics with Examples

The examples below build a small online shop. Run them in psql connected to the shop database.

9.1 Create tables with constraints

CREATE TABLE customers (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  full_name   text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL,
  price       numeric(10, 2) NOT NULL CHECK (price >= 0),
  attributes  jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),
  status       text NOT NULL DEFAULT 'PENDING'
               CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
  placed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id  bigint NOT NULL REFERENCES products (id),
  quantity    int    NOT NULL CHECK (quantity > 0),
  unit_price  numeric(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Constraints are your safety net: NOT NULL, UNIQUE, CHECK and REFERENCES (foreign keys) are enforced by the database on every change, no matter which application or script makes it.

9.2 Insert, query, update and delete

-- RETURNING gives back generated values without a second query
INSERT INTO customers (email, full_name)
VALUES ('meera@example.com', 'Meera Iyer')
RETURNING id, created_at;

INSERT INTO products (sku, name, price, attributes) VALUES
  ('KET-200', 'Steel Kettle', 1499.00, '{"colour": "silver", "litres": 1.7}'),
  ('MUG-010', 'Ceramic Mug',   299.00, '{"colour": "blue", "dishwasher_safe": true}');

-- Join tables and aggregate
SELECT c.full_name, o.id AS order_id, SUM(i.quantity * i.unit_price) AS total
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items i  ON i.order_id = o.id
GROUP BY c.full_name, o.id
ORDER BY total DESC;

UPDATE products SET price = price * 0.90 WHERE sku = 'MUG-010' RETURNING name, price;

DELETE FROM orders WHERE status = 'CANCELLED' AND placed_at < now() - interval '1 year';

9.3 Upserts with ON CONFLICT

An upsert inserts a row, or updates it if it already exists, in one atomic statement:

INSERT INTO products (sku, name, price)
VALUES ('KET-200', 'Steel Kettle (2026 model)', 1599.00)
ON CONFLICT (sku)
DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

EXCLUDED refers to the row that you tried to insert. For more complex synchronisation, such as applying a whole batch of changes from a staging table, use the SQL standard MERGE command (PostgreSQL 15 and later).

10. Powerful SQL Features

10.1 Querying JSONB

-- ->> returns a field as text; @> tests whether the document contains a JSON fragment
SELECT name, attributes ->> 'colour' AS colour
FROM products
WHERE attributes @> '{"dishwasher_safe": true}';

-- A GIN index makes containment queries fast on large tables
CREATE INDEX products_attributes_gin ON products USING gin (attributes);

-- Change one key without rewriting the whole document in the application
UPDATE products
SET attributes = jsonb_set(attributes, '{colour}', '"black"')
WHERE sku = 'KET-200';

JSONB is ideal for attributes that vary from row to row. Keep core fields that you filter, join or constrain on as normal columns, and put the flexible extras in JSONB.

10.2 Common table expressions and window functions

-- Each customer's orders with a running total and a rank by order value
WITH order_totals AS (
  SELECT o.customer_id, o.id AS order_id, o.placed_at,
         SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  GROUP BY o.customer_id, o.id, o.placed_at
)
SELECT customer_id, order_id, placed_at, total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total,
       RANK()     OVER (PARTITION BY customer_id ORDER BY total DESC) AS value_rank
FROM order_totals
ORDER BY customer_id, placed_at;

A common table expression (the WITH part) names an intermediate result so a long query reads top to bottom. Window functions calculate values across related rows, such as running totals, rankings and moving averages, without collapsing them as GROUP BY would.

10.3 Full-text search

ALTER TABLE products
  ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;

CREATE INDEX products_search_idx ON products USING gin (search);

SELECT name, ts_rank(search, query) AS rank
FROM products, websearch_to_tsquery('english', 'steel kettles') AS query
WHERE search @@ query
ORDER BY rank DESC;

The English configuration reduces words to their stems, so a search for "kettles" also finds "Kettle". websearch_to_tsquery accepts the kind of input users type into a search box, including quotes and a minus sign to exclude words.

10.4 Functions and triggers

ALTER TABLE orders ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_touch
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Triggers are useful for small, universal rules such as timestamps and audit records. Keep business logic that changes often in your application, where it is easier to test and deploy.

11. Indexes and Query Tuning

An index lets PostgreSQL find rows without scanning the whole table. Choosing the right type matters:

-- Foreign keys are NOT indexed automatically; index them for joins and deletes
CREATE INDEX orders_customer_idx ON orders (customer_id);

-- Multicolumn index: filter by customer, then sort by date
CREATE INDEX orders_customer_placed_idx ON orders (customer_id, placed_at DESC);

-- Partial index: only the rows a hot query actually needs
CREATE INDEX orders_pending_idx ON orders (placed_at) WHERE status = 'PENDING';

-- Expression index for case-insensitive lookups
CREATE INDEX customers_email_lower_idx ON customers (lower(email));

-- Build an index on a busy production table without blocking writes
CREATE INDEX CONCURRENTLY order_items_product_idx ON order_items (product_id);

11.1 Reading EXPLAIN ANALYZE

EXPLAIN shows the plan PostgreSQL chooses for a query, and EXPLAIN ANALYZE actually runs it and reports real timings and row counts:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, placed_at
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 10;

Things to look for in the output:

• Seq Scan on a large table where you expected an Index Scan: a missing or unusable index.
• A big difference between estimated rows and actual rows: outdated statistics, so run ANALYZE.
• Sort nodes that spill to disk (external merge): work_mem may be too small, or an index could provide the order.
• Many shared read buffers: data is coming from disk rather than the cache.

12. Transactions and Isolation Levels

A transaction groups statements so they succeed or fail together. Here, money moves between two wallets, and nobody can ever see the money missing from both:

CREATE TABLE wallets (
  id      bigint PRIMARY KEY,
  balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO wallets VALUES (1, 1000.00), (2, 250.00);

BEGIN;
UPDATE wallets SET balance = balance - 300 WHERE id = 1;
UPDATE wallets SET balance = balance + 300 WHERE id = 2;
COMMIT;      -- or ROLLBACK; to undo both changes

-- Lock a row you are about to change, so concurrent transactions wait their turn
BEGIN;
SELECT balance FROM wallets WHERE id = 1 FOR UPDATE;
-- ... check the balance in the application, then update ...
COMMIT;

If either update breaks the CHECK constraint, the whole transaction fails and neither balance changes. PostgreSQL also supports SAVEPOINT to roll back part of a transaction.

PostgreSQL accepts READ UNCOMMITTED but treats it as Read Committed. Thanks to MVCC, it never shows uncommitted data from other transactions.

13. Backup, Replication and Scaling

13.1 Backups

• Logical backups with pg_dump (one database) or pg_dumpall (the whole cluster) export SQL or archive files. Restore them with psql or pg_restore. They are simple and portable between versions.
• Physical backups with pg_basebackup copy the data files. Combined with archived WAL, they allow point-in-time recovery: restoring the database to any moment, such as one second before an accidental DELETE. PostgreSQL 17 added incremental physical backups.

# Compressed custom-format dump of one database
pg_dump -h localhost -U postgres -Fc -f shop.dump shop

# Restore it into a new, empty database using 4 parallel jobs
createdb -h localhost -U postgres shop_restored
pg_restore -h localhost -U postgres -d shop_restored -j 4 shop.dump

13.2 Replication

• Streaming (physical) replication sends WAL from a primary to one or more standby servers, which can serve read-only queries. Replication can be asynchronous (faster) or synchronous (no data loss on failover).
• Logical replication publishes changes to selected tables, which other PostgreSQL servers subscribe to. It is used for upgrades with minimal downtime, data distribution and feeding other systems.

PostgreSQL does not include automatic failover itself. Tools such as Patroni, or a managed cloud service, monitor the primary and promote a standby when it fails.

13.3 Scaling

A single PostgreSQL server goes a long way with good indexes, connection pooling and enough memory. Beyond that, common steps are read replicas for read-heavy traffic, declarative partitioning to split huge tables by range, list or hash, and, for very large write workloads, sharding with extensions such as Citus.

14. Connecting to PostgreSQL from Java

Java applications use the PostgreSQL JDBC driver. Add it with Maven (check Maven Central for the latest 42.x version):

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.7.4</version>
</dependency>

The program below inserts a customer inside a transaction and reads it back. It uses PreparedStatement parameters, which prevent SQL injection and let the server reuse query plans.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CustomerDemo {

    private static final String URL = "jdbc:postgresql://localhost:5432/shop";

    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "shop_app", "change-me")) {
            conn.setAutoCommit(false);                       // start an explicit transaction

            long id;
            try (PreparedStatement insert = conn.prepareStatement(
                    "INSERT INTO customers (email, full_name) VALUES (?, ?) RETURNING id")) {
                insert.setString(1, "ravi@example.com");
                insert.setString(2, "Ravi Menon");
                try (ResultSet rs = insert.executeQuery()) {
                    rs.next();
                    id = rs.getLong("id");
                }
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();                             // undo everything on failure
                throw e;
            }

            try (PreparedStatement select = conn.prepareStatement(
                    "SELECT full_name, created_at FROM customers WHERE id = ?")) {
                select.setLong(1, id);
                try (ResultSet rs = select.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getString("full_name") + " joined at "
                            + rs.getObject("created_at", java.time.OffsetDateTime.class));
                    }
                }
            }
        }
    }
}

In a Spring Boot application, add spring-boot-starter-data-jpa (or spring-boot-starter-jdbc) and the driver, then configure the connection. Spring Boot uses the HikariCP connection pool by default:

spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=shop_app
spring.datasource.password=change-me
spring.datasource.hikari.maximum-pool-size=10
# Let a migration tool such as Flyway or Liquibase manage the schema in production
spring.jpa.hibernate.ddl-auto=validate

15. PostgreSQL vs. MySQL

Both are excellent open-source relational databases. The differences are mostly about emphasis:

If you are learning both, the MySQL tutorial covers MySQL in depth, and the MySQL vs. PostgreSQL article compares them further.

16. Best Practices and Common Mistakes

Best practices

• Use the right types: timestamptz for time, numeric for money and identity columns or UUIDs for keys.
• Let the database protect your data with NOT NULL, foreign keys, UNIQUE and CHECK constraints.
• Index foreign keys and the columns used in frequent filters, joins and sorts, then check plans with EXPLAIN ANALYZE.
• Use a connection pool and keep transactions short.
• Manage schema changes with migrations (Flyway or Liquibase), and use CREATE INDEX CONCURRENTLY on live tables.
• Give each application its own role with only the privileges it needs; never connect as the superuser.
• Test your backups by restoring them regularly.
• Stay on a supported major version and apply minor releases promptly.

Common mistakes to avoid

17. Summary

• PostgreSQL is a free, community-developed, object-relational database known for correctness, rich SQL and extensibility.
• It uses one process per connection, a shared buffer cache, and a write-ahead log that guarantees durability and powers replication.
• MVCC keeps multiple row versions so readers and writers do not block each other; autovacuum cleans up old versions.
• Constraints, transactions and three isolation levels keep data consistent.
• Features such as JSONB, CTEs, window functions, full-text search and many index types let one database serve many needs.
• EXPLAIN ANALYZE and pg_stat_statements are the main tools for performance tuning.
• Java applications connect through the JDBC driver, usually via HikariCP and Spring Boot.

A good next step is to create the shop schema above, load a few thousand rows with generate_series, and compare query plans before and after adding indexes. Then open the FAQ tab for answers to the questions developers most often ask about PostgreSQL.

Frequently Asked Questions (FAQ)