Optimizing bulk update performance in PostgreSQL

Question

Using PG 9.1 on Ubuntu 12.04.

It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(We're just overwriting fields of objects identified by ID.) The values come from an external data source (not already in the DB in a table).

The tables have handfuls of indices each and no foreign key constraints. No COMMIT is made till the end.

It takes 2h to import a pg_dump of the entire DB. This seems like a baseline we should reasonably target.

Short of producing a custom program that somehow reconstructs a data set for PostgreSQL to re-import, is there anything we can do to bring the bulk UPDATE performance closer to that of the import? (This is an area that we believe log-structured merge trees handle well, but we're wondering if there's anything we can do within PostgreSQL.)

Some ideas:

• dropping all non-ID indices and rebuilding afterward?
• increasing checkpoint_segments, but does this actually help sustained long-term throughput?
• using the techniques mentioned here? (Load new data as table, then "merge in" old data where ID is not found in new data)

Basically there's a bunch of things to try and we're not sure what the most effective are or if we're overlooking other things. We'll be spending the next few days experimenting, but we thought we'd ask here as well.

I do have concurrent load on the table but it's read-only.

Answer 1

Assumptions

Filling in for missing information in the question:

• Input data comes from a file on the database server.

• File format matches COPY output, with a unique id per row to match the target table.
  If not, use COPY options to adapt to the format if possible, or else format the input properly.

• You are updating most or all rows in the target table.

• You can afford to drop and recreate the target table.
  That means concurrent access (if any) is blocked for the time. Else consider:

  • Best way to populate a new column in a large table?

• There are no depending objects, except for indices. (Else you have to drop and recreate.)

• You have plenty of free RAM and storage.

Solution

Go with a similar approach as outlined in the linked post. With major optimizations.

Simple & fast way to create the temporary table:

CREATE TEMP TABLE tbl_tmp AS SELECT * FROM tbl LIMIT 0;

A single big UPDATE from a (temporary) table inside the database is typically faster than single-row updates from outside the database by orders of magnitude.

In PostgreSQL's MVCC model, an UPDATE means to create a new row version and mark the old one as deleted. That's about as expensive as an INSERT and a DELETE combined (except for "toasted" values).
Plus, it creates a lot of dead tuples. When updating the whole table anyway, it's a lot faster to create a new table and drop the old one.

At the start of for the session, set temp_buffers high enough to hold the temp table in RAM. To get an estimate how is needed, run a test with a small sample and use db object size functions:

SELECT pg_size_pretty(pg_relation_size('tbl_tmp'));  -- complete size of table
SELECT pg_column_size(t) FROM tbl_tmp t LIMIT 10;  -- size of sample rows

Without concurrent access

This allows for the simplest & fastest route.

SET temp_buffers = '1GB';  -- example value

CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM tbl LIMIT 0;

COPY tbl_tmp FROM '/absolute/path/to/file.csv';

-- free up org. tbl name
ALTER TABLE tbl RENAME TO tbl_old;

CREATE TABLE tbl AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl_old t
JOIN   tbl_tmp u USING (id);

-- free up org. index & constraint names
DROP TABLE tbl_old;

-- create indexes and constraints like in original table
ALTER TABLE tbl ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl (...);
CREATE INDEX ... ON tbl (...);

-- optional; dropped at end of session automatically
DROP TABLE tbl_tmp;

With concurrent access

SET temp_buffers = '1GB';      -- example value

CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM tbl LIMIT 0;

COPY tbl_tmp FROM '/absolute/path/to/file.csv';

-- protect against concurrent writes - if applicable
-- reads are still allowed
LOCK TABLE tbl IN SHARE MODE;

CREATE TABLE tbl_new AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl     t
JOIN   tbl_tmp u USING (id);

-- Create indexes like in original table (with new names)
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

-- optionally rename indexes and constraints to now free org. names.

DROP TABLE tbl_tmp;

Concurrent, blocked operations on the table will wait, once the table is locked near the end and fail as soon as the transaction is committed, because the table name is resolved to its OID immediately, but the new table has a different OID. The table stays consistent, but concurrent operations may get an exception and have to be repeated. See:

• Best way to populate a new column in a large table?

UPDATE route

If you (have to) go the UPDATE route, drop any index that is not needed during the update and recreate it afterwards. It is much cheaper to create an index from scratch than to update it incrementally. Plus, removing unneeded indexes may allow for HOT updates.

Related:

• How to update selected rows with values from a CSV file in Postgres?

Answer 2

If the data can be made available in a structured file you could read it with a foreign data wrapper and perform a merge on the target table.