Timestamp Sorting for Faster Data Access

[EnoRage]

Current Problem

When we fetch mempool data (transactions), we don't sort transactions by timestamp. This makes it slow to find transactions within specific time ranges.

Why This Matters

• Searching for transactions in a time range is much slower than it could be
• Each search has to scan through unsorted data
• This affects performance of time-based queries

Simple Solution

Sort the data by timestamp when saving it. This will make time-based searches much faster. Also we could split it with smaller equal row_group_size like 5000

Expected Benefits

• Much faster time-range searches

[metachris]

What makes you say that transactions are not sorted by timestamp?

They are sorted by the time they were received:

mempool-dumpster/cmd/merge/transactions.go

Lines 138 to 153 in a492c4f

	log.Info("Sorting transactions by timestamp...")
	txsSlice := make([]*common.TxSummaryEntry, 0, len(txs))
	for _, v := range txs {
	txsSlice = append(txsSlice, v)
	}
	sort.Slice(txsSlice, func(i, j int) bool {
	return txsSlice[i].Timestamp < txsSlice[j].Timestamp
	})
	log.Infow("Transactions sorted...", "txs", printer.Sprintf("%d", len(txsSlice)), "memUsed", common.GetMemUsageHuman())
	//
	// Write output files
	//
	cntTxWritten := writeFiles(txsSlice, fnParquetTxs, fnCSVTxs, fnCSVMeta)
	log.Infow("Finished merging!", "cntTx", printer.Sprintf("%d", cntTxWritten), "duration", time.Since(timeStart).String())

[EnoRage]

You are right, I misidentified the cause when I was experimenting to understand why timestamp-based searches were slow. Transactions are already sorted. The actual issue seems to be the row_group_size. It is not set correctly and if you will try to set it to 10_000 - the same time-based query will be much faster since data filtering with smaller groups works better