Date: Fri, 08 Jan 2010 12:19:38 +0100
To: "Carlo Stonebanks" <stonec.register@sympatico.ca>
From: Eduardo Morras <emorras@s21sec.com>
Subject: Re: Massive table (500M rows) update nightmare
Cc: pgsql-performance@postgresql.org
In-Reply-To: <hi41os$eiv$1@news.hub.org>
References: <hi41os$eiv$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <20100108112431.46277558B18@s21sec.com>

At 08:17 07/01/2010, Carlo Stonebanks wrote:
>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 >=3D 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

You can dump the table with pg_dumpand get a file like this (only a few=
 columns, not all of them). Note that the last line, has the data in TSV=
 (Tab Separate Format) plus a LF.

SET statement_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D off;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
SET escape_string_warning =3D off;

SET search_path =3D public, pg_catalog;

SET default_tablespace =3D '';

SET default_with_oids =3D false;

CREATE TABLE mdx_core.audit_impt
(
 audit_impt_id serial NOT NULL,
 impt_session integer,
 impt_version character varying(255),
}

COPY mdx_core.audit_impt (audit_impt_id, impt_session, impt_version) FROM=
 stdin;
1       1       tateti
2       32      coll

You can add a new column hacking it, just adding the new column to the=
 schema, the name in the copy statement and a tabulator+data at the end of=
 the line (before the LF). Note that the table name is different from the=
 original.

SET statement_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D off;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
SET escape_string_warning =3D off;
SET search_path =3D public, pg_catalog;
SET default_tablespace =3D '';
SET default_with_oids =3D false;
CREATE TABLE mdx_core.audit_impt2
(
 audit_impt_id serial NOT NULL,
 impt_session integer,
 impt_version character varying(255),
 source_table character varying(255)=20
}
COPY mdx_core.audit_impt2 (audit_impt_id, impt_session, impt_version,=
 source_table) FROM stdin;
1       1       tateti  tentown
1       32      coll    krof


After this, add indexes, constraints as usual.

HTH

--------------------------------
Eduardo Morr=E1s Gonz=E1lez
Dept. I+D+i e-Crime Vigilancia Digital
S21sec Labs
Tlf: +34 902 222 521
M=F3vil: +34 555 555 555=20
www.s21sec.com, blog.s21sec.com=20


Salvo que se indique lo contrario, esta informaci=F3n es CONFIDENCIAL y
contiene datos de car=E1cter personal que han de ser tratados conforme a la
legislaci=F3n vigente en materia de protecci=F3n de datos. Si usted no es
destinatario original de este mensaje, le comunicamos que no est=E1=
 autorizado
a revisar, reenviar, distribuir, copiar o imprimir la informaci=F3n en =E9l
contenida y le rogamos que proceda a borrarlo de sus sistemas.

Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO
informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen
datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta
honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula
bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko.
Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea.=20

Antes de imprimir este mensaje valora si verdaderamente es necesario. De
esta forma contribuimos a la preservaci=F3n del Medio Ambiente.=20