Slow simple update query on PostgreSQL database with 3 million rows

by

I have to update tables of 1 or 2 billion rows with various values for each rows. Each run makes ~100 millions changes (10%).
My first try was to group them in transaction of 300K updates directly on a specific partition as Postgresql not always optimize prepared queries if you use partitions.

  1. Transactions of bunch of “UPDATE myTable SET myField=value WHERE
    myId=id”
    Gives 1,500 updates/sec. which means each run would
    take at least 18 hours.
  2. HOT updates solution as described here with FILLFACTOR=50. Gives
    1,600 updates/sec. I use SSD’s so it’s a costly improvement as it
    doubles the storage size.
  3. Insert in a temporary table of updated value and merge them after
    with UPDATE…FROM Gives 18,000 updates/sec. if I do a VACUUM
    for each partition; 100,000 up/s otherwise. Cooool.
    Here is the
    sequence of operations:
CREATE TEMP TABLE tempTable (id BIGINT NOT NULL, field(s) to be updated,
CONSTRAINT tempTable_pkey PRIMARY KEY (id));

Accumulate a bunch of updates in a buffer depending of available RAM
When it’s filled, or need to change of table/partition, or completed:

COPY tempTable FROM buffer;
UPDATE myTable a SET field(s)=value(s) FROM tempTable b WHERE a.id=b.id;
COMMIT;
TRUNCATE TABLE tempTable;
VACUUM FULL ANALYZE myTable;

That means a run now takes 1.5h instead of 18h for 100 millions updates, vacuum included. To save time, it’s not necessary to make a vacuum FULL at the end but even a fast regular vacuum is usefull to control your transaction ID on the database and not get unwanted autovacuum during rush hours.

More Related Contents:

  1. How do I (or can I) SELECT DISTINCT on multiple columns?
  2. updating table rows in postgres using subquery
  3. Return pre-UPDATE column values using SQL only
  4. Casting NULL type when updating multiple rows
  5. SQL update fields of one table from fields of another one
  6. Bulk/batch update/upsert in PostgreSQL
  7. Postgres error updating column data
  8. Update a column of a table with a column of another table in PostgreSQL
  9. Functions with variable number of input parameters
  10. PostgreSQL “Column does not exist” but it actually does
  11. Any downsides of using data type “text” for storing strings?
  12. Query for array elements inside JSON type
  13. Calculating Cumulative Sum in PostgreSQL
  14. Change PostgreSQL columns used in views
  15. Way to try multiple SELECTs till a result is available?
  16. How to set auto increment primary key in PostgreSQL?
  17. List columns with indexes in PostgreSQL
  18. PostgreSQL IF statement
  19. Split given string and prepare case statement
  20. SQL UPDATE order of evaluation
  21. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  22. PostgreSQL wildcard LIKE for any of a list of words
  23. Postgres not using index when index scan is much better option
  24. How to store array or multiple values in one column
  25. convert Postgres geometry format to WKT
  26. Which datatype should be used for currency?
  27. How to return two columns with function
  28. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment