Best practices when using Appender API

[charlespnh]

I have an use case to handle traffic data and since it'll be write-heavy the Appender API is suitable for this. The traffic data will be parsed and inserted into 3 tables, so I'll have to create 3 appenders. I have a few questions regarding the best practices of using the Appender API in my particular cases:

1. Should each of this appender be created with a different connection, or can they share the same connection?
2. I'm storing these appenders as part of the application's lifetime, is this what I should be doing? Or is the appender only supposed to be short-lived in a function and the connection should be released back to db managed connection pool? It's a write-heavy use case so that's why I chose to go with the former, and the latter might incur overhead with creating a new connection and appender every time new traffic data arrives...

Thanks!

[taniabogatsch]

I haven't used the appender in production, but I've worked with others asking the same questions. So, my answer might not work perfectly for your use case, but it could be seen as an indication.

1. Each Appender should have its own connection. You limit parallelism if you create them on the same connection. Within a connection, DuckDB does not parallelize between append operations. So you'll end up appending to the (different) tables sequentially.
2. The Appender is supposed to be short-lived. I.e., you get a connection from the pool, create the appender, append your data, close the appender, and then release the connection back into the pool.
   2.1. Creating connections is very lightweight in duckdb. Additionally, they are already cached in the pool, so there shouldn't be any significant overhead.
   2.2. The Appender creation is also lightweight, and should be negligible in comparison to the actual append operations.

[charlespnh]

Thanks for the insight!!!