Java – use JDBC to insert slowly on PostgreSQL

I work on a system that downloads data from a cloud system to a local database (PostgreSQL, mysql,...) Now I have a PostgreSQL performance problem because it takes a lot of time to insert data

Many columns and data may vary in size In a sample project, I have one with about 170 columns There is a unique index - the speed of insertion does not change even after the index is deleted

I am using the jdbc driver to connect to the database, and I insert data in batches of 250 rows (using namedparameterjdbctemplate)

It took me about 18 seconds to insert data on Postgres The same dataset on MySQL takes only one second This is a huge difference - where does it come from? Is Postgres's jdbc driver slow? Can you configure it in some way to make it faster? What else do I miss? The difference between Postgres and MySQL is so great How can any other idea make it faster?

I made one that can be in GitHub – https://github.com/varad/postgresql-vs-mysql Sample projects provided on Everything happens in the "run" method of letsgo class

Solution

It seems that this is a combination of spring "bug" and driver "bug"

Every time setValue () is called, spring tries to determine the data type of the column It calls preparedstatementmetadata getParameterMetaData()

This obviously causes a "prepare" statement to be sent to the database itself, which is very fast (never more than 1ms on my laptop), but it is called each column of each row, which summarizes a lot of time (it calls each non null value, resulting in about 23.000 calls)

To some extent, this is a spring bug, a driver bug, because there is no cache parameter metadata, which is not really meaningful (at least in my opinion) The MySQL jdbc driver does not support getparametermetadata(), which spring knows, so this "bug" will not be displayed in mysql, because spring will never call this method

I don't know if Postgres's jdbc driver behavior can be classified as an error, but it would be good if the driver caches the metadata after the first call

Spring can convince you not to use the property spring jdbc. getParameterType. Ignore get statement metadata

So put:

System.setProperty("spring.jdbc.getParameterType.ignore","true");

front:

LetsGo letsGo = new LetsGo();

This behavior is disabled

This property must be set before spring initialization

When I use your sample project to do this, insert and run 500ms on my laptop

edit

After seeing the comments on using the Postgres ng driver, I put them into the source code of the "official" driver and the ng driver. The ng driver will cache the parameter metadata after the first call, and the official driver will not explain why using the ng driver is much faster (calls are not disabled in spring)