Alright thanks all of you for your answers, but i've got 3 more questions :
- Why "... partitionning is not a good idea ..." like you said Robert and Conor "... I grant that it would be better to never need to do that" ?
- Is there another way or strategy to deal with very large tables (over 100 000 000 rows per year in one table) beyond indexing and partitionning?
- If you had to quantify a limit of numbers of rows per table in a single postgresql database server what would you say?
PS: i'm using postgresql since less than 2 month because i thought that partitioning was a possible solution that doesn't offer me Apache Derby for my large table problem so if these questions sounds "dummy" for you this is a postgresql novice talking to you.
Regards
Le 14/03/2011 20:40, Robert Haas a écrit :
On Mon, Mar 14, 2011 at 12:42 PM, Samba GUEYE <samba.gueye@intesens.com> wrote:
Yeah but is there a workaround to force the root table to propagate the
foreign key to the partitionned table
because right now all foreign keys to partitionned table throws constraints
violation and it's a big problem for me
No. Generally, table partitioning is not a good idea unless you are
dealing with really large tables, and nearly all of your queries apply
only to a single partition. Most likely you are better off not using
table inheritance in the first place if you need this feature.
It would be nice if we had a way to do this for the rare cases where
it would be useful, but we don't.