Retrieving Historical Transactions by TxHash

[mollykarcher]

What

Stellar RPC has limited "archival" node support as of protocol 23. This was implemented as Option 1 as articulated in https://github.com/orgs/stellar/discussions/1717 but as of yet the only endpoint that supports history is getLedgers. This limited use satisfies several key use cases around backfilling/reingesting full history via RPC.

It was always the intent to expand this functionality into the full set of endpoints that RPC offers, including:

• getTransaction: Spike: Full history for getTransaction(hash) stellar-rpc#492
• getTransactions: Support full history in getTransactions stellar-rpc#493
• getEvents: Support full history in getEvents stellar-rpc#494

Among these, getTransaction by tx_hash has emerged as the most in-demand endpoint, based on feedback from ecosystem partners and operators that have already started running the archival node. This aligns with common developer expectations across chains; being able to retrieve a transaction by its hash, regardless of age, is considered useful for debugging, compliance, and auditing.

Options

There's been some investigation done into the options here: stellar/stellar-rpc#492

At this point, we'd like to align on the how this should be implemented, as there are some pretty significant trade-offs in balancing operator cost/burden and end-user ergonomics. This discussion should be focused on the trade-offs between those options. A couple points to keep in mind when assessing the pros/cons of each of these options:

• All RPC operators that currently support the "archive" node, also run their own Galexie instances and host their own datalakes. So where exactly the additional storage ends up landing might be immaterial to them
• There is likely a class of users that we aren't in close contact with that may be running RPC's locally/independently and falling back to public/third party datalakes only for historical data. It's really this class of user's that is most affected by RPC's operational costs going up
• It's probably acceptable from an end-user perspective if requests for older transaction data is slightly slower than more recent data, so the differences in end-user request speed may not be as impactful as they seem at first glance

Option 1: RPC-side Index

BLUF; super fast for users, but makes RPC operators store a lot more data

Implementation

• RPC (when run in "archival" mode) maintains its own tx_hash → ledger_sequence index across full history.
• When queried for a historical transaction outside the normal retention window, RPC:
  • Resolves the hash to a ledger sequence from the local index (local query)
  • Fetches the ledger metadata from the data lake (network request)
  • Parses and returns the transaction response

Pros

• Fastest end-user experience (minimal network hops)
• Index is co-located with RPC, so queries don’t depend on external services

Cons

• On-disk storage size/cost: estimated to go up by about ~1TB (see Spike: Full history for getTransaction(hash) stellar-rpc#492)
• Higher operational burden for RPC node operators. However, this potentially may be "acceptable" to said operators as typically in other chains, running "archive" nodes is expected to be more burdensome with much higher on-disk storage requirements than full nodes
• When this feature is rolled out, initialization of this index will likely take a long time (estimation TBD)

Option 2: Datalake-side Index

BLUF; cheaper for RPC operators, but slower for users and makes the data lake more complicated

Implementation

• Galexie, when writing ledger metadata, writes a global index of tx_hash -> ledger_sequence into the datalake
• When queried for a historical transaction outside the normal retention window, RPC:
  • Resolves the hash to a ledger sequence from the datalake's index (network request)
  • Fetches the ledger metadata from the data lake (network request)
  • Parses and returns the transaction response

Pros

• Minimal storage cost for RPC operators. Note that this makes the presumption that RPC operators are not also running Galexie and hosting their own datalake, which is unlikely)
• Centralized, shared index could serve other tools (not just RPC)
• Data integrity guaranteed by the same ingestion pipeline used for ledger metadata

Cons

• Slower end-user experience (multiple network round trips: RPC → data lake index → ledger metadata).
• Some datastore options (notably, S3) are likely not acceptable from a read latency perspective, further exacerbating the above point.
• Requires full reingest of history to initialize the index (a heavy lift—operators recently had to reingest full history due to Protocol 23 / unified events)
• Sets precedent for feature creep in the raw data lake, blurring boundaries of responsibilities
• Storage growth in the data lake (less impactful than RPC-side, but still notable)

Option 3: Datalake-side transaction store

BLUF; faster than Option 2 (+ maybe 1), but blows up data storage even more.

Implementation

• Galexie, when writing ledger metadata, dual-writes each transaction’s metadata (envelope, results, etc.) into a separate bucket keyed directly by tx_hash
• When queried for a historical transaction outside the normal retention window, RPC directly proxies to this new store in the datalake

Pros

• End-user lookup is faster than Option 2 (single request to data lake), potentially faster than Option 1 (would need estimation/spike to validate either way)
• Transaction retrieval becomes straightforward RPC-side, no extra parsing required

