Foreign Rel Tables — Representing Graphs in SQL Databases

[adsharma]

1. What Is a Foreign Table?

In Ladybug, a foreign table is a table whose data lives outside the
native Ladybug storage engine — typically in an external SQL database such as
DuckDB, PostgreSQL, SQLite, or any database accessible via ADBC. Foreign tables
are registered through the ATTACH command:

ATTACH 'path/to/database.db' AS mydb (dbtype duckdb);
ATTACH 'host=localhost dbname=mydb' AS mydb (dbtype PG_CLIENT);

When a database is attached, its tables become visible to Cypher queries through
a catalog extension (e.g., DuckDBCatalog, PgClientCatalog, ADBCCatalog).
Each foreign table is represented in the catalog as either a
NodeTableCatalogEntry (for node tables) or a RelGroupCatalogEntry (for rel
tables), backed by a scan function that delegates reads to the remote database
via SQL queries.

Because the data is not managed by Ladybug's transaction engine, foreign tables
are read-only — inserts, updates, and deletes are rejected at the storage
layer.

2. Representing Graphs in Foreign Tables

Ladybug uses a convention-based approach to recognize graph elements in a
foreign SQL database. When a database is attached, the catalog extension
enumerates the tables in the remote schema and classifies them by name prefix.

2.1. Naming Convention

Prefix	Classification	Internal Representation
node_*	Node table	NodeTableCatalogEntry (shadow entry)
rel_*	CSR-style rel table	RelGroupCatalogEntry + ForeignRelTable
fkrel_*	FK-based rel table	RelGroupCatalogEntry + ForeignRelTable
Other	Plain foreign table	NodeTableCatalogEntry (generic, no graph semantics)

The two rel-table variants correspond to different physical representations of
edges in the foreign database:

rel_* — CSR-like Foreign Key Columns

A rel_* table stores edges with explicit src / dst columns that act like
CSR (Compressed Sparse Row) foreign keys — they reference the primary keys of
the corresponding node_* tables. The naming mirrors the adjacency-list mental
model:

rel_knows (id INTEGER, src INTEGER, dst INTEGER, since DATE)

Here src REFERENCES node_person(id) and dst REFERENCES node_person(id).
The table is backed by a ForeignRelTable whose scan function fetches rows from
the remote database, and the optimizer uses the FK metadata to plan joins.

fkrel_* — Foreign-Key-backed Rel Table

A fkrel_* table is identical in purpose but the src/dst columns are
explicitly declared as foreign keys in the remote database. The catalog
extension queries information_schema.table_constraints to discover the FK
relationships:

SELECT kcu.column_name, ccu.table_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'fkrel_knows';

This produces a scan-driven rel table — the optimizer generates hash joins
between the foreign node tables and the rel table at query time, rather than
pre-building CSR columns.

2.2. Initialization Order

Node tables must be registered before rel tables because fkrel_* and
rel_* entries need to resolve their src/dst node table IDs at catalog
initialization time. The catalog extension performs a two-pass initialization:

// First pass: register node tables
for (auto& table : tables) {
    if (lowerName.rfind("node_", 0) == 0) {
        createForeignNodeTable(tableName);
    }
}
// Second pass: register rel tables (need node table IDs)
for (auto& table : tables) {
    if (lowerName.rfind("fkrel_", 0) == 0) {
        createForeignRelTable(tableName);  // FK-driven, scan-based
    } else if (lowerName.rfind("rel_", 0) == 0) {
        // TODO: COPY data into a local on-disk CSR RelTable
        createForeignRelTable(tableName);
    }
}

3. Alternatives & Design Trade-offs

There are several possible strategies for combining foreign SQL tables with
Ladybug's native graph engine. Each has different engineering costs, query
performance characteristics, and levels of planner integration.

3.1. Read Node/Rel Tables into Arrow Memory, Run Cypher Locally

The simplest approach: stream all rows from the foreign tables into Arrow
columnar batches, then run Cypher queries entirely inside Ladybug's native
engine. This is the default path for the DuckDB scan function — LOAD FROM
and MATCH on a single foreign node table pull data through the
TableFunction interface into Arrow vectors, which are then processed by the
standard Cypher operator pipeline.

Pros:

• Full Cypher expressiveness (filters, projections, graph patterns)
• No planner changes needed for single-table scans
• Works with any foreign database

Cons:

• High data movement cost — every query materializes the full column data
• No pushdown of filters or projections to the foreign database
• Scales poorly with large tables (edges must all be moved into memory)

3.2. Mixed Tables: Foreign Node Tables + Foreign Rel Tables (Shipped Design)

This is the primary shipped design. When attaching a foreign database, the
catalog extension creates two catalog entries for each foreign table:

