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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 Apr 1 '14 at 6:25

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

That unique index is going to be a pain point for you. Is it strictly required? – Craig Ringer Apr 1 '14 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 '14 at 10:38
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 '14 at 12:12
@CraigRinger No, the index is not strictly required, can manage without it. – Andy Stow Away Apr 2 '14 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 '14 at 3:36

Your Answer


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

Browse other questions tagged or ask your own question.