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 IF NOT EXISTS 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.

Parent directory must already exist

fs9_write (and fs9_append) do not create parent directories automatically. Writing to a path whose parent directory does not exist yet fails with 58P01 — fs9 resource was not found, which names no path. Call extensions.fs9_mkdir('/data', true) first if the directory may not exist.

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

Append does not insert a line separator

fs9_append writes the bytes exactly as given. If the existing file does not already end with a newline, the appended record is joined onto the last line. For JSONL that produces one malformed line, which is silently skipped when the file is read as JSON — so both the previous record and the new one disappear from query results, with no error. The bytes are still on disk and remain visible with format => 'text'.

Always terminate appended records with E'\n' (as above), and make sure the file’s existing final line ends with one.

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.

Storage stats do not currently track writes accurately

fs9_storage_stats() does not reliably reflect the current filesystem contents — it can return 0 for all columns on a database with existing files, or a stale count that does not update after new writes. Do not rely on it for accurate monitoring yet; enumerate files directly with extensions.fs9('/path/') per directory if you need an accurate count.

fs9_cached_storage_stats

Table-valued function that returns the most recently cached filesystem statistics, plus the time they were computed. It only reads the cache; it never triggers a scan.

SQL

SELECT total_files, total_directories, total_logical_bytes, computed_at
FROM extensions.fs9_cached_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
computed_at	INT64	Unix epoch seconds at which the cached values were computed — not a TIMESTAMP. Convert with TO_TIMESTAMP(computed_at)

Always returns exactly one row — never an empty result — so check the column values, not the row count. In practice the cache is already warm: a brand-new database returns zeros with a computed_at of the current time rather than NULL. Handle NULL defensively anyway, since the cold-cache path returns an all-NULL row:

SQL

SELECT CASE
         WHEN computed_at IS NULL THEN 'no cached stats yet'
         ELSE 'computed at ' || TO_TIMESTAMP(computed_at)::text
       END AS cache_state
FROM extensions.fs9_cached_storage_stats();

Inherits the same accuracy limitation, and computed_at is not monotonic

These are cached values from the same subsystem as fs9_storage_stats(), so the caution above applies here too — the counts can be 0 or stale on a database that has files. computed_at tells you how old the numbers are, not whether they are correct.

Two further traps if you are polling:

• Calling fs9_storage_stats() does not refresh this cache. Only a background worker does, roughly once a minute, so repeated calls return an unchanged computed_at for up to that long. There is no way to force a refresh from SQL — DB9_REFRESH_STORAGE_STATS() is sealed in the current release.
• computed_at can go backwards between consecutive calls. The cache is per-node and the workers are not synchronised, so successive queries on the same database can land on different nodes and report timestamps tens of seconds apart in either direction. Do not use it as a monotonic clock or to detect “did the stats update since I last looked”.

Table Function

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

Use named parameters, not positional arguments

Optional arguments — recursive, exclude, format, delimiter, header — must be passed by name (format => 'csv'). A positional second argument is rejected:

ERROR: fs9: unexpected positional argument (use named parameters like format => 'csv')

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;

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;

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

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

Globs do not decode Parquet

Glob expansion decodes CSV/TSV and JSONL only. A .parquet file matched by a glob falls back to the plain-text reader, so a pattern like extensions.fs9('/data/*.parquet') returns zero rows with the raw-text schema (_line_number, line, _path) instead of the file’s real columns — it does not raise an error.

Read Parquet a file at a time, where the schema is taken from the file metadata:

SQL

SELECT * FROM extensions.fs9('/data/export.parquet');

To load several Parquet files, COPY each one — see Parquet Import.

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.

JSONL Query Function

extensions.fs9_jg(path TEXT, query TEXT) queries JSON Lines files directly from SQL. Each line is parsed independently and every match produces one row, so a JSONL log becomes a relation without an intermediate COPY.

SQL

SELECT _line_number, _match_path, value
FROM extensions.fs9_jg('/logs/app.jsonl', 'level');

▶ Run

