A Guide to Data Analysis on PostgreSQL
Start supercharging your PostgreSQL today.
Written by Anber Arif
As an open-source relational database with a variety of built-in data types, operators, and functions for data manipulation, PostgreSQL can be an extremely helpful tool for data analysis. In this guide, we’ll cover how to implement common data analysis use cases on PostgreSQL and show you how to scale it 📈 as your data grows.
From fundamental querying to advanced aggregation and grouping strategies, we’ll explore how PostgreSQL serves as a robust platform for processing data. Then, we’ll show you an easier and more effective way of handling complex analytical tasks with PostgreSQL, especially for time series and statistical analysis, by delving into Timescale hyperfunctions.
Basics of Data Analysis
Data analysis is the art of using information to make informed decisions. It’s a process that involves several key steps:
Identification: recognizing relevant data for analysis based on specific questions or problems.
Collection: gathering data from various sources like databases, sensors, or software.
Cleaning: refining data by removing errors, duplicates, and inconsistencies.
Analysis: applying statistical or computational techniques to find patterns or relationships.
Interpretation: transforming analyzed data into actionable insights for decision-making.
Now, let’s break down some essential use cases where data analysis truly shines:
Optimizing processes and operational efficiency: Data analysis plays a pivotal role in various fields like business or manufacturing. By scrutinizing data, businesses can streamline operations, identify bottlenecks, and fine-tune processes for maximum efficiency.
Identifying patterns: One of the most powerful aspects of data analysis is its ability to unveil patterns. These patterns could be trends over time, seasonal fluctuations, irregularities indicating potential fraud, or customer behavioral tendencies. By recognizing these patterns, businesses can anticipate changes, make proactive decisions, and detect anomalies that might need attention.
Product development: Data analysis isn’t just about refining existing processes; it’s also a catalyst for innovation. Companies leverage data insights to develop new products that align better with market demands or customer preferences.
Enhancing customer experience: Understanding customers is key to business success. Data analysis empowers organizations to gain deeper insights into customer preferences, behavior, and satisfaction.
Types of Data Analysis
In data analysis, different approaches help derive a wide range of insights. Here’s a brief overview of the different types of data analysis:
Descriptive analysis
Descriptive analysis involves summarizing and organizing data to present a clear and concise overview. It focuses on understanding the fundamental characteristics of the data, including measures of central tendency, dispersion, and visualization. It aims to describe what the data is telling without making inferences or predictions. For instance, percentiles, quartiles, mean, median, and mode are common descriptive statistics used to uncover patterns, trends, or anomalies within datasets.
Learn how percentile approximation works and why it’s more useful than averages for data analysis.
Diagnostic analysis
Diagnostic analysis aims to uncover relationships between different variables or datasets to identify cause-and-effect connections. It delves deeper into the data to understand why certain patterns or outcomes occur. This type of analysis often involves techniques like correlation, regression analysis, or time-series analysis. For instance, in time-series analysis, diagnostic analysis explores how changes in one variable affect another over time, establishing correlations or causal links. You can learn more about it in our deep dive into time-series analysis with examples and applications.
Predictive analysis
Predictive analysis focuses on forecasting future trends, outcomes, or behaviors based on historical data patterns. It uses statistical algorithms, machine learning models, or other techniques to predict what might happen in the future. Regression analysis and time-series forecasting models fall under predictive analytics. By analyzing historical data, predictive analysis helps anticipate future scenarios or trends.
Prescriptive analysis
Prescriptive analysis goes beyond prediction by recommending possible actions or strategies. It uses insights from predictive analysis to suggest the best course of action to optimize outcomes. Techniques like optimization algorithms, simulation models, and A/B testing are part of prescriptive analysis. For instance, in A/B testing, different versions or strategies are compared to determine which performs better and to guide decision-making.
This article on types of data analysis provides a concise but thorough explanation of the data analysis types and methodologies.
Data Analysis in PostgreSQL
Now that we covered the basics of data analysis, let’s see how you can leverage them in PostgreSQL. In this section, we’ll delve into the following key aspects of data analysis in PostgreSQL:
Exploring and describing data: This process involves understanding the structure and data stored in PostgreSQL databases. It includes methods to examine data layout, column types, and overall information stored. This exploration lays the groundwork for detailed analysis and informed decision-making by comprehending data distributions and characteristics.
Aggregating and grouping data: Going deeper into data manipulation, this aspect involves condensing and organizing PostgreSQL data based on specific criteria. Techniques like aggregations provide summarized insights, unveiling patterns or trends within the dataset. Grouping data enables categorization, uncovering relationships, and facilitating a more profound comprehension of the dataset.
Exploring and describing data
Exploratory commands such as SELECT with JOIN are pivotal in comprehending the structure and relationships within data. JOIN allows the combination of tables based on common columns, revealing connections between datasets. This command shows how data is shaped by displaying interrelated information from multiple tables, aiding in understanding relationships crucial for comprehensive analysis.
Now, let’s take a look at basic commands for exploring and describing data in PostgreSQL.
Basic SELECT statement
SELECT *
FROM table_name;
This command fetches all columns (*) from the specified table, offering a comprehensive view of the table’s contents.
Filtering data using WHERE clause
SELECT column1, column2
FROM table_name
WHERE condition;
This command selects specific columns from the table based on a specified condition. For instance, WHERE column1 = 'value' retrieves rows where column1 matches a particular value.
SELECT with JOIN
SELECT t1.column_name1, t2.column_name2
FROM table1 t1
JOIN table2 t2 ON t1.shared_column = t2.shared_column;
In this example, the SELECT statement retrieves specified columns from both table1 and table2. The JOIN clause links the tables based on a shared column, displaying related information from both tables in the resulting output. This simple command showcases how data from multiple tables can be merged and displayed, providing an understanding of the data’s structure and relationships.
Aggregating and grouping data
PostgreSQL provides an extensive collection of aggregate functions. These functions encompass fundamental aggregation operations like AVG(), COUNT(), SUM(), MAX(), MIN(), ARRAY_AGG(), and STRING_AGG(). PostgreSQL allows the concatenation of strings or the creation of arrays as part of the aggregation process. This diverse array of functions equips users with versatile tools for manipulating and summarizing data.
Built-in aggregates in PostgreSQL
In PostgreSQL, functions primarily operate on individual rows, executing specific operations or transformations. Conversely, aggregate functions perform computations across multiple rows within a column, summarizing or aggregating values to produce a single result.
PostgreSQL supports various categories of built-in aggregates:
General-purpose aggregates: cover standard functions like AVG(), SUM(), and COUNT() for basic mathematical and counting operations.
Statistical aggregates: include statistical functions like STDDEV(), VARIANCE(), and CORR() for analytical purposes.
Ordered-set aggregates: provide functions like PERCENTILE_CONT() and PERCENTILE_DISC() to compute percentile values and related operations.
Hypothetical-set aggregates: offer hypothetical-set functions for analyzing various scenarios without altering the dataset.
Grouping operations: enable operations like GROUPING SETS, CUBE, and ROLLUP to perform multiple grouping within a query.
Furthermore, it allows users to craft their custom aggregate functions, tailoring functionality to suit specific analytical requirements. This capability grants users the flexibility to extend PostgreSQL's aggregate capabilities that cater precisely to their unique data analysis.
Here is an example of how to compute percentiles in PostgreSQL:
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median_sales
FROM
sales_data;
This query uses the percentile_cont() aggregate function to compute the median sales amount within the dataset. In this code:
percentile_cont(0.5) is the function used to calculate the percentile. In this case, 0.5 represents the 50th percentile, which corresponds to the median value.
WITHIN GROUP (ORDER BY sales_amount) specifies the column sales_amount to order the dataset. This ensures the accurate computation of the median based on the sorted values in the sales_amount column.
AS median_sales assigns the label median_sales to the resulting computed value.
Grouping data
Grouping data involves organizing data into distinct categories based on specified criteria, such as states, departments, or any categorical information. This process collapses multiple rows sharing common characteristics into one consolidated row representing the designated group. For instance, grouping sales data by region collapses individual sales entries into summarized information for each region.
You can use time-series binning to group data into intervals or bins, allowing for analysis within these designated periods. To compute percentiles per bin, you can employ the time_bucket function for binning and percentile_cont() for percentile calculation within each bin. Let’s consider a table sensor_data with columns timestamp and sensor_value.
SELECT
time_bucket('1 hour', timestamp) AS hourly_bin,
percentile_cont(0.25) WITHIN GROUP (ORDER BY sensor_value) AS p25,
percentile_cont(0.50) WITHIN GROUP (ORDER BY sensor_value) AS p50,
percentile_cont(0.75) WITHIN GROUP (ORDER BY sensor_value) AS p75
FROM sensor_data
GROUP BY hourly_bin
ORDER BY hourly_bin;
This query uses the time_bucket function to create hourly bins based on the timestamp column. Inside each hourly bin, it computes the 25th, 50th (median), and 75th percentiles of the sensor_value using the percentile_cont function.
Partitioning data for aggregation
Partitioning data involves performing aggregations within defined partitions or subsets without collapsing rows. This technique allows for computations like averages or sums within distinct partitions based on certain criteria, such as time intervals or categories, without losing granularity in the dataset.
Consider a table named sales_data with columns time_stamp, category, and sales_value.
SELECT
time_stamp,
category,
sales_value,
AVG(sales_value) OVER (PARTITION BY category ORDER BY time_stamp ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_avg_per_category
FROM sales_data;
This query calculates a rolling average for sales data categorized by different segments within the sales_data table. The PARTITION BY clause segments the data based on the category column, creating separate partitions for each unique category. The ORDER BY time_stamp sorts the data within each partition based on the time_stamp column in ascending order. Then, the AVG() function, used in combination with the OVER clause, computes the average of sales_value over a window that includes the current row and the three preceding rows within each category partition.
Data Analysis in PostgreSQL With Hyperfunctions
Certain calculations in PostgreSQL exhibit varying speeds, with some being faster than others. However, as your dataset grows, the efficiency of all analyses tends to decrease. This slowdown occurs due to the increased volume of data being processed, impacting query execution times and overall analysis performance.
With Timescale, you can optimize and accelerate specific types of data analysis with the help of hyperfunctions, which significantly improve the efficiency of data operations within PostgreSQL. Timescale hyperfunctions are a collection of optimized functions, procedures, and data types specially tailored for time-series query, aggregation, and analysis tasks. They are deeply incorporated with Timescale's cloud service and PostgreSQL's TimescaleDB extension. While some hyperfunctions are included by default, others are part of the Timescale Toolkit, which includes high-performance Rust functions that execute directly within the database.
Using Timescale hyperfunctions, you can handle extensive data volumes while sustaining real-time analytics within PostgreSQL. Exactly how extensive, you may be wondering? In our biggest dogfooding effort to date, we are successfully managing a 350 TB+ cloud database, with a single Timescale instance handling the ingestion of 10 billion new records per day.
This same database powers real-time dashboards across all our customers to fuel our Insights tool, and we’re aggregating most of the interesting statistics into a UDDSketch, which is part of our collection of hyperfunctions. As you can see, there’s no reason why you can’t scale PostgreSQL for complex data analysis.
However, to achieve a feat like the Insights tool, we had to use many of the tools in our box to scale PostgreSQL. Here are some that you can explore for your own data analysis tasks:
Data compression: Data compression in Timescale stands out as a pivotal feature, optimizing storage and query performance. It significantly reduces the amount of stored data, leading to impressive storage savings that often exceed 90 %. This compression capability is tailored to enhance the efficiency of certain queries, thereby accelerating their execution and response times. By utilizing advanced compression techniques, Timescale minimizes the storage footprint without compromising data integrity, ensuring streamlined data management and efficient query processing.
Time-series optimization: Timescale is finely tuned and optimized specifically for time-series analysis across diverse use cases. It shows exceptional performance in handling time-series data, significantly accelerating time-series queries by up to 1,000 times compared to vanilla PostgreSQL. This optimization spans various time-series analysis scenarios, demonstrating Timescale’s ability to deliver rapid query execution and insightful analysis over time-oriented datasets.
Query time reduction: Timescale’s efficiency extends to reducing the execution time of frequently run queries to mere milliseconds. This capability significantly enhances query performance, enabling lightning-fast responses to commonly executed queries by pre-aggregating the data and automatically and incrementally updating it in the background. By leveraging its optimized architecture and specialized functionalities, such as automatic partitioning, Timescale drastically minimizes query processing times, offering users the ability to retrieve critical information swiftly.
Geospatial and vector data toolkits: Timescale incorporates specialized tools like the PostGIS extension, enriching PostgreSQL with robust geospatial capabilities. This extension enables spatial indexing, supports diverse geometric data types, and facilitates efficient spatial operations and analyses. Additionally, the platform leverages PostgreSQL ’s evolution into a proficient vector database, efficiently managing vector-based data types and optimizing storage mechanisms.
Hyperfunctions are Timescale's versatile toolbox, empowering analysts and developers to harness the capabilities of these database systems.
Time-series foundation
Time vectors serve as a structured way to organize and handle time-oriented information efficiently. At their core, time vectors encapsulate aggregate accessors and mutators designed to streamline data aggregation and manipulation processes. Aggregate accessors are instrumental in retrieving summarized information from time-series datasets. They facilitate the extraction of essential aggregated data points, such as maximum, minimum, or average values, which are vital for comprehensive time-series analysis.
On the other hand, mutators empower users to modify specific data points within the temporal dataset, allowing for targeted alterations or adjustments.
Rolling aggregates in Timescale
Rolling aggregates in Timescale refers to a powerful analytical technique used in time-series data analysis. These aggregates allow for the computation of summary statistics, such as averages, standard deviations, percentiles, or other statistical measures, over a sliding or rolling window of time. Rather than computing an aggregate value for an entire dataset at once, rolling aggregates break down the data into smaller, consecutive time intervals, calculating aggregate values within each interval.
This method offers several advantages. It enables the examination of data trends and patterns over specific time periods, providing insights into how statistical measures change over time. By using rolling aggregates, analysts can capture dynamic changes, detect anomalies, and understand the variability of data within shorter time frames.
Consider the following example:
SELECT
bucket,
average(rolling(stats_agg) OVER fifteen_min),
stddev(rolling(stats_agg) OVER fifteen_min, 'pop'),
kurtosis(rolling(stats_agg) OVER fifteen_min, 'pop')
FROM (SELECT
time_bucket('1 min'::interval, ts) AS bucket,
stats_agg(val)
FROM measurements
GROUP BY 1) AS stats
WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING);
This query organizes the time-series data into one-minute intervals using the time_bucket() function, creating distinct time buckets labeled as bucket. The stats_agg() function then aggregates values within each time bucket, gathering statistical insights. Then, the query employs a WINDOW function named fifteen_min, establishing a rolling window of 15 minutes preceding the current row, effectively calculating rolling statistics. The average() function computes the rolling average, stddev() computes the rolling standard deviation, and kurtosis() computes the kurtosis within this defined rolling window.
Time-series partition aggregates in PostgreSQL
In contrast to the streamlined approach offered by Timescale's rolling aggregates, performing similar time-series partition aggregates in standard PostgreSQL can be more intricate and complex. PostgreSQL does support window functions, which are crucial for time-series analysis, but the implementation of sliding or rolling window aggregates involves a more manual and intricate process compared to Timescale.
In PostgreSQL, the process typically involves constructing explicit partitions or segments of data using window functions like PARTITION BY. These partitions segment the dataset into specified intervals but require a more detailed definition and management of the window specifications. Aggregating data over these partitions demands precise ordering and partitioning to calculate values accurately, often leading to more intricate SQL queries and increased query complexity.
Furthermore, in PostgreSQL, achieving sliding window aggregations might involve more convoluted syntax or manual handling of time intervals, making it less intuitive and potentially error-prone compared to the specialized functions offered by Timescale. This additional complexity in PostgreSQL may demand a deeper understanding of SQL window functions and more intricate query structuring, potentially increasing the chance of errors and requiring more extensive testing to ensure accurate results.
Percentiles with hyperfunctions
Percentiles play a crucial role in statistical analysis, providing insights into data distribution. In Timescale, hyperfunctions offer efficient methods to compute percentiles, allowing users to understand the data distribution more comprehensively. Unlike traditional approaches like averages that provide a single summary value, percentiles divide data into intervals and determine the value below which a certain percentage of observations fall.
Here’s an example demonstrating percentile computation using hyperfunctions in Timescale:
SELECT
approx_percentile(0.25, percentile_agg(response_time)) as p25,
approx_percentile(0.75, percentile_agg(response_time)) as p75
FROM responses;
In this SQL query, we're employing the approx_percentile function in conjunction with the percentile_agg hyperfunction in Timescale to compute approximate percentiles for the response_time column within the responses dataset. The approx_percentile function allows us to specify the desired percentiles (in this case, 25 % and 75 %) and apply them to the aggregated response_time data using percentile_agg. When comparing percentile computation between Timescale's hyperfunctions and PostgreSQL's standard percentile functions, the contrast primarily lies in the efficiency and handling of large-scale time-series data. Timescale's hyperfunctions implement approximate percentile calculations efficiently, which is especially suitable for larger datasets.
In contrast, while accurate, PostgreSQL's standard percentile functions, such as percentile_cont and percentile_disc, might face performance challenges with substantial volumes of time-series data due to their computation complexities.
Time bucketing for percentiles
Time bucketing simplifies the assessment of percentiles across different intervals by organizing data into discrete time periods. It facilitates the calculation of percentiles within these defined buckets, enabling effortlessly retrieving percentile values over specific timeframes or intervals.
SELECT * FROM responses
WHERE ts >= now()-'1 hour'::interval
AND response_time > (
SELECT approx_percentile(0.95, percentile_agg)
FROM responses_1h_agg
WHERE bucket = time_bucket('30 minutes'::interval, now()-'30 minutes'::interval)
);
This example retrieves records from the responses table based on instances where the response_time exceeds the approximate 95th percentile value. The outer query confines the data retrieval within the last hour. Meanwhile, leveraging time bucketing, the inner subquery efficiently calculates the 95th percentile value for a specific 30-minute interval within that hour. This highlights the ease with which time buckets allow for the computation of percentile values across distinct time periods, facilitating efficient and precise analysis within specific intervals of a time series dataset.
Regression
Regression analysis in databases is a statistical method used to identify and quantify relationships between variables. In Timescale, regression is facilitated through its hyperfunctions, particularly the stats_agg function. Here’s an example that demonstrates the application of stats_agg for two-variable regression:
SELECT
average_y(stats_agg(val2, val1)), -- equivalent to average(stats_agg(val2))
stddev_x(stats_agg(val2, val1)), -- equivalent to stddev(stats_agg(val1))
slope(stats_agg(val2, val1)) -- the slope of the least squares fit line of the values in val2 & val1
FROM measurements_multival;
This query employs the stats_agg hyperfunction to perform a regression analysis on two variables, val2 and val1, within the measurements_multival dataset. It computes several regression metrics in a single function call, including the average of val2 (equivalent to the average function applied to val2), the standard deviation of val1 (the equivalent to stddev applied to val1), and the slope of the least squares fit line when plotting val2 against val1. This streamlined approach simplifies regression analysis, offering a concise method to derive multiple regression metrics in one query.
Contrasting this approach with PostgreSQL, traditional regression in PostgreSQL often involves using dedicated regression functions like regr_slope, regr_intercept, etc., applied separately to each variable or pair. Timescale's stats_agg simplifies this by allowing the calculation of multiple regression metrics within a single function call, providing a more streamlined and comprehensive method for conducting regression analysis. This capability reduces query complexity and enhances the efficiency of regression calculations in comparison to PostgreSQL's individual regression functions.
Timescale: Fast Time Series and Data Analytics Workloads on PostgreSQL
When dealing with extensive time-series data or analytics workloads, native PostgreSQL might encounter challenges in performance and scalability. As datasets grow larger or when working with complex time-series analyses, the limitations of PostgreSQL’s conventional architecture might hinder efficient data processing.
Timescale extends PostgreSQL’s capabilities, particularly enhancing performance and scalability for time-series and analytics applications. Create a free Timescale account to gain access to a powerful platform that optimizes PostgreSQL for time-series data, enabling faster and more efficient data analytics.