fs9 — File System

fs9 is a TiKV-backed file system accessible from SQL. It provides scalar functions for reading and writing files, a table function for querying file contents as rows, and a WebSocket API for programmatic access from the TypeScript SDK.

Files are stored in TiKV using page-based storage (16 KB pages) with inode metadata — not on a local disk. Each database has its own isolated file system.

Installation

SQL

CREATE EXTENSION fs9;

All fs9 functions require superuser privileges (the default admin role).

Scalar Functions

fs9_read

Read an entire file as text.

SQL

SELECT extensions.fs9_read('/logs/app.log');

Returns TEXT (UTF-8, lossy conversion for non-UTF-8 bytes). Returns NULL if the path argument is NULL.

fs9_write

Write content to a file, creating it if it does not exist and overwriting if it does. Returns the number of bytes written.

SQL

SELECT extensions.fs9_write('/data/output.txt', 'hello world');
-- Returns: 11

Accepts both TEXT and BYTEA content.

fs9_append

Append content to the end of a file. Creates the file if it does not exist. Returns the number of bytes appended.

SQL

SELECT extensions.fs9_append('/logs/events.jsonl', '{"event":"click","ts":"2026-03-12T10:00:00Z"}' || E'\n');

fs9_read_at

Read a range of bytes from a file. Returns TEXT.

SQL

SELECT extensions.fs9_read_at('/data/large.bin', 0, 1024);
-- Reads first 1024 bytes

Both offset and length must be non-negative. Reading beyond the end of the file returns whatever bytes are available.

fs9_write_at

Write data at a specific offset in a file. Creates the file if it does not exist. Fills any gap between the current end and the offset with null bytes. Returns the number of bytes written.

SQL

SELECT extensions.fs9_write_at('/data/file.bin', 100, 'data at offset 100');

fs9_truncate

Truncate a file to a specific size. If the new size is larger than the current size, the file is padded with null bytes. Returns TRUE on success.

SQL

SELECT extensions.fs9_truncate('/logs/rolling.log', 0);
-- Empties the file

fs9_exists

Check whether a file or directory exists.

SQL

SELECT extensions.fs9_exists('/data/config.json');
-- Returns: true or false

fs9_size

Return the size of a file in bytes.

SQL

SELECT extensions.fs9_size('/data/export.csv');
-- Returns: 48271

fs9_mtime

Return the last modification time of a file as an RFC 3339 timestamp (UTC).

SQL

SELECT extensions.fs9_mtime('/data/export.csv');
-- Returns: '2026-03-12T15:30:45Z'

fs9_remove

Remove a file or directory. Returns the number of items removed. Supports glob patterns.

SQL

-- Remove a single file
SELECT extensions.fs9_remove('/tmp/scratch.txt');

-- Remove a directory and its contents
SELECT extensions.fs9_remove('/tmp/work/', true);

-- Remove files matching a glob
SELECT extensions.fs9_remove('/logs/2026-01-*.jsonl');

The second argument (recursive) defaults to false. A non-empty directory requires recursive = true.

fs9_mkdir

Create a directory. Returns TRUE on success.

SQL

-- Create a single directory
SELECT extensions.fs9_mkdir('/data/exports');

-- Create nested directories
SELECT extensions.fs9_mkdir('/data/exports/2026/03', true);

The second argument (recursive) defaults to false. With recursive = true, parent directories are created as needed.

Table Function

The extensions.fs9() table function reads files and directories as SQL rows. It operates in three modes depending on the path.

Directory listing

When the path ends with /, fs9 returns directory entries:

SQL

SELECT path, type, size, mtime
FROM extensions.fs9('/logs/')
ORDER BY path;

Column	Type	Description
path	TEXT	Full path
type	TEXT	"file" or "dir"
size	INT64	Size in bytes (0 for directories)
mode	INT64	Unix permission mode
mtime	TEXT	Last modified (RFC 3339 UTC)

Use recursive => true to walk subdirectories:

SQL

SELECT path, size FROM extensions.fs9('/data/', recursive => true)
WHERE type = 'file'
ORDER BY size DESC;

Use exclude to skip files matching a pattern:

SQL

SELECT * FROM extensions.fs9('/logs/', exclude => '*.tmp');

File reading

When the path points to a file, fs9 reads its contents as rows. The format is auto-detected from the file extension:

Extension	Format	Row schema
.csv	CSV	_line_number INT, columns from header, _path TEXT
.tsv	TSV	_line_number INT, columns from header, _path TEXT
.jsonl, .ndjson	JSON Lines	_line_number INT, line JSONB, _path TEXT
.parquet	Parquet	Schema from file metadata
Other	Plain text	_line_number INT, line TEXT, _path TEXT

SQL

-- CSV with auto-detected columns
SELECT * FROM extensions.fs9('/data/users.csv');

-- JSONL as queryable JSONB
SELECT line->>'level' AS level, count(*)
FROM extensions.fs9('/logs/app.jsonl')
GROUP BY 1;

Override the format or delimiter with named parameters:

SQL

-- Force CSV format on a .dat file
SELECT * FROM extensions.fs9('/data/raw.dat', format => 'csv');

