Spike: Full history for `getTransaction(hash)`

[Shaptic]

@stellar/platform-eng please edit this with your baseline suggestions so we don't clutter with comments.

• @urvisavla approximates the size of a full txhash -> ledger seq index table at ~500GiB
• In the lighthorizon proof-of-concept, we built a prefix-trie index (build code and the trie itself) from the transaction hashes that you could then read from to do transaction lookups by hash, something like aa/12/34/.../transaction.xdr. We could, feasibly, extend galexie to build a similar index and have RPC proxy to it.

[urvisavla]

@urvisavla approximates the size of a full txhash -> ledger seq index table at ~500GiB

I used an untuned sqllite db to store 1 week worth of recent transactions. The workflow involved reading ledger data from bucket, parsing the metadata and then storing that into a transactions table with two columns: transaction hash, ledger id.

It took 1 hour 11 minutes to ingest 120,960 ledgers with total of 33,882,707 transactions. The resulting database size was 5.2 GB, with the database index taking up approximately half of that space.

Projecting this for the entire history (~8 billion transactions):

• the total db size would exceed 1 TB. So while the raw data would be ~500 GB, the size of the db including index pushes the total size past TB.

• it would require minimum of 11 days to create the initial db. In practice, however, as the database grows, the actual time required would likely be significantly longer.

[karthikiyer56]

Refer this file for stats:

---

Current & Projected Stats

Metric	Value
Transactions so far	8,890,088,995
Average transactions per year	1,675,734,813
Projection (next 5 years)	8,378,674,065
Total (Current + 5 Years)	17,268,763,060
Lookup needed	tx_hash (32 bytes) → ledger_sequence (4 bytes)
Recent tx retention window	7 days (~32 million txs) (Appx 4.5 million tx per day)

---

Storage Cost per Row (Estimated)

NOTE: Using 4 bytes for ledgerSequence deliberately instead of 8 bytes, since you can still have 600+ years worth of ledgers with 32 bits

Component	PostgreSQL	SQLite	RocksDB	S3 (Flat File Store)
Key (tx_hash)	32 bytes	32 bytes	32 bytes	32 bytes
Value (ledger_seq)	4 bytes	4 bytes	4 bytes	4 bytes
Overhead	~27 B (tuple + alignment)	~20 B (row/page overhead)	~16 B (metadata)	~4 B (delimiters, newline)
Total per row	~63 B (no index) ~119 B (with index)	~56 B (no index) ~110 B (with index)	~52 B (raw) ~35–42 B (compressed)	~40 B (raw) ~25–30 B (compressed)

---

Total Storage Estimate (for 17.27 Billion Transactions)

Storage Layer	Raw Size Estimate	With Compression	Notes
PostgreSQL	~1.11 TB (table) + ~967 GB (index) = ~2.05 TB	~1.4–1.6 TB	B-tree index on tx_hash
SQLite	~1.9 TB	~1.2–1.4 TB	Somewhat compressible with VACUUM
RocksDB	~850 GB	~600–700 GB	Highly efficient for key-value
S3 (CSV files)	~690 GB	~400–520 GB	Best for archive, not live lookup

---

Operational & Functional Comparison

Feature	PostgreSQL	SQLite	RocksDB	S3 (Flat Files)
Write performance	⚠️ Moderate	⚠️ Low–moderate	✅ High (LSM)	❌ N/A (batch write only)
Random access	✅ Yes	✅ Yes	✅ Yes	❌ No
Compression support	✅ TOAST/ZSTD	⚠️ Limited / Manual	✅ Snappy / ZSTD	✅ GZIP / ZSTD
Suited for recent tx cache	❌ No	⚠️ Possible (<1B rows)	✅ Yes (hybrid cache)	❌ No
Embedded (no server)	❌ No	✅ Yes	✅ Yes	✅ Yes
Max scale (rows)	✅ 100B+	⚠️ ~1B (realistic)	✅ Trillions+	✅ (but not queryable)
Deployment complexity	Medium	Very low (1 file)	Low (lib + files)	Low (just static files)
Best use case	Full-featured DB	Small embedded DB	High-speed KV lookup	Cold storage / archival

---