Re: Transaction table

On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan
<deepatulsidasan@yahoo.co.in> wrote:
> Dear All,
>
> I have a query in postgresql if any one can support.
>
> A transaction table in a vehicle tracking application is  inserted with the current position of each vehicle at
regularinterval (seconds). 
> This  transaction table consists of 12 columns, which are of the type varchar, time, numeric or double precision. A
newtransaction table is created every day. Total number of records at the end of the day is around 1 million records.
Datais only inserted in to this table and there is no update or delete. This table is indexed using 2 columns. Now, we
areexpecting this transaction table to grow by 10 times in near future. In this regard, we would like to know if this
samestructure of the transaction table and the indexing would be sufficient for quick retrivel of data  or do we have
topartition this table? If so what kind of partition would be suitable? 

You generally wanna partition on the one (or maybe two) fields you'll
regularly use to restrict your data set.  For many systems like this
that is a partition on date.  Sometimes you can partition on two
things, and if it makes sense to do so your current usage patterns
will show it.  Normally one axis of partitioning is fine.