Improving performance for large dataset inserts

[cachapa]

My package postgres_crdt uses postgres to interface with the database backend, and one of its main features is being able to merge sometimes large datasets from other nodes in the network.

I created a simple benchmark that merges 10k records. On my machine it takes 10+ seconds as individual queries; ~2s using transactions, and ~1.5s using transactions and prepared statements.

I suppose the meager improvement when using prepared statements is because each statement is still causing a roundtrip to the database, so I'd like to ideally find a way to avoid that overhead if possible.

Sqflite has a batch method that collects multiple queries on the Dart side and executes them in a single call to Sqlite. In the same benchmark I'm seeing 4x performance improvements using that strategy, and I suspect the gains would be much better for Postgres since it's using network rather than IPC calls.

Though I haven't been able to find a direct equivalent for Postgres, it seems to be possible to pass multiple values using prepared statements - if I'm reading this SO answer correctly:

PREPARE addFriend(INTEGER, INTEGER) AS
    INSERT INTO friendRelation (u_id, friendid) VALUES ($1, $2), ($2, $1);

I wonder if it would be possible to expose this mechanic via the postgres API, or if there are other strategies that could help in my case that I'm missing.

[isoos]

~2s using transactions

I assume this is 10k individual INSERT INTO x VALUES ($1, $2); executed inside a transaction block. You can certainly insert multiple records using INSERT INTO x VALUES ($1, $2), ($3, $4), ($5, $6);, and in fact it would be interesting if you could benchmark what difference it may make.

One performance bottleneck you may be hitting here is an implementation detail of the 3.x version of the package: we decided to wait for each query to have their own values completed before sending in the next query, which, in this case, may add a few milliseconds here and there. In that regard it may be better to send a huge insert with statement with 100s of records than rewriting the package and queue up the queries to postgres earlier.

I wonder if it would be possible to expose this mechanic via the postgres API,

Any idea how that would look like?

[cachapa]

Any idea how that would look like?

Off the top of my head, and inspired on the design of Sqflite's batch, something like this:

final batch =  connection.prepareBatch(sql);
for (final values in changeset) {
  batch.add(values);
}
await batch.commit();

Though on second thought maybe this method wouldn't work because the insertion for my use case uses upserts and string comparison, and probably needs to be executed one by one.

For example:

INSERT INTO users (id, name, hlc, node_id, modified)
  VALUES (?1, ?2, ?3, ?4, ?5)
ON CONFLICT (id) DO
  UPDATE SET
    id = ?1, 
    name = ?2, 
    hlc = ?3, 
    node_id = ?4, 
    modified = ?5
  WHERE
    excluded.hlc > users.hlc

Which kind of brings me back to square one.

As an alternative I tried sending multiple queries in a single execute, but the library doesn't like that either:

await _connection.execute('''
    INSERT INTO users (id, name, hlc, node_id, modified)
    VALUES (?1, ?2, ?3, ?4, ?5);
    INSERT INTO users (id, name, hlc, node_id, modified)
    VALUES (?6, ?7, ?8, ?9, ?10);
    ''', parameters: _combinedArgs);

Severity.error 42601: cannot insert multiple commands into a prepared statement

[isoos]

I'd expect this to work:

await _connection.execute('''
    INSERT INTO users (id, name, hlc, node_id, modified)
    VALUES (?1, ?2, ?3, ?4, ?5), (?6, ?7, ?8, ?9, ?10);
    ''', parameters: _combinedArgs);

Severity.error 42601: cannot insert multiple commands into a prepared statement

I think this is coming from Postgresql, 42601 is a generic error code for syntax error, and the message makes it specific.

[cachapa]

I'd expect this to work:

You're right, that did work, though it's not useful for my particular case because of the upsert and where clause.

Do you think it's feasible to perform the queries in parallel, e.g. by using multiple sessions? I'm not really interested in the result of each query.

I also found pipeline mode but it doesn't seem to be supported by this package.

[isoos]

Do you think it's feasible to perform the queries in parallel, e.g. by using multiple sessions? I'm not really interested in the result of each query.

I think it depends on the primary key and indexes: it is very likely that the parallel queries will eventually fight for the same lock. It may be worth trying out though, like 2, 3 or 4 parallel connection may be worth a try.

I also found pipeline mode but it doesn't seem to be supported by this package.

If somebody is interested implementing this over or independent of the current connection handling, I am willing to review the code, but I don't have any plans to implement it right now.

[cachapa]

Thanks for the help. I created issue #311 to track this.