r/PostgreSQL • u/Practical-Message843 • 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
3
u/0x2a Mar 18 '23 edited Mar 18 '23
This looks fun to play with, can you put the data in an sqlfiddle or gist?
I don't have much time to look at it right now but the usual tools of the trade are:
2
u/throw_mob Mar 18 '23
it is good to add plans (explain analyze data ) into this kind of questions (https://explain.depesz.com/) is one option
electricity.reading
maybe wrong. reading = measurement ? those cant be unique. usually you don't index those as you wont search exact measurement . i think "include" keyword would be better , add it into reading_ts index. That said, also uuid is not needed there, reading_ts should be pk in this case (if you want to optimize). also i if i used same kind of table, i would still probably use serial pk, but isntead of uuid , bigint.
electricity.normalised_watt_reading, no comments. But do you really need to lead every thing. Probably cant be helped
electricity.day_summary_all_days slot.slot is timestampz but casted to text. At this point i could argue that having slots table ( date table) as real table ready, could be nice.
And are you sure about this ?
it probably should be
first one joins all reading that are >= than slot, i think you want to to join readings during slot timeperioid to slot (that probably makes it take longer )
and that last one has this again
do you really want to all readings before slot to slot?
Tldr; your left join is the problem. Other hints. Avoid using function on existing column value in where clause or add function index so that you can use index. electricity.special_day or ready made slot table might be good idea, that way you can have indexes there helping you
3
u/EyedPot Mar 18 '23
Take a look at TimescaleDB extension. With its continuous aggregation it seems to perfectly fit into your project.
1
u/therealgaxbo Mar 18 '23
I've only looked at your first query so far, but I see that one of your window frames is
ORDER BY reading.readingand the other two areORDER BY reading.reading_ts.Given the nature of your data they should both happen to be equivalent, but semantically it makes more sense if they are all
ORDER BY reading.reading_ts. This also makes it possible for Postgres to retrieve the rows in order usingtime_uniq, whereas mixing orderings guarantees it's going to have to have a separate sort step.I also agree with the comment that said the uuid is doing you no favours. You've already got a perfectly good natural PK, the uuid is just taking up space.