postbis

Overview

The packaged repository is an untagged copy of PostBIS, inactive since 2019; Pigsty pins commit ce454ebf and patches PostgreSQL 14-18 compatibility plus alphabet output and indexed slice correctness.

Version

Build

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

pig build pkg postbis         # build RPM / DEB packages

Install

You can install postbis 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:

pig install postbis;          # Install for current active PG version

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

dnf install -y postbis_18       # PG 18
dnf install -y postbis_17       # PG 17
dnf install -y postbis_16       # PG 16
dnf install -y postbis_15       # PG 15
dnf install -y postbis_14       # PG 14

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

Create Extension:

CREATE EXTENSION postbis;

Usage

Sources:

• Project README
• Extension control file
• Version 1.0 SQL API
• Sequence regression tests

postbis 1.0 provides compact native types for DNA, RNA, amino-acid, and aligned sequences. It also provides configurable alphabets and type modifiers, casts, sequence operations, biological transformations, comparison operators, and B-tree and hash operator classes.

Store typed sequences

CREATE EXTENSION postbis;

CREATE TABLE specimen (
  specimen_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  dna dna_sequence(SHORT, FLC, CASE_SENSITIVE) NOT NULL,
  rna rna_sequence(IUPAC, CASE_SENSITIVE),
  protein aa_sequence(IUPAC, CASE_SENSITIVE)
);

INSERT INTO specimen (dna, rna, protein)
VALUES ('AACCGGTT', 'AACGUU', 'ACDEFG');

SELECT specimen_id,
       char_length(dna) AS bases,
       substr(dna, 3, 4)::text AS fragment
FROM specimen;

Input validation depends on the selected alphabet, case-sensitivity, and type modifiers. Verify that casts reject symbols outside the required biological convention and that aligned and unaligned types are not mixed accidentally.

Transform and translate sequences

SELECT complement('ACGTN'::dna_sequence)::text;
-- TGCAN

SELECT reverse_complement('ACGTN'::dna_sequence)::text;
-- NACGT

SELECT transcribe('AACGTT'::dna_sequence)::text;
-- AACGUU

SELECT translate('AUGGCCUAA'::rna_sequence)::text;
-- MA

The extension also exposes reverse_transcribe(), six_frame(), get_alphabet(), entropy(), gc_content(), and sequence generators. The translation functions accept explicit translation tables when the standard genetic code is not appropriate.

Inspect compression and add indexes

SELECT char_length(sequence) AS symbols,
       octet_length(sequence) AS storage_bytes,
       compression_ratio(sequence) AS storage_ratio
FROM (
  SELECT repeat('ACGT', 256)::dna_sequence AS sequence
) AS sample;

CREATE INDEX specimen_dna_btree ON specimen USING btree (dna);
CREATE INDEX specimen_dna_hash  ON specimen USING hash  (dna);

Equality, ordering, concatenation, substring, search, and length functions are available for the sequence types. Check plans and realistic data distributions before relying on an index for a production workload.

Packaging and durability risk

Pigsty applies a downstream compatibility patch and packages PostBIS 1.0 for PostgreSQL 14–18. That packaging result does not change the upstream lifecycle: the project is inactive and has no extension upgrade path beyond 1.0.

The custom types use native compressed on-disk representations. Treat stored values and indexes as tied to an exact tested build. Before adoption or migration, prove dump and restore, binary and logical upgrades, replication, driver decoding, index rebuilds, malformed input handling, and large-sequence memory behavior.

Functions such as reverse(), char_length(), and substr() overload familiar names, so schema qualification and controlled search_path settings matter. For new durable datasets, prefer maintained sequence tooling or plain PostgreSQL types unless the extension has been locally audited, packaged, and assigned an explicit long-term migration owner.