CODEWITHSUNDEEP
sqlpostgresqldatabase-design
WuBe
WuBe

Reputation: 41

PostgresSQL - how to design an effective table

I need to collect data from tens of sensors, with are saving data in 15s interval, in postgres database and want that select with values from last month/week/day were run fast - to draw charts. What indexes, partitions or other things I should use?

CREATE TABLE temperatures(
  sensor_id integer NOT NULL,
  val real NOT NULL,
  audit_date timestamp with time zone NOT NULL DEFAULT now()
)

Upvotes: 0

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

For each sensor, you are collecting 5,760 rows per day. That can start to add up.

First, I would suggest partitioning the data. The right way to partition the data depends on how you really intend to use it. You mention returning the most recent data, so that definitely suggests using time. You might also want to include the sensor_id as a partitioning key, but that is less likely. You can start learning about partitioning in the documentation.

Next you want indexes. Presumably, you want to fetch values by sensor. That suggests an index on (sensor_id, audit_date). However, the index might need to be tweaked to be compatible with the partitioning scheme.

Upvotes: 2

WuBe
WuBe

Reputation: 41

select 

select 
    s.sensor_id,
    audit_date::timestamp(0) as audit_date,
    t.val,
    s.comment
from 
    temperatures t, 
    sensors s
where 
    t.audit_date >= timestamp '$date1'
    and t.audit_date <= timestamp '$date2'
    and t.sensor_id in (select sensor_id from users_sensors us, users u where us.user_id = u.user_id and u.login = '$login')
    and t.sensor_id = s.sensor_id
order by 2

Upvotes: 0

Related Questions