-- Pipe-delimited file without a header row
SELECT col_0, col_1
FROM extensions.fs9('/data/raw.dat', format => 'csv', delimiter => '|', header => false);

For CSV/TSV without a header, columns are named col_0, col_1, etc.

Invalid JSON lines in JSONL files are silently skipped.

Glob matching

When the path contains *, ?, or [, fs9 expands the glob and reads all matching files:

SQL

-- All CSV files in a directory
SELECT * FROM extensions.fs9('/data/sales/*.csv');

-- Recursive match across subdirectories
SELECT _path, line->>'event' AS event
FROM extensions.fs9('/logs/**/*.jsonl')
ORDER BY _path, _line_number;

The schema is determined by the first matching file. All files use the same format. The _path column identifies which file each row came from.

Glob parameters:

• format, delimiter, header — same as file reading
• exclude — glob pattern to skip (e.g., *.tmp)

Hidden files (dotfiles) are excluded unless the pattern explicitly starts with . or contains /..

If total bytes read across all files exceeds the budget (100 MB), fs9 stops reading and returns partial results with a warning.

Practical Patterns

Append-only log ingestion

SQL

-- Write a log entry
SELECT extensions.fs9_append(
  '/logs/agent.jsonl',
  '{"ts":"2026-03-12T10:00:00Z","action":"search","query":"revenue"}' || E'\n'
);

-- Query log entries
SELECT line->>'action' AS action, count(*)
FROM extensions.fs9('/logs/agent.jsonl')
GROUP BY 1 ORDER BY 2 DESC;

Periodic report generation with pg_cron

SQL

SELECT cron.schedule('daily-report', '0 6 * * *', $$
  SELECT extensions.fs9_write(
    '/reports/daily-' || to_char(now(), 'YYYY-MM-DD') || '.csv',
    (SELECT string_agg(id || ',' || name || ',' || total, E'\n')
     FROM (SELECT id::text, name, sum(amount)::text AS total
           FROM orders WHERE created_at > now() - interval '1 day'
           GROUP BY id, name) t)
  )
$$);

Upload via HTTP and store in fs9

SQL

SELECT extensions.fs9_write(
  '/imports/feed.json',
  (SELECT content FROM http_get('https://api.example.com/feed'))
);

Clean up old files

SQL

-- List files older than 7 days
SELECT path, mtime FROM extensions.fs9('/logs/')
WHERE type = 'file'
  AND mtime < to_char(now() - interval '7 days', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');

-- Remove them
SELECT extensions.fs9_remove('/logs/2026-02-*.jsonl');

Permissions

All fs9 scalar functions and the table function require superuser privileges. The default admin role is a superuser. Regular database users created via db9 db users create cannot use fs9.

Connect key scopes

When accessing fs9 via the WebSocket API (used by the TypeScript SDK), connect keys control access:

Scope	Permissions
fs9:ro	Read-only: stat, read, readdir, size, mtime, exists
fs9:rw	Read and write: all operations

Connect keys are created via the REST API:

Terminal

curl -X POST https://api.db9.ai/customer/databases/<id>/connect-keys \
  -H "Authorization: Bearer $DB9_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "agent-fs", "scopes": ["fs9:rw"]}'

WebSocket API

The TypeScript SDK uses a WebSocket connection for fs9 file operations. This provides streaming support for large files and avoids SQL overhead for file I/O.

The WebSocket server listens on port 5480 by default. The protocol uses JSON frames with an op field and a request id for correlation.

Available operations: auth, stat, readdir, mkdir, read, write, pwrite, append, truncate, unlink, rm, rename.

Files larger than 1 MB are automatically streamed in 64 KB chunks.

Connection limits:

• Auth timeout: 10 seconds
• Idle timeout: 5 minutes
• Max connections per tenant: 50
• Max JSON frame: 2 MB

See the TypeScript SDK for the client-side API.

Limits

Limit	Value
Max file size	100 MB
Max total bytes per glob query	100 MB
Max files per glob expansion	10,000
Concurrent read budget (scalar functions)	128 MB
Page size (internal storage)	16 KB
Write stream flush threshold	256 KB
WebSocket max connections per tenant	50
WebSocket idle timeout	5 minutes
WebSocket max JSON frame	2 MB
WebSocket streaming chunk size	64 KB

Error Messages

Error	Cause
fs9: permission denied (superuser required)	Non-superuser role attempted fs9 operation
fs9: TiKV storage backend not available	Extension context not initialized
fs9: file too large: N bytes exceeds limit M	File exceeds 100 MB
fs9_read: concurrent read budget exceeded	Too many concurrent fs9_read calls (>128 MB total)
fs9_read_at: offset must be non-negative	Negative offset passed to fs9_read_at
fs9_truncate: size must be non-negative	Negative size passed to fs9_truncate

Related Docs

• Analyze Agent Logs with fs9 — tutorial: write, query, and aggregate agent logs
• Scheduled Jobs with pg_cron — automate fs9 reports on a schedule
• HTTP from SQL — fetch external data and store in fs9
• Extensions Overview — all 9 built-in extensions
• Limits and Quotas — all operational limits
• TypeScript SDK — programmatic fs9 access via WebSocket