Re: PostgreSQL versus MySQL for GPS Data

On Tue, Mar 17, 2009 at 7:47 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Juan Pereira wrote:
>
>
>> - The database also should create a table for every truck -around 100
>> trucks-.
>
> Why?
>
> That's a rather clumsy design that makes it really hard to get aggregate
> data across the fleet or do many interesting queries.
>
> You're almost always better off using a single table with a composite
> primary key like (truckid, datapointid) or whatever. If you'll be doing
> lots of queries that focus on individual vehicles and expect performance
> issues then you could partition the table by truckid, so you actually do
> land up with one table per truck, but transparently accessible via table
> inheritance so you can still query them all together.
>
> Read up on PostgreSQL's table partitioning features.

If there is little/no reason to span queries over various trucks, then
the OP's approach is ok, better than standard TP even.  I agree though
that a single table approach is best unless 1) the table has to scale
to really, really large sizes or 2) there is a lot of churn on the
data (lots of bulk inserts and deletes).

merlin