A Closer Look at Variance Implementations in Modern Database Systems

Abstract: Variance is a popular and often necessary component of sampled aggregation queries. It is typically used as a secondary measure to ascertain statistical properties of the result such as its error. Yet, it is more expensive to compute than simple, primary measures such as \texttt{SUM}, \texttt{MEAN}, and \texttt{COUNT}. There exist numerous techniques to compute variance. While the definition of variance is considered to require multiple passes on the data, other mathematical representations can compute the value in a single pass. Some single-pass representations, however, can suffer from severe precision loss, especially for large number of data points. In this paper, we study variance implementations in various real-world systems and find that major database systems such as PostgreSQL 9.4 and most likely System X, a major commercially used closed-source database, use a representation that is efficient, but suffers from floating point precision loss resulting from catastrophic cancellation. We note deficiencies in another popular representation, used by databases such as MySQL and Impala, that suffers from not being distributive and therefore cannot take advantage of modern parallel computational resources. We review literature over the past five decades on variance calculation in both the statistics and database communities, and summarize recommendations on implementing variance functions in various settings, such as approximate query processing and large-scale distributed aggregation.