From: | Tino Wildenhain <tino(at)wildenhain(dot)de> |
---|---|
To: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Is It Good Practice That I use TableName-Month-Year Convention |
Date: | 2010-01-13 09:29:05 |
Message-ID: | 4B4D9261.7080502@wildenhain.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Am 13.01.2010 09:16, schrieb Yan Cheng Cheok:
> I realize the READ performance goes down dramatically when my table goes large. Every new day goes on, my table can increase x millions of new rows.
>
> I was wondering whether this is good practice I can design my database in this way?
>
> Instead of having
>
> lot<-> unit<-> measurement
>
> Can I have
>
> lot-March-2010<-> unit-March-2010<-> measurement-March-2010
> lot-April-2010<-> unit-April-2010<-> measurement-April-2010
>
> (1) That's mean in my stored procedure, I need to dynamically generate the table name. Is this the "dynamic SQL" to correct way, to dynamically generate table name : http://www.postgresql.org/docs/8.1/interactive/ecpg-dynamic.html
>
> (2) Is this consider a good approach, to overcome speed problem (especially read speed). Any potential problem I should put an eye on, before I implement this strategy?
You might combine this approach with table partitioning to give you a
cleaner view to your data like this:
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
in your situation it would probably make sense to put the actual
partitiones into a separate schema to keep your main work area clean
from clutter.
HTH
Tino
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Schuchardt | 2010-01-13 10:15:54 | postgresql 8.1 windows 2008 64 bit |
Previous Message | Yan Cheng Cheok | 2010-01-13 08:47:44 | Re: Extremely Slow Cascade Delete Operation |