PostgreSQL: Roles, JSONB, Indexes, Backups, and Upgrades

PostgreSQL is an open-source object-relational database that combines SQL with a broad set of types, constraints, indexes, and extensions.

Think of a PostgreSQL database as a planned city. Tables are buildings, schemas are districts, roles carry access permits, and constraints are the building code. The application can move quickly because the database keeps unsafe structures from being approved quietly.

How PostgreSQL Began

The Berkeley POSTGRES implementation began in 1986 under Professor Michael Stonebraker. The first demonstration system operated in 1987, and Version 1 reached a small group of external users in June 1989.

Andrew Yu and Jolly Chen added an SQL interpreter in 1994, and the open-source descendant was released as Postgres95. The project adopted the PostgreSQL name in 1996 to connect the original POSTGRES work with its SQL capability. The shorter name "Postgres" remains an official alternative.

The long history matters because current PostgreSQL combines a mature relational core with features added over many release cycles. It does not remove the need to choose a supported version, test extensions, or plan major upgrades.

Where PostgreSQL Fits

PostgreSQL fits applications whose database must express more than basic row storage. It is a strong candidate when the data model relies on detailed constraints, several index types, JSONB alongside relational columns, geospatial work through extensions, or database-side functions and types.

Choose it when these conditions match the application:

• Relationships and business rules need database constraints.
• Queries benefit from partial, expression, GIN, GiST, or BRIN indexes.
• Extensions such as PostGIS solve a documented database requirement.
• The team can operate backups, restores, monitoring, and major upgrades.
• The hosting platform supports the required major version and extensions.

Use MySQL when its hosting, compatibility, and team experience are a better fit. Use SQLite when the application needs a local database file instead of a shared server. Choose PostgreSQL for matching data and operational requirements.

How to Install, Connect, and Check the Server

Follow PostgreSQL's platform or provider instructions because packages, service names, authentication rules, and data-directory paths differ. Connect with psql after installation:

psql -h 127.0.0.1 -U postgres -d postgres

The examples below target PostgreSQL 18.4, the current stable documentation release on July 29, 2026. PostgreSQL 19 Beta 2 remained a development release on that date.

Ask the server for its identity and current connection context:

SELECT version();
SELECT current_database(), current_user, current_schema();
SHOW search_path;
SHOW server_encoding;

The result identifies the server, database, role, active schema, name-resolution path, and encoding. Keep these details with deployment evidence because a local psql version does not prove the remote server version.

How to Create Roles and a Schema

PostgreSQL represents identity and privileges with roles. Some roles can log in, while others collect permissions or own objects. Separate ownership from the account used by ordinary application requests.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN
PASSWORD 'replace-with-a-generated-secret';

CREATE SCHEMA app AUTHORIZATION app_owner;

GRANT USAGE ON SCHEMA app TO app_runtime;

The runtime role can use the application district without owning the entire city. Migrations can run through an owner role or a controlled deployment identity, while web requests receive only the permissions needed for their queries.

Create objects with schema-qualified names so their ownership is visible:

CREATE TABLE app.users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE app.users OWNER TO app_owner;
GRANT SELECT, INSERT, UPDATE, DELETE
ON app.users TO app_runtime;
GRANT USAGE, SELECT
ON SEQUENCE app.users_id_seq TO app_runtime;

The explicit sequence grant lets runtime inserts obtain identity values. Default privileges need separate planning for tables and sequences created by future migrations. Test the complete migration with the migration identity, then test runtime queries again as app_runtime.

How JSONB and Indexes Work Together

PostgreSQL stores json as input text and jsonb in a decomposed binary form that can be processed and indexed. JSONB works well for fields whose shape varies, while stable application facts remain ordinary columns.

This event table keeps ownership and event type relational:

CREATE TABLE app.events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app.users (id),
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO app.events (user_id, event_type, payload)
VALUES (
  42,
  'article_viewed',
  '{"article_id": 17, "referrer": "newsletter"}'
);

