From: Leonardo Francalanci <m_lists@yahoo.it>
To: benjamin.johnson@getcarbonblack.com <benjamin.johnson@getcarbonblack.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Guidance Requested - Bulk Inserting + Queries
Date: Wed, 30 Nov 2011 16:17:33 +0000 (GMT)
Message-ID: <1322669853.10239.YahooMailNeo@web29003.mail.ird.yahoo.com> (raw)
> We now found (thanks Andres and Snow-Man in #postgresql) that in our
> tests, after the indexes get too large performance drops signficantly
> and our system limps forward due to disk reads (presumably for the
> indexes). If we remove the indexes, performance for our entire sample
> test is great and everything is written to postgresql very quickly.
It's usually the fact that the data you index is "random" as opposed to,
say, an always incremented value (could be a timestamp, or a sequence)
that leads to insert problems with btrees.
> My question is, what possible routes can I take where we can have both
> fast inserts (with indexes removed until the end of the day), but still
> allow a user to query against today's data? Is this even possible? One
> idea would be possibly have hourly tables for today and as soon as we
> can try to re-add indexes.
Yep, that's the only way I've found: use smaller partitions. That leads
to slower reads (due to the fact that you have to visit more indexes to
read the same amount of data). But you'll get faster writes.
> Another possible solution might be to stream
> the data to another "reader" postgres instance that has indexes,
> although I'm not very versed in replication.
I don't think you can do that.
Another option that you have is to use ssd instead of HD for the indexes
only (that is, having the indexes in a separate tablespace of ssds). The
problem is that your disks usually can't keep up with the number of
random writes it takes to update N "random values" btrees; ssd might help.
Can you post some numbers, such as # of indexes, # of rows you're trying
to insert per hour etc etc?
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: m_lists@yahoo.it, benjamin.johnson@getcarbonblack.com
Subject: Re: Guidance Requested - Bulk Inserting + Queries
In-Reply-To: <1322669853.10239.YahooMailNeo@web29003.mail.ird.yahoo.com>
* 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