Regarding database optimization
Miraj,
Have you checked whether there are active indexes on the tables for the queries being run, and whether the DB optimizer is using those indexes in the queries?
How many millions of records and how many are you trying to extract?
@Tony Shelver I don't have any idea about that so I would be happy if you'll elaborate it more.
@Anton Tananaev We have around 20 millions of records and we just need roughly 3 or 4 columns from positions table and events table for some data processing and that thing is taking time as we are fetching all the records from database.
If you're requesting all 20 million, it will be slow. There's no way around it. If you only need specific columns, try adding indexes for those columns. That might improve things a bit.
Given what you have described to Anton above, his suggestion is worth exploring, the standard indexes won't help you.
Are these reports you are running customer reports? We have several custom reports which we run against our own DB which is synched from Traccar and another proprietary tracking system. There we do a lot of analysis of chunks of data (limited by time periods and groups of vehicles) in Postgresql functions and using PostGIS functions / datatypes for geospatial purposes) and returning the relevant / summarized data to the reporting programs.
What about preloading the data into a caching / in memory database? We use Redis for caching / state management / process control for our Telegram vehicle tracking bot (messaging, vehicle status, and report extracts).
Thanks @Anton and @Tony
I will try to implement your suggestions.
Hello,
I have some questions regarding database optimization.
1)We have millions of records in tc_positions and tc_events tables and we are using postgresql-14 as database so how should we manage this huge database so that it will fetch the results very fast because as of now it is taking too much time to fetch the data from these two tables and so reports are taking too much time to be generated.
Is there any other solution rather than deleting records from the database?
or we should increase the resources as we are running traccar on ec2 instance and postgres is in rds and we are having 2gb ram for server and rds.