Re: Transaction table

On Sat, Mar 20, 2010 at 5:26 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Sat, Mar 20, 2010 at 2:47 AM, Deepa Thulasidasan
> <deepatulsidasan(at)yahoo(dot)co(dot)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 regular interval (seconds).
> > This transaction table consists of 12 columns, which are of the type
> varchar, time, numeric or double precision. A new transaction table is
> created every day. Total number of records at the end of the day is around 1
> million records. Data is only inserted in to this table and there is no
> update or delete. This table is indexed using 2 columns. Now, we are
> expecting this transaction table to grow by 10 times in near future. In this
> regard, we would like to know if this same structure of the transaction
> table and the indexing would be sufficient for quick retrivel of data or do
> we have to partition 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.
>

That'll help with reporting, how are you inserting the data now? Are you
using individual inserts, or are you loading in batches. Typically, if you
can buffer some of the inserts into a group on the application side and then
load them you'll be better off then just doing straight inserts for every
'event'. Then, you can combine that with partitioning to report over
multiple days pretty easily.

--Scott Mead

(Just realized that not only are there lots of Scott's on this list... we
have multiple Scott M's! :)

>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>