Re: Should I partition this table? - Mailing list pgsql-general

On Thu, 10 Jul 2014 08:40:59 -0700 (PDT) AlexK <alkuzo@gmail.com> wrote:

> Bill,
>
> Regarding "SELECT performance improve nearly linerally to the number of
> partitions," - can you elaborate why? If I split my table into several
> partitions, even the index depth may stay the same, because the PK is
> narrow, it only consists of 2 4-byte integers.

That statement is based on experimentation.  About a year ago, I did a
research project for a former employer to determine the best way to
store a large amount of data.  Like you, we were talking about narrow
rows, but we were looking at the total # of rows exceeding 10 billion.
Also different, we didn't expect the number of unique "ParentID"s to
ever exceed 100,000.

I managed to borrow some beefy hardware from another project that
wouldn't need it for a few weeks and do some experimenting with
different partition configurations, all compared to a baseline of
an unpartitioned table.  I don't remember exactly, but I believe I
was populating the test databases with 1 billion rows.

That's where I came up with the "linear" determination.  A query that
averaged 100ms on a single table averaged 10ms on 10 partitions and
1ms on 100 partitions (etc).  Every test I concocted seemed to support
that the improvement was linear.

Another point that I expermimented with, and is worth noting: not all
queries are able to benefit from the partitioning, but the test queries
that I ran that could not, saw only a few percent of performance hit.
Since our app design would take advantage of the partitioning for 99%
of its queries, it was a no-brainer.

As a result, I can only theorize, but my theory is that since each
partition is actually a table, and has indexes independently of the
other table/partitions, that both indexes searches and table scans
required less work with smaller tables.  That theory doesn't 100%
make sense, since index performance should not degrade linerally with
index size, but that was the behavior I observed.  It's likely that
because of the significant size of the hardware, that the performance
curve was simply so close to flat that it looked linear at the sizes
I was working with.

> At this time we would rather not introduce bugs related to cache
> invalidation. Although we do need to read data fast, we do not want stale
> data, and we cannot afford any bugs in this application.

I can't make your decisions for you.  But I'll make an attempt here
to try to help you avoid the same mistake I made.

On that same project, we made a similar decision: caches are sources
of bugs so we'll just make sure the DB is tuned well enough that we
don't need a cache.

That decision led to a lot of late night work under duress to get
caching reliably implemented at the last minute.  There are a lot of
things that partitioning doesn't speed up: parsing and planning the
queries, the time it takes the network to move data back and forth,
and the time it takes the application to demarshall the results
provided by the SQL server.  Caching data in RAM completely removes
all of that overhead.

Of course, only you and your developers can make the determination as
to whether you need it.  I'm just recommending that you don't fail
to consider it simply because it's difficult to do reliably.  If
you're use the "can't risk bugs" argument, you might benefit more
by improving your QA process than anything else.

--
Bill Moran <wmoran@potentialtech.com>