pfc-export-timescaledb

One-shot CLI tool to export a TimescaleDB hypertable (or a time range within it) directly to a compressed .pfc archive with block-level timestamp index.

Export once, query forever — directly from DuckDB without loading the full archive.

---

How it works

TimescaleDB  →  fetchmany() batches  →  JSONL temp file  →  pfc_jsonl compress  →  .pfc + .pfc.bidx

A single command streams rows from your hypertable, compresses them to .pfc format (~9% of original JSONL size), and writes the archive with a block-level timestamp index baked in — ready for time-range queries without full decompression.

---

Install

pip install pfc-export-timescaledb

# Or from source
git clone https://github.com/ImpossibleForge/pfc-export-timescaledb
cd pfc-export-timescaledb
pip install -r requirements.txt

The pfc_jsonl binary must be installed:

# Linux x64:
curl -L https://github.com/ImpossibleForge/pfc-jsonl/releases/latest/download/pfc_jsonl-linux-x64 \
     -o /usr/local/bin/pfc_jsonl && chmod +x /usr/local/bin/pfc_jsonl

# macOS Apple Silicon (M1–M4):
curl -L https://github.com/ImpossibleForge/pfc-jsonl/releases/latest/download/pfc_jsonl-macos-arm64 \
     -o /usr/local/bin/pfc_jsonl && chmod +x /usr/local/bin/pfc_jsonl

# macOS Intel:
curl -L https://github.com/ImpossibleForge/pfc-jsonl/releases/latest/download/pfc_jsonl-macos-x64 \
     -o /usr/local/bin/pfc_jsonl && chmod +x /usr/local/bin/pfc_jsonl

# Windows (PowerShell):
Invoke-WebRequest https://github.com/ImpossibleForge/pfc-jsonl/releases/latest/download/pfc_jsonl-windows-x64.exe `
  -OutFile "$env:LOCALAPPDATA\Microsoft\WindowsApps\pfc_jsonl.exe"

---

Usage

# Export entire hypertable
pfc-export-timescaledb \
  --host db.example.com \
  --table sensor_data \
  --output sensors.pfc

# Export a specific time range (Q1 2025)
pfc-export-timescaledb \
  --host db.example.com \
  --table sensor_data \
  --ts-column time \
  --from-ts "2025-01-01T00:00:00" \
  --to-ts   "2025-04-01T00:00:00" \
  --output  sensors_q1_2025.pfc \
  --verbose

# Custom schema, user, database
pfc-export-timescaledb \
  --host db.example.com \
  --schema metrics \
  --table cpu_usage \
  --user myuser --dbname mydb \
  --output cpu_usage.pfc

---

Options

Flag	Default	Description
--host	(required)	TimescaleDB hostname or IP
--port	5432	PostgreSQL port
--user	postgres	Username
--password	(empty)	Password
--dbname	postgres	Database name
--schema	public	PostgreSQL schema
--table	(required)	Hypertable name
--ts-column	(none)	Timestamp column for filtering and ORDER BY
--from-ts	(none)	Start of time range (ISO 8601, inclusive)
--to-ts	(none)	End of time range (ISO 8601, exclusive)
--output	(auto)	Output .pfc file path
--batch-size	10000	Rows per fetch batch
--verbose	false	Show progress and compression stats
--pfc-binary	(auto)	Path to pfc_jsonl binary

---

Output

The export produces:

sensor_data.pfc        # compressed archive (~9% of JSONL size)
sensor_data.pfc.bidx   # block-level timestamp index

The .bidx index allows DuckDB to query a specific time window without loading the full archive.

---

Querying the archive

INSTALL pfc FROM community;
LOAD pfc;
LOAD json;

-- Scan the full archive
SELECT *
FROM read_pfc_jsonl('./sensors_q1_2025.pfc')
LIMIT 100;

-- Time-window query (only decompresses relevant blocks)
SELECT *
FROM read_pfc_jsonl(
    './sensors_q1_2025.pfc',
    ts_from = epoch(TIMESTAMPTZ '2025-02-01 00:00:00+00'),
    ts_to   = epoch(TIMESTAMPTZ '2025-02-02 00:00:00+00')
);

---

Part of the PFC Ecosystem

→ View all PFC tools & integrations

Direct integration	Why
pfc-archiver-timescaledb	Same DB, different mode — archiver runs continuously as a daemon; exporter is one-shot
pfc-export-cratedb	Same concept for CrateDB
pfc-export-questdb	Same concept for QuestDB

---

Disclaimer

pfc-export-timescaledb is an independent open-source project and is not affiliated with, endorsed by, or associated with Timescale, Inc. or the TimescaleDB project. TimescaleDB is a trademark of Timescale, Inc.

---

License

pfc-export-timescaledb (this repository) is released under the MIT License — see LICENSE.

The PFC-JSONL binary (pfc_jsonl) is proprietary software — free for personal and open-source use. Commercial use requires a license: info@impossibleforge.com