pg_regresql

pg_regresql : Trust pg_class statistics for planning instead of physical relation size

Overview

Packages

Source

pig build pkg pg_regresql;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_regresql;		# install via package name, for the active PG version

pig install pg_regresql -v 18;   # install for PG 18
pig install pg_regresql -v 17;   # install for PG 17
pig install pg_regresql -v 16;   # install for PG 16
pig install pg_regresql -v 15;   # install for PG 15
pig install pg_regresql -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_regresql';

Create this extension with:

CREATE EXTENSION pg_regresql;

Usage

Syntax:

regresql init postgres://localhost/mydb
regresql add src/sql/
regresql update
regresql test

Sources: README, Product page

RegreSQL is documented upstream as a language-agnostic SQL regression testing tool for PostgreSQL, not as a CREATE EXTENSION-style in-database module. It discovers .sql files, runs them against PostgreSQL, snapshots expected output, and tracks query plan changes.

Quick Start

The README’s basic workflow is:

regresql init postgres://localhost/mydb
regresql discover
regresql add src/sql/
regresql update
regresql test

This initializes a test suite, discovers query files, creates plan definitions, captures expected output, and runs regression checks.

What It Tracks

The upstream docs emphasize:

• expected query output snapshots
• EXPLAIN plan baselines
• sequential scan warnings
• migration-related query regressions
• CI-oriented output formats such as junit, json, pgtap, and github-actions

Query Files and Plans

RegreSQL works with normal SQL files and supports multiple queries per file using -- name: annotations:

-- name: get-user-by-id
SELECT * FROM users WHERE id = :id;

Plan files provide test parameters:

"1":
  id: 42
"2":
  id: 100

Snapshots and Migrations

The tool can build and restore database snapshots and compare query behavior across migrations:

regresql snapshot build
regresql snapshot restore
regresql migrate --script db/migrations/001_add_column.sql

Installation

The README documents installation via Homebrew or Go:

brew tap boringsql/boringsql
brew install regresql

or

go install github.com/boringsql/regresql@latest

PostgreSQL client tools such as pg_dump, pg_restore, and psql are required for snapshot commands.