In the fast-paced world of financial technology and high-frequency trading (HFT), a microsecond is an eternity. When processing millions of transactions, even a microsecond of latency or a rounded timestamp is an outright compliance failure. For a long time, data engineers moving high-precision data between VMware Tanzu Greenplum and data lake hit a hard ceiling: microsecond truncation. But that’s about to change.
We have recently released PXF 8.0.2 which provides native support for nanosecond-precision timestamps (timestamp9, timestamp9_ntz and timestamp9_ltz) in the Tanzu Greenplum Platform Extension Framework (PXF) for Parquet files.
The Gap: Why Microseconds Speeds Aren’t Enough
Historically, PXF faced a dual dilemma when dealing with nanosecond timestamps:
The Read Path Truncation: If PXF ingested a Parquet file containing TIMESTAMP(NANOS) data, it would either error out or silently truncate the values down to microseconds. By the time the data reached the query layer, the critical sub-microsecond delta was gone.
The Write Path Barrier: Attempting to export Tanzu Greenplum’s timestamp9 or timestamp9_ntz columns to Parquet using foreign tables (or writable external tables in Greenplum 6) resulted in immediate type-support errors. Data pipelines were blocked from archiving ultra-high-precision logs to the lake.
This precision gap isolated Tanzu Greenplum from modern data lakehouse engines like Apache Hive and Spark SQL, which handle nanosecond timestamps natively. For many enterprises who mandate “TS9 across the board”, losing that data fidelity simply wasn’t an option.
The Solution: Full Round-Trip Fidelity in PXF 8
The upcoming enhancement completely rebuilds the bridge between Tanzu Greenplum and data lakehouse. PXF 8 introduces full, bit-exact round-trip fidelity for nanosecond data types, supporting both scalar values and arrays.
Whether you are writing data out to the lake or pulling it back in for complex analytics, your nanosecond values remain completely untouched.
This new capability is baked directly into the core engine of PXF, meaning it is supported equally across both available table interfaces:
- The Classic
EXTERNAL TABLEAPI: The reliable, native Tanzu Greenplum protocol. - The Modern
FOREIGN TABLE(FDW) API: The PostgreSQL-standard Foreign Data Wrapper interface.
Reading Nanosecond Parquet Files via FDW
Below is the table definition for foreign table to read high-precision transaction logs where we store the trade_id big integer along with a symbol column represented as a text. The trade details such as price and event/local time are stored as numeric, timestamp9, timestamp9_ntz respectively. The files are stored on the s3_server in parquet format inside the folder path /data/warehouse/trades.
CREATE FOREIGN TABLE trades_ft (
trade_id BIGINT,
symbol TEXT,
event_time TIMESTAMP9,
local_time TIMESTAMP9_NTZ,
price NUMERIC
)
SERVER s3_server
OPTIONS (resource '/data/warehouse/trades', format 'parquet')Below is the user query that identifies the trade_id(s) and symbol(s) of trade events. However, it does not pull the whole file into Tanzu Greenplum’s memory. We however, filter the data right there in the data lake, and only bring back rows where the transaction happened after exactly one billionth of a second past midnight on New Year’s Day, 2026 for the symbol AVGO. For this test suite, there are two rows that occur after one-billionth of a second past midnight on New Year’s Day, 2026 for that symbol.
SELECT trade_id, symbol, event_time
FROM trades_ft
WHERE event_time > '2026-01-01 00:00:00.000000001+00'::timestamp9
AND symbol = 'AVGO';Before nanosecond precision support in PXF
Prior to implementing full support for the timestamp9 extension, querying the data reveals a critical gap in accuracy. As shown in the output below, the event_time field is missing its correct nanosecond precision, which prevents the system from properly evaluating the high-precision timestamps. Furthermore, because PXF cannot natively process this extension type yet, it fails to correctly convert the timezones during the filtering process. As a result, the WHERE clause evaluates inaccurately and returns incorrect data.
SET TIME ZONE 'America/Los Angeles';
SELECT trade_id, symbol, event_time
FROM trades_ft
WHERE event_time > '2026-01-01 00:00:00.000000001+00'::timestamp9
AND symbol='AVGO';
trade_id | symbol | event_time
----------+--------+-------------------------------------
1004 | AVGO | 2025-12-31 16:00:00.000002000 -0800
1012 | AVGO | 2025-12-31 16:00:00.000002000 -0800
(2 rows)
SET TIME ZONE 'UTC';
SELECT trade_id, symbol, event_time
FROM trades_ft
WHERE event_time > '2026-01-01 00:00:00.000000001+00'::timestamp9
AND symbol='AVGO';
trade_id | symbol | event_time
----------+--------+------------
(0 rows)After nanosecond precision support in PXF
With full support for the timestamp9 extension implemented, the pipeline functions seamlessly. As demonstrated in the successful query output below, the event_time field now retains its complete, un-truncated nanosecond precision. Furthermore, PXF correctly handles and converts the timezone data during the filtering process. This enables the WHERE clause to evaluate flawlessly, returning accurate and precise results regardless of the underlying database’s timezone configuration.
SET TIME ZONE 'America/Los Angeles';
SELECT trade_id, symbol, event_time
FROM trades_ft
WHERE event_time > '2026-01-01 00:00:00.000000001+00'::timestamp9
AND symbol='AVGO';
trade_id | symbol | event_time
----------+--------+-------------------------------------
1004 | AVGO | 2025-12-31 16:00:00.000002340 -0800
1012 | AVGO | 2025-12-31 16:00:00.000002436 -0800
(2 rows)
SET TIME ZONE 'UTC';
SELECT trade_id, symbol, event_time
FROM trades_ft
WHERE event_time > '2026-01-01 00:00:00.000000001+00'::timestamp9
AND symbol='AVGO';
trade_id | symbol | event_time
----------+--------+-------------------------------------
1004 | AVGO | 2026-01-01 00:00:00.000002340 +0000
1012 | AVGO | 2026-01-01 00:00:00.000002436 +0000
(2 rows)Writing Nanosecond Parquet Files via Foreign Tables
Exporting data from Tanzu Greenplum to datalake uses the exact same intuitive pattern. In the example below we build a one-way data pipeline that points directly to the data lake’s /trades folder. We take the data from our local, high-performance Tanzu Greenplum table, but only select the historical rows where the transaction happened strictly before midnight on January 1, 2010.
The user can offload cold historical data (pre-2010 trades) from the internal database into cheaper external Parquet/object storage, freeing up space locally while keeping the data queryable via the foreign table. This can be done via the following SQL.
-- Export directly from native tables without data truncation
INSERT INTO trades_ft SELECT * FROM trades_internal
WHERE event_time < '2010-01-01 00:00:00.000000000'::timestamp9;Before nanosecond precision support in PXF
axue=# INSERT INTO trades_ft SELECT * FROM trades_internal WHERE event_time < '2010-01-01 00:00:00.000000000'::timestamp9;
ERROR: PXF server error : Type 18264 for column event_time is not supported for writing Parquet. (seg2 127.0.0.1:7704 pid=57921)
HINT: Check the PXF logs located in the '/Users/axue/pxf-base/clusters/default/groups/default/logs' directory on host 'PG3QC5X7KH' or 'set client_min_messages=LOG' for additional details.In the above example, the INSERT statement fails because PXF lacked support for the timestamp9 data type. Because timestamp9 is a custom extension, its Object Identifier (OID) is dynamically allocated and will vary across different database installations. PXF did not have the ability to handle these variable OID datatypes. In this particular database instance, timestamp9 was assigned OID 18264, so we see that it is not supported for writing.
After nanosecond precision support in PXF
axue=# INSERT INTO trades_ft SELECT * FROM trades_internal WHERE event_time < '2010-01-01 00:00:00.000000000'::timestamp9;
INSERT 0 5
axue=# SELECT trade_id, symbol, event_time, local_time FROM trades_ft WHERE event_time < '2010-01-01 00:00:00.000000000'::timestamp9 ORDER BY trade_id;
trade_id | symbol | event_time | local_time
----------+--------+-------------------------------------+-------------------------------
2001 | MSFT | 2009-12-31 23:59:59.999999999 -0800 | 2009-12-31 23:59:59.999999999
2002 | AAPL | 2009-06-15 14:30:25.123456789 -0700 | 2009-06-15 14:30:25.123456789
2003 | GOOGL | 2008-10-03 09:45:12.987654321 -0700 | 2008-10-03 09:45:12.987654321
2004 | IBM | 2007-03-22 16:20:45.555555555 -0700 | 2007-03-22 16:20:45.555555555
2005 | ORCL | 2005-11-11 11:11:11.111111111 -0800 | 2005-11-11 11:11:11.111111111
(5 rows)Once support for the timestamp9 extension is introduced, PXF successfully bridges the gap. As shown in the example, the INSERT statement now executes without throwing an OID error, safely writing the high-precision data to the external table.
To verify that the nanosecond metadata was preserved perfectly during the write operation, you can execute a SELECT query utilizing a WHERE clause targeted at the timestamp9 field. The output demonstrates that the records are not only correctly filtered but are also returned with their full, un-truncated level of precision—proving that the pipeline can now seamlessly serialize and deserialize 9-digit sub-second timestamps.
Fail-Safe Engineering: Guardrails and Performance
Adding precision shouldn’t mean sacrificing stability. This update was built with rigorous non-functional guardrails to protect existing workloads:
No Silent Truncation: If you attempt to write nanosecond data using an older Parquet specification version that doesn’t support it, PXF won’t silently drop your data. It will immediately abort the transaction and raise a clear, actionable error.
Timezone Safeguards: Timezone translation is a notorious pitfall in distributed systems. PXF automatically standardizes timestamp9 values to UTC before writing, while keeping timestamp9_ntz locked to local representations.
The Bottom Line: True End-to-End Precision
Ultimately, PXF 8 eliminates a major hurdle for high-frequency data pipelines by delivering transparent, zero-configuration support for nanosecond timestamps. By enabling full-fidelity ingestion without silent downcasting and enabling truncation-free exports for timestamp9 and timestamp9_ntz data types, Tanzu Greenplum enables your ultra-high-precision data to remain bit-exact. Whether data is entering your cluster or being archived back out to the lake, the rule is simple: what you write is exactly what you keep; down to the very last nanosecond.
