I am designing a database which will have many inserts and selects, but no updates.

Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will occur?

There is one simple table, of int (id),timestamp,real, there will be about 100 inserts per second.

Edit: timestamp is not the insertion time, the int is a random short number. Most of the queries are selection of data between two dates (1 minute - 1 week), for a group of id (up to 10).

  • 1
    crucial information missing: what kind of queries will you be doing (eg "give me all rows for a particular int or for a particular timestamp range). Should we assume the timestamp is the insert time and the int is randomly distributed? May 19, 2015 at 20:33
  • Thanks, added more details, I assume no read locks are needed as the historical data never get updated, I wonder if there is a way to tell Postgres the table data is not updated.
    – Ofiris
    May 20, 2015 at 5:48
  • How often will this thing being queried? Should the queries be really real-time, or is a (possibly very small) delay acceptable?
    – dezso
    May 20, 2015 at 8:12
  • A delay of 1 minutes is acceptable, this thing is queries for small data sets (last 10 minutes of data per id) every few minutes. Once per few hours, a larger queries might happen, but the performance is less important. I am having a covering index which decreased the query time significantly.
    – Ofiris
    May 20, 2015 at 8:14
  • what's you covering index? How many rows are you expecting per id? May 20, 2015 at 8:53


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.