Hi… I am inserting data over PGWire and 84056 rows took around half hour to insert. Most of this data was Out of order so I guess commit lag can be culprit here for slow queries. But still this seems too high. Is it normal or something is wrong here?

what is your time partition?

Hi Ajay, Are you committing the data in each inserted row? Also, commit lag doesn't apply to PGWire.

@Nicolas Hourcard PARTITION BY DAY @Andrey Pechkurov I am using below code which uses batch insertion so ideally it is committing at some fixed interval or after some set of records instead of each row (We are using mybatis lib)

try (var session = getSqlSessionFactory().openSession(ExecutorType.BATCH, false)) {
    for (T entity : entityList) {
        entity._setCa(System.currentTimeMillis());
        var mapper = session.getMapper(getMapperClass());
        mapper.insert(entity, PartnerId);
    }
    session.commit();
}

I see. How big are your batches? 84056 rows or less?

at max in one go 10k rows will be there in

entityList

Could you double check if mybatis doesn't enable auto-commit? To have all rows applied in the same transaction, the database should receive the same commands:

BEGIN;
INSERT INTO t VALUES(...); -- row 1
-- ...
INSERT INTO t VALUES(...); -- row N
COMMIT;

@Andrey Pechkurov I tried inserting 1000 rows using same method and only one commit was performed so I don’t think mybatis is commiting every row. It commits all 100k rows together

Does it issue a

BEGIN;

to open a new transaction? Could you check?

@Andrey Pechkurov From the look of source library, I can see that when I call openSession method, it starts a new transaction and it will commit only when I call

session.commit()

which I only call after 10k rows. So it starts txn, takes all rows and will commit all 10k rows together. I am not sure what’s the exact issue here due to which we are getting such bad performance

Makes sense. Yes, using ILP would solve the concurrent writes problem. We have the following documentation page for the Java client: https://questdb.io/docs/reference/clients/java_ilp

@Andrey Pechkurov So I tried implementing a lock on write operations. So basically at any time only one write thread can acquire this lock and write the data. Even though writes were happening synchronously one after another, after 10th batch I got the same error

table busy reason = insert

So is it possible that even though quest send OK response to my write query but behind the scenes write might still be busy for few more seconds?

The database should do a force commit once you close all ILP connections for a table. But indeed there might be a short delay between the event when all connections are closed and the later event when the writer thread releases the writer. So, you should add a retry to handle this. One more thing to mention: we're going to release WAL support for tables soon (should happen in Jan). WAL tables support concurrent writes over PGWire and also don't have other

table busy

problems like ILP + concurrent UPDATE. So, once we release it, you may want to switch to WAL-enabled tables and get rid of the workarounds.