53

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.

3
  • Crucial information is missing in your question: Your version of Postgres? Where do the values come from? Sounds like a file outside the database, but please clarify. Do you have concurrent load on the target table? If yes, what exactly? Or can you afford to drop and recreate? No foreign keys, ok - but are there other depending objects like views? Please edit your question with the missing information. Don't squeeze it in a comment. Apr 28, 2013 at 13:42
  • @ErwinBrandstetter Thanks, updated my question.
    – Yang
    Apr 28, 2013 at 19:41
  • I assume you've checked via explain analyze that it's using an index for the lookup?
    – rogerdpack
    Nov 23, 2017 at 20:27

2 Answers 2

61

Assumptions

Since information is missing in the Q, I'll assume:

  • Your data comes from a file on the database server.
  • The data is formatted just like COPY output, with a unique id per row to match the the target table.
    If not, format it properly first or use COPY options to deal with the format.
  • You are updating every single row in the target table or most of them.
  • You can afford to drop and recreate the target table.
    That means no concurrent access. Else consider this related answer:
  • There are no depending objects at all, except for indices.

Solution

I suggest you go with a similar approach as outlined at the link from your third bullet. With major optimizations.

To create the temporary table, there is a simpler and faster way:

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

A single big UPDATE from a temporary table inside the database will be faster than individual updates from outside the database by several 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. Plus, it leaves you with a lot of dead tuples. Since you are updating the whole table anyway, it would be faster overall to just create a new table and drop the old one.

If you have enough RAM available, set temp_buffers (only for this session!) high enough to hold the temp table in RAM - before you do anything else.

To get an estimate how much RAM is needed, run a test with a small sample and use db object size functions:

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

Complete script

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

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

COPY tmp_tbl FROM '/absolute/path/to/file';

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

-- Create indexes like in original table
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 window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

Concurrent load

Concurrent operations on the table (which I ruled out in the assumptions at the start) 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. Details in this related answer:

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 in one piece than to update it for every individual row. This may also allow for HOT updates.

I outlined a similar procedure using UPDATE in this closely related answer on SO.

 

6
  • 1
    I'm actually just updating 20% of the rows in the target table - not all, but a big enough portion that a merge is probably better than random update seeks.
    – Yang
    Apr 28, 2013 at 19:38
  • 1
    @AryehLeibTaurog: That shouldn't be happening since DROP TABLE takes out an Access Exclusive Lock. Either way, I already listed the prerequisite at the top of my answer: You can afford to drop and recreate the target table. It might help to lock the table at the start of the transaction. I suggest you start a new question with all relevant details of your situation so we can get to the bottom of this. Jul 9, 2014 at 15:03
  • 1
    @ErwinBrandstetter Interesting. It seems to depend on the server version. I have reproduced the error on 8.4 and 9.1 using psycopg2 adaptor and using the psql client. On 9.3 there is no error. See my comments in the first script. I'm not sure if there's a question to post here, but it may be worth soliciting some information on one of the postgresql lists. Jul 10, 2014 at 5:56
  • 1
    I wrote a simple helper class in python to automate the process. Jul 14, 2014 at 13:41
  • 3
    Very useful answer. As a slightly variation, one may create the temporary table with only the columns to be updated and columns of reference, delete columns to be updated from the original table, then merge tables using CREATE TABLE tbl_new AS SELECT t.*, u.field1, u.field2 from tbl t NATURAL LEFT JOIN tmp_tbl u;, the LEFT JOIN allowing to keep rows for which there is no update. Of course the NATURAL can be changed to any valid USING() or ON. Sep 15, 2014 at 20:02
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.

4
  • 3
    What do you mean specifically by "merge on the target table"? Why is using FDW better than COPYing into a temp table (as suggested in the third bullet in the original question)?
    – Yang
    Apr 27, 2013 at 20:58
  • "Merge" as in the MERGE sql statement. Using FDW allows you to do that without the additional step of copying the data into a temporary table. I'm assuming that you're not replacing the entire data set, and that there would be a certain amount of data in the file that would not represent a change from the current data set -- if a significant amount has changed then a complete replacement of the table might be worthwhile. Apr 28, 2013 at 17:28
  • 1
    @DavidAldridge: While defined in the SQL:2003 standard, MERGE is not implemented in PostgreSQL (yet). Implementations in other RDBMS vary quite a bit. Consider the tag info for MERGE and UPSERT. May 1, 2013 at 17:37
  • @ErwinBrandstetter [glurk] Oh yes quite so. Well Merge is the icing on the cake really I suppose. Accessing the data without the import-to-temporary-table-step is really the crux of the FDW technique. May 1, 2013 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.