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;

▶ Run

Superuser required

All fs9 functions require superuser privileges. Only the default admin role can use fs9 from SQL. Regular database users created via db9 db users create will get a permission denied error.

FUSE Mount on macOS

If you use db9 fs mount on macOS, install macFUSE first:

Terminal

brew install --cask macfuse

Then approve the macFUSE system extension in System Settings > Privacy & Security if prompted. After approval, retry the mount command.

Notes:

• This prerequisite applies to db9 fs mount only.
• Other db9 fs commands such as fs cp, fs ls, and fs sh do not require macFUSE.
• See the CLI Reference for the command-level mount docs.

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_read_bytea

Read an entire file as binary.

SQL

SELECT extensions.fs9_read_bytea('/data/image.png');

Returns BYTEA. Use this instead of fs9_read when the file contains non-UTF-8 binary data.

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_read_at_bytea

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

SQL

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

Same semantics as fs9_read_at, but returns raw bytes instead of text.

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.

fs9_storage_stats

Table-valued function that returns aggregated filesystem storage statistics.

SQL

SELECT total_files, total_directories, total_logical_bytes
FROM extensions.fs9_storage_stats();

Column	Type	Description
total_files	INT64	Total number of files
total_directories	INT64	Total number of directories
total_logical_bytes	INT64	Total logical size of all files in bytes

Always returns a single row. Useful for monitoring filesystem usage from SQL or building storage dashboards.

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;

▶ Run

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;

▶ Run

Use exclude to skip files matching a pattern:

SQL

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

▶ Run

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;

▶ Run

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);

▶ Run

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;

▶ Run

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 /..

Glob read budget

If total bytes read across all matching files exceeds 100 MB, fs9 stops reading and returns partial results with a warning. Use exclude patterns or more targeted paths for large directories.

Event Notifications

fs9_events() not available via SQL

The fs9_events() table-valued function is not yet available as a direct SQL call. Calling it returns:

ERROR: unsupported table-valued function: fs9_events (0A000)

Use db9 fs watch from the CLI instead, which handles event polling internally.

The fs9_events() table function returns a stream of filesystem mutation events. It powers db9 fs watch and can be queried directly from SQL for custom change-tracking.

SQL

-- All events since the beginning
SELECT * FROM extensions.fs9_events();

-- Events after a known stream ID (cursor-based polling)
SELECT * FROM extensions.fs9_events('1711324800000-0');

-- Events under a specific path prefix
SELECT * FROM extensions.fs9_events('0', '/data/');

-- Events with a custom limit (default: 10,000)
SELECT * FROM extensions.fs9_events('0', '/logs/', 5000);

Parameters

Parameter	Type	Default	Description
since_id	TEXT	'0'	Return events with stream ID greater than this value (Redis Stream ID format, e.g. '1711324800000-0')
path_prefix	TEXT	NULL	Filter to events where path starts with this prefix
limit	INT64	10000	Maximum number of event rows to return

Return columns

Column	Type	Description
stream_id	TEXT	Redis Stream ID (e.g. '1711324800000-0')
event_type	TEXT	CREATE, WRITE, DELETE, RENAME, or MKDIR
path	TEXT	Affected path (post-mutation)
old_path	TEXT	Previous path (only set for RENAME events)
inode	INT64	Inode ID
generation	INT64	Post-mutation generation
is_dir	BOOLEAN	Whether the target is a directory
size	INT64	Post-mutation file size (0 for directories)
timestamp	TIMESTAMPTZ	Event timestamp

Note: since_id also accepts integer values for backwards compatibility — they are converted to string internally.

Cursor-based polling pattern

SQL

-- Initial read: get the latest stream_id
SELECT stream_id FROM extensions.fs9_events('0') ORDER BY stream_id DESC LIMIT 1;

-- Subsequent polls: pass the last seen stream_id as since_id
SELECT stream_id, event_type, path, old_path, size, timestamp
FROM extensions.fs9_events(:last_stream_id)
ORDER BY stream_id;

For CLI-based watching, use db9 fs watch which handles the polling loop, overflow detection, and formatting automatically. See the CLI Reference.

Symbolic Links

fs9 supports symbolic links through the CLI FUSE mount and WebSocket API.

CLI (FUSE Mount)

Terminal

# Create a symlink
ln -s /data/report.csv /data/latest.csv

# Read the symlink target
readlink /data/latest.csv

WebSocket API

The symlink and readlink operations are available over the WebSocket protocol:

• symlink(path, target) — create a symbolic link at path pointing to target
• readlink(path) — return the target of a symbolic link

Symlink properties:

• Mode: 0o777 (all permissions)
• Type: reported as "symlink" in stat responses
• Maximum target length: 4096 bytes
• Writing to a symlink directly is not allowed — write to the target path instead

File Permissions (Mode Bits)

When creating files and directories, fs9 preserves Unix permission bits:

Operation	Default Mode
File create	0644 (rw-r--r--)
Directory create	0755 (rwxr-xr-x)
Symlink	0777 (rwxrwxrwx)

Executables uploaded with db9 fs cp or written via FUSE retain their original mode bits (e.g., 0755 for shell scripts). The mode field is visible in stat responses and db9 fs ls -l output.

JSONL Support in fssh

The jq builtin in db9 fs sh supports JSON Lines (JSONL) format — when input contains multiple JSON objects (one per line), the filter is applied to each object independently:

Terminal

db9 fs sh mydb -c "cat /logs/events.jsonl | jq '.event'"

Output

"click"
"pageview"
"submit"

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;

▶ Run

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');

▶ Run

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, symlink, readlink.

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

Serverless Functions

fs9 is also accessible from Serverless Functions via ctx.fs9 — you can read and write files from your deployed JavaScript/TypeScript code without going through SQL. See the Serverless Functions runtime for details.

Next Steps

• 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