pgproto

Overview

Release tag 0.2.4 still ships extension SQL version 1.0; Pigsty republishes a cleaned source tarball.

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

Syntax:

CREATE EXTENSION pgproto;
INSERT INTO pb_schemas (name, data) VALUES ('MySchema', '\x...');
CREATE TABLE items (id serial PRIMARY KEY, data protobuf);
SELECT data #> '{Outer, inner, id}'::text[] FROM items;

Source: README

pgproto adds native Protocol Buffers support to PostgreSQL. It provides a protobuf type, runtime schema registration, nested field extraction, update helpers, and indexing support for schema-aware access to protobuf payloads.

Setup

Enable the extension:

CREATE EXTENSION pgproto;

Register protobuf schemas by loading FileDescriptorSet blobs:

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

Create a table using the custom protobuf type:

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

Querying

The README highlights nested field extraction with PostgreSQL-style operators:

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

It also mentions custom operators such as -> and #> for schema-aware navigation.

Modification Functions

pgproto includes pure functions that return a new protobuf value:

• pb_set(...)
• pb_insert(...)
• pb_delete(...)

Because they return modified values rather than mutating in place, they are normally used in UPDATE statements:

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']);

The || operator merges two protobuf messages of the same type.

Indexing

The README documents B-tree expression indexes on extracted fields:

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

The project also advertises GIN support for retrieval workflows.

Notes

The upstream README positions pgproto as more storage-efficient than JSONB for protobuf-native payloads and highlights protobuf schema evolution, enums, oneof, and map/repeated field access as supported use cases.