Skip to content
This repository was archived by the owner on Aug 18, 2026. It is now read-only.
This repository was archived by the owner on Aug 18, 2026. It is now read-only.

Batch inserts to same table into a single INSERT statements #36

Closed as not planned

Description

@maoueh

Right now, we batch SQL operations together and send them as a single transaction. It appears that a further tweak could be implemented, coalescing INSERT to the same tables together. Right now, each insert is packed as a single query unit:

insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

But we could actually package those above as a single INSERT query:

insert into Info(id, Cost,city) values
    (1, 100,"Pune"), (2, 50, "Satara"), (3, 65,"Pune"), (4, 97,"Mumbai"), (5, 12,"USA");

Leading to speed up when a lot of inserts is performed.

Ordering

Right now, we fully respects ordering as received from the DatabaseChanges. Full coalescing grouping by table would lead to a different insertion order at the expenses of a faster injection, this exhibits mostly when using some auto-increment column in rows.

We could still coalesce and keeping ordering, in the optimal case everything is still packed in a single query, worst case everything is made as a single query.

Partials Data on same table

The implementation will also need to deal with insert to same table but with a different set of columns. The safest will be to group query by table and set of inserted columns. In practice, we expect most insert to remain constants on terms of initial column inserted.

Activity

  1. GabrielCartier commented on Aug 18, 2026

    @GabrielCartier
    Contributor

    This repository is deprecated (see #133). The SQL sink now lives in the main substreams CLI as substreams sink postgres / substreams sink clickhouse, shipped in substreams v1.20.2. No new features land here.

    Closing as won't-fix in this repo. If you still want this, please open it against streamingfast/substreams.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions