Time-series data

Is Your Data Time Series? The Types of Data Supported by PostgreSQL and Timescale

Written by Ana Tavares and Carlota Soto

If you’ve ever heard of Timescale, you know that we’re committed to building a faster, fiercer PostgreSQL to support heavy workloads, including time-series data (or temporal data). However, in conversations with our community members and customers, we often find that people don’t refer to their types of data as time series, even though they often are. 

If you want to know if your data is time-series data, this is the litmus test: does your data have some kind of timestamp or time element related to it, even if that may not be its main dimension? If the answer is “yes,” you’re dealing with time-series data. We tried to cover this topic in the fittingly-named article “Do You Have Time-Series Data?” but to make things as clear as possible, we came up with a list of all the data types supported by PostgreSQL and Timescale.

Timescale works just like PostgreSQL under the hood (with some cool optimizations engineered by our team to make it blazing-fast), but both databases can handle a broad range of data types, thus meeting the diverse needs of different sectors. In this article, we’ll list many of them, and you can expect this to grow as we add more use cases to our portfolio.

Now, is your data…

Sensor data

PostgreSQL and Timescale can process and store sensor data, making them ideal for IoT applications. For instance, they can capture temperature or humidity sensor readings for analysis or real estate usage data to optimize large real estate footprints, among many other data points collected by IoT sensors.

CREATE TABLE smart_home_data (
    timestamp TIMESTAMPTZ NOT NULL,
    device_id UUID NOT NULL,
    temperature DOUBLE PRECISION,
    motion_detected BOOLEAN,
    PRIMARY KEY(timestamp, device_id)
);Copy

Transaction data/Financial transactions/Customer transactions/Order history

Transaction data, financial or customer transactions, or order history—whatever you call this data, it is well accommodated by these databases. This category covers everything from purchases to ATM withdrawals to online shopping activities, facilitating thorough user behavior analysis.

CREATE TABLE financial_transactions (
    transaction_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    transaction_type VARCHAR(50) NOT NULL,
    description TEXT
);
Copy

Operational analytics/Application data

PostgreSQL and Timescale can manage data from various applications, including operational analytics and real-time applications. This helps in enhancing the efficiency of the apps and troubleshooting issues. Operational analytics, in particular, is a more specific term for analytics that involves the analysis of business operations. It can be applied to various business areas, such as supply chain management, customer service, human resources, production, and logistics.

CREATE TABLE operational_data (
    data_id SERIAL PRIMARY KEY,
    app_id INT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    cpu_usage DECIMAL(5,2) NOT NULL,
    memory_usage DECIMAL(5,2) NOT NULL,
    response_time INT NOT NULL,
    error_rate DEC
);Copy

Fleet data/Logistics

For transport or delivery companies, fleet data—like vehicle location, condition, and usage statistics—can be well managed by these databases. Delivery vehicles today are fitted with GPS trackers, logging their location and speed at frequent intervals. This allows delivery companies to track routes in real time, optimize driving times, improve safety protocols, and guarantee on-time deliveries. With PostgreSQL/Timescale, managing this data becomes simple and efficient, highlighting the benefits of a technically informed approach to logistics.

CREATE TABLE fleet_data (
    record_id SERIAL PRIMARY KEY,
    vehicle_id INT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    location GEOGRAPHY NOT NULL,
    speed DECIMAL(5,2) NOT NULL,
    fuel_level DECIMAL(5,2),
    engine_status BOOLEAN NOT NULL
);
Copy

Metrics data

Metrics data, including energy metrics, can also be handled by PostgreSQL and Timescale, providing vital insight into energy usage patterns and efficiencies. Here’s an example: to enhance energy efficiency, office complexes utilize smart sensors throughout each floor to continuously track electricity usage, lighting levels, temperature, and room occupancy. This system can generate millions of data points in a standard workday. 

CREATE TABLE energy_metrics (
    metric_id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    facility_id INT NOT NULL,
    power_usage DECIMAL(10,2) NOT NULL,
    voltage DECIMAL(10,2) NOT NULL,
    current DECIMAL(10,2) NOT NULL,
    efficiency DECIMAL(5,2)
);Copy

Tick data/Fintech data/Trading data

These databases are equipped to handle fintech data, including trading data and tick data. High-frequency trading applications track price fluctuations of individual stocks or crypto coins multiple times per second, amassing millions of daily records for each symbol. This data analysis allows traders to identify market trends, forecast price changes, and plan their strategies. Additionally, real-time analysis enables immediate responses to market shifts, either capitalizing on opportunities or minimizing risks. 

CREATE TABLE tick_data (
    tick_id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    volume INT NOT NULL
);Copy

Event data

PostgreSQL and Timescale can manage event data, capturing every interaction users have with a system or application, and providing substantial insights for enhancing user experience, among other benefits.

Consider an online shopping platform with daily traffic in the millions. Each user interaction—from clicks and searches to adding products to the cart, is meticulously logged with time stamps. Analyzing these events provides valuable insights into user behavior, enabling optimization of their experience, customization of marketing strategies, anomaly detection, and refinement of the platform's functionality. This data-driven approach can significantly enhance engagement and sales. 

Another great example is online gaming, where sometimes millions of players interact in the same environment. These interactions generate billions of data points, which can be analyzed to improve the game dynamics and in-game selling.

CREATE TABLE event_data (
    event_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    details JSONB
);Copy

Vector data

In the realm of geospatial analytics, these databases can handle vector data. Vector data is a type of geospatial data that represents objects as points, lines, or polygons. It can include information about physical features such as buildings, roads, and bodies of water, as well as abstract features such as administrative boundaries and electoral districts. Vector data is commonly used in Geographic Information Systems (GIS) for spatial analysis, mapping, and visualization.

Weather data

Weather data refers to information collected and recorded about atmospheric conditions at a specific location and time. This data can include temperature, humidity, wind speed and direction, pressure, precipitation, and other meteorological variables. 

Consider a research institute focused on decoding Earth's century-long climate history. They've amassed global hourly weather data, tracking temperature, humidity, rain, and wind speed. This deep dive into past weather trends enables them to identify patterns, link climatic events, forecast future anomalies, and contribute vital insights to the climate change dialogue.

CREATE TABLE weather_data (
    record_id SERIAL PRIMARY KEY,
    location_id INT NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    temperature DECIMAL(5,2),
    humidity DECIMAL(5,2),
    wind_speed DECIMAL(5,2),
    wind_direction DECIMAL(5,2),
    pressure DECIMAL(7,2),
    precipitation DECIMAL(5,2)
);
Copy

Insurance data

Insurance data, including policy details, claim history, and customer records, can be effectively managed by these databases for accurate risk assessment and claim processing.

Visualize a leading insurance firm processing thousands of diverse claims daily. Every claim call is thoroughly logged, noting details like call time, duration, claim nature, and caller sentiment patterns. This data allows them to spot claim trends, evaluate their claim processing efficiency, refine customer service strategies, identify potential fraud, and ultimately enhance operations for more effective and efficient policyholder service. 

CREATE TABLE insurance_data (
    record_id SERIAL PRIMARY KEY,
   policy_number VARCHAR(50) NOT NULL,
    customer_id INT NOT NULL,
    issue_date TIMESTAMPTZ NOT NULL,
    expiry_date TIMESTAMPTZ,
    coverage_type VARCHAR(50) NOT NULL,
    coverage_amount DECIMAL(20,2) NOT NULL,
    premium DECIMAL(10,2) NOT NULL,
  claim_status VARCHAR(50)
);
Copy

Call records

For telecommunication companies, these databases offer efficient handling of call records, aiding in billing, fraud detection, and network optimization.

CREATE TABLE call_records (
    call_id SERIAL PRIMARY KEY,
    caller_id INT NOT NULL,
    receiver_id INT NOT NULL,
   start_time TIMESTAMPTZ NOT NULL,
   end_time TIMESTAMPTZ NOT NULL,
    call_duration INT NOT NULL,
    call_type VARCHAR(20) NOT NULL,
    location GEOGRAPHY
);
Copy

Do You Have Time-Series Data? ✅

So, maybe you didn’t think you had time-series data, and this article changed your mind. Maybe you know you have a time-series problem, but you call it a different way (any of the above). 

Either way, remember that PostgreSQL and Timescale support all these types of data, making them a versatile choice for various industries. While they provide a flexible and reliable solution to many data management needs, Timescale has the upper hand, allowing you to store and query your data alongside your historical data. Just take a look at some of our examples of time-series analysis.

And if you need to scale PostgreSQL, there are other ways we can help you. 😼 Are your queries getting slower and sluggish? Is your database performance degrading? Check out how you can solve this problem with automatic Postgres partitioning via hypertables.