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? – Jack says try topanswers.xyz May 19 '15 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 '15 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 '15 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 '15 at 8:14
  • what's you covering index? How many rows are you expecting per id? – Jack says try topanswers.xyz May 20 '15 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.