EXTERNAL property storage: paired-bucket layout for heavy property values (vectors, large strings, JSON)

[lvca]

Summary

Add a per-property EXTERNAL flag in the schema. When set, the property's value is stored in a paired external bucket instead of inline in the primary record. The main record carries only a TYPE_EXTERNAL pointer ([bucketId:int][position:long]) to the external record.

The goal is topology shard vs property shards, but at the bucket level on a single node: keep the primary bucket's pages dense for graph traversal, push heavy payloads (vector embeddings, long strings, embedded JSON, full-text payloads) to a separate file that traversal-only queries do not touch.

Motivation

ArcadeDB is increasingly used for workloads where a small fraction of records carry a heavy property:

• Vector embeddings on vertices/documents: typically 768-4096 floats (3-32 KB per record).
• Wide JSON sub-documents on vertices.
• Full-text payloads that the query never projects.

Today every property is serialised inline in the same page as the topology, so even traversal-only queries (MATCH (n:Person)-[:KNOWS]->(m) RETURN m.name) pay for the heavy properties in cache misses and I/O.

API

Java

type.createProperty(\"embedding\", Type.ARRAY_OF_FLOATS).setExternal(true);

SQL DDL

CREATE PROPERTY Person.embedding ARRAY_OF_FLOATS (EXTERNAL true);
ALTER PROPERTY Person.embedding EXTERNAL true;
ALTER PROPERTY Person.embedding EXTERNAL false;

REBUILD TYPE (eager migration)

After toggling the flag, existing records are migrated lazily on next write. To migrate them eagerly:

REBUILD TYPE Person;
REBUILD TYPE Parent POLYMORPHIC;
REBUILD TYPE Person WITH batchSize = 1000;

Compression (auto / lz4)

EXTERNAL property values can be LZ4-compressed in the paired bucket. Per-property setting; persists in schema.json:

CREATE PROPERTY Doc.body STRING (EXTERNAL true, COMPRESSION 'auto');
ALTER PROPERTY Doc.body COMPRESSION 'lz4';
ALTER PROPERTY Doc.body COMPRESSION 'none';

Modes:

• none (default) - no compression, current behaviour.
• lz4 - always LZ4-compress the value.
• auto - try LZ4; keep the compressed bytes only if they save more than 10% of the raw size, otherwise fall back to raw. The decision is per-record, so a single property happily mixes compressed and uncompressed records (text gets compressed, vector embeddings fall back to raw).

The decision is encoded in the main record's type byte (TYPE_EXTERNAL vs TYPE_EXTERNAL_COMPRESSED_LZ4), not inside the blob - so reads dispatch in one byte already-on-hand and there is no per-blob algo marker. The reader knows which decoder to use from the type byte alone.

schema:buckets visibility (new column)

SELECT name, purpose FROM schema:buckets WHERE purpose <> 'PRIMARY';

External buckets show purpose: 'EXTERNAL_PROPERTY'.

The purpose column is new in this change (it did not exist in ArcadeDB before). It is added to both schema:buckets and schema:bucket(<name>) so tooling (Studio etc.) can hide or label internal buckets. Values mirror the new LocalBucket.Purpose { PRIMARY, EXTERNAL_PROPERTY } enum on LocalBucket:

• PRIMARY is the default for every bucket - user-targetable for DML.
• EXTERNAL_PROPERTY is set on paired buckets that hold externalised property values - rejected by user-facing DML.

Indexes are not LocalBuckets (they are separate Component types and never appeared in schema:buckets), so before this change there was no notion of an internal bucket and the column was not needed.

schema:types visibility (new fields)

SELECT FROM schema:types now returns three new pieces of information so tooling can render external storage clearly:

• Per-property: a boolean external flag (only emitted when true; absence means inline).
• Per-property: a compression string (auto | lz4); only emitted when set, absence means none.
• Per-type: an externalBuckets map of primaryBucketName -> externalBucketName for every primary bucket that has a paired external bucket. Empty/absent on types without EXTERNAL properties.

Studio integration

• The Database -> Buckets tab hides internal buckets via WHERE purpose = 'PRIMARY' OR purpose IS NULL so end users see only their data buckets. (Power users can still inspect them via SELECT FROM schema:buckets in the Query tab.)
• The type-detail Properties table has a new "Storage" column. EXTERNAL properties show a purple External badge with a tooltip listing the paired primary -> external bucket mapping (e.g. Person_0 -> Person_0_ext). Inline properties show a muted "Inline" tag.
• The "Add Property" dialog has a new "External" checkbox (with explanation tooltip) that emits (EXTERNAL true) in the generated CREATE PROPERTY SQL.

Design

• Per-primary-bucket pairing. Each primary bucket of a type with at least one EXTERNAL property gets a paired bucket named <primaryBucket>_ext with Purpose.EXTERNAL_PROPERTY. Mapping persisted in schema.json under each type's externalBuckets field.
• Larger default page size and smaller slot table for external buckets. External buckets default to 256 KB pages (matching the LSM-index default) and a 256-slot page-record table (vs 2048 on primary buckets). The smaller slot table reclaims ~7KB of header overhead per page (8194 → 1034 bytes), better fitting the 1-2KB records typical for compressed text/JSON. Encoded via a new bucket file-format version (LocalBucket.EXTERNAL_BUCKET_VERSION = 1) so existing primary buckets keep the legacy 2048-slot v0 layout untouched. Page size is tunable via arcadedb.externalPropertyBucketDefaultPageSize (GlobalConfiguration.EXTERNAL_PROPERTY_BUCKET_DEFAULT_PAGE_SIZE).
• Tiered storage placement. New configuration arcadedb.externalPropertyBucketPath (GlobalConfiguration.EXTERNAL_PROPERTY_BUCKET_PATH, DATABASE scope). When set, paired external-property buckets are created in that directory instead of the database directory, so heavy payloads can live on cheaper/slower storage (HDD, network mount, separate SSD pool) while topology stays on fast disk. FileManager rediscovers tiered files at startup via a secondary scan path. Existing external buckets are not relocated when this configuration changes; users must move files manually if migrating after the fact. Empty by default (everything in the database directory).
• Inheritance. When setExternal(true) is called on a supertype property, every subtype recursively gets paired external buckets for its own primary buckets (records of the subtype live in the subtype's primary buckets). Same hook fires when addSuperType() is called on a subtype if the new parent already has EXTERNAL properties.
• Storage format.
  • Main record's content area: [type-byte : 1B][bucketIdVarint : 1-3B][positionVarint : 1-3B]. Bucket id and position use the same varint encoding as TYPE_COMPRESSED_RID (~3-7 bytes per pointer instead of 12 fixed).
  • The type-byte is TYPE_EXTERNAL (29) for raw payloads or TYPE_EXTERNAL_COMPRESSED_LZ4 (30) when the blob holds LZ4-compressed bytes. The compression discriminator lives entirely in this single byte (no per-blob algo marker).
  • External bucket record (raw): [ExternalValueRecord.RECORD_TYPE : 1B][value type byte : 1B][value bytes : ...].
  • External bucket record (LZ4): [ExternalValueRecord.RECORD_TYPE : 1B][value type byte : 1B][uncompressedSizeVarint : 1-3B][LZ4 bytes : ...].
  • New record type ExternalValueRecord.RECORD_TYPE = 5.
  • LZ4 reuses the existing com.arcadedb.compression.LZ4Compression (already on the classpath; no new dependency).
• Transactionality. Both the primary and external bucket writes share the same TransactionContext and WAL group, so commit/rollback/recovery is atomic across both. Per-bucket record-count delta is updated for both, so count() stays accurate.
• Update in place. When updating a record, the existing external RID is recovered from the OLD buffer (via findExistingExternalRids) and the external record is updated in place. The main record bytes are NOT rewritten if only an EXTERNAL property changed.
• Cascade delete. When the primary record is deleted, every linked external record is deleted in the same transaction (cascadeDeleteExternalValues in LocalDatabase.deleteRecordNoLock).
• Orphan cleanup. When re-serialising, any external RID that was tracked in the OLD buffer but is not consumed by the NEW buffer (property toggled EXTERNAL=false, renamed, or dropped) is deleted in the same transaction. This means casual updates AND REBUILD TYPE both reclaim orphan storage.
• Provisional identity for inserts. LocalBucket.createRecord now sets a (bucketId, -1) placeholder identity before serialise so the serializer can resolve the target primary bucket id for EXTERNAL property routing. The actual position overwrites the placeholder when the caller stores the returned RID.
• Polymorphic-scan robustness. The external bucket lookup uses schema.getTypeByBucketId(bucketId) (the type that actually owns the bucket) rather than record.getType(). This handles the case where scanType POLYMORPHIC tags subtype records with the queried parent type.
• DML guard. LocalDatabase.createRecordNoLock(record, bucketName) rejects writes targeting any isSystem() bucket. SQL INSERT INTO bucket:<external> is also rejected at the planner because external buckets have no associated user type.

v1 caveats / follow-ups

• No bulk on-toggle migration; lazy on next write OR explicit REBUILD TYPE.
• No replication-side externalisation. Phase 2 is to give the external bucket its own Ratis Raft group, replicated independently from topology, using the existing Ratis log index as the read-your-writes bookmark - the bridge to a multi-node design without the limitation of Neo4j Infinigraph's single-graph-shard ceiling.
• No crash-recovery test reusing the Ratis HA harness yet (transaction-rollback test already exercises the same WAL atomicity path; explicit crash test is a follow-up).

[lvca]

Merged