Backfill prod data

[amishas157]

What problem does your feature solve?

We would like to setup indexer with historical data for contract data and TTL.

What would you like to see?

We need a backfill script which exports data from BQ and imports to postgres instance.

What alternatives are there?

We could run indexer from historical ledger, but this is time expensive process

[amishas157]

I experimented with both the options to backfill data

Option 1: Import data via script which exports data from Bigquery, exports to GCS and then import to GCloud

Time: 3-4 hours
Pros: Super quick, less resource intensive
Cons:

• Chances of encountering PK violations if we re-ingest existing PKs
• Hard to maintain/productionize as it depends on BQ dataset. Need maintainance of SQL script and GO code for transformations. Little hacky.
• Needs more permissioning for service accounts

Option 2: Run an instance of ledger indexer with set start and end ledger set to old ledger sequences. I load tested for 1M ledger sequences.

Time: Loading 1M ledger sequences took ~17 hours and there are 10M ledger sequences. Even if parallelize multiple instances, I expect this to take 3 * 17 hours= ~50 hours (2.5 days)

Pros: Already productionized and will create deterministic results
Cons: Quite slow

My take is that we should go forward with Option 2 here for backfill because it is more resilient and robust. Gracefully handles errors and retries. Need less human intervention and is less hacky in general. Yes, it comes at the cost of more compute and time expensive, however since we will not need to run backfill quite often, this option is more justified.

@stellar/data-eng what do you think?

[chowbao]

I'd go with option 2. A little over 2 days isn't bad considering a full data lake backfill takes 4+ days and historically backfills have taken weeks to months on other networks and within other projects/teams within SDF

[sydneynotthecity]

+1 I agree that option 2 is how we should move forward. Since this indexer does not filter data and we don't expect to build the service often, there is no need for us to optimize backfilling further. Integrity of the data is more important that backfill speed, and we should favor whatever option is deterministic and idempotent.

There are two many risks with option 1, and the risks outweigh the benefits (speed + cheaper)

If we needed to, could we parallelize the ingestion so that multiple indexers were writing to the same db just for backfilling?

[amishas157]

If we needed to, could we parallelize the ingestion so that multiple indexers were writing to the same db just for backfilling?

Yes, my estimate is based on the fact that we will run 3 instances in parallel. Note that bottleneck is on the DB's connection pool. I believe it should be able to take connections from 3 consequently running instances.