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

Only the table functions depend on this. The scalar functions work on a new database without it, but extensions.fs9(), extensions.fs9_jg(), extensions.fs9_events(), extensions.fs9_storage_stats() and extensions.fs9_cached_storage_stats() fail with ERROR: unsupported table-valued function: fs9 (0A000, naming the function you called) until the extension is created. Run it once per database.

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.

Which error you get depends on the kind of function, so do not branch on it — see Permissions.

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. Returns NULL if the path argument is NULL.

Non-UTF-8 content is rejected, not decoded lossily

fs9_read does not substitute replacement characters for bytes it cannot decode — it refuses the file outright. A single invalid byte anywhere in the file is enough:

ERROR: fs9_read: path /data/blob.bin: file is not valid UTF-8; use fs9_read_bytea for binary data

The SQLSTATE is 22021. Unlike 58P01 fs9 resource was not found, this message does name the path. fs9_read_at fails the same way and points at fs9_read_at_bytea instead.

That is the loud failure. The quiet one is the table function: read as extensions.fs9('/data/blob.bin'), the same file raises nothing at all. It returns the complete lines that precede the first invalid byte and silently stops there — zero rows when the bad byte comes before any newline, a truncated prefix otherwise. Use fs9_read_bytea whenever the content may not be text.

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. If you cannot guarantee that — the file was written by something else — also prefix the record with E'\n', as in Append-only log ingestion. The blank lines this can leave are skipped when the file is read as JSONL, but they still count toward _line_number, so line numbers can have gaps.

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.

The range must decode as UTF-8, not just the file. If the requested bytes are not valid UTF-8 the call fails with 22021 exactly as fs9_read does, naming fs9_read_at_bytea as the fix — and a byte offset that lands mid-character is enough to trigger it on a file that is perfectly valid text. Reading aé漢字bc (11 bytes) at offset 0:

Length	Result
1	a
2	22021 — splits the two-byte é
3	aé
4	22021 — splits the three-byte 漢

Offsets and lengths are byte counts, so pick them on character boundaries, or use fs9_read_at_bytea and decode client-side.

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'). Any positional argument after the path is rejected as if the function did not exist — extensions.fs9('/data/raw.dat', 'csv') fails with:

ERROR: function extensions.fs9(unknown, unknown) does not exist

The SQLSTATE is 42883, and the listed types follow the arguments you passed ('csv'::text gives fs9(unknown, text), true gives fs9(unknown, boolean)). The function does exist — pass the option by name.

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", "dir", or "symlink"
size	INT64	Size in bytes. Directories report 4096, not 0; a symlink reports the length of its target path, not the target’s size
mode	INT64	Unix permission mode
mtime	TEXT	Last modified (RFC 3339 UTC)

size here is metadata about the entry, not a byte count you can sum: filtering on WHERE type = 'file' before aggregating avoids counting a flat 4096 per directory. fs9_events.size reports 0 for a directory only on a MKDIR event — its RENAME and DELETE events carry the same 4096. Use is_dir to identify directory events, never size = 0.

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 INT64, columns from header, _path TEXT
.tsv	TSV	_line_number INT64, columns from header, _path TEXT
.jsonl, .ndjson	JSON Lines	_line_number INT64, line JSONB, _path TEXT
.parquet	Parquet	Schema from file metadata
Other	Plain text	_line_number INT64, line TEXT, _path TEXT

A non-UTF-8 file is truncated silently, not reported as an error

Every format above decodes as text first. Where the decode fails the table function does not raise and does not warn — it yields the complete lines before the first invalid byte and stops:

SQL

-- /data/blob.bin: 4 bytes, invalid from byte 0
SELECT count(*) FROM extensions.fs9('/data/blob.bin');   -- 0, even though the file has content

-- /data/mixed.bin: 3 newline-terminated lines, invalid byte on line 2
SELECT _line_number, line FROM extensions.fs9('/data/mixed.bin');
--  1 | good line        <- lines 2 and 3 are dropped, no error, no warning

Zero rows is just the special case where the bad byte precedes any newline. The dangerous case is the partial one: the result looks like a perfectly good, smaller file. Nothing in the result set distinguishes “this is all the data” from “this is where decoding gave up”.

The scalar readers are the honest ones here: fs9_read on either file raises 22021. Use it — or compare against extensions.fs9_size(...) — before trusting a row count, and read genuinely binary content with fs9_read_bytea.

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_number, line
FROM extensions.fs9('/logs/**/*.jsonl')
ORDER BY _path, _line_number;

▶ Run

All matched files are read with one format and must produce one identical column list. The _path column identifies which file each row came from.

Every matched file must yield the same columns — a mismatch fails the whole query

