Feature Request: Native Nanosecond Precision Support for PostgreSQL Temporal Types

Core Problem

PostgreSQL's temporal types (timestamp, timestamptz, time, interval) currently provide microsecond precision (6 fractional digits), stored internally as a 64-bit integer representing microseconds since 2000-01-01. This design decision, made during the transition from the older floating-point timestamp representation (completed around PostgreSQL 8.0), has served well for most use cases but is now being challenged by modern analytical workloads.

The request is to extend temporal resolution to nanosecond precision (9 fractional digits) — a 1000x increase in granularity. This is fundamentally an architectural challenge, not merely a surface-level type system change.

Architectural Implications

Storage Representation

The current int64 microsecond representation spans approximately ±292,000 years — far more range than needed for practical timestamps. Moving to nanoseconds within the same 64-bit integer would reduce the representable range to approximately ±292 years (roughly 1854–2146), which may be acceptable for many use cases but represents a significant departure from current capabilities.

Alternative approaches include:

1. 128-bit integer representation: Stores nanoseconds with effectively unlimited range, but doubles storage from 8 to 16 bytes per timestamp. This has cascading effects on index sizes, tuple width, TOAST thresholds, and buffer cache efficiency.

2. New distinct type (e.g., timestamp_ns): Avoids breaking existing behavior but creates type coercion complexity and doubles the operator/function surface area for temporal operations.

3. Variable precision via typmod: Following Oracle's approach where TIMESTAMP(9) requests nanosecond precision. PostgreSQL already has typmod-based precision for timestamps, but the underlying storage is always microseconds regardless of declared precision.

Cascading System Impact

A nanosecond timestamp type would require changes across numerous subsystems:

• WAL format: Timestamps appear in WAL records (e.g., commit timestamps, logical decoding messages). Changing their size affects WAL format compatibility.
• Replication protocol: The streaming replication protocol includes timestamps; logical replication's publication/subscription protocol would need updates.
• System catalogs: pg_stat_activity.query_start, pg_stat_replication, and other catalog views expose timestamps.
• libpq protocol: The binary format for timestamp wire transfer is well-established; a new type or modified encoding affects every client driver.
• Date/time arithmetic: All interval operations, date_trunc, date_part, extract, timezone conversions, and the full temporal function library would need nanosecond-aware variants.
• btree/hash operator classes: Index support for ordering and equality.
• Partitioning: Range partitioning on timestamp columns with nanosecond bounds.

The timestamp9 Extension Approach

The thread references timestamp9, a community extension by Optiver that provides nanosecond timestamps. This extension demonstrates both the feasibility and limitations of the extension-based approach:

• It cannot integrate with native BETWEEN, range types, or partition pruning as seamlessly as core types
• It lacks full driver/ORM ecosystem support
• It cannot participate in the system catalog or replication infrastructure

This is precisely the kind of functionality that is difficult to get right as an extension because temporal types are so deeply woven into PostgreSQL's infrastructure.

Key Design Tensions

Backward Compatibility vs. Clean Design

Any solution must not break the billions of existing applications relying on microsecond timestamp/timestamptz. This strongly favors a new type rather than modifying existing types — but new types carry enormous maintenance burden.

Storage Efficiency vs. Precision

The 8-byte timestamp is a core assumption in many places. Index entries, sort operations, hash computations, and tuple alignment all assume 8-byte timestamps. A 16-byte timestamp has real performance implications for time-series workloads — ironically the exact workloads that need nanosecond precision.

SQL Standard Compliance

The SQL standard specifies that TIMESTAMP precision can range from 0 to implementation-defined maximum. PostgreSQL documents its maximum as 6 (microseconds). Extending to 9 is permitted by the standard, but changing the default or existing behavior is not straightforward.

Assessment of Feasibility

This is a major undertaking — likely multi-year development effort touching nearly every subsystem. Historical precedent suggests this kind of change (comparable in scope to the float-to-int64 timestamp transition) requires:

1. A concrete, well-designed patch series
2. A committed developer willing to maintain it through multiple review cycles
3. Buy-in from core committers with timestamp/temporal expertise (notably Tom Lane, who has historically maintained date/time code)

The thread as posted is a feature request without an accompanying patch or detailed design proposal. While the motivation is well-articulated and the ecosystem pressure is real, threads of this nature on pgsql-hackers typically require a concrete implementation proposal to gain traction.

Comparison with Prior Art

• Oracle: Supports TIMESTAMP(0) through TIMESTAMP(9) with variable internal storage
• DuckDB: Has TIMESTAMP_NS as a separate type alongside TIMESTAMP (microsecond)
• ClickHouse: DateTime64(9) uses a 64-bit integer with configurable epoch and precision

The DuckDB approach (separate type) is probably most compatible with PostgreSQL's architecture, avoiding disruption to existing types while providing a clean integration point.

Open Questions

1. Should this be a new type or an extension of existing typmod behavior?
2. What storage format — 64-bit (reduced range) or 128-bit (doubled size)?
3. How should cross-precision arithmetic work (e.g., timestamp_ns - timestamptz)?
4. What is the interaction with clock_timestamp() and other system clock functions on platforms without nanosecond clocks?
5. How does this interact with logical replication and pg_dump/pg_restore across versions?