How to Calculate Percentiles in PostgreSQL
Let's say we want to look at the percentiles for query durations. We can use PostgreSQL's percentile_cont
function to do that:
select
percentile_cont(0.25) within group (order by duration asc) as percentile_25,
percentile_cont(0.50) within group (order by duration asc) as percentile_50,
percentile_cont(0.75) within group (order by duration asc) as percentile_75,
percentile_cont(0.95) within group (order by duration asc) as percentile_95
from query_durations
If we want to view those percentiles by day:
select
day,
percentile_cont(0.25) within group (order by duration asc) over (partition by day) as percentile_25,
percentile_cont(0.50) within group (order by duration asc) over (partition by day) as percentile_50,
percentile_cont(0.75) within group (order by duration asc) over (partition by day) as percentile_75,
percentile_cont(0.95) within group (order by duration asc) over (partition by day) as percentile_95
from query_durations
group by 1
order by 1 asc
Real-time SQL collaboration is here
Get started with PopSQL and PostgreSQL in minutes