PostgreSQL+ Traccar
I know a lot of people are using PostgreSQL with Traccar, but from our testing MySQL works better. I don't know if it's due to more efficient Java driver or something else, but it can handle 5x more data comparing to PostgreSQL. We did the test a few years back, so it might have improved since. If someone has more up to date info, please share.
Works fine for me. This is a longish answer, as for DBs, one solution / size doesn't fit all.
We are running PG13 at the moment, there have been some performance improvements. But Postgres is also susceptible to tuning, maybe far more so than MySQL, hence maybe some of the performance differentials observed.
We use PG for other stuff than Traccar that MySQL just can't do, such as heavy lifting of geospatial data, functions and queries, as well as a fair bit of JSON data, in our own system, of which Traccar is one of the feed systems, so we are really stuck with using PG.
My take on MySQL is that it works great for standardized ORM-style queries which is what it was originally designed for, a relatively light-weight DB for object-oriented developers who couldn't be bothered with back-end database intricacies.
If you have specialized / more complex DB requirements such as complex geospatial or time-series functionality, then something like PG is going to work much better as it has a well-proven and extensive set of add-on modules such as PostGIS.
Also if you have functionality that requires custom DB functions, PG is well ahead here, with it's own well tested procedural language, as well as allowing these functions and procedures to be written in C, Python, and many more.
We did some testing for geospatial data against MS SQL Server some years back, and PG was far easier to set up, and several times faster, as well as having a far richer set of functionality, that is widely accepted in the geo community. MySQL geospatial functionality (and procedural capabilities) are still in their infancy, and relatively unused. We didn't bother to test as the functionality wasn't there.
We could have used Oracle, but the cost is just way too much.
As for reporting we have run custom reports such as stops / trips against our custom DB using geospatial functions and a well tuned Postgres function that we have written, and it is significantly faster than the Traccar equivalent for non-trivial mounts of data.
Other stuff like working out if a particular position is within a set distance of any other position for a vehicle using PostGIS functions is also way faster than trying to do it from using standard SQL through an ORM.
if your only requirement for a DB is basic ORM support the way Traccar uses it, then MySQL should work fine. If you have other requirements and the ability to tune and manage the requirements, Postgres is well worth looking at, and very hard to beat.
Our server is oversized for our needs, as we got it cheap. Also it supports systems other than Traccar, and we run all reporting and many of the queries through our own system that Traccar feeds, rather than in Traccar itself, so it's not a good benchmark to use.
Thanks for the feedback. Let me commission both PostgreSQL and MySQL and monitor performance then choose the most appropriate/ better performance.
@Maurice Oduor, did you perform the performance testing. Please let us know what is the result you got
Thank you.
Anyone using PostgreSQL and Traccar, how's the performance and what's your server specs and number of devices?
Thanks.