Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 1 at 6:25

This question came from our site for professional and enthusiast programmers.

1  
That unique index is going to be a pain point for you. Is it strictly required? –  Craig Ringer Apr 1 at 6:26
    
Presumably, you have a time column. Have you indexed it? And showing the exact queries and execution plans would help... –  Branko Dimitrijevic Apr 1 at 10:38
1  
Unless the data is accessed in ways you are not telling us, then I don’t know if a database is the best solution. I am thinking of a message queue that is forked, with one output going to the GUI and anther going to a process that write the data to files. The GUI can keep the last 300 secs in memory. –  Ian Ringrose Apr 1 at 12:12
    
@CraigRinger No, the index is not strictly required, can manage without it. –  Andy Stow Away Apr 2 at 3:33
    
@BrankoDimitrijevic There is a time column but its not the default timestamp type. It is stored as 'Modified Julian Days' and is of type double –  Andy Stow Away Apr 2 at 3:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.