Re: Multi Inserts in CREATE TABLE AS - revived patch - Mailing list pgsql-hackers

On Thu, Nov 26, 2020 at 12:25 PM Luc Vlaming <luc@swarm64.com> wrote:
>
> What I'm wondering about is the reason for wanting a cap on data volume.
> When doing some local (highly concurrent) ingest speed tests a few weeks
> ago it seemed to mostly matter how many pages were being written and the
> resulting pressure on locks, etc. and not necessarily so much the actual
> memory usage. I didn't collect proof on that though (yet). There was
> however a very clearly observable contention point where with bigger
> buffers the performance would not only stagnate but actually drop.
>
> So what I'm kinda wondering is if we should worry more about the amount
> of pages that are going to be written and maybe not so much about the
> memory usage?
>
> If this were to be the case then maybe we can consider improving the
> current design, potentially in a follow-up patch? The problem I see is
> that generically each tableam will have different choices to make on how
> to buffer and flush multiple rows, given that a storage engine might
> have more or less write amplification, a different way of extending a
> relation, fsm use, etc.
> Assuming we indeed want a per-tableam implementation, we could either:
> - make multi_insert buffer the tuples itself and add a flush_multi_insert.
> - add a new function called create_multi_insert which returns something
> like a MultiInsertState, which, like a destreceiver, has a set of
> callbacks to start, shutdown and insert.
>
> With both solutions one part that to me seems appealing is that we
> buffer the data in something that likely resembles the disk format very
> much. Thoughts?
>

IMHO, I would like to go with your option 1 i.e. add a few APIs to the
TableAmRoutine structure. Advantage is that we could use these APIs in
at least 3 places, without much code duplication: 1) COPY 2) CTAS and
3) Refresh Materialized View. I could roughly sketch the APIs in below
way:

typedef struct MultiInsertStateData
{
    MemoryContext         micontext; /* A temporary memory context for
multi insert. */
    BulkInsertStateData *bistate;   /* Bulk insert state. */
    TupleTableSlot      **mislots; /* Array of buffered slots. */
    uint32              nslots; /* Total number of buffered slots. */
    uint64              nbytes; /* Flush buffers if the total tuple
size >= nbytes. */
    int32              nused; /* Number of current buffered slots for
a multi insert batch. */
    int64              nsize; /* Total tuple size for a multi insert
batch. */
} MultiInsertStateData;

/* Creates a temporary memory context, allocates the
MultiInsertStateData, BulkInsertStateData and initializes other
members. */
    void        (*begin_multi_insert) (Relation rel,
MultiInsertStateData **mistate, uint32 nslots, uint64 nbytes);

/* Buffers the input slot into mistate slots, computes the size of the
tuple, and adds it to the total tuple size of the buffered tuples, if
this size crosses mistate->nbytes, flush the buffered tuples into
table. For heapam, existing heap_multi_insert can be used. Once the
buffer is flushed, then micontext can be reset and buffered slots can
be cleared. */
    void        (*do_multi_insert) (Relation rel, MultiInsertStateData
*mistate, struct TupleTableSlot *slot, CommandId cid, int options);

/* Flush the buffered tuples if any. For heapam, existing
heap_multi_insert can be used. Deletes temporary memory context and
deallocates mistate. */
    void        (*end_multi_insert) (Relation rel,
MultiInsertStateData *mistate, CommandId cid, int options);

Thoughts?

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com