DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Z046F4/eCZ4jSf24doFP0oQi/E4l+m85XW9Fg3CBW1bfdMmxbRjZLxIxboCOjba4RXSHRUPp2s0XaK8tKp9Eg3yDOma5pxlet1NDhgqcC/3e+W28FYGMaEXN4stSav+gnfPBWazKzEcsv3uLbXSwc50cVW2r4BO0WgUO8Ysaqtk=;
References: 
 <CAFcOn2_W6v_vqwomCf6DEtXk=N8iB5WU-7XEdcYgX-VFUE32+Q@mail.gmail.com>
	<k223s0$e83$1@ger.gmane.org>
	<CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
Message-ID: <1349138158.88943.YahooMailNeo@web125905.mail.ne1.yahoo.com>
Date: Mon, 1 Oct 2012 17:35:58 -0700 (PDT)
From: Greg Williamson <gwilliamson39@yahoo.com>
Reply-To: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: Inserts in 'big' table slowing down the database
To: Stefan Keller <sfkeller@gmail.com>, Ivan Voras <ivoras@freebsd.org>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAFcOn2_zpBQ1Tky_vHOkBGMLwjHJFdNSUgyZhkg-i3b8-WWMLA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Stefan --=0A=0A=0A----- Original Message -----=0A> From: Stefan Keller <sfk=
eller@gmail.com>=0A> To: Ivan Voras <ivoras@freebsd.org>=0A> Cc: pgsql-perf=
ormance@postgresql.org=0A> Sent: Monday, October 1, 2012 5:15 PM=0A> Subjec=
t: Re: [PERFORM] Inserts in 'big' table slowing down the database=0A> =0A> =
Sorry for the delay. I had to sort out the problem (among other things).=0A=
> =0A> It's mainly about swapping.=0A> =0A> The table nodes contains about =
2^31 entries and occupies about 80GB on=0A> disk space plus index.=0A> If o=
ne would store the geom values in a big array (where id is the=0A> array in=
dex) it would only make up about 16GB, which means that the=0A> ids are den=
se (with few deletes).=0A> Then updates come in every hour as bulk insert s=
tatements with entries=0A> having ids in sorted manner.=0A> Now PG becomes =
slower and slower!=0A> CLUSTER could help - but obviously this operation ne=
eds a table lock.=0A> And if this operation takes longer than an hour, it d=
elays the next=0A> update.=0A> =0A> Any ideas? Partitioning?=0A=0A=0Apg_reo=
rg if you have the space might be useful in doing a cluster-like action:=0A=
=A0<http://reorg.projects.postgresql.org/>=0A=0AHaven't followed the thread=
 so I hope this isn't redundant.=0A=0APartitioning might work if you can cr=
eate clusters that are bigger than 1 hour -- too many partitions doesn't he=
lp.=0A=0AGreg Williamson=0A