How to tune my Postgres server?

Question

I have a 4 GB server with 80GB hard disk. The website is really slow when switching between pages. I checked top processes and postgres processes with 70 -100 % cpu usage keep popping up. they dont last for more than 4 seconds usually and then its a new one. In my postgres database table i have about 12 tables but one of them has about 90 million entries( rows). This is the table causing the slowdown. But how do i tune my postgres parameters like shared_buffer size and all for optimum performance?

It runs on Ubuntu 16 OS, its a django webapp.

my database name is roctim it has size 15 gigabites (as of now, but is increasing)

postgres=# select datname, pg_size_pretty(pg_database_size(oid)) as db_size from pg_database;

  datname  | db_size
-----------+---------
 template1 | 6857 kB
 template0 | 6857 kB
 postgres  | 6992 kB
 roctim    | 15 GB

the size of my biggest table is 15 GB called "Webapp_sensordata". It collects and stores livedata from machines like crushers, conveyors etc.

When I run select query from pg_stat_statements order by total_time desc;, I get

SELECT "Webapp_sensordata"."id", "Webapp_sensordata"."timestamp",
       "Webapp_sensordata"."value", "Webapp_sensordata"."machine_id",
       "Webapp_sensordata"."type_id"
FROM "Webapp_sensordata"
WHERE "Webapp_sensordata"."machine_id" = ?
ORDER BY "Webapp_sensordata"."timestamp" DESC
LIMIT ?

and

SELECT "Webapp_sensordata"."id", "Webapp_sensordata"."timestamp",
       "Webapp_sensordata"."value", "Webapp_sensordata"."machine_id",
       "Webapp_sensordata"."type_id"
FROM "Webapp_sensordata"
WHERE ("Webapp_sensordata"."type_id" = ?
  AND "Webapp_sensordata"."machine_id" = ?)
ORDER BY "Webapp_sensordata"."timestamp" DESC
LIMIT ?

as the top two queries, they both access the big table sensor data.

Also my shared_buffer and working_mem is set to the default right now (128mb and 4mb)

When I ran EXPLAIN ANALYZE the second query shown above, i got

QUERY PLAN
----------------------------------------------------------------------------- 
----------------------------------------------------------------------------- 
----------------------
Sort  (cost=62127.86..62135.64 rows=3111 width=24) (actual 
time=781.051..781.230 rows=2860 loops=1)
Sort Key: "timestamp" DESC
Sort Method: quicksort  Memory: 320kB
->  Index Scan using "Webapp_sensordata_machine_id_e353fc5a" on 
"Webapp_sensordata"  (cost=0.57..61947.37 rows=3111 width=24) (actual 
time=4.190..779.783 rows=2860 loops=1)
     Index Cond: (machine_id = 3)
     Filter: (type_id = 1)
     Rows Removed by Filter: 31440
Planning time: 4.572 ms
Execution time: 781.449 ms
(9 rows)

Answer 1

Your query would be faster if both conditions and the sort can be handled during the index scan by a multi-column index:

CREATE INDEX ON "Webapp_sensordata" (type_id, machine_id, timestamp);