Cons

• All drawbacks of Option 2 still apply
• Significant storage growth: essentially duplicating transaction metadata

[chowbao]

Regarding data size of tx_envelope/result/etc... averages for 2025

• Per transaction
  • tx_envelope ~= 606 bytes
  • tx_result ~= 169 bytes
  • tx_fee_meta ~= 323 bytes
  • tx_meta (xdr.TransactionMeta so it includes operations, sorobanMeta, events, etc...) ~= 2374 bytes
• ~268 transactions per ledger
• ledger metadata ~= 160 KB (guesstimate from looking at the latest 100 .xdr.zst)

Other notes:

• GCS egress cost is $0.12 per GB (cost lowers after 1 TB)
• The tx_hash → ledger_sequence index across full history is ~500 GiB which is ~= $85/month
  • GCP disk storage cost for SSD provisioned space | $0.17 / 1 gibibyte month

None of this is super accurate. More for ballparking and vibes

[Shaptic]

When this feature is rolled out, initialization of this index will likely take a long time (estimation TBD)

Understatement of the year 😂 a full reingest of the network, even off of a data lake, would take weeks.

---

To add to the pros of (2), it's worth noting that you would have extremely fast lookups in S3 (and the smallest storage footprint) because it's a 1:1 mapping vs. other options that need to build indices "on top of" the table. As in, there's no need to do prefix trieing or anything special, it's just a single folder with every single txhash in it as a file by name. This makes lookups trivial (just don't ever ls the folder lol).

[mollykarcher]

Horizon full history reingestion went down from 3-4 months (captive core) to 1-2 weeks (data lake). Not saying 1-2 weeks is fast per se 😬 , but it can definitely be heavily parallelized and could probably get lower than Horizon since it'd be doing a lot less writes.

[Shaptic]

Hmm yeah, we'd have to figure out how to parallelize ingestion in RPC (since SQLite doesn't like that); with parallel downloads and batched inserts it might not be so bad. We could also do a galexie-like local store (stellar/stellar-rpc#492 (comment)) and get around that entirely.

[chowbao]

What if we or the ecosystem provides historical snapshots instead of doing a manual full history reingestion?

Also technically the full history of tx_hash: ledger can be dumped from Hubble very easily. It'd be a simple ingestion for sqllite from there vs needing to traverse/parse all the data lake files

[mollykarcher]

That's an idea we've already been playing around with, so it's definitely something we could consider. That said, that would help with the time to initialize full history but not the cost (assuming options 2/3, per karthik's estimates below).

[overcat]

What if we or the ecosystem provides historical snapshots instead of doing a manual full history reingestion?

I like the idea, when creating Galaxie data lake, we used 50 machines and it took nearly five days. If creating the index also requires this many machines to reduce ingest time, it would be great to provide snapshots for users to download directly, which could significantly reduce operational costs.

[karthikiyer56]

I did some non-trivial math for storage costs/write costs/read costs to S3, based on my understanding of S3 pricing for Option 2 (data lake side index) and Option 3 (data lake side tx store)

This is predicated on having 1 file per transaction with a potential naming scheme like - s3://transaction_data/dd330bdf9dc7870ee1fb53beb51464a4891da3f2249f62f847b742b273e538f5

Some Numbers

• Total Transactions so far (till September 2025) - appx 9 billion. (see this)
• Size of ledgerSequence - 4 bytes (Using 4 bytes for ledgerSequence deliberately instead of 8 bytes, since you can still have 600+ years worth of ledgers with 32 bits)
• Average size of transaction data - appx 2.5 kb = 2.5 * 1024 = 2560 bytes (based on estimates from @chowbao here)
• Currently:
  • average tx/ledger = appx 250 (as mentioned here. This is irrelevant in calculations below since we are going to use the total transactions so far (9 billion). I am mentioning this only for completeness)
  • Ledgers currently close appx 5 seconds
  • Tx/second = 250/5 = 50
• We want to get to ingest 500 tx/second (500 TPS). Currently we are at ~50 TPS.
• We want to be able to serve 500 getTransactionByHash requests/second. (I have done read cost estimation with this 500 reqs/sec number in mind. This number could very well be way less, especially if most requests are for most recent (less than 7 days old) transactions. In that case, RPC itself can fulfill most requests)

S3 Billing Details

S3 Storage Classes and Minimum Object Sizes