The schema is not inferred from the first file and reused for the rest; it is required to match. Formats whose schema is fixed (line JSONB for JSONL, line TEXT for plain text) always match, so they tolerate any content. CSV and TSV take their columns from each file’s own header line, so one differing header fails the entire read:

Files matched by the glob	Result
.csv with identical header lines	rows from every file
.csv with the same column count but different names (x,y and p,q)	XX000
.csv with the same names in a different order (x,y and y,x)	XX000
.csv with different column counts (in either order)	XX000
.csv and .jsonl matched by one pattern	XX000
.jsonl with completely different keys per file	rows from every file
Extensionless or unrecognised files with any content	rows from every file

The failure is ERROR: internal error (XX000) and it names neither the offending file nor the column, so with many matches you cannot tell which file broke it. It is all-or-nothing: one odd file among ten returns no rows at all rather than skipping that file.

Three ways through, in order of how much they preserve:

SQL

-- 1. Exclude the odd file, keeping real column names for the rest
SELECT * FROM extensions.fs9('/data/*.csv', exclude => 'legacy.csv');

-- 2. Read positionally — header rows become data, columns become col_0, col_1, …
--    Fixes differing NAMES; still fails when the column COUNTS differ.
SELECT * FROM extensions.fs9('/data/*.csv', format => 'csv', header => false);

-- 3. Read as raw lines — always works, parse per row yourself
SELECT _path, line FROM extensions.fs9('/data/*.csv', format => 'text');

Forcing format => 'csv' does not rescue a mixed-extension glob: it makes every file parse as CSV, which usually leaves the column counts different and fails the same way.

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') comes back with the raw-text schema (_line_number, line, _path) instead of the file’s real columns — and raises no error.

What you get in those rows is whatever survives UTF-8 decoding of a binary file, which is truncated silently at the first invalid byte: usually zero rows, sometimes a handful of rows of mangled header bytes. Neither outcome is a signal you can test for — the absence of the real column names is.

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.

A single-file read still fails with ERROR: Parquet feature is not supported (0A000) if the file contains a column type DB9 cannot map — a TIME column is the most common case. The message names neither the file nor the column; see Parquet type mapping for the full list.

Glob read budget — exceeding it drops files silently

If total bytes read across all matching files exceeds 100 MB, or the pattern matches more than 10,000 files, fs9 stops reading and returns what it had — silently. There is no warning, no notice and no error on either the wire protocol or the HTTP SQL API; the query simply succeeds with fewer files than it matched. Two 60 MB files matched by one pattern return the rows of the first and nothing from the second:

SQL

SELECT count(DISTINCT _path) FROM extensions.fs9('/logs/*.log');  -- 1, not 2

Because the cut-off looks exactly like “those files had no rows”, an aggregate over a growing directory can start under-reporting without anything changing in the query. Compare count(DISTINCT _path) against the file count from a directory listing when the total could approach the budget, and 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 subscribes to pushed filesystem events for you and is the recommended interface for most applications. It does not need the fs9 extension; it falls back to polling fs9_events() only on a server without push-watch support.

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 that you can query directly from SQL for custom change-tracking.

Resume only from a stream_id you read back — a numeric or legacy cursor returns the full backlog

Each stream_id has the form v2:<milliseconds>-<sequence>:<hash>, for example v2:1790005680240-0:8a7be825c1ea93227ab02abe8ff46e48. Pass one back unchanged as since_id and you get only the events after it.

A numeric or <milliseconds>-<sequence> since_id without the v2: prefix is treated as a legacy cursor and returns the full retained backlog, the same as '0' — no matter which timestamp it names. That includes a future timestamp, and the <milliseconds>-<sequence> part of a real stream_id with the prefix and hash stripped:

SQL

SELECT count(*) FROM extensions.fs9_events('0');                -- the full backlog
SELECT count(*) FROM extensions.fs9_events('1750000000000-0');  -- the full backlog, not 0
SELECT count(*) FROM extensions.fs9_events('9999999999999-0');  -- the full backlog, not 0

A v2: cursor that does not match an event still in the stream — malformed, pointing at a position that is not there, or carrying a different hash for that position — fails with ERROR: internal error (XX000), as does a non-numeric cursor such as 'abc'. On that error, start again from '0'.

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(:'last_stream_id');

-- 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 after this cursor — a stream_id read back from an earlier call, or '0' for everything retained. A numeric or legacy <ms>-<seq> cursor is ignored and returns the full retained backlog; a malformed value fails (see the caution above)
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	Event cursor, v2:<milliseconds>-<sequence>:<hash> (e.g. 'v2:1790005680240-0:8a7be825c1ea93227ab02abe8ff46e48'). Pass it back unchanged as since_id
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	Always 0 in the current release — see below
generation	INT64	Always 0 in the current release — see below
is_dir	BOOLEAN	Whether the target is a directory
size	INT64	Post-mutation size. 0 for a directory on MKDIR, but 4096 for a directory on RENAME/DELETE — use is_dir, not size, to spot directory events
timestamp	TIMESTAMPTZ	Event timestamp

