Delete in the hot path of insert staged envelope slows down the write

[fbac]

The current publish worker loop is:

SELECT N envelopes
... process ...
INSERT as originator envelope
... check if it was inserted ...
DELETE staged envelope

This is likely creating delete bloat / vacuum pressure.

Can this process become more efficient? Ideas:

• SKIP LOCKED + delete in same transaction
• claim + mark processed_at, delete later in another sweeper/pruner process
• append only, partition and drop old partitions?

[neekolas]

Right now we are effectively using the staged envelopes table as a queue for writing to originator envelopes. The semantics we would need to preserve is that the staged envelopes are always transitioned to originator envelopes in order.

[tylerhawkes]

If everything is going to age out quickly, I would probably go with relatively short lived partitions that can be dropped as a whole. The nice thing is that there is very little WAL and db writes as it just needs to update the metadata and mark blocks as unused.

[fbac]

@neekolas @tylerhawkes that's the direction I lean towards, as staged envelopes should always age quickly we can partition them same as meta, and drop it once all of them are marked as processed by the publish worker.
That approach should give us the same semantics.