Part 7 · 3 chapters · ~18 min

Types, Extensions and Extensibility

The type system (base types, domains, composites, ranges, arrays), JSON against JSONB, writing a C extension with its control file and SQL script, foreign data wrappers, logical decoding plugins, pg_stat_statements, auto_explain and the extensions worth knowing, and PostGIS as the case study.

Types, domains, ranges and JSONB

code

-- domains: a type with a rule
CREATE DOMAIN kobo AS bigint CHECK (VALUE >= 0);
-- ranges: intervals as a single value, with operators and GiST indexes
CREATE TABLE rates (pair text, valid tstzrange, rate numeric(18,8), EXCLUDE USING gist (pair WITH =, valid WITH &&));
SELECT rate FROM rates WHERE pair = 'NGN/USD' AND valid @> now();
-- composites and arrays
CREATE TYPE money_amount AS (minor bigint, currency char(3));
SELECT ARRAY[1,2,3] @> ARRAY[2];

	json	jsonb
storage	the original text	a decomposed binary format, keys sorted, duplicates removed
write cost	cheap (validate only)	parse and convert
read and query	reparse every time	fast operators (`@>`, `?`, `->`), jsonpath
indexing	expression indexes only	GIN (`jsonb_ops`, smaller `jsonb_path_ops`)
use when	you must keep exact text (signatures, audits)	almost always otherwise

Writing an extension, FDWs and decoding plugins

code

// money.c: a C function callable from SQL
#include "postgres.h"
#include "fmgr.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(kobo_to_naira_text);
Datum kobo_to_naira_text(PG_FUNCTION_ARGS) {
    int64 k = PG_GETARG_INT64(0);
    PG_RETURN_TEXT_P(cstring_to_text(psprintf("%lld.%02lld", (long long)(k / 100), (long long)llabs(k % 100))));
}

-- money--1.0.sql
CREATE FUNCTION kobo_to_naira_text(bigint) RETURNS text AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT;

# Makefile using PGXS
EXTENSION = money
MODULES = money
DATA = money--1.0.sql
PG_CONFIG = pg_config
include $(shell $(PG_CONFIG) --pgxs)

Foreign data wrappers let Postgres query other systems as tables (postgres_fdw for other Postgres servers, with pushdown of WHERE, joins and aggregates; file_fdw; community FDWs for MySQL, S3, HTTP APIs). Logical decoding plugins turn WAL into a change stream in any format: pgoutput (built-in, for logical replication), wal2json, decoderbufs (Debezium).

WHAT AN EXTENSION IS

SQL objects, a control file and a shared library, installed into the catalogue

swipe the figure sideways, or tap expand for full screen

1/5

the control file

money.control names the default version, the schema, whether it is relocatable and what it requires. CREATE EXTENSION reads it from the share/extension directory.

metadata: version, schema, requirementsCREATE EXTENSION money reads it

The extensions worth knowing, and PostGIS

extension	what it gives you
`pg_stat_statements`	normalised query statistics: calls, total and mean time, rows, buffers. The first thing to install.
`auto_explain`	logs plans of slow queries automatically
`pgcrypto`	hashing and encryption functions
`pg_trgm`	trigram similarity and GIN/GiST indexes for `LIKE '%x%'`
`pgvector`	vector type with HNSW and IVFFlat indexes for embeddings
`pg_partman`, `pg_cron`	partition management, scheduled jobs inside the database
`pg_repack`, `pgstattuple`, `pageinspect`	online rebuilds, bloat measurement, raw page reading
`PostGIS`	geometry and geography types, spatial functions, GiST and SP-GiST support: a full GIS built entirely on the extension APIs

code

-- the slowest queries by total time
SELECT left(query, 80), calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms, rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;