pg_nanotime

pg_nanotime : Nanosecond timestamp and fixed-duration interval types

Overview

Packages

Source

pig build pkg pg_nanotime;		# 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_nanotime;		# install via package name, for the active PG version

pig install pg_nanotime -v 18;   # install for PG 18
pig install pg_nanotime -v 17;   # install for PG 17

Create this extension with:

CREATE EXTENSION pg_nanotime;

Usage

Sources:

• 0.2.0 README
• 0.2.0 control file
• 0.2.0 SQL objects
• Technical reference
• 0.2.0 changelog
• PGXN release metadata

pg_nanotime 0.2.0 provides nanosecond timestamp and interval types on PostgreSQL 17–18 for event timestamps, latency measurements and externally supplied nanosecond data. Nanosecond storage precision does not imply nanosecond clock accuracy.

Core Workflow

CREATE EXTENSION pg_nanotime;
CREATE TABLE events (at nanotimestamptz DEFAULT clock_ns(), body text);
INSERT INTO events (body) VALUES ('received');
SELECT at, at::timestamptz FROM events;

Conversion to built-in timestamps floors to microseconds, including before the Unix epoch. Casting an instant to bigint yields Unix nanoseconds and fails if the result exceeds bigint range. Do not use double precision to transport large integer nanosecond values.

Durations, Aggregates and Series

Subtracting timestamps produces a nanosecond duration. Version 0.2.0 adds interval aggregates and series generation:

WITH samples(ns) AS (VALUES (1250::bigint), (2751::bigint))
SELECT sum(nanointerval_from_ns(ns)) AS total,
       avg(nanointerval_from_ns(ns)) AS mean,
       abs(nanointerval_from_ns(-1250::bigint)) AS magnitude
FROM samples;

SELECT *
FROM generate_series(
  '2026-10-24 00:00:00+00'::nanotimestamptz,
  '2026-10-24 00:00:00.000000003+00'::nanotimestamptz,
  nanointerval_from_ns(1::bigint)
);

sum and avg accumulate exactly, ignore NULL values and return NULL for no input; overflow raises an error. The average rounds to the nearest nanosecond, with ties away from zero. Series include a reachable stop value, support negative steps and reject a zero step.

Use nanointerval_from_ns(bigint) for exact integer input and nanointerval_from_ns(numeric) for larger values; fractional input rounds to nanoseconds. The double-precision overload becomes inexact beyond 2^53 nanoseconds.

Other useful objects include time_bucket_ns for fixed-duration buckets, extract_ns for precise fields, date_trunc and timezone. Explicitly type duration literals: an untyped string in timestamp arithmetic may resolve to PostgreSQL’s calendar-aware interval. Timestamp types support B-tree, hash and BRIN minmax indexes; the interval type supports B-tree and hash.

Clocks and Boundaries

clock_ns() reads the wall clock on each call. now_ns() caches an extension clock reading for the transaction and statement_ns() for the statement; these readings are not the same sampling instant as PostgreSQL’s built-in timestamps. Ordinary clocks can move backwards with the system clock.

clock_ns_monotonic() produces strictly increasing values within one backend. clock_ns_global() does so across backends in one PostgreSQL instance and requires this configuration followed by a server restart:

shared_preload_libraries = 'pg_nanotime'

Preserve other libraries already in the setting. Ordinary types and clocks do not require preloading. Global monotonic state is in shared memory and does not survive restart; it is not a durable sequence or distributed ordering mechanism. Install as a superuser. The extension is relocatable and is not marked trusted.

nanotime_clock_status() reports clock and monotonic-state diagnostics. nanotime.clock_source defaults to realtime; the optional tai source depends on the operating system’s configured TAI offset. TAI values still use UTC-style output, so do not mix these sources in the same column.

Upgrade and Migration

Upstream marks 0.2.0 as a PGXN testing release. After replacing library and SQL files, update each database:

ALTER EXTENSION pg_nanotime UPDATE TO '0.2.0';

The column-conversion helper nanotime_migrate_column defaults to the 16-byte type, even for a bigint source, and changes the column with ALTER TABLE, rebuilding its indexes. Review the conversion and locking cost before running it. TimescaleDB integration is optional; its integer-time and continuous-aggregate requirements are separate from merely storing nanosecond timestamps.