Column	Type	Description
_path	TEXT	File the match came from — useful when path is a glob
_line_number	INT64	1-based line number within that file
_match_path	TEXT	Dotted path of the matched value (array indices are rendered as tags.0)
value	JSONB	The matched value

path accepts a glob, so one call can span files: extensions.fs9_jg('/logs/*.jsonl', 'level').

The query is a bare key path — not jq, not JSONPath

This is the single most likely thing to get wrong, because neither wrong syntax reports itself clearly, and db9 fs sh’s jq builtin on this same page does take jq syntax:

Spelling	Result
level, user.name	works
.level, .user.name (jq style)	XX000 internal error — the underlying message is not surfaced
$.level, $.user.name (JSONPath style)	parses, matches nothing, returns zero rows silently

The JSONPath form is the dangerous one: a query returning no rows looks like “the data has no such field” rather than “the syntax is wrong”. Write paths bare.

Supported path forms, all verified against a two-line file whose objects are {"user":{"name":…,"age":…},"tags":[…]}:

Query	Matches
user.name	one row per line — nested object member
tags[0]	array index; _match_path comes back as tags.0
tags[*]	every array element
user.*	every member of an object
**	recursive descent — the whole document plus every subpath

A key that does not exist returns zero rows rather than an error, so treat an empty result as “no match” only after confirming the path spelling.

Event Notifications

db9 fs watch is the recommended way to consume events

fs9_events() can be queried directly from SQL (see below), but db9 fs watch from the CLI handles cursor-based polling for you and is the recommended interface for most applications.

fs9_events() must be called schema-qualified — SELECT * FROM extensions.fs9_events(). The unqualified name is not registered as a table-valued function and fails with ERROR: function fs9_events() does not exist (42883).

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.

Start your cursor at '0', not at an arbitrary timestamp

Events are delivered — a fs9_write shows up as a CREATE/WRITE row immediately. But a since_id older than the stream’s retained range returns zero rows instead of the full backlog:

SQL

SELECT count(*) FROM extensions.fs9_events();                     -- 17
SELECT count(*) FROM extensions.fs9_events('0');                  -- 17
SELECT count(*) FROM extensions.fs9_events('1750000000000-0');    -- 0  (cursor too old)

Seed a new consumer with '0', then advance it using stream_id values you actually read back. Do not synthesize a cursor from a wall-clock timestamp.

SQL

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

-- Events after a stream ID you previously read back (cursor-based polling)
SELECT * FROM extensions.fs9_events('1786979518971-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

Job scheduling is unavailable in the current release

cron.schedule(...) currently returns ERROR: feature "cron" is unavailable (PreActivationSeal) (55000). fs9_write itself is unaffected — only the scheduling wrapper is disabled. See 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 || ',' || item || ',' || total, E'\n')
     FROM (SELECT id::text, item, sum(amount)::text AS total
           FROM orders WHERE created_at > now() - interval '1 day'
           GROUP BY id, item) t)
  )
$$);

Upload via HTTP and store in fs9

SQL

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

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	SQLSTATE	Cause
fs9: permission denied (superuser required)	42501	Non-superuser role attempted an fs9 operation
fs9 resource was not found	58P01	The path does not exist — including the parent directory of a write
fs9 resource has the wrong object type	42809	The path exists but is not the kind of object the call needs (reading a directory, for example)
fs9 resource already exists	58P02	Creating something that is already there — e.g. fs9_mkdir on an existing directory
fs9 directory is not empty	42809	fs9_remove on a directory that still has entries; pass true as the second argument to recurse
permission denied for fs9 resource	42501	The role may use fs9 but not this particular resource
fs9 resource exceeds the supported size limit		File exceeds 100 MB
internal error	XX000	Argument validation failures (a negative fs9_read_at offset or fs9_truncate size) and I/O faults both surface this way, with no detail

Argument-validation messages are not passed through to the client. fs9_truncate still builds the text fs9_truncate: size must be non-negative internally, but the wire only ever carries internal error, so do not match on it.

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