Thread: Very Large Table Partitioning

Hi guys.

here is our problem:

We have a table that if we want to save all user's records in it, we
have a very large table. maybe 10TB+
so we are deciding to use table partitioning. But again we have problem
here:

if we decide to partition table per user we have lots of tables (maybe
more than 100000+) with only 10000 records each.
is this a good idea? is there any limit for number of tables?

The table structure is not in a way that we can partition in a better
way. is this a good idea to add a column like "date inserted" and
partition per year for example?

Majid Azimi <majid.merkava@gmail.com> writes:
> if we decide to partition table per user we have lots of tables (maybe
> more than 100000+) with only 10000 records each.
> is this a good idea? is there any limit for number of tables?

No, it's a fantastically bad idea.  Please note the caveats in the
partitioning documentation --- the facility is not meant for more than
order-of-a-hundred partitions.  Even if Postgres didn't have issues with
it, your filesystem might get ill with hundreds of thousands of files in
one directory.

            regards, tom lane

Tom Lane wrote:
> Majid Azimi <majid.merkava@gmail.com> writes:
>
>> if we decide to partition table per user we have lots of tables (maybe
>> more than 100000+) with only 10000 records each.
>> is this a good idea? is there any limit for number of tables?
>>
>
> No, it's a fantastically bad idea.  Please note the caveats in the
> partitioning documentation --- the facility is not meant for more than
> order-of-a-hundred partitions.  Even if Postgres didn't have issues with
> it, your filesystem might get ill with hundreds of thousands of files in
> one directory.
>
>             regards, tom lane
>
>
Tom, at one time you mentioned "getting the proper partitioning". Any
inklings on what was meant by that and if there was any progress on that?

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-
> owner@postgresql.org] On Behalf Of Majid Azimi
> Sent: Friday, December 17, 2010 11:59 AM
> To: PostgreSQL - Novice
> Subject: [NOVICE] Very Large Table Partitioning
>
> Hi guys.
>
> here is our problem:
>
> We have a table that if we want to save all user's records in it, we
> have a very large table. maybe 10TB+
> so we are deciding to use table partitioning. But again we have problem
> here:
>
> if we decide to partition table per user we have lots of tables (maybe
> more than 100000+) with only 10000 records each.
> is this a good idea? is there any limit for number of tables?


As tom (the authority on it) said it would be bad to have 100s or more of
partitions. You might base the partitions on a range of users . We have had
mixed success doing this. Sometimes it works well, other times we found it
didn't work so great and had to rework it all to work a different way.

>
> The table structure is not in a way that we can partition in a better
> way. is this a good idea to add a column like "date inserted" and
> partition per year for example?


Do you roll off data ever? Table partitioning right now can be very useful
when you need to remove large amounts of data, since it's much better to
drop an entire child partition than doing a delete from XXXX where YYYY due
to the maintenance costs with a large delete like that. But if you don't
already have a date field then I suspect this might not be your use case.

do you do updates to existing data ?

do you have any low cardinality columns now ? sometimes these lead to what
to partition on. I know you said that the table structure is not in a way
that lends its self to partitioning, but maybe there is a pattern that you
haven't considered that could lend it self to partitioning. It would
typically be something that the majority of queries have in their filter
clauses.

-Mark