Re: PostgreSQL versus MySQL for GPS Data

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.

> The question is: Which DBMS do you think is the best for this kind of
> application? PostgreSQL or MySQL?

As you can imagine, PostgreSQL.

My main reasons are that in a proper transactional environment (ie
you're not using scary MyISAM tables) Pg is *much* better about handling
concurrent load, particularly concurrent activity by readers and writers.

Pg's table partitioning support is also an ideal fit for your application.

--
Craig Ringe