The payload holds event-specific details, while the user, event type, and timestamp remain visible to constraints and common reports. Add an index only after the query shape is known:

CREATE INDEX events_article_id_idx
ON app.events ((payload ->> 'article_id'))
WHERE event_type = 'article_viewed';

EXPLAIN
SELECT id, created_at
FROM app.events
WHERE event_type = 'article_viewed'
  AND payload ->> 'article_id' = '17';

This partial expression index supports one repeated lookup without indexing every key in every payload. The city plan still shows its main roads as columns; JSONB carries the variable details inside a building.

Read SQL indexes and query optimization before adding indexes that have no measured query, row count, or write-cost review.

How to Back Up and Upgrade PostgreSQL

A logical backup can capture one database in PostgreSQL's custom archive format:

pg_dump \
  --format=custom \
  --file=codewalkers_app.dump \
  codewalkers_app

createdb codewalkers_app_restore
pg_restore \
  --dbname=codewalkers_app_restore \
  codewalkers_app.dump

A complete recovery plan may also need roles, tablespaces, configuration, large objects, and cluster-wide settings that a single-database dump does not cover.

Verify restored row counts, constraints, extensions, permissions, and application queries. Keep the backup away from the server it protects and measure whether the restore finishes inside the required recovery window.

PostgreSQL supports each major version for five years. Minor upgrades within a major do not require dump and restore, although release notes can still require extra steps. Major upgrades normally use pg_upgrade or dump and restore because data-directory compatibility can change. Test extensions and query plans before the production cutover.

Common Pitfalls

Using the Superuser for the Application

Symptom: an application bug can alter schemas or bypass ordinary permission boundaries. Cause: the web process connects as a superuser or object owner. Fix: create a restricted runtime role, grant only required schema and table access, and run migrations through a separate identity.

Trusting the Search Path

Symptom: a migration or job reads the wrong object after a schema is added or reordered. Cause: unqualified names depend on a session's search_path. Fix: use schema-qualified names in migrations and background jobs, and set a deliberate path for application sessions.

Hiding Core Fields in JSONB

Symptom: joins, reports, uniqueness checks, and permissions repeatedly extract the same JSON keys. Cause: stable relational facts were stored inside one flexible payload. Fix: move identifiers, money, states, and common filters into typed columns with constraints.

Adding Indexes Without a Query

Symptom: writes slow down while the intended read still uses a scan. Cause: an index was chosen by column name rather than a real predicate and sort order. Fix: run EXPLAIN on the target query and add the smallest index that matches its access pattern.

Frequently Asked Questions

Is PostgreSQL 19 ready for production?

PostgreSQL 19 was not ready for production on July 29, 2026. Beta 2 was a development release, while PostgreSQL 18.4 was the current stable documentation release. Use a supported stable major version, keep it on the current minor release, and test application plus extension compatibility before upgrading.

What is a PostgreSQL schema?

A schema is a namespace inside a database. It can contain tables, views, functions, types, and other objects. Schemas help separate ownership and names, but permissions and a controlled search_path still matter. Schema-qualified names make migrations and background jobs easier to review.

When should an application use JSONB?

Use JSONB for variable payloads whose fields differ by event or integration type. Keep identifiers, relationships, money, permissions, common filters, and reporting fields in typed columns with constraints. JSONB can be indexed, but indexing does not restore a relational model hidden inside one document.

Does a PostgreSQL major upgrade require planning?

A PostgreSQL major upgrade requires deliberate planning. Major versions can change the data-directory format, so an upgrade normally uses pg_upgrade or dump and restore. Verify extensions, read release notes, test migrations and query plans on production-shaped data, preserve a rollback path, and rehearse the complete cutover before production.

What to Read Next

Continue with SQL schema design basics for keys and constraints, then use SQL transactions and ACID to define multi-statement writes. Compare MySQL vs PostgreSQL vs SQLite when the deployment choice remains open.

Return to the SQL databases hub for the other database guides.