Note: since_id also accepts an integer, which is converted to text. Like a legacy <milliseconds>-<sequence> cursor, it returns the full retained backlog.

inode and generation are placeholders in the event stream

Both columns are present and typed, but every event carries 0 — including events raised seconds ago. They cannot be used to correlate events with a file, to detect that a path was replaced, or to order two writes.

SQL

SELECT count(*) FILTER (WHERE inode <> 0) AS real_inodes,
       count(*) FILTER (WHERE generation <> 0) AS real_generations
FROM extensions.fs9_events();   -- 0 and 0

Identity within the stream comes from stream_id, and identity of a file comes from path. A real, changing generation is available per file from the WebSocket stat response — just not here. Do not write a consumer that dedupes on inode; it would collapse every event into one.

Cursor-based polling pattern

SQL

-- Initial read: get the latest stream_id (psql \gset stores it in :last_stream_id).
-- stream_id compares as text, so order by its numeric millisecond and sequence parts.
SELECT stream_id AS last_stream_id
FROM extensions.fs9_events('0')
ORDER BY split_part(split_part(stream_id, ':', 2), '-', 1)::bigint DESC,
         split_part(split_part(stream_id, ':', 2), '-', 2)::bigint DESC
LIMIT 1 \gset

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

The cursor contains colons, so pass it as a quoted string literal (in psql, :'last_stream_id' rather than :last_stream_id). An event can take a moment to appear after the write that caused it, so an empty poll does not mean nothing changed — keep the same cursor and poll again.

For CLI-based watching, use db9 fs watch, which handles the event subscription, gap warnings, 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 and in the fs9() listing
• Size: the length of the target path, not the size of the target
• Maximum target length: 4096 bytes

Writing to a symlink path is permitted, and the two ways it resolves differ

Nothing rejects a write aimed at a symlink, so the link is only as durable as the code that touches it. Which of two very different things happens depends on the call, not on the link, and every one of them reports success. Behaviour is identical over SQL and the WebSocket API:

Call on the symlink’s path	Effect on the link	Effect on the target
fs9_write / WS write	Destroyed — replaced by a regular file holding the new bytes (mode drops 0777 → 0644)	None; it keeps its old contents
fs9_append / WS append	Preserved	Followed — bytes appended to the target
fs9_write_at / WS pwrite	Preserved	Followed — bytes written at that offset in the target
fs9_truncate / WS truncate	Preserved	Followed — the target is truncated

fs9_write is the destructive one: the link silently becomes a file, readlink on that path starts failing, and the data the link pointed at is orphaned rather than updated. The other three reach through and modify the real file, so fs9_truncate(link, 0) empties the target.

Reads never follow the link. fs9_read and the fs9() table function fail with invalid fs9 request (22023), and WS read returns EINVAL cannot read symlink as file; use readlink. fs9_size returns the target path length.

Resolve the link with readlink and address the target explicitly whenever you intend to write through it.

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

The builtin prints strings unquoted

Extracted strings come back raw, without the surrounding quotes that jq itself would print for the same filter — the output above is what jq -r '.event' gives you elsewhere, so pipe it straight into other shell commands. Passing -r to the builtin is not the way to ask for this: it produces no output at all and still exits 0. Non-string values and whole objects are rendered as JSON.

Practical Patterns

Append-only log ingestion

SQL

