pgproto

Overview

release 0.3.3; SQL v1.0

Version

Build

You can build the RPM / DEB packages for pgproto using pig build:

pig build pkg pgproto         # build RPM / DEB packages

Install

You can install pgproto directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgproto;          # Install for current active PG version

pig ext install -y pgproto -v 18  # PG 18
pig ext install -y pgproto -v 17  # PG 17
pig ext install -y pgproto -v 16  # PG 16
pig ext install -y pgproto -v 15  # PG 15
pig ext install -y pgproto -v 14  # PG 14

dnf install -y pgproto_18       # PG 18
dnf install -y pgproto_17       # PG 17
dnf install -y pgproto_16       # PG 16
dnf install -y pgproto_15       # PG 15
dnf install -y pgproto_14       # PG 14

apt install -y postgresql-18-pgproto   # PG 18
apt install -y postgresql-17-pgproto   # PG 17
apt install -y postgresql-16-pgproto   # PG 16
apt install -y postgresql-15-pgproto   # PG 15
apt install -y postgresql-14-pgproto   # PG 14

Create Extension:

CREATE EXTENSION pgproto;

Usage

Sources: README, release 0.3.3, pgproto.control

pgproto adds a protobuf type for storing binary Protocol Buffers with schema-aware extraction and update helpers. The latest upstream release is 0.3.3, while the extension control file advertises SQL default version 1.0.

CREATE EXTENSION pgproto;

Basic Workflow

Register a FileDescriptorSet so the extension can interpret message layouts:

INSERT INTO pb_schemas (name, data) VALUES ('MySchema', '\x...');

Store protobuf payloads in a protobuf column:

CREATE TABLE items (
  id serial PRIMARY KEY,
  data protobuf
);

Querying

Use PostgreSQL-style path operators for nested fields:

SELECT data #> '{Outer, inner, id}'::text[] FROM items;
SELECT data #> '{Outer, tags, mykey}'::text[] FROM items;

The README highlights -> and #> as the primary navigation operators for nested, repeated, and map fields.

Updates and Merge

The write helpers are pure functions that return a new protobuf value:

• pb_set(...)
• pb_insert(...)
• pb_delete(...)
• || to merge two messages of the same type

UPDATE items SET data = pb_set(data, ARRAY['Outer', 'a'], '42');
UPDATE items SET data = pb_insert(data, ARRAY['Outer', 'scores', '0'], '100');
UPDATE items SET data = pb_delete(data, ARRAY['Outer', 'a']);
UPDATE items SET data = data || other_data;

Indexing and Evolution

Expression indexes work on extracted fields:

CREATE INDEX idx_pb_id ON items ((data #> '{Outer, inner, id}'::text[]));

The README also documents schema evolution as a first-class use case: adding fields is backward-compatible, deprecated fields remain readable if present in older payloads, and re-registering schemas with ON CONFLICT is the expected update path.

Caveats

• pgproto relies on registered runtime schemas; without the descriptor set, path-based extraction cannot interpret the payload.
• The update helpers do not mutate in place, so they need to be used in UPDATE ... SET data = ....