Batch status check queries for pending transactions in txsub

[bartekn]

What version are you using?

2.1.0

What did you do?

@tomerweller was running into performance issues when submitting thousands of transactions to a standalone network. It looks like the problem is related to this code:

go/services/horizon/internal/txsub/system.go

Lines 307 to 325 in 56d94a2

	for _, hash := range sys.Pending.Pending(ctx) {
	tx, err := txResultByHash(db, hash)
	if err == nil {
	logger.WithField("hash", hash).Debug("finishing open submission")
	sys.Pending.Finish(ctx, hash, Result{Transaction: tx})
	continue
	}
	if _, ok := err.(*FailedTransactionError); ok {
	logger.WithField("hash", hash).Debug("finishing open submission")
	sys.Pending.Finish(ctx, hash, Result{Transaction: tx, Err: err})
	continue
	}
	if err != ErrNoResults {
	logger.WithStack(err).Error(err)
	}
	}

With many pending transactions getting its' results, even when separate queries are fast, can cumulate to a very long updates time (few seconds time). The chart below shows the time distribution of tx status updates after an example ledger ingestion:

What did you expect to see?

When checking the status of pending transaction we should send a single batch query instead of thousands queries asking for a single tx result. This should make this magnitudes faster.

When working on this please remember about two things:

• Postgres param limit of around 65k. If a batch query needs to send more params it should be called multiple times.
• The query used right now is searching for both normal txs and fee bump txs. The batch query should do the same thing.

[ire-and-curses]

How do we decide when we've accumulated enough status checks to send a single batch query? There's a tradeoff here between not waiting and batching for performance.

[bartekn]

Currently we check it on every app tick which is every second, even when there are no pending txs.