Insert latency increases significantly with increased load

[mkysel]

this is most likely caused by a slow SelectStagedEnvelopes processing path.

This might have significant impact on our ability to handle load at scale

There are alternative options to our 2-phase insert pathway that we should explore.

There are also known improvements to the second stage.

[mkysel]

The main motivator for a two-phase insert is that we need the sequence ID before we can sign it. Can't put it into gateway envelopes without it being signed. Chicken and egg problem.

This can be resolved by grabbing a sequenceID before the full insert is executed.

There are two main challenges with this:

1. the sequenceID might be lost if the transaction rolls back or fails, creating a gap. Gapless sequences are a really nice theoretical concept, but fall apart in real environments.
2. selects can not return envelopes with sequenceIDs higher any currently unresolved transactions.

There has to be a solution for 2). Transactions can commit out of order and fail which is the main challenge.

The general pattern used is that transactions queue behind each other and once all previous transactions resolve, the entire chain can resolve.

[mkysel]

Note:

CREATE FUNCTION insert_staged_originator_envelope(topic BYTEA, payer_envelope BYTEA)
	RETURNS SETOF staged_originator_envelopes
	AS $$
BEGIN
	-- Ensures that the generated sequence ID matches the insertion order
	-- Only released at the end of the enclosing transaction - beware if called within a long transaction
	PERFORM
		pg_advisory_xact_lock(hashtext('staged_originator_envelopes_sequence'));
	RETURN QUERY INSERT INTO staged_originator_envelopes(topic, payer_envelope)
		VALUES(topic, payer_envelope)
	ON CONFLICT
		DO NOTHING
	RETURNING
		*;
END;
$$
LANGUAGE plpgsql;

will be another bottleneck that we are hitting in stage 1.