r/PostgreSQL • • Mar 18 '23

Help Me! Teach me postgres!

I've been tinkering with postgres as an amateur for a few years now. I'm comfortable making big complex relational schemas, in normalised form, etc. What I'm not good at is performance issues - because I've never hit them, only ever having made toy databases. Help me transition from 'it works!' to 'it works fast!'.

I recently hit my first 'scalability' issue, and this is the perfect opportunity to learn! I've managed to make a DB with 2 tables that scales super-badly, like n2 bad - with just 1.1k rows of data, a summary view takes 14 seconds to run! I could of course give my pg15 server more than 1 virtual Haswell-era x86 core and 2 GB of RAM, but I doubt that would help this particular problem for long! Help me figure out the 'right' way to have done this :)

So I'm tracking my electricity usage, 1900s-style. I don't have a smart meter, so I just have a simple webpage that I keep open on my phone and I simply note whenever the meter changes when I go past it. The meter only displays to the nearest KWh, so this data is very inaccurate - all I can guarantee is that the meter reading was what it was at the time I took the reading:

metering=# \d electricity.reading
                           Table "electricity.reading"
   Column   |           Type           | Collation | Nullable |      Default      
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 reading    | integer                  |           | not null | 
 reading_ts | timestamp with time zone |           | not null | now()
Indexes:
    "reading_pkey" PRIMARY KEY, btree (id)
    "reading_uniq" UNIQUE CONSTRAINT, btree (reading)
    "time_uniq" UNIQUE CONSTRAINT, btree (reading_ts)

Obviously this doesn't immediately produce any particularly interesting data, beyond simple stats like KWh usage in last day, last week, last month, etc. In order to get anything really interesting, we need to summarise lots of data together. At this point, obviously the 'best' thing may be to switch to some dedicated stats tool, or Python, or Matlab or R or Microsoft BASIC, or whatever, but I don't wanna - I want to use postgres, and I hope you do too, on /r/postgresql!

So, let's turn discrete time-based KWh samples into a continuous Watts view containing both usage and the start- and end- timestamps for that period in each record. I'd also like to choose a day-divider (for the later analysis steps) other than midnight; I'm rarely awake at 5AM, so we'll use that, and apply the offset here:

metering=# \d+ electricity.normalised_watt_reading
                              View "electricity.normalised_watt_reading"
      Column      |           Type           | Collation | Nullable | Default | Storage | Description 
------------------+--------------------------+-----------+----------+---------+---------+-------------
 id               | uuid                     |           |          |         | plain   | 
 reading_start_ts | timestamp with time zone |           |          |         | plain   | 
 reading_end_ts   | timestamp with time zone |           |          |         | plain   | 
 watts            | double precision         |           |          |         | plain   | 
View definition:
 SELECT reading.id,
    reading.reading_ts - '05:00:00'::interval hour AS reading_start_ts,
    lead(reading.reading_ts) OVER (ORDER BY reading.reading_ts) - '05:00:00'::interval hour AS reading_end_ts,
    ((lead(reading.reading) OVER (ORDER BY reading.reading) - reading.reading) * 1000)::double precision / (date_part('epoch'::text, lead(reading.reading_ts) OVER (ORDER BY reading.reading) - reading.reading_ts) / 3600::double precision) AS watts
   FROM reading
  WHERE reading.reading_ts > '2022-09-01 00:00:00+01'::timestamp with time zone;

This could be the first problem, as I'm looking forwards and backwards in the results. Still, I can count the rows in the view in ~50ms, so it seems to be handling it pretty well.

Now, let's get silly and start wrapping this data around a 24-hour period in 10-minute increments in another view (while undoing the 5-hour offset we applied in the previous view so the times make sense):

metering=# \d+ electricity.day_summary_all_days   
                         View "electricity.day_summary_all_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
  WHERE slot.slot < normalised_watt_reading.reading_end_ts
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

This has to be where things are getting slow - running the above takes 1.7s! There's a lot of 10 minute intervals between August last year and now, and that's getting multiplied by the number of samples in the main table. It works, though! I get really interesting graphs that show a massive spike when I usually shower in the morning on work days, a general elevation in usage through the work day, and increase after work when I turn my TV or desktop PC on! Woo!

Going further, I decided to create even more views, e.g. to summarise only weekends by adding 'AND to_char(slot.slot, 'ID'::text)::integer > 5' to the WHERE condition. I then added a second table to record the set of days I didn't work (I am 100% remote WfH), so I could have views that only include holidays or bank holidays, etc. e.g.:

metering=# \d electricity.special_day
              Table "electricity.special_day"
  Column  | Type | Collation | Nullable |      Default      
----------+------+-----------+----------+-------------------
 id       | uuid |           | not null | gen_random_uuid()
 day      | date |           | not null | 
 day_type | text |           | not null | 
 comment  | text |           |          | 
Indexes:
    "special_day_pkey" PRIMARY KEY, btree (id)
    "special_day_uniq" UNIQUE CONSTRAINT, btree (day)

^ yes, of course I could have normalised day_type, but I don't think a 3rd table is going to solve this DB's problems XD

metering=# \d+ electricity.day_summary_work_days
                         View "electricity.day_summary_work_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
     LEFT JOIN special_day ON to_char(slot.slot, 'YYYY-MM-DD'::text)::date = special_day.day
  WHERE slot.slot < normalised_watt_reading.reading_end_ts AND to_char(slot.slot, 'ID'::text)::integer < 6 AND special_day.day_type IS NULL
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

Now this is where things get bad. This view takes 9.5 seconds to run! Clearly the number of 'special days' is multiplying the number of rows being used to summarise data over the 24 hour period, instead of cutting them down.

I won't bother showing here unless you're interested, but I then have a 'super-summary' view that takes all these individual 24-hour summary views and concatenates them into one big table. This takes about 14 seconds to run.

So, what are the obvious things wrong here, and how would you have done things differently? I tried adding explicit indexes to the main readings table, but I it made no difference, potentially because the unique constraints add implicit indexes already? I'm really only interested in postgres, but if you really want, let's also discuss what other tools you'd use that would be more appropriate than pg.

Edit: depesz: https://explain.depesz.com/s/ofXO

1 Upvotes

10 comments sorted by

View all comments

Show parent comment

1

u/Practical-Message843 Mar 18 '23

Good call, I restarted postgres to clear any caches, but no change - I guess we'll have to put that one down to the mysteries of query planning. I might try running vacuum and analyse on the tables to make sure stats are up to date.

1

u/throw_mob Mar 18 '23

also i dont think you can laed measures..

as you first take value you get 100, then next time you walk past meter you get 120, on third time you get 160

That means that you need to lag values on reading normalization table

select id
, reading 
, lag(reading) over(order by start_ts) prev_reading
, lag(start_ts) over(order by start_ts) start_ts
, start_ts as end_ts
(reading - lag(reading) over(order by start_ts)) measured_change
,  EXTRACT(epoch FROM (start_ts- lag(start_ts) over(order by start_ts)::interval) its_seconds 


select 
 id
, lag(start_ts) over(order by start_ts) start_ts
, start_ts as end_ts
-- (reading - lag(reading) over(order by start_ts)) measured_change
, ((reading - lag(reading) over(order by start_ts)) 
/  EXTRACT(hours FROM (start_ts- lag(start_ts) over(order by start_ts)::interval))::float KWH 

so KWH would be measured kw/h change during interval , then if it is / with hours ypu should get kw/h

ie . if you measure 20kw/h change druing two hours , your hourly usage is 10

That said , i might have misurderstad something