Storage Class	Minimum Billable Object Size	Use Case
S3 Standard	NONE - pay exactly for what you store	Frequently accessed data
S3 Intelligent-Tiering	NONE	Automatic cost optimization
S3 Standard-IA	128 KB minimum	Infrequently accessed data
S3 One Zone-IA	128 KB minimum	Infrequent, non-critical data
S3 Glacier	40 KB minimum	Archive, rarely accessed

For this analysis, we'll use S3 Standard (no minimum object size penalty).

Storage Billing for S3 Standard

S3 Standard charges $0.023 per GB per month (us-east-1) with NO minimum object size.

Example: 9 billion × 4-byte files

Storage: 9,000,000,000 × 4 bytes = 36,000,000,000 bytes
                                  = 36 GB
                                  = 0.035 TB

Monthly cost: 36 GB × $0.023/GB = $0.83/month

Example: 9 billion × 2,560-byte files

Storage: 9,000,000,000 × 2,560 bytes = 23,040,000,000,000 bytes
                                      = 23,040 GB
                                      = 23.0 TB

Monthly cost: 23,040 GB × $0.023/GB = $529.92/month

Storage Efficiency: 100% for both scenarios (no waste on S3 Standard)

---

Request Billing: Per-Request Pricing

NOTE: Request costs depend on NUMBER of requests, NOT data size

Request Type	Cost per 1,000 requests	Cost per request
PUT/POST/COPY	$0.005	$0.000005
GET/SELECT	$0.0004	$0.0000004
DELETE	$0 (free)	$0

Examples:

Operation	File Size	Cost
Write 1 file of 4 bytes	4 bytes	$0.000005
Write 1 file of 2.5 KB	2,560 bytes	$0.000005 (same!)
Read 1 file of 4 bytes	4 bytes	$0.0000004
Read 1 file of 2.5 KB	2,560 bytes	$0.0000004 (same!)

Why file size doesn't matter for request costs:

• S3 charges per API call (PUT, GET, etc.)
• Whether you PUT 1 byte or 5 GB, it's still ONE request
• The request cost is the same regardless of payload size

Write 1 billion files:

• Cost = (1,000,000,000 ÷ 1,000) × $0.005 = $5,000
• Same whether files are 4 bytes or 2.5 KB each

Read 1 billion files:

• Cost = (1,000,000,000 ÷ 1,000) × $0.0004 = $400
• Same whether files are 4 bytes or 2.5 KB each

---

Data Transfer Billing

For small files (<10 MB), data transfer costs are typically negligible compared to request costs.

Data Transfer OUT (to internet):

• First 100 GB/month: Free
• Next 10 TB/month: $0.09/GB
• 50-500 TB/month: $0.085/GB

For this analysis: With 500 queries/sec × 2.5 KB = ~3.2 TB/month transferred, cost would be ~$280/month.
We'll focus on storage + request costs as the primary drivers.

---

Use Case Parameters

Transaction Volumes

Parameter	Value	Calculation
Historical transactions (one-time)	9,000,000,000	9 billion existing transactions
Ongoing rate	500/second	Continuous ingestion
Transactions per month	1,296,000,000	500 × 60 × 60 × 24 × 30
Transactions per year	15,778,800,000	500 × 60 × 60 × 24 × 365.25
Total after 10 years	166,788,000,000	9B + (15.78B × 10) = 166 billion transactions

Storage Options

Option	File Size	Content
Scenario 1	4 bytes	Only ledger sequence number (uint32)
Scenario 2	2,560 bytes	Full transaction data (2.5 KB)

Query Pattern

Parameter	Value
Read queries	500/second
Queries per month	1,296,000,000

---

SCENARIO 1: Store 4-Byte Ledger Sequence

Initial Historical Ingestion (9 Billion Transactions)

Storage Calculation

Calculation Step	Formula	Result
Number of files	-	9,000,000,000
Data per file	-	4 bytes
Total storage	9,000,000,000 × 4 bytes	36 GB
S3 Standard billing	No minimum per file - exact size	36 GB
Monthly storage cost	36 GB × $0.023/GB	$0.83/month

One-Time Write Cost

Calculation Step	Formula	Result
Total PUT requests	9,000,000,000 files	9 billion requests
Cost per 1,000 PUTs	-	$0.005
Total PUT cost	(9,000,000,000 ÷ 1,000) × $0.005	$45,000

Note: This is a ONE-TIME cost to upload historical data. Takes approximately 4 weeks (?) with parallel ingestion.

---

Ongoing Monthly Costs (500 tx/sec)

Monthly Write Costs

Calculation Step	Formula	Result
Transactions per second	-	500
Seconds per month	60 × 60 × 24 × 30	2,592,000
Transactions per month	500 × 2,592,000	1,296,000,000
Cost per 1,000 PUTs	-	$0.005
Monthly PUT cost	(1,296,000,000 ÷ 1,000) × $0.005	$6,480.00

Monthly Read Costs

Calculation Step	Formula	Result
Queries per second	-	500
Queries per month	500 × 2,592,000	1,296,000,000
Cost per 1,000 GETs	-	$0.0004
Monthly GET cost	(1,296,000,000 ÷ 1,000) × $0.0004	$518.40

Monthly Storage Growth

Calculation Step	Formula	Result
New files per month	-	1,296,000,000
Size per file	-	4 bytes
Storage growth	1,296,000,000 × 4 bytes	4.84 GB/month
Additional storage cost	4.84 GB × $0.023	$0.11/month

---

10-Year Cost Projection (Scenario 1)

Time Period	Total Files (Billions)	Total Storage	Storage Cost	Write Cost	Read Cost	Total Monthly
Month 0 (initial)	9.00	36.0 GB	$0.83	$6,480.00	$518.40	$6,999.23
Month 1	10.30	41.1 GB	$0.95	$6,480.00	$518.40	$6,999.35
Year 1 (Month 12)	24.58	98.3 GB	$2.26	$6,480.00	$518.40	$7,000.66
Year 2 (Month 24)	40.15	160.6 GB	$3.69	$6,480.00	$518.40	$7,002.09
Year 5 (Month 60)	86.67	346.7 GB	$7.97	$6,480.00	$518.40	$7,006.37
Year 10 (Month 120)	164.79	659.2 GB	$15.16	$6,480.00	$518.40	$7,013.56

Key Insight: Storage cost grows from $0.83 to $15.16/month over 10 years, but request costs dwarf storage costs at $6,998.40/month.

Summary Costs (Scenario 1 - 4 bytes)

Cost Component	Amount	Notes
One-time ingestion	$45,000	9B PUT requests
Month 1 operational	$6,999	Storage barely matters
Month 120 operational	$7,014	Storage still minimal
Average monthly (10 years)	~$7,005	Very stable
10-year operational total	~$840,600	120 months × ~$7,005
GRAND TOTAL (10 years)	~$885,600	One-time + operational

---

SCENARIO 2: Store 2.5 KB Transaction Data

Initial Historical Ingestion (9 Billion Transactions)

Storage Calculation

Calculation Step	Formula	Result
Number of files	-	9,000,000,000
Data per file	-	2,560 bytes (2.5 KB)
Total storage	9,000,000,000 × 2,560 bytes	23,040 GB = 23.0 TB
S3 Standard billing	No minimum - exact size	23.0 TB
Monthly storage cost	23,040 GB × $0.023/GB	$529.92/month

One-Time Write Cost

Calculation Step	Formula	Result
Total PUT requests	9,000,000,000 files	9 billion requests
Cost per 1,000 PUTs	-	$0.005
Total PUT cost	(9,000,000,000 ÷ 1,000) × $0.005	$45,000

Note: Same as Scenario 1 - cost is per request, not per byte!

---

Ongoing Monthly Costs (500 tx/sec)

Monthly Write Costs

Calculation Step	Formula	Result
Transactions per month	500/sec × 2,592,000 sec	1,296,000,000
Cost per 1,000 PUTs	-	$0.005
Monthly PUT cost	(1,296,000,000 ÷ 1,000) × $0.005	$6,480.00

Note: Same as Scenario 1 - file size doesn't affect request cost!

Monthly Read Costs

Calculation Step	Formula	Result
Queries per month	500/sec × 2,592,000 sec	1,296,000,000
Cost per 1,000 GETs	-	$0.0004
Monthly GET cost	(1,296,000,000 ÷ 1,000) × $0.0004	$518.40

Note: Same as Scenario 1 - file size doesn't affect request cost!

Monthly Storage Growth

Calculation Step	Formula	Result
New files per month	-	1,296,000,000
Size per file	-	2,560 bytes
Storage growth	1,296,000,000 × 2,560 bytes	3,089.9 GB/month
Additional storage cost	3,089.9 GB × $0.023	$71.07/month

---

10-Year Cost Projection (Scenario 2)

