I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that adds data every second. Each row is uniquely indexed by a serial type.
My requirements are:
The latest available data (~300 s of data) on the table is required to be displayed on a GUI monitor tool.
Also a cleanup program needs to remove data that is older than 3 days from the table and write it to a file.
When I run this setup for over a day, I notice my query delays are increasing with time (upto 30 ms on insert and upto 1.5 s on fetch ). How do I keep my delays constant and below 1 s in both cases ). I am interested in a solution that is scalable
I am currently using PostgreSQL database. The insert and cleanup program are written in C using the ODBC driver and the GUI toolkit is web based with PHP/Apache as the back-end.