SPIKE: Investigate block time ingestion

[zone117x]

Block ingestion is taking either ~3.5 second or around 30 seconds, roughly evenly distributed over time. The slow 30 second ingestion time is going to be an issue with the 5 second Nakamoto blocks.

There isn't much deviation from these two durations, which implies there should be a relatively straightforward cause. The first thing that comes to mind is that re-orgs may trigger the 30 second blocks.

More debugging is needed to confirm the cause and look into a fix.

[zone117x]

The spike is caused by the nft_custody materialized view refresh. The query runs concurrently, i.e. the /new_block event processing kicks off the view refresh in the background. The query can take a couple minutes, and while it's running all subsequent interactions with the postgres instance are slowed down, including ingestion for the next block.

It doesn't look like the materialized view sql can be sufficiently optimized. We likely need to move to an approach of tracking nft custody more explicitly.

Relevant code here:

stacks-blockchain-api/src/datastore/pg-write-store.ts

Lines 3000 to 3026 in b95eac7

	async refreshNftCustody(txs: DataStoreTxEventData[], unanchored: boolean = false) {
	await this.sqlWriteTransaction(async sql => {
	const newNftEventCount = txs
	.map(tx => tx.nftEvents.length)
	.reduce((prev, cur) => prev + cur, 0);
	if (newNftEventCount > 0) {
	// Always refresh unanchored view since even if we're in a new anchored block we should update the
	// unanchored state to the current one.
	await this.refreshMaterializedView('nft_custody_unanchored', sql);
	if (!unanchored) {
	await this.refreshMaterializedView('nft_custody', sql);
	}
	} else if (!unanchored) {
	// Even if we didn't receive new NFT events in a new anchor block, we should check if we need to
	// update the anchored view to reflect any changes made by previous microblocks.
	const result = await sql<{ outdated: boolean }[]>`
	WITH anchored_height AS (SELECT MAX(block_height) AS anchored FROM nft_custody),
	unanchored_height AS (SELECT MAX(block_height) AS unanchored FROM nft_custody_unanchored)
	SELECT unanchored > anchored AS outdated
	FROM anchored_height CROSS JOIN unanchored_height
	`;
	if (result.length > 0 && result[0].outdated) {
	await this.refreshMaterializedView('nft_custody', sql);
	}
	}
	});
	}

stacks-blockchain-api/migrations/1636130197558_nft_custody.js

Lines 3 to 22 in b95eac7

	pgm.createMaterializedView('nft_custody', {}, `
	SELECT
	DISTINCT ON(asset_identifier, value) asset_identifier, value, recipient, tx_id, nft.block_height
	FROM
	nft_events AS nft
	INNER JOIN
	txs USING (tx_id)
	WHERE
	txs.canonical = true
	AND txs.microblock_canonical = true
	AND nft.canonical = true
	AND nft.microblock_canonical = true
	ORDER BY
	asset_identifier,
	value,
	txs.block_height DESC,
	txs.microblock_sequence DESC,
	txs.tx_index DESC,
	nft.event_index DESC
	`);