Time Period	Total Files (Billions)	Total Storage	Storage Cost	Write Cost	Read Cost	Total Monthly
Month 0 (initial)	9.00	23.0 TB	$529.92	$6,480.00	$518.40	$7,528.32
Month 1	10.30	26.3 TB	$604.97	$6,480.00	$518.40	$7,603.37
Year 1 (Month 12)	24.58	62.7 TB	$1,442.10	$6,480.00	$518.40	$8,440.50
Year 2 (Month 24)	40.15	102.4 TB	$2,354.27	$6,480.00	$518.40	$9,352.67
Year 5 (Month 60)	86.67	221.1 TB	$5,085.30	$6,480.00	$518.40	$12,083.70
Year 10 (Month 120)	164.79	420.3 TB	$9,666.90	$6,480.00	$518.40	$16,665.30

Key Insight: Storage cost grows from $530 to $9,667/month over 10 years. Storage becomes a major cost factor unlike Scenario 1.

Summary Costs (Scenario 2 - 2.5 KB)

Cost Component	Amount	Notes
One-time ingestion	$45,000	9B PUT requests
Month 1 operational	$7,603	Storage starts significant
Month 120 operational	$16,665	Storage dominates at end
Average monthly (10 years)	~$12,000	Growing over time
10-year operational total	~$1,440,000	Varies month to month
GRAND TOTAL (10 years)	~$1,485,000	One-time + operational

---

Side-by-Side Comparison

Cost Comparison Table

Metric	Scenario 1 (4 bytes)	Scenario 2 (2.5 KB)	Ratio
One-time ingestion	$45,000	$45,000	Same
Initial storage (Month 0)	36 GB	23.0 TB	640× larger
Initial storage cost	$0.83/mo	$529.92/mo	638× more
Monthly writes	$6,480	$6,480	Same
Monthly reads	$518.40	$518.40	Same
Storage growth/month	4.84 GB	3,089.9 GB	638× faster
Month 1 total	$6,999	$7,603	+9%
Year 10 monthly	$7,014	$16,665	+138%
10-year total	$886k	$1,485k	+68%

Storage vs Request Cost Balance

Scenario 1 (4 bytes):

• Request costs: $6,998/month (constant)
• Storage costs: $0.83 → $15/month (negligible)
• Requests dominate 99.8% of costs

Scenario 2 (2.5 KB):

• Request costs: $6,998/month (constant)
• Storage costs: $530 → $9,667/month (significant and growing)
• Storage becomes 58% of costs by year 10

Value Analysis

Metric	Calculation	Result
Extra data stored	2,560 bytes vs 4 bytes	640× more data
Extra 10-year cost	$1,485k - $886k	+$599k

---

Key Insights

1. S3 Standard Has NO Minimum Object Size

2. Request Costs Are Identical

Both scenarios:

• Writes: $6,480/month (1.296B PUT requests)
• Reads: $518.40/month (1.296B GET requests)
• File size irrelevant for request pricing

3. Storage Costs Differ Dramatically

Scenario 1 (4 bytes):

• Storage costs: $0.83 → $15/month
• Negligible compared to request costs
• Total 10-year cost dominated by requests

Scenario 2 (2.5 KB):

• Storage costs: $530 → $9,667/month
• Significant and grows to dominate costs
• Storage becomes 58% of costs by year 10

[tamirms]

these calculations are really helpful! The $45,000 cost to ingest the data is prohibitively expensive IMO. For this reason, and because using S3 / GCS as a transaction hash database seems like an inappropriate application of an object store, I am in favor of option 1 (index which lives in the rpc service).

With regards to what database we should use in rpc for option 1, I think rocks db could be a good option because of its backup functionality which would make it easier for RPC providers to distribute backups of the db when provisioning new nodes. My only concern would be if the golang bindings for rocks db are well maintained / mature.

[tupui]

There are a few assumptions there but it sounds extremely costly. If you have these levels of costs, it really makes sense to look into collocation instead of using S3 or else. Just saying that as to keep that in sight in architecting the final solution. You would not want to make that harder to do.

If I would have that kind of money and business needs to put into infra, I would definitely ask my team twice if we cannot collocate (at Equinix directly even.) On a more philosophical note, it also makes more sense to me as well. What is web3 on AWS otherwise?

[overcat]

My only concern would be if the golang bindings for rocks db are well maintained / mature.

As far as I know, the Trezor team has been using RocksDB to store their Bitcoin/Ethereum index data for years, and they are also using Go, which works quite well.

[overcat]

Yes, the writing costs are very high. Currently, https://quasar.lightsail.network/ is using Cloudflare R2. If we choose options 2 or 3 in the future, I will build an S3-compatible storage layer on bare metal to make the costs acceptable.

[johncanneto]

@overcat and @tmosleyIII what are your thoughts on the above options?

[tmosleyIII]

I could work with Option 1 even with the additional operational burden