1. An attached catalog entry (e.g., PgClientTableCatalogEntry) in the
   extension's own catalog set — this holds the scan function and bind data.
2. A shadow entry in the main Ladybug catalog (a
   NodeTableCatalogEntry constructed with the ShadowTag{} marker) — this
   makes the table visible to the Cypher query planner and binder.

The shadow entry holds a pointer (referencedEntry) back to the attached
entry, so the planner can find the scan function when it needs to read data:

// From PgClientCatalog::createForeignNodeTable
auto mainTableEntry = std::make_unique<catalog::NodeTableCatalogEntry>(
    tableName, pkName, foreignDatabaseName, catalog::ShadowTag{});
mainTableEntry->setReferencedEntry(attachedEntryPtr);
context_->getDatabase()->getCatalog()->addTableEntry(std::move(mainTableEntry));

For rel tables, a RelGroupCatalogEntry is created in the main catalog so
MATCH ... -[...]-> ... patterns can find the relationship. The rel table is
backed by a ForeignRelTable (which extends RelTable) and uses the foreign
scan function directly, mixing foreign node table data (read via scan function)
with the foreign rel table (also read via scan function). Both are resolved at
query time through hash joins.

The shadow mechanism does mean there are two catalog entries for the same
logical table — one in the extension's catalog and one as a shadow in the main
catalog. The planner resolves the shadow to find the scan function, then
delegates to the attached entry for actual data access. This allows Cypher
queries to reference foreign tables transparently without the core planner
needing to know about each extension's catalog layout.

3.3. Transpile Graph Traversal into SQL Joins

The ForeignJoinPushDownOptimizer attempts to detect Cypher graph patterns
where all nodes and relationships are backed by foreign tables from the
same external database, and rewrite the entire pattern into a single SQL
JOIN query that is pushed down to the foreign database.

The optimizer detects a pattern like:

