coins.sqlite contains the full TXO, and this is too slow

[BrannonKing]

The coins cache (coins.sqlite) in v17 (and v19) contains every unspent output (TXO). This duplicates the majority of what we store in the block files. It's particularly painful in lbrycrd because we use the TXO for claim reservation. Hence, it's storing all the metadata there. We need to change this table to one of these options:

1. Store the index into the block files instead and pull the data from the block files when it's needed.
2. Store the claim-stripped TXO and look up the claim prefix when we need the full TXO.

Update: I realized that this same issue exists in v17.3 and earlier.

[bitcoinbrisbane]

@BrannonKing do you mean to change this table? : https://github.com/lbryio/lbrycrd/blob/v19_master/src/txdb.cpp#L162

[BrannonKing]

No, this refers to the script column in the unspent table. See https://github.com/lbryio/lbrycrd/blob/68ecea3fdbf9615e7ee23043d5c13b62dd9c3669/src/txdb.cpp#L32

[bvbfan]

Since we have #383 maybe 1 is not desired option? For 2 we may want to have another db to store only scripts, readed on demand.

[BrannonKing]

If we put the scripts into some other DB, we're still duplicating the majority of the block files. The script data is larger than the block filter data. It's the writing of that (large amount of) data that is slow.

[BrannonKing]

I was pondering changing the Coin class to look like this below, but I didn't know how to flesh out the FillInScript call:

class Coin
{
    //! unspent transaction output
    CTxOut out;

    enum CoinFlags {IS_COINBASE=1, NEEDS_SCRIPT=2, IS_STRIPPED_SCRIPT=4};
    CoinFlags flags;

    void FillInScript() {
        // options:
        // 1. use the TxIndex to read in the transaction (where's our TXID?)
        // 2. read in the block
    }
public:
    bool IsCoinBase() const { return flags & IS_COINBASE; }

    const CAmount& value() const { return out.nValue; }

    const CScript& strippedScript () const {
        if ((flags & IS_STRIPPED_SCRIPT) || (flags & IS_COINBASE))
            return out.scriptPubKey;
        if (flags & NEEDS_SCRIPT)
            FillInScript();
        return StripClaimScriptPrefix(out.scriptPubKey)
    }

    const CScript& script() const {
        if (flags & (IS_STRIPPED_SCRIPT | NEEDS_SCRIPT))
            FillInScipt();
        return out.scriptPubKey;
    }

    //! at which height this containing transaction was included in the active block chain
    const uint32_t nHeight;

    //! construct a Coin from a CTxOut and height/coinbase information.
    Coin(CTxOut&& outIn, int nHeightIn, bool fCoinBaseIn) : out(std::move(outIn)),
        flags(fCoinBaseIn ? IS_COINBASE : CoinFlags(0)), nHeight(nHeightIn) {}
    Coin(const CTxOut& outIn, int nHeightIn, bool fCoinBaseIn) : out(outIn),
        flags(fCoinBaseIn ? IS_COINBASE : CoinFlags(0)), nHeight(nHeightIn) {}
    Coin(CAmount nValueIn, int nHeightIn, bool fCoinBaseIn) : out(nValueIn, {}),
        flags(CoinFlags((fCoinBaseIn ? IS_COINBASE : 0) | NEEDS_SCRIPT)), nHeight(nHeightIn) {}

    Coin(CAmount nValueIn, const CScript& stripped, int nHeightIn, bool fCoinBaseIn) : out(nValueIn, {}),
        flags(CoinFlags((fCoinBaseIn ? IS_COINBASE : CoinFlags(0)) | IS_STRIPPED_SCRIPT)), nHeight(nHeightIn) {}

    void Clear() {
        out.SetNull();
        fCoinBase = false;
        nHeight = 0;
    }

    //! empty constructor
    Coin() : fCoinBase(false), nHeight(0) { }

    template<typename Stream>
    void Serialize(Stream &s) const {
        assert(!IsSpent());
        uint32_t code = nHeight * 2 + (flags & IS_COINBASE);
        ::Serialize(s, VARINT(code));
        ::Serialize(s, CTxOutCompressor(REF(out)));
    }

    template<typename Stream>
    void Unserialize(Stream &s) {
        uint32_t code = 0;
        ::Unserialize(s, VARINT(code));
        nHeight = code >> 1;
        flags = CoinFlags(code & IS_COINBASE);
        ::Unserialize(s, CTxOutCompressor(out));
    }
...