4 ms·

Spc-kit: A toolkit for statistical process control using SQL

43 points by kqr 3y ago

apwheele 3y ago
Not SPC, but in a similar spirit usecase, I have code examples here of calculating confidence intervals around proportions in SQL, https://andrewpwheeler.com/2020/11/30/confidence-intervals-around-proportions/ https://andrewpwheeler.com/2020/11/30/confidence-intervals-a... I am one of the folks that for dashboarding prefer to push everything to SQL VIEWS and functions. So I use this to monitor proportions for different processes month to month, which may have error bars +/- five to ten percent. It is technically not the right test to know if the process changed, but is useful by eye to know typical variation.
- jacques_chester 3y ago
  I think the SPC tests for detecting process shift would be EWMA and Cusum. The code has EWMA support, but it relies on a function to work and so is tied directly to PostgreSQL.
  - apwheele 3y ago
    Agreed a bit of different use cases. (Or maybe should say the SPC approach is a better/more principled approach, whereas this is an easy graphical approach and I don't need to worry about "resetting" the CUSUM.) I use the binomial CIs in dashboarding scenarios, so if the user selects a different subset of data, all the graphs are auto-updated (on the SQL side). Which when you get people subset into tinier slices of data it becomes more important. So if you can upfront identify the processes you want to monitor, then when flagged have this send an alert. That is better than a dashboard forcing people to click on stuff and hope they identify a anomaly in the process.
    - melondonkey 3y ago
      More dashboards need this I think. I’ve also added relative standard error values on aggregations before to serve as a reliability filter that doesn’t even show users data when they slice it too then.
jacques_chester 3y ago
Oh hey, this is me. The background is that I've thought about applying SPC to software behavior for a long time[0]. While I was at Shopify I began to think about using it for detecting regressions in the YJIT benchmark suite[1]. My first thought was to code a Ruby library (Shopify is a Ruby on Rails outfit), but I decided that it would be more accessible to more folks if it worked inside a database instead. I got caught in a 20% layoff at Shopify and that somewhat took the wind out of my sails, which is why it's been dormant for a year. There are some limitations to be aware of in a software context. First, sample sizes have to be constant. In a world of unreliable systems and networks this is a pretty tough constraint. It could be achieved by taking fixed subsamples at random from a varying larger sample (eg, normally we get 100, pick 50 at random). Switching to variable sample size would be possible, but it will be a lot of work and I haven't had the motivation to tackle it yet. It's also only lightly treated in the main textbook I've worked from, because the usual focus is manufacturing operations where fixed sample sizes are common. Second, it doesn't deal with non-parametric distributions. Classical SPC is rooted in the Normal distribution, and continues to work quite well with any distribution that has some kind of centrally-located mass that tapers off to the edges. But a lot of software behavior follows power laws, especially Pareto distributions. There is SPC literature to deal with non-normal distributions using non-parametric statistics. But I haven't bought and digested the relevant books. It's worth noting that SPC is as much a system of problem solving as it is a bunch of statistical tools. Most major metrics vendors now offer some kind of anomaly detection, but then what? What are your out-of-control action plans? What is your concept of an acceptable amount of staying close to the mean? What is your target, and why is it your target? Can you even achieve such a target with the system as-is, or are you dreaming? SPC attends to these questions as well. I can definitely recommend kqr's introduction[2], which is linked from the repo. [0] https://theoryof.predictable.software/articles/what-is-predictable-software/ https://theoryof.predictable.software/articles/what-is-predi... [1] https://speed.yjit.org https://speed.yjit.org [2] https://two-wrongs.com/statistical-process-control-a-practitioners-guide.html https://two-wrongs.com/statistical-process-control-a-practit...
- jacques_chester 3y ago
  I might add that if you're wondering about how the SPC tools work, read the comments in the SQL. I deliberately set out to write comments that would be helpful both to experienced SPC folks and to curious beginners. Unfortunately the GitHub Linguist grammar for SQL doesn't recognize PostgreSQL comments, so highlighting will be wonky in places.

43 points by kqr 3y ago

Not SPC, but in a similar spirit usecase, I have code examples here of calculating confidence intervals around proportions in SQL, https://andrewpwheeler.com/2020/11/30/confidence-intervals-around-proportions/ https://andrewpwheeler.com/2020/11/30/confidence-intervals-a... I am one of the folks that for dashboarding prefer to push everything to SQL VIEWS and functions. So I use this to monitor proportions for different processes month to month, which may have error bars +/- five to ten percent. It is technically not the right test to know if the process changed, but is useful by eye to know typical variation.

jacques_chester 3y ago
I think the SPC tests for detecting process shift would be EWMA and Cusum. The code has EWMA support, but it relies on a function to work and so is tied directly to PostgreSQL.
- apwheele 3y ago
  Agreed a bit of different use cases. (Or maybe should say the SPC approach is a better/more principled approach, whereas this is an easy graphical approach and I don't need to worry about "resetting" the CUSUM.) I use the binomial CIs in dashboarding scenarios, so if the user selects a different subset of data, all the graphs are auto-updated (on the SQL side). Which when you get people subset into tinier slices of data it becomes more important. So if you can upfront identify the processes you want to monitor, then when flagged have this send an alert. That is better than a dashboard forcing people to click on stuff and hope they identify a anomaly in the process.
  - melondonkey 3y ago
    More dashboards need this I think. I’ve also added relative standard error values on aggregations before to serve as a reliability filter that doesn’t even show users data when they slice it too then.

jacques_chester 3y ago

Oh hey, this is me. The background is that I've thought about applying SPC to software behavior for a long time[0]. While I was at Shopify I began to think about using it for detecting regressions in the YJIT benchmark suite[1]. My first thought was to code a Ruby library (Shopify is a Ruby on Rails outfit), but I decided that it would be more accessible to more folks if it worked inside a database instead. I got caught in a 20% layoff at Shopify and that somewhat took the wind out of my sails, which is why it's been dormant for a year. There are some limitations to be aware of in a software context. First, sample sizes have to be constant. In a world of unreliable systems and networks this is a pretty tough constraint. It could be achieved by taking fixed subsamples at random from a varying larger sample (eg, normally we get 100, pick 50 at random). Switching to variable sample size would be possible, but it will be a lot of work and I haven't had the motivation to tackle it yet. It's also only lightly treated in the main textbook I've worked from, because the usual focus is manufacturing operations where fixed sample sizes are common. Second, it doesn't deal with non-parametric distributions. Classical SPC is rooted in the Normal distribution, and continues to work quite well with any distribution that has some kind of centrally-located mass that tapers off to the edges. But a lot of software behavior follows power laws, especially Pareto distributions. There is SPC literature to deal with non-normal distributions using non-parametric statistics. But I haven't bought and digested the relevant books. It's worth noting that SPC is as much a system of problem solving as it is a bunch of statistical tools. Most major metrics vendors now offer some kind of anomaly detection, but then what? What are your out-of-control action plans? What is your concept of an acceptable amount of staying close to the mean? What is your target, and why is it your target? Can you even achieve such a target with the system as-is, or are you dreaming? SPC attends to these questions as well. I can definitely recommend kqr's introduction[2], which is linked from the repo. [0] https://theoryof.predictable.software/articles/what-is-predictable-software/ https://theoryof.predictable.software/articles/what-is-predi... [1] https://speed.yjit.org https://speed.yjit.org [2] https://two-wrongs.com/statistical-process-control-a-practitioners-guide.html https://two-wrongs.com/statistical-process-control-a-practit...

jacques_chester 3y ago
I might add that if you're wondering about how the SPC tools work, read the comments in the SQL. I deliberately set out to write comments that would be helpful both to experienced SPC folks and to curious beginners. Unfortunately the GitHub Linguist grammar for SQL doesn't recognize PostgreSQL comments, so highlighting will be wonky in places.