HASH_JOIN (c._ID)
  ├── [FLATTEN]
  │     └── HASH_JOIN (a._ID)
  │           ├── EXTEND (a)-[b]->(c)
  │           │     └── SCAN_NODE_TABLE (a)
  │           └── TABLE_FUNCTION_CALL (a's SQL scan)
  └── TABLE_FUNCTION_CALL (c's SQL scan)

And rewrites it into a single TABLE_FUNCTION_CALL that executes one SQL JOIN
query on the foreign database.

Requirements for the rewrite:

1. Both node scans must use TABLE_FUNCTION_CALL with supportsPushDown = true
2. The rel table must have a scanFunction (foreign-backed)
3. All three tables must be from the same foreign database

Current status: This optimizer exists (ForeignJoinPushDownOptimizer) but
is not yet producing optimal SQL for complex graph traversals. The Ladybug
planner does not currently optimize general graph traversal patterns into
recursive SQL joins — the pushdown is limited to simple star patterns.
Optimizing arbitrary graph traversals (e.g., variable-length path patterns,
multi-hop traversals) into efficient SQL remains open work.

3.4. Copy rel_* Tables into the Ladybug Catalog at Attach Time

For rel_* tables (CSR-style), another strategy is to materialize the remote
data into a local on-disk CSR rel table during ATTACH. Rather than scanning
the foreign table at every query, the data is bulk-copied once into Ladybug's
native CSR storage format. Subsequent queries run entirely against the local
copy with full performance (CSR adjacency-list traversal, columnar scans,
compression).

This approach is noted in the source as a TODO:

} else if (lowerName.rfind("rel_", 0) == 0) {
    // CSR-based rel table: materialized into a local on-disk CSR rel table.
    // TODO: COPY data from PostgreSQL into a local RelTable.
    createForeignRelTable(tableName);
}

Once materialized, the local rel table can be combined with foreign node tables
(the mixed table approach from §3.2) — node data is still read from the
foreign database via scan functions, but edge traversal uses the local CSR
storage, giving fast extend operations without moving all edge data on every
query.

Pros:

• One-time copy cost at attach time vs. per-query data movement
• Full native CSR performance for graph traversals
• Works with databases that have no rowid mechanism

Cons:

• Stale data: the local copy is a snapshot. Writes to the foreign database
  after attach are invisible until re-attach.
• Storage cost: edges are duplicated locally.
• Copy time for very large rel tables.

4. Rowid Challenges

A critical challenge when representing graphs in foreign SQL databases is the
internal node identifier — Ladybug's native storage uses a compact offset
within each table as the internal _ID of a node. When the data lives in a
foreign database, there is no universal mechanism to get a cheap internal ID.

4.1. DuckDB: Rowid Support

DuckDB exposes a rowid pseudo-column for each table (unless the table is a
view or has a WITHOUT ROWID-like structure). The DuckDB scan function uses
rowid as the internal _ID:

// From DuckDBTableCatalogEntry::getBoundScanInfo
if (!nodeUniqueName.empty()) {
    auto idUniqueName = nodeUniqueName + "." + std::string(common::InternalKeyword::ID);
    columns.push_back(std::make_shared<binder::VariableExpression>(
        common::LogicalType::INT64(), idUniqueName, "rowid"));
}

This works well because DuckDB's rowid is a stable 64-bit identifier for each
row that can be used as the node's internal offset. The internalID_t struct
pairs this offset with a tableID to form a globally unique node reference:

struct LBUG_API internalID_t {
    offset_t offset;   // rowid from the foreign database
    table_id_t tableID; // Ladybug table ID (shadow entry's ID)
};

4.2. PostgreSQL: No Rowid

PostgreSQL does not have a stable rowid analogue. The ctid system column
is a physical location ((page, tuple index)) that changes on VACUUM or row
update and is not a stable identifier. There is no rowid pseudo-column.

For the PostgreSQL connector (pg_client extension), the workaround is to use
the primary key column of the node table as the internal ID. The first
column of the table is convention-bound to be the primary key:

// From PgClientCatalog::createForeignNodeTable
auto columnInfo = getTableColumnInfoFromConnector(connector, catalogName,
    defaultSchemaName, tableName);
if (columnInfo.empty()) return;
std::string pkName = columnInfo[0].name;
// ... use pkName as the internal identifier

This imposes a constraint: every node_* table in PostgreSQL must have a
numeric primary key (typically INTEGER or BIGINT) as its first column.
String or composite primary keys would not work as internal IDs because Ladybug
expects INT64 offsets.

4.3. ADBC: Delegated to the Driver

ADBC (Arrow Database Connectivity) delegates the rowid question to the
underlying driver. The ADBC extension uses the primary key column as the
internal ID, similar to the PostgreSQL approach, but relies on the ADBC driver
to provide a stable ordering.

4.4. Implications for Rel Tables

The absence of stable rowids affects how rel tables reference nodes. For
fkrel_* tables, the src / dst columns must contain values that match the
internal ID scheme of the corresponding node tables:

• DuckDB: src and dst should contain rowid values from the node tables
• PostgreSQL: src and dst should contain primary key values from the
  node tables

The FK discovery code in PgClientCatalog::createForeignRelTable handles this
by looking up the referenced tables and using their catalog entry IDs, ensuring
the rel table's srcTableID / dstTableID match the entries that MATCH
queries will resolve to:

// Look up src/dst node tables in the attached catalog
auto* srcEntry = tables->getEntry(&transaction::DUMMY_TRANSACTION, srcTableName);
auto* dstEntry = tables->getEntry(&transaction::DUMMY_TRANSACTION, dstTableName);
common::table_id_t srcTableID = srcEntry->cast<catalog::TableCatalogEntry>().getTableID();
common::table_id_t dstTableID = dstEntry->cast<catalog::TableCatalogEntry>().getTableID();

5. ForeignRelTable Implementation

The ForeignRelTable class (in src/include/storage/table/foreign_rel_table.h)
is the storage-layer adapter that bridges a foreign rel table into Ladybug's
native rel table abstraction. It extends RelTable but overrides the scan
path to use the external scan function instead of native CSR columns.

Key design points:

• Scan-driven: Unlike native rel tables which read pre-built CSR adjacency
  lists from disk, ForeignRelTable delegates to a TableFunction that issues
  SQL queries to the foreign database.

• No CSR columns: The setToTable override skips the CSR-column resolution
  that native RelTable performs (which would dereference an empty
  directedRelData and throw). All column pointers are set to nullptr.

• No mutations: insert, update, and delete_ all throw
  RuntimeException.

• Morsel-driven parallelism: The scan function's shared state is lazily
  created on the first initScanState call and shared across all worker
  threads. Each worker acquires disjoint offset ranges (morsels) from the
  foreign table via the scan function.

• Owns its own scan state: ForeignRelTableScanState holds a local
  dataChunk, a sharedState, and a localState — all managed by the
  scan function's lifecycle.

class ForeignRelTable final : public RelTable {
public:
    ForeignRelTable(catalog::RelGroupCatalogEntry* relGroupEntry,
        common::table_id_t fromTableID, common::table_id_t toTableID,
        const StorageManager* storageManager, MemoryManager* memoryManager,
        function::TableFunction scanFunction,
        std::shared_ptr<function::TableFuncBindData> scanBindData);
    // scanInternal delegates to scanFunction->match -> initSharedState -> initLocalState -> ...
};

6. Summary

Approach	Node Data	Rel Data	Planner Integration	Freshness	Performance
All in Arrow memory	Foreign	Foreign	Minimal	Per-query	Low (full scan)
Foreign nodes + foreign rels	Foreign	Foreign	Mixed (shadow)	Per-query	Medium (hash joins)
SQL transpilation	Foreign	Foreign	Full pushdown	Per-query	High (in-db joins)
Copy rels at attach	Foreign	Copied	Mixed (shadow)	Snapshot	High (CSR traversals)

The mixed tables approach (foreign node tables shadowed in the main catalog

• foreign rel tables backed by ForeignRelTable) is Ladybug's shipped design as
  of this writing. The ForeignJoinPushDownOptimizer represents a path toward
  full SQL transpilation, and the rel_* COPY strategy offers a middle ground
  for deployments that want fast CSR traversal without sacrificing relational
  storage for edges.

[ericyuanhui]

@adsharma

Can Ladybug support the following use case? If so, what should I do to enable it?

I have three tables: two node tables, User and Session, and one relationship table, User_owns_Session. Their Ladybug DDL is:

CREATE NODE TABLE `User` (
    `id` STRING,
    `account_id` STRING,
    `usergroup_id` STRING,
    `groupinfo_id` STRING,
    `usertemplate_id` STRING,
    `visitor_auth_type` INT64,
    PRIMARY KEY(`id`)
);

CREATE NODE TABLE `Session` (
    `id` STRING,
    `login_ts` INT64,
    `logout_ts` INT64,
    `online_sec` INT64,
    `access_type` INT64,
    `term_cause` STRING,
    `time_seg` STRING,
    `total_traffic` DOUBLE,
    `gw_strategy` STRING,
    `actual_gw_strategy` STRING,
    `proxy_name` STRING,
    `policy_id` STRING,
    `accounting_rule` STRING,
    `package_name` STRING,
    `service_suffix` STRING,
    `service_id` STRING,
    `is_roaming` BOOL,
    `passthrough_type` STRING,
    PRIMARY KEY(`id`)
);

CREATE REL TABLE `User_owns_Session` (
    FROM `User` TO `Session`,
    `login_ts` INT64,
    `logout_ts` INT64,
    `access_type` INT64,
    MANY_MANY
);

I also have an Iceberg data lake containing the same three tables. Their schemas are as follows:

def user_schema() -> Schema:
    return Schema(
        NestedField(1, "id", StringType(), required=True),
        NestedField(2, "account_id", StringType(), required=False),
        NestedField(3, "usergroup_id", StringType(), required=False),
        NestedField(4, "groupinfo_id", StringType(), required=False),
        NestedField(5, "usertemplate_id", StringType(), required=False),
        NestedField(6, "visitor_auth_type", LongType(), required=False),
        identifier_field_ids=[1],
    )


def user_arrow_schema() -> pa.Schema:
    return pa.schema(
        [
            pa.field("id", pa.string(), nullable=False),
            pa.field("account_id", pa.string()),
            pa.field("usergroup_id", pa.string()),
            pa.field("groupinfo_id", pa.string()),
            pa.field("usertemplate_id", pa.string()),
            pa.field("visitor_auth_type", pa.int64()),
        ]
    )


def session_schema() -> Schema:
    return Schema(
        NestedField(1, "id", StringType(), required=True),
        NestedField(2, "login_ts", LongType(), required=False),
        NestedField(3, "logout_ts", LongType(), required=False),
        NestedField(4, "online_sec", LongType(), required=False),
        NestedField(5, "access_type", LongType(), required=False),
        NestedField(6, "term_cause", StringType(), required=False),
        NestedField(7, "time_seg", StringType(), required=False),
        NestedField(8, "total_traffic", DoubleType(), required=False),
        NestedField(9, "gw_strategy", StringType(), required=False),
        NestedField(10, "actual_gw_strategy", StringType(), required=False),
        NestedField(11, "proxy_name", StringType(), required=False),
        NestedField(12, "policy_id", StringType(), required=False),
        NestedField(13, "accounting_rule", StringType(), required=False),
        NestedField(14, "package_name", StringType(), required=False),
        NestedField(15, "service_suffix", StringType(), required=False),
        NestedField(16, "service_id", StringType(), required=False),
        NestedField(17, "is_roaming", BooleanType(), required=False),
        NestedField(18, "passthrough_type", StringType(), required=False),
        identifier_field_ids=[1],
    )


def session_arrow_schema() -> pa.Schema:
    return pa.schema(
        [
            pa.field("id", pa.string(), nullable=False),
            pa.field("login_ts", pa.int64()),
            pa.field("logout_ts", pa.int64()),
            pa.field("online_sec", pa.int64()),
            pa.field("access_type", pa.int64()),
            pa.field("term_cause", pa.string()),
            pa.field("time_seg", pa.string()),
            pa.field("total_traffic", pa.float64()),
            pa.field("gw_strategy", pa.string()),
            pa.field("actual_gw_strategy", pa.string()),
            pa.field("proxy_name", pa.string()),
            pa.field("policy_id", pa.string()),
            pa.field("accounting_rule", pa.string()),
            pa.field("package_name", pa.string()),
            pa.field("service_suffix", pa.string()),
            pa.field("service_id", pa.string()),
            pa.field("is_roaming", pa.bool_()),
            pa.field("passthrough_type", pa.string()),
        ]
    )


def rel_schema() -> Schema:
    return Schema(
        NestedField(1, "from_user_id", StringType(), required=True),
        NestedField(2, "to_session_id", StringType(), required=True),
        NestedField(3, "from_type", StringType(), required=True),
        NestedField(4, "to_type", StringType(), required=True),
        NestedField(5, "login_ts", LongType(), required=False),
        NestedField(6, "logout_ts", LongType(), required=False),
        NestedField(7, "access_type", LongType(), required=False),
        identifier_field_ids=[1, 2],
    )


def rel_arrow_schema() -> pa.Schema:
    return pa.schema(
        [
            pa.field("from_user_id", pa.string(), nullable=False),
            pa.field("to_session_id", pa.string(), nullable=False),
            pa.field("from_type", pa.string(), nullable=False),
            pa.field("to_type", pa.string(), nullable=False),
            pa.field("login_ts", pa.int64(), nullable=True),
            pa.field("logout_ts", pa.int64(), nullable=True),
            pa.field("access_type", pa.int64(), nullable=True),
        ]
    )

The fields are now clearly defined. Here is my use case:

I want to use Ladybug’s graph-topology capabilities. Therefore, I need to create a relationship table in the graph in order to obtain graph traversal functionality. This is a prerequisite. Consequently, the approach of joining native graph node tables with external tables does not meet my requirements.

I would like the data for the Session table to remain in the Iceberg data lake, while Ladybug stores the data for the User table. I would then like to create the User_owns_Session relationship table in Ladybug without creating a Session node table in Ladybug.

The Session data will be continuously streamed into the data lake, but it will not be written into Ladybug. At the same time, streaming data will also be written into the Ladybug User table and the User_owns_Session relationship table.

I need to support real-time or near-real-time queries. Offline queries are not acceptable for our use case.

Given the currently available options, such as:

• attaching external tables;
• using DuckDB;
• the Iceberg extension;
• Icebug;
• Arrow in-memory tables, which we have already tried but found unsuitable for production because the data volume is very large and would consume too much memory;

how can these technologies be combined to satisfy this use case?

The root cause of this requirement is that Ladybug is an embedded graph database. At very large data volumes, it can impose significant memory and disk-resource costs. We therefore hope to keep some data in an external storage system while still using Ladybug’s computational engine for graph traversal and other graph computations.

[adsharma]

Thanks for bringing this up! Here's what I'm thinking:

• Let's make it work for duckdb
• Then figure out how to make iceberg tables work the same way

Suggested naming convention:

rel_*: table is a REL table with foreign keys
csr_rel_*: table is REL table where keys are table_offsets (dense, gapless CSR index)
everything else: node table

See postgres extension for an example:

https://github.com/LadybugDB/extensions/blob/b72b889432ebade41a51a0bf606e83131c84b908/pg_client/src/catalog/pg_client_catalog.cpp#L74

This proposal would rename:

fkrel -> rel (more natural naming)
rel -> csr_rel_*

Then we can land LadybugDB/extensions#31 with a similar rename.

Thoughts?

[adsharma]

In general, there is little value in duplicating User table in LadybugDB. You're likely better off keeping it in the data lake wherever it is. Stream updates to the REL table to LadybugDB.

Increasing Performance:

• LadybugDB reads foreign table and performs JOINS (lowest)
• LadybugDB pushes down cypher to SQL
• LadybugDB creates a (User)-[owns]->(Session) MIXED REL table where User and Session are foreign tables, owns is native.
• LadybugDB mirrors all 3 in native storage (highest)

Implementing and optimizing MIXED REL tables is a good long term direction.

[adsharma]

LadybugDB/extensions#65
LadybugDB/extensions#31
#842

The design doc in #842 interestingly has a title very similar to this thread. It was written a few days ago, but sat in my repo as an untracked markdown file. Pushing it out now since it seems to be relevant to this thread.

[ericyuanhui]

Alternatively, we could lower our requirements a little. If the data for all three tables is stored in the data lake with stream‑ingestion, can we use Ladybug to support near‑real‑time queries, with graph‑topology capabilities as a prerequisite?

From an architectural perspective, this would turn into a cloud‑native storage‑compute‑separated design: Iceberg data‑lake acts as the storage layer for graph data, while Ladybug functions as a stateless compute‑only engine. Could this architecture work? I don’t fully understand the role of Iceberg‑Ladybug integration. Could you provide a detailed explanation? @adsharma

[adsharma]

Yes - this architecture already works with multiple database backends:

• Postgres
• DuckDB
• SQLite (via duckdb's sqlite extension)
• icebug-format

It could be made to work for iceberg/delta lake as well.

[ericyuanhui]

I believe there are design flaws in the Iceberg extension. Currently, it relies on capabilities from duckdb.so. When accessing tables in a specified directory, DuckDB requires the version‑hint.text file to exist to run queries.

However, for real‑world Iceberg data‑lake users, native PyIceberg or Java Iceberg implementations do not generate this file within the metadata folder. The version‑hint.text file is not part of official Iceberg semantics — it is a DuckDB‑specific dependency.

This forces end‑users to run an extra external program to update version‑hint.text every time they commit streaming writes to the data lake. This introduces risks around consistency guarantees and file‑locking issues under concurrent‑write scenarios, which is not a reasonable design.

Additionally, the configuration command SET unsafe_enable_version_guessing = true; provided as a workaround is native DuckDB syntax. It cannot be executed via the Ladybug lbug CLI, as it will trigger a parsing error.

[adsharma]

Is this specific to Ladybug or duckdb users accessing iceberg tables will also run into these issues? If it's the latter, why not take it up with duckdb?

[ericyuanhui]

Yes, metadata/version‑hint.text (some references refer to it as version‑hint.txt, while version‑hint.text is the conventional filename in Iceberg‑Java) is essentially generated by the filesystem‑based catalog mechanism of HadoopCatalog / HadoopTables.

This file is not mandated by the Iceberg table‑format specification for all catalog implementations. Instead, it is a hint maintained by HadoopTableOperations to resolve which metadata version is currently active. Iceberg’s filesystem‑table implementation is powered by HadoopTableOperations.
Reference: https://github.com/apache/iceberg/blob/main/format/spec.md

A typical directory layout looks like this:

table/
├── data/
└── metadata/
    ├── v1.metadata.json
    ├── v2.metadata.json
    ├── v3.metadata.json
    └── version-hint.text

For example, the content inside version‑hint.text:

3

Indicates that readers should prioritize loading:
metadata/v3.metadata.json

The approximate write path is as follows:

HadoopCatalog
    ↓
HadoopTableOperations
    ↓
commit(...)
    ↓
Persist vN.metadata.json
    ↓
writeVersionHint(N)
    ↓
metadata/version‑hint.text

Stack traces from historical Iceberg issues also confirm this call chain:

• HadoopTableOperations.writeVersionHint(...)
• HadoopTableOperations.commit(...)

Furthermore, an official Iceberg issue explicitly states that tables created via HadoopCatalog will generate an initial v1.metadata.json together with version‑hint.text.
Reference: apache/iceberg#1349

Key differences across catalog implementations for locating the active metadata file:

Catalog	Mechanism for Resolving Current Metadata	Presence of version‑hint.text
HadoopCatalog	Filesystem lookup + version‑hint / metadata files	Yes
HadoopTables	Filesystem‑managed table	Yes
HiveCatalog	metadata_location stored inside the HMS	Usually absent
GlueCatalog	metadata_location stored inside AWS Glue	Usually absent
RESTCatalog	Metadata location returned by the REST catalog service	Usually absent
Nessie	Table references managed by Nessie	Usually absent

Therefore, if you observe the following structure:

metadata/
  version-hint.text
  v1.metadata.json
  v2.metadata.json

This is a strong signal that the table was at one point created or maintained under Hadoop filesystem‑table semantics.
Conversely, the absence of version‑hint.text does not disqualify a table from being a valid Iceberg table. For instance, Iceberg tables created via Glue / Athena frequently lack this file, because the active‑metadata pointer is persisted inside the catalog service rather than within the table’s own directory.
Reference: duckdb/duckdb-iceberg#10

Another common source of confusion:
The numeric value 3 stored inside version‑hint.text is neither Iceberg’s format‑version=3 nor a snapshot‑id.
It denotes the suffix of the metadata file: v3.metadata.json.
Internally, v3.metadata.json could still declare:

{
  "format-version": 2
}

These two version identifiers represent completely separate concepts.

If you encounter a scenario where one table contains version‑hint.text while another does not, the first troubleshooting step should be verifying which catalog created each table: HadoopCatalog, RESTCatalog, or another implementation.

Writing Iceberg tables natively with DuckDB is a separate topic. Official write workflows require mounting an Iceberg catalog, predominantly using the REST Catalog path. Metadata commits are orchestrated through the catalog service, and transactions are not committed by directly modifying version‑hint.text.

The current blocker is that the Ladybug Iceberg extension depends on the duckdb.so library, yet there is no way to configure DuckDB’s REST‑catalog connection parameters from within Ladybug. As a result, this workflow is currently unusable for end‑users.
@adsharma

[adsharma]

Please test: LadybugDB/extensions#70

[ericyuanhui]

Now that the fix in #872 has been completed, I believe the Iceberg Extension is ready for access via the REST Catalog. Could we move on to discuss the point mentioned earlier:

Increasing Performance:

• LadybugDB reads foreign table and performs JOINS (lowest)
• LadybugDB pushes down Cypher to SQL
• LadybugDB creates a (User)-[owns]->(Session) MIXED REL table where User and Session are foreign tables, owns is native.
• LadybugDB mirrors all 3 in native storage (highest)

Implementing and optimizing MIXED REL tables is a good long‑term direction.

If we are to introduce a "MIXED REL table", are there any relevant design ideas? Or, to meet our expected storage‑compute separation requirements, what should be our next steps?

@adsharma

[ericyuanhui]

I’d like to discuss if this solution is a good fit for our direction. Can Ladybug support this feature? https://www.puppygraph.com/blog/graph-lakehouse @adsharma

[ericyuanhui]

We talked about the graph lake as a defining feature of Ladybug since Oct last year. Icebug-format v1 is a key differentiator our competition doesn't have.

https://blog.ladybugdb.com/post/graph-lake-icebug-format/

A minor suggestion: The Iceberg format offers an offline approach. It struggles to support real-time query states if that is what users require. In lake data scenarios, after bulk data ingestion, Iceberg format data must be converted into the graph CSR structure recognizable by Ladybug before queries can be served. This is an offline query pattern and cannot satisfy real-time stateful queries.

The preferable approach is to enable Ladybug to act as a compute engine for the data lake and deliver real-time graph query capabilities. This would be a better fit given current AI data requirements.
@adsharma

[adsharma]

I disagree vehemently with these statements. It's confusing "in-place" with "real-time".

I can see that Iceberg and existing lake house formats can be queried "in-place" (without transformation to another format) and it's convenient for some users. This is why Ladybug with it's Iceberg extension can query those formats just fine. Looks like you tested some (based on your earlier bug reports).

But icebug-format v1 is real time as well. If you're thinking about "offline" formats, graphar is an example offline format as explained here.

Icebug-format v1 is more suitable for graph workloads and is just as "real time" as any other parquet based format. They're all coming from a pipeline from OLTP data. The proposal is to add the CSR conversion step to the end of that pipeline.

It might take some time for the industry to see the light and adopt this format. In the meanwhile you can continue to use iceberg extension.

[ericyuanhui]

I may have misunderstood Icebug-format. As we discussed earlier, although the Iceberg extension is real-time, it cannot fully leverage graph topology capabilities. It only enables two-table joins on Iceberg data within Ladybug. But the reason we adopt Ladybug is to explore relationships and connections — that is where the value of graphs lies.

My current understanding of Icebug-format is as follows: users write data into Iceberg to generate Parquet files. Then we use the Icebug-format tool to convert those Parquet files into the CSR storage format recognizable by Ladybug, which we refer to as CSR Parquet files. After that, we create node tables and relationship tables in Ladybug and point the data source to these converted CSR Parquet files. Only then can graph queries be executed.

This brings up two questions:

1. How long does it take to convert Iceberg Parquet files via Icebug-format? What if Iceberg is receiving continuous data writes, with new Parquet files being added all the time? Do we need a background task running perpetually to convert these newly added Parquet files?
2. Suppose I define tables with statements like these:

CREATE NODE TABLE city(id INT32, name STRING, population INT64, PRIMARY KEY(id)) WITH (storage = '<path-to-dir>', format = 'icebug-disk');
CREATE NODE TABLE user(id INT32, name STRING, age INT64, PRIMARY KEY(id)) WITH (storage = '<path-to-dir>', format = 'icebug-disk');
CREATE REL TABLE follows(FROM user TO user, since INT32) WITH (storage = '<path-to-dir>', format = 'icebug-disk');
CREATE REL TABLE livesin(FROM user TO city) WITH (storage = '<path-to-dir>', format = 'icebug-disk');

After specifying the storage path and format, can newly converted CSR Parquet files (from the continuous writes mentioned in question 1) be dynamically picked up? Or do I have to re-establish db.connection every time?

@adsharma

[adsharma]

The icebug-format cli tool is a stop gap measure to convert. The better option is to modify the pipeline that generates iceberg tables to follow CSR format.

A parquet file can be a part of iceberg catalog and icebug catalog at the same time in this setup. No conversion necessary.

The second difference is how metadata is stored. In iceberg they're stored in metadata files on object storage.

In icebug they're stored in schema.cypher etc, but the actual design objective is to be ducklake like. The metadata would be stored in the system catalog of a ladybugdb instance.

In main this is partially implemented. See the storage format option to import database

[adsharma]

The benefit of using the system catalog of ladybugdb is concurrency control and ordering of data vs metadata.

[adsharma]

@ericyuanhui Please review:

#996
LadybugDB/extensions#86

[adsharma]

A lot of this code has landed in the last couple of days.

[ericyuanhui]

Great line of thinking. I believe we now have the capabilities covering A through D. However, regarding E (Graph‑Lake v2), could you help evaluate whether the following two requirements are achievable:

First, Ladybug acts as a stateless graph compute engine, with all data persisted in object storage (including CSR‑formatted graph files).

Second, Ladybug can run as read‑only nodes, yet its graph files need to support CDC‑based incremental updates. From the Icebug‑Disk design perspective: if users keep writing continuously to their Iceberg tables and Parquet files receive incremental updates, Icebug‑Disk currently has to rebuild CSR Parquet files from the full dataset on every usage, with no support for incremental updates. Full‑rebuild operations cannot satisfy real‑time query requirements. Is there any viable approach to enable incremental update capabilities here?
@adsharma

[adsharma]

The first requirement is easy to satisfy. It's functional in main.

Here are my thoughts about the second:

• We need to make icebug-disk compatible with iceberg/unity catalog
• Icebug-disk = special type of Iceberg table with some constraints (such as some tables should be named indptr* and indices*)
• You'd modify your iceberg writer to generate CSR
• You'd modify your iceberg readers to provide the same flat table view as before with a shim layer

Is this framing acceptable to your situation? If not, what are some problems on your end preventing this?

[ericyuanhui]

First, regarding point one: while Graph‑D appears to already have this capability, it is essentially implementing join computation by translating Cypher into SQL for predicate push‑down. This raises a key question: do we still need to create CSR for the relationship tables? If CSR for relationship tables must be created, does that mean relationship data still has to be stored locally within lbug files? If so, this would break the stateless property of the compute engine.

Furthermore, even though push‑down joins can produce results, I believe there remain significant gaps in both functionality and performance. There are deficiencies in raw performance as well as supported graph‑algorithm capabilities. Therefore, I do not consider this feature mature enough for production adoption; it still requires ongoing iteration and improvement.

Now for point two: the concept is promising, yet I have encountered multiple practical obstacles during real‑world implementation. To begin with, one Iceberg commit corresponds to a set of Parquet files. Even if we add branches within the PyIceberg write‑and‑read path to generate Parquet files compliant with icebug‑disk, Ladybug’s CSR representation is produced via full‑dataset computation and offers no native incremental support.

Even in an ideal scenario — where each commit can generate CSR Parquet solely from data in that commit (assuming newly ingested vertex records can generate all required edges without relying on historical Parquet files) — Ladybug still cannot treat each commit‑level CSR Parquet fragment as a complete graph. To expose a fully consistent graph view, we must recompute CSR Parquet from the union of data across all prior commits. This fundamental distinction between incremental computation and full‑rebuild computation represents the biggest blocker preventing practical deployment today.

[adsharma]

This raises a key question: do we still need to create CSR for the relationship tables?

No. SQL pushdown works without CSR. The SQL pushdown expanded support for a larger range of queries in the last couple of days. Please retest functionality.

Performance: I believe CSR will give the highest performance. But implementing it has practical obstacles that you point out.

This fundamental distinction between incremental computation and full‑rebuild computation represents the biggest blocker preventing practical deployment today.

Yes - my suggestion is that we prototype this with ducklake and then expand to iceberg/unity catalog. Potential path:

a) Implement icebug-disk extension for duckdb
b) Implement incremental update support in ducklake

[adsharma]

https://github.com/Ladybug-Memory/duckdb-icedisk-extension

LOAD icedisk;
SELECT icedisk_attach('my_graph_csr');
-- mounted 4 views from 'my_graph_csr': edges_follows, edges_livesin, nodes_city, nodes_user
-- (or: PRAGMA icedisk_attach('...'))

SELECT * FROM edges_follows;   -- (source, target, since) with original IDs
SELECT * FROM nodes_user;      -- pass-through parquet scan

[adsharma]

This should help ducklake read icebug-disk as if it was a native table (via the view). Let's update this extension to handle incremental updates and see what it looks like and then generalize the solution