What is PostgreSQL?

Almost every system ends up storing data that is related: users, orders, products, payments. Instead of scattering that across files, you use a database that keeps the information correct even when many people write at the same time. PostgreSQL is one of the most widely used databases for that job.

Developers like it because it does the basics well (tables, SQL, transactions) and keeps up as the project grows, with JSON, geographic data and extensions.

What is PostgreSQL?#

PostgreSQL, also called Postgres, is an open source relational database management system (RDBMS). It started in 1986 as the POSTGRES project at the University of California, Berkeley, and has decades of active development. The license is the PostgreSQL License, a permissive license similar to BSD and MIT.

Relational means the data lives in tables, with rows and columns, and the tables link to each other through keys. You describe what you want in SQL, the standard query language, and the server decides how to fetch it.

How does PostgreSQL work?#

PostgreSQL runs as a server. Your application opens a connection (default port 5432), sends SQL commands and gets rows back. Three ideas explain most of its behavior.

Schema. Each table has columns with defined types, and the database rejects data that breaks the rules. Constraints such as NOT NULL, UNIQUE, primary keys and foreign keys protect integrity: you cannot have an order pointing to a user that does not exist.

ACID transactions. A transaction groups several commands that either all happen or none happen. ACID stands for atomicity, consistency, isolation and durability. If you debit one account and credit another, both steps count or neither does. PostgreSQL has been ACID-compliant since 2001.

MVCC and WAL. With multiversion concurrency control (MVCC), each statement sees a consistent version of the data, so readers do not block writers and writers do not block readers. The write-ahead log (WAL) records every change in a log before applying it to the tables. It is what lets the database recover after a crash and what feeds replication.

Example: two tables and a query#

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id),
  total   numeric(10, 2) NOT NULL
);

INSERT INTO users (email) VALUES ('ana@example.com');
INSERT INTO orders (user_id, total) VALUES (1, 49.90);

SELECT u.email, sum(o.total) AS spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

REFERENCES blocks an order without a user, and JOIN combines the two tables in the query.

Example: using it from Node.js#

With the pg driver, the pool reads the PGHOST, PGUSER, PGPASSWORD, PGPORT and PGDATABASE variables, the same ones psql uses. Never hardcode the password. If the server requires TLS with a client certificate, read the certificate files and pass their contents in the ssl option.

import { readFileSync } from "node:fs";
import pg from "pg";

// host, user, password, port and database come from the PG* environment variables
const pool = new pg.Pool({
  ssl: {
    ca: readFileSync("ca-certificate.crt", "utf8"), // CA the client trusts
    cert: readFileSync("certificate.pem", "utf8"), // client certificate
    key: readFileSync("private-key.key", "utf8"), // client private key
  },
});

const email = "ana@example.com";
const { rows } = await pool.query(
  "SELECT id, email FROM users WHERE email = $1",
  [email],
);
console.log(rows);

await pool.end();

$1 is a parameter: the value travels separately from the SQL text, which prevents SQL injection. Never build the query by concatenating strings.

What is PostgreSQL used for?#

• Web apps and APIs with users, orders, permissions and reports.
• Financial and inventory systems, where a half-finished transaction is unacceptable.
• Bots that store per-server settings, economy data, history or moderation records, especially when the data is related.
• Geographic data, with the PostGIS extension.
• Analytics with aggregations and complex queries, without leaving SQL.

What makes PostgreSQL different?#

JSONB. The json and jsonb types store documents inside a column. The documentation recommends jsonb for most cases: it is faster to process and supports GIN indexes and the containment operator @>.

CREATE TABLE events (id bigserial PRIMARY KEY, data jsonb NOT NULL);
CREATE INDEX events_data_idx ON events USING GIN (data);

SELECT id, data ->> 'type' AS type
FROM events
WHERE data @> '{"source": "bot"}';

Many index types. Besides the default B-tree, there are GiST, GIN and BRIN, each suited to a kind of data and query.

Extensions. You add functions, types and indexes without switching databases. PostGIS, for geographic data, is the best-known example.

Rich types. Arrays, ranges, UUID, XML and types you define yourself.

Replication and recovery. Synchronous, asynchronous and logical replication, plus point-in-time recovery from the WAL.

When should you not use PostgreSQL?#

• Caches and very hot counters: an in-memory store like Redis answers faster in that role.
• Large files: images and videos belong in object storage. Keep only the file path or URL in the database.
• Tiny local projects: if the app runs on one machine with little data, a SQLite file may be enough.
• Data with no fixed structure and no relations: a document database like MongoDB can be more direct, although JSONB covers a lot.

To decide between options, read which database to choose. If you are torn between the two most common relational databases, see PostgreSQL vs MySQL and what is MySQL.

How is PostgreSQL versioned?#

One major version ships each year, and each one gets bug and security fixes for 5 years. The official versions table shows which ones are still supported. Minor updates (from one x.5 release to x.6, for example) usually only require restarting the server with the new binaries (read the release notes first). Moving to a new major version requires pg_upgrade or a dump and restore, so plan it before a version loses support.

Whatever the version, keep copies of your database. The guide on how to back up a database walks through it.

How to do this on Square Cloud#

What this article showed from core PostgreSQL (a schema with foreign keys, transactions, JSONB with a GIN index and parameterized queries) works on Square Cloud's PostgreSQL hosting, available from the Standard plan; Hobby does not include databases. It is native PostgreSQL, without extensions: PostGIS, pgvector and the like are not available, so check this first if your project depends on one. You create the database in the dashboard, always on a recent version, with at least 1024 MB of the plan's RAM, and it is ready in seconds in the same datacenter as your applications, with about 1 ms average latency.

The Node.js example already follows the required connection setup. TLS is mandatory: download ca-certificate.crt, certificate.pem and private-key.key from the dashboard and read them into the ssl option, because the client must trust the CA and present its own certificate. Store the connection URL's details (host, port, user, password and database) in the application's PG* variables and restart it so they take effect. For the very hot counters PostgreSQL should not carry, a Redis can run alongside it on the same plan.

You take manual snapshots whenever you want, for example before a schema change, and every plan that includes databases also takes an automatic daily snapshot, kept for 7 days. A snapshot restores into the same database, another PostgreSQL or a new database, or downloads as a .zip.

See details and plans on PostgreSQL hosting.