On Fri, 2020-04-03 at 18:27 +1100, Tim Cross wrote:
> > I am using the COPY command to insert 10 million rows from a CSV file
> > into a database table and I am finding the performance is
> > unacceptable. When the COPY command is executed the disk I/O is
> > extremely high which leads to degraded query performance for other
> > queries being executed on the database.
> >
> > What are my options in this scenario?
>
> I don't think there is any way you can disable/drop indexes just for one
> transaction and not impact on other activities, such as queries. What
> options you have depends on a number of factors. Some
> questions/suggestions.
These are all good suggestions.
Let me add that there is a trade-off between fast queries and fast data
modifications. You may drop those indexes that are not absolutely required
and accept that some queries become slower.
Another way to save one index is to partition on one column and drop the
index on that column. Then queries can use a sequential scan on one
partition rather than an index scan, which might still be acceptable.
Speaking of partitioning: perhaps it is an option to use a partitioned
table and load the new data in a new partition. Then you can add the index
after loading and only then you turn the new table into a table partition.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