-- Write a log entry (the leading E'\n' guards against a file whose last line has no newline)
SELECT extensions.fs9_append(
  '/logs/agent.jsonl',
  E'\n' || '{"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 functions require superuser privileges. The default admin role is a superuser. Regular database users created via db9 db users create cannot use fs9.

Only the scalar functions report the denial as a permission error

The privilege boundary is the same everywhere, but it is not reported the same way, so a client that keys off the error cannot tell “you may not do this” from “something broke”:

Called as a non-superuser	Error	SQLSTATE
Any of the 13 scalar functions — fs9_read, fs9_read_bytea, fs9_write, fs9_append, fs9_read_at, fs9_read_at_bytea, fs9_write_at, fs9_truncate, fs9_exists, fs9_size, fs9_mtime, fs9_remove, fs9_mkdir	fs9: permission denied (superuser required)	42501
Any of the 5 table functions — fs9, fs9_jg, fs9_events, fs9_storage_stats, fs9_cached_storage_stats	internal error	XX000

The XX000 really is the privilege path rather than a missing function: calling a name that does not exist returns 42883 instead. Treat XX000 from a table function as “check your role first”.

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.

Operations verified to answer successfully on the current release:

Group	Operations
Session	auth
Metadata	stat, readdir, readdir_recursive, chmod
Files	read, write, pwrite, append, truncate
Namespace	mkdir, unlink, rm, rename, symlink, readlink
Batch	batch_stat, batch_inline_read, batch_write
Watch	watch_subscribe, watch_unsubscribe

The rest of the protocol is parsed but does not currently complete. batch_write_atomic returns ENOSYS (batch_write_atomic is not supported by this backend); the multipart-upload family — create_upload, presign_part, presign_parts, complete_upload, abort_upload — and prepare_download all return EIO (FS9 operation failed), including for well-formed requests. Treat them as unavailable until they answer ok, and upload large files with write, which streams.

An op the server does not recognise at all is not reported as an error: the connection is closed instead, so a typo in op looks like a dropped socket rather than a bad request. The request id must be a JSON string, but an integer id is not reported as an error either: like an unrecognised op, and like any malformed frame, it closes the connection. Every protocol-level mistake surfaces as a dropped socket rather than a response.

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 — files past it are dropped silently
Max files per glob expansion	10,000 — a larger match silently reads only 10,000 of the files
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

Max file size bounds a single call, not the file on disk

The 100 MB ceiling is exact where it applies — fs9_write accepts a 104,857,600-byte value and rejects 104,857,601 with stored value exceeds server limit (54000) — but it is enforced per call, against the value moving through SQL, not as an invariant on the stored file.

The incremental writers are not checked against it. fs9_append, fs9_write_at and fs9_truncate will all grow a file past 100 MB and return success, and fs9_truncate will do it in one step. Nothing warns you at the moment you cross the line — you find out on the next read:

Reading a file that is over the limit	Result
fs9_read	stored value exceeds server limit (54000)
extensions.fs9(path)	fs9 resource exceeds the supported size limit (54000)
fs9_read_at	works — ranges are unaffected
fs9_size, fs9_exists, fs9_mtime	work, and report the real size
WebSocket read	works — it switches to 64 KB streaming instead of rejecting

So an over-limit file is not corrupt and not lost; it has simply dropped out of every whole-file SQL path, and only ranged or streaming access can still reach it. If you append to a file over time, track its size with fs9_size and roll over before the limit rather than relying on a write to fail.

Error Messages

Error	SQLSTATE	Cause
fs9: permission denied (superuser required)	42501	Non-superuser role called one of the 13 scalar functions. The 5 table functions report the same denial as XX000 — see Permissions
unsupported table-valued function: fs9	0A000	A name was used in FROM position that is not a table function there. Either a table function called before CREATE EXTENSION fs9, or a scalar function used in FROM with the extension installed (SELECT * FROM extensions.fs9_read(...)). The message names the function you called
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
stored value exceeds server limit	54000	A scalar call moved more than 100 MB in one go — fs9_write with a larger payload, or fs9_read of a larger file
fs9 resource exceeds the supported size limit	54000	The table function was pointed at a file larger than 100 MB. Same SQLSTATE as the row above but a different message, so match on the code, not the text. The WebSocket path spells its equivalent FS9 resource…, but WS read streams large files rather than rejecting them
file is not valid UTF-8; use fs9_read_bytea for binary data	22021	fs9_read/fs9_read_at on bytes that are not valid UTF-8. Names the path and the _bytea variant to use
invalid fs9 request	22023	The path is not a valid target for the call — reading a symlink with fs9_read or the fs9() table function, for example
internal error	XX000	Argument validation failures (a negative fs9_read_at offset or fs9_truncate size), a glob whose files disagree on columns, a table function called without superuser, and I/O faults all 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.

Grant --fs9-scope at deploy time, or every call reports the file as missing

ctx.fs9 is present on the context object whichever way you deploy, but a function created without at least one --fs9-scope has access to nothing. Every call — read, list, stat, write — then fails with FS9 resource was not found, which reads as “that file isn’t there” even when the file demonstrably is.

Terminal

# Read-write access to /logs; repeat the flag for more paths, :ro for read-only
db9 functions create log-roller --db mydb -f roller.js --fs9-scope '/logs:rw'

Once at least one scope exists the errors become self-explaining: a path outside the granted scopes fails with Path '/data/report.csv' is outside all declared fs9 scopes. So the bare “not found” is specifically the signal that no scope was declared — check the deploy command before you go looking for the file.

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 built-in extensions
• Limits and Quotas — All operational limits
• TypeScript SDK — Programmatic fs9 access via WebSocket