Обсуждение: [table partitioning] How many partitions are possibel?

Hello *,

Long time ago I used VIEWs on my history database and when PostgreSQL
introduced table partitioning I was switching to it.

Currently I use Debian GNU/Linux 7.11 with PostgreSQL 9.1 (table space
and table partitioning) using an Adaptec 16-Channel Raid-1 Controller
with 16x 6 TByte SAS UltraStar (HGST) drives.

I created partitions of 100 years range and have currently 132 of them.

There are 8 base columns (1 data colum in english) and then for each
language used an additional colum. Some of the data where translated
into 27 languages but I have 56 non-english columns.

So in total I have currently 64 columns and arround 156mio rows.

Th etables are organised into

    history_earth
    history_earth_021_a
    history_earth_020_a
    history_earth_019_a
    ...
    history_earth_124_b
    ...

"a" mean A.C. and "b" B.C.

While checking my Root-Servers I discovered, that my PostgreSQL Box has
a huge performance problem, because some table partitions became VERY
large (3 table partitions exceed already 1 TByte and 14 are 500-1000GB).

I like to make the partitions smaller, but the documentation say, you
should not dare to make several 1000 partitions..

Question:

What is with is today possibel with table partitioning?

And there is another thing:

The 16 "new" SAS Drives where sponsored, hence free for me including the
new Raid-1 Controller, but I do not like the idea, to have a singel
physical database of arround 40 TByte diskspace...

I can get much less expensive (not cheaper) SAS drives of 1 or 2 TByte
and since the Raid-1 Controller cost only 400€, I can use several LOW
POWER machines (Mini-ITX) to accomplish the task because the webinterface
is anyway stored on a seperated server.

Any suggestions?


-- 
Michelle Konzack
00372-54541400

On Tue, Dec 26, 2017 at 11:04:55AM +0100, Michelle Konzack wrote:
> I like to make the partitions smaller, but the documentation say, you
> should not dare to make several 1000 partitions..

Even more than a hundred may be already risky in my opinion here. The
issue with a large number of partitioning using the pre-9.6 grammar
comes from the planning time which sky-rockets because the optimizer
uses a O(N^2) algorithm to consider all the partitions. The v10 grammar
does not take care of this planning problem as far as I recall, but v11
gets that better for partition-wise joins if my memory does not fail me.
--
Michael

Hi,

Am 2017-12-26 hackte Michael Paquier in die Tasten:
------------------------------------------------------------------------
> On Tue, Dec 26, 2017 at 11:04:55AM +0100, Michelle Konzack wrote:
>> I like to make the partitions smaller, but the documentation say, you
>> should not dare to make several 1000 partitions..
>
> Even more than a hundred may be already risky in my opinion here. The
> issue with a large number of partitioning using the pre-9.6 grammar
> comes from the planning time which sky-rockets because the optimizer
> uses a O(N^2) algorithm to consider all the partitions. The v10 grammar
> does not take care of this planning problem as far as I recall, but v11
> gets that better for partition-wise joins if my memory does not fail me.
> --
> Michael

I think on migrating to v10, but I have to backport the PostgreSQL.

Currently I am thinking about hot to backup 28 TByte of data...
Have to dump and compress table by table and thios take endless time,
especially, if I am in Estonia and the database is in Germany.

Is there already a rease date for v11?

Thanks in avance

-- 
Michelle Konzack
00372-54541400

On Wed, Dec 27, 2017 at 08:53:11AM +0100, Michelle Konzack wrote:
> Is there already a release date for v11?

Based on the pace of the most recent major releases, this could happen
around September. This depends on any issues encountered
post-development though.
--
Michael

Thanks for the info.

I think, it is not worth to do the migration to v10 now
and then some month later to v11.

I hope, my v9.1 will survive the version jump.

What do you think?

My database has no complex things in it. I keept it realy
on basic level to avoid problems if I have to upgrade.

Thanks in avance



Am 2017-12-27 hackte Michael Paquier in die Tasten:
> On Wed, Dec 27, 2017 at 08:53:11AM +0100, Michelle Konzack wrote:
>> Is there already a release date for v11?
>
> Based on the pace of the most recent major releases, this could happen
> around September. This depends on any issues encountered
> post-development though.
> --
> Michael

-- 
Michelle Konzack
00372-54541400