From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: Massive table (500M rows) update nightmare
Date: Thu, 7 Jan 2010 02:17:22 -0500
Message-ID: <hi41os$eiv$1@news.hub.org> (raw)
Our DB has an audit table which is 500M rows and growing. (FYI the objects
being audited are grouped semantically, not individual field values).
Recently we wanted to add a new feature and we altered the table to add a
new column. We are backfilling this varchar(255) column by writing a TCL
script to page through the rows (where every update is a UPDATE ... WHERE id
>= x AND id < x+10 and a commit is performed after every 1000 updates
statement, i.e. every 10000 rows.)
We have 10 columns, six of which are indexed. Rough calculations suggest
that this will take two to three weeks to complete on an 8-core CPU with
more than enough memory.
As a ballpark estimate - is this sort of performance for an 500M updates
what one would expect of PG given the table structure (detailed below) or
should I dig deeper to look for performance issues?
As always, thanks!
Carlo
Table/index structure:
CREATE TABLE mdx_core.audit_impt
(
audit_impt_id serial NOT NULL,
impt_session integer,
impt_version character varying(255),
impt_name character varying(255),
impt_id integer,
target_table character varying(255),
target_id integer,
target_op character varying(10),
note text,
source_table character varying(255),
CONSTRAINT audit_impt_pkey PRIMARY KEY (audit_impt_id)
)
CREATE INDEX audit_impt_impt_id_idx
ON mdx_core.audit_impt
USING btree
(impt_id);
CREATE INDEX audit_impt_impt_name
ON mdx_core.audit_impt
USING btree
(impt_name, impt_version);
CREATE INDEX audit_impt_session_idx
ON mdx_core.audit_impt
USING btree
(impt_session);
CREATE INDEX audit_impt_source_table
ON mdx_core.audit_impt
USING btree
(source_table);
CREATE INDEX audit_impt_target_id_idx
ON mdx_core.audit_impt
USING btree
(target_id, audit_impt_id);
CREATE INDEX audit_impt_target_table_idx
ON mdx_core.audit_impt
USING btree
(target_table, target_id, audit_impt_id);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca
Subject: Re: Massive table (500M rows) update nightmare
In-Reply-To: <hi41os$eiv$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox