DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.it;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:Cc:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=2szk3luyCbO2WzbnyL3NbT4Bbd8Ms0p7A1xqkdpl2KsaolCkx1Z++FYsCen8dInXxfyWf4/ofjvKLzIxJddGOz5YAKSw6xhFr0QLZYODGL9uPaU9W9drJjktcGZjIzcliFAbpJQVL23OtIzEv4OuRZvTASsg3H39jYpu6hElyYM=;
Message-ID: <1322669853.10239.YahooMailNeo@web29003.mail.ird.yahoo.com>
Date: Wed, 30 Nov 2011 16:17:33 +0000 (GMT)
From: Leonardo Francalanci <m_lists@yahoo.it>
Reply-To: Leonardo Francalanci <m_lists@yahoo.it>
Subject: Re: Guidance Requested - Bulk Inserting + Queries
To: "benjamin.johnson@getcarbonblack.com"
 <benjamin.johnson@getcarbonblack.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

> We now found (thanks Andres and Snow-Man in #postgresql) that in our=0A> =
tests, after the indexes get too large performance drops signficantly=0A> a=
nd our system limps forward due to=A0 disk reads (presumably for the=0A> in=
dexes).=A0 If we remove the indexes, performance for our entire sample=0A> =
test is great and everything is written to postgresql very quickly.=A0 =0AI=
t's usually the fact that the data you index is "random" as opposed to, =0A=
say, an always incremented value (could be a timestamp, or a sequence) =0At=
hat leads to insert problems with btrees.=A0 =0A> My question is, what poss=
ible routes can I take where we can have both=0A> fast inserts (with indexe=
s removed until the end of the day), but still=0A> allow a user to query ag=
ainst today's data? Is this even possible?=A0 One=0A> idea would be possibl=
y have hourly tables for today and as soon as we=0A> can try to re-add inde=
xes.=A0 =0AYep, that's the only way I've found: use smaller partitions. Tha=
t leads =0Ato slower reads (due to the fact that you have to visit more ind=
exes to =0Aread the same amount of data). But you'll get faster writes.=A0 =
=0A=0A> Another possible solution might be to stream=0A> the data to anothe=
r "reader" postgres instance that has indexes,=0A> although I'm not very ve=
rsed in replication.=A0 =0AI don't think you can do that.=A0 =0AAnother opt=
ion that you have is to use ssd instead of HD for the indexes =0Aonly (that=
 is, having the indexes in a separate tablespace of ssds). The =0Aproblem i=
s that your disks usually can't keep up with the number of =0Arandom writes=
 it takes to update N "random values" btrees; ssd might help.=A0 =0ACan you=
 post some numbers, such as # of indexes, # of rows you're trying =0Ato ins=
ert per hour etc etc?=A0 =0A=0A