Thread: Is It Good Practice That I use TableName-Month-Year Convention

I realize the READ performance goes down dramatically when my table goes large. Every new day goes on, my table can
increasex 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
correctway, 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
putan eye on, before I implement this strategy? 

Thanks and Regards
Yan Cheng CHEOK

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
increasex 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
correctway, 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
shouldput 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