Hi everyone. I have one doubt regarding insert queries. Suppose I am running some Insert queries on QuestDB. For 10k rows I send 10k insert queries one by one and it seems to work fine. But what if my data increases? Is it a good practice to insert 1000 rows in a single insert statement? If I try to combine 1000 rows in single insert statement it will make Insert query large and I am not sure if that’s a good practice.

Hi @Ajay Pilaniya. Are you using web console or pg wire ? If you're using pg wire then the recommended approach is to use insert statements with bind variables and jdbc batching . Pushing 1000 rows in a single INSERT (a), (b), (c), ... statement isn't recommended because every such statement needs to be parsed .

@Bolek Ziobrowski I am using PGWire. So if I have 10k rows I send 10k queries like this :

INSERT INTO sampleTable(colA, colB, colC) VALUES (A,B,C)
INSERT INTO sampleTable(colA, colB, colC) VALUES (A1,B2,C2)
.
.
.
INSERT INTO sampleTable(colA, colB, colC) VALUES (A10000,B10000,C10000)

So is this approach okay? Sending one statement per row? If not can you share some more info on how to achieve batching

You'd need to use prepared statements and ? in place of values. That only works if the library you use to connect to qdb supports it (e.g. postgresql jdbc driver)

@Ajay Pilaniya what library/client do you use to insert the data?

@Jaromir Hamala I am using mybatis java library

if you do this: https://mybatis.org/mybatis-dynamic-sql/docs/insert.html#single-row-insert then my understanding is that it will use prepared statements. so questdb will not re-compile the same INSERT over and over again. but the JDBC driver will still likely keep sending the query text over the wire. that does not sound very efficient. you could eliminate it with: https://mybatis.org/mybatis-dynamic-sql/docs/insert.html#multiple-row-insert-support this should send the query text just once. but it has limitations too. they are described in the docs. so I would try a combination: 1. chop your data into smaller batches (say 1,000 rows) 2. then insert each batch using the Multiple Row Insert Support strategy. this way you will avoid a round-trip for each row. and yet you can be sure a single INSERT won’t be too long. it’ll be capped by your batch-size. does this make sense?

Yeah I think so. I found another link which refers to batch insert - https://github.com/mybatis/mybatis-3/wiki/FAQ#how-do-i-code-a-batch-insert This should also work

you can try it too. I am not sure what it does under the hood. if it does not work with QuestDB then please give us a shout.

Yeah sure