111 lines
3.3 KiB
Markdown
111 lines
3.3 KiB
Markdown
# Particle Hierarchy & Storage Organization
|
|
|
|
## The Question
|
|
|
|
Particles form a recursive file-system-like hierarchy (streams contain folders, folders contain files, etc.). Should we align the logical hierarchy with physical storage paths?
|
|
|
|
```
|
|
Logical: stream_123 -> folder_456 -> file_789
|
|
Storage: gs://bucket/stream_123/folder_456/file_789/document.pdf
|
|
```
|
|
|
|
## Two Approaches
|
|
|
|
### Adjacency List (Current)
|
|
|
|
Each particle stores a reference to its parent:
|
|
|
|
```sql
|
|
CREATE TABLE particles (
|
|
id TEXT PRIMARY KEY,
|
|
parent_id TEXT REFERENCES particles(id),
|
|
...
|
|
);
|
|
```
|
|
|
|
**To get ancestors:** Recursive CTE query
|
|
**To move a particle:** Update one `parent_id`
|
|
**Storage path:** Independent, based on `object_id`
|
|
|
|
### Materialized Path
|
|
|
|
Each particle stores its full path:
|
|
|
|
```sql
|
|
CREATE TABLE particles (
|
|
id TEXT PRIMARY KEY,
|
|
path TEXT UNIQUE, -- '/net_abc/stream_123/folder_456'
|
|
...
|
|
);
|
|
```
|
|
|
|
**To get ancestors:** Parse the path string
|
|
**To get descendants:** `WHERE path LIKE '/net_abc/stream_123/%'`
|
|
**To move a particle:** Update paths of particle AND all descendants
|
|
|
|
## Why Materialized Path is Tempting
|
|
|
|
If `path` doubles as the storage URI:
|
|
|
|
```
|
|
Particle path: /net_abc/stream_123/folder_456/file_789
|
|
Storage URI: gs://bucket/net_abc/stream_123/folder_456/file_789/video.mp4
|
|
```
|
|
|
|
- Single source of truth for "where things live"
|
|
- No recursive queries for hierarchy
|
|
- Elegant alignment between logical and physical structure
|
|
|
|
## Why It Breaks Down
|
|
|
|
**Moves are expensive:**
|
|
|
|
Moving `folder_456` under a different stream requires:
|
|
1. Update `folder_456.path`
|
|
2. Update paths of ALL descendants (could be thousands)
|
|
3. Move ALL storage objects to new GCS paths
|
|
|
|
GCS "moves" are copy + delete operations:
|
|
- Slow and costs money
|
|
- Links/references break during move
|
|
- Concurrent access during move is undefined
|
|
- Failure mid-move leaves inconsistent state
|
|
|
|
**Storage should be immutable:**
|
|
|
|
Once a file is uploaded to `gs://bucket/obj_abc123`, that path should never change. This enables:
|
|
- Stable URLs (even if signed)
|
|
- CDN caching
|
|
- No coordination during particle reorganization
|
|
|
|
## Decision: Keep Them Decoupled
|
|
|
|
```
|
|
Logical hierarchy: parent_id references (mutable, cheap to change)
|
|
Physical storage: object_id (immutable, never moves)
|
|
```
|
|
|
|
| Operation | Adjacency List | Materialized Path |
|
|
|-----------|---------------|-------------------|
|
|
| Move particle | O(1) - update parent_id | O(n) - update all descendant paths + move storage |
|
|
| Get ancestors | O(depth) recursive query | O(1) parse path |
|
|
| Get descendants | O(n) recursive query | O(1) prefix match |
|
|
| Storage move | Not needed | Required on every move |
|
|
|
|
The read optimization of materialized paths doesn't justify the write complexity, especially when writes involve physical storage operations.
|
|
|
|
## Storage Organization
|
|
|
|
Objects are stored with stable IDs, optionally prefixed by network for operational convenience:
|
|
|
|
```
|
|
gs://bucket/{network_id}/{object_id}/{original_filename}
|
|
```
|
|
|
|
This enables:
|
|
- Bulk operations per network (audit, delete, lifecycle policies)
|
|
- Preserved original filename for downloads
|
|
- No coupling to particle hierarchy
|
|
|
|
The particle stores `object_id` in its data. The depot service handles signed URL generation. The particle's logical position in the hierarchy is independent of where its assets physically live.
|