Implementing Time-Series Data: Schema Design and Performance

Previously in this course, we covered modeling audit trails to track state changes in our SaaS entities. While audit trails capture what changed, time-series data—like system logs, metrics, or granular subscription activity—captures when and how often events occur.

In this lesson, we shift from tracking state to tracking events. If your SaaS application needs to analyze user engagement or subscription activity trends over time, you need a strategy that handles high-volume inserts without slowing down your primary operational tables.

Designing Time-Series Schemas

Time-series data is characterized by its append-only nature and its reliance on a timestamp as the primary filter. Unlike our standard user or subscription tables, these records should never be updated. Once an event (e.g., "user clicked upgrade") is written, it is immutable.

For our SaaS project, we will add a subscription_activity_logs table. Because this table will grow rapidly, we must prioritize efficient storage.

The Schema Structure

When designing for time-series, keep your table lean. Avoid wide rows with dozens of text columns. Instead, use indexed foreign keys and a high-precision timestamp.

SQL
CREATE TABLE subscription_activity_logs (
    log_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subscription_id UUID NOT NULL REFERENCES subscriptions(id),
    event_type VARCHAR(50) NOT NULL, -- e.g., 'plan_upgrade', 'billing_retry'
    metadata JSONB,                  -- Flexible storage for event-specific details
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Indexing for performance
CREATE INDEX idx_sub_activity_time ON subscription_activity_logs(created_at DESC);
CREATE INDEX idx_sub_activity_sub_id ON subscription_activity_logs(subscription_id, created_at DESC);

Optimizing Time-Based Queries

The most common operation on this data is time-range filtering (e.g., "show me all upgrades in the last 30 days"). Because our index includes created_at in descending order, the database engine can quickly seek the most recent events without a full table scan.

Worked Example: Extracting Monthly Metrics

If you need to report on subscription activity, you’ll likely group by time intervals. Using SQL's date_trunc function is the standard way to aggregate time-series data:

SQL
SELECT 
    date_trunc('day', created_at) AS activity_date,
    event_type,
    COUNT(*) AS event_count
FROM subscription_activity_logs
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1 DESC;

This query is efficient because it utilizes the idx_sub_activity_time index to filter the range before performing the aggregation.

Hands-on Exercise

1. Add the subscription_activity_logs table to your local development environment using the DDL provided above.
2. Insert three dummy records representing a user upgrading their plan at different times today.
3. Write a query to count the total number of events for a specific subscription_id that occurred within the last 24 hours.

Common Pitfalls

• Over-indexing: Adding too many indexes on a high-velocity table will kill write performance. Only index columns you frequently use in your WHERE or JOIN clauses.
• Data Type Bloat: Avoid using TEXT for event types. Use VARCHAR or, even better, an ENUM type if the list of events is known and stable.
• Ignoring Data Growth: Even with a good schema, time-series tables grow indefinitely. Familiarize yourself with data archiving strategies to prevent your main database from becoming unwieldy.

FAQ

Q: Should I use a dedicated time-series database like InfluxDB? A: Not yet. For a growing SaaS, a relational database (PostgreSQL) is sufficient until you hit millions of rows per day. Start with standard tables, then look into table partitioning if performance degrades.

Q: Why use TIMESTAMPTZ instead of TIMESTAMP? A: TIMESTAMPTZ (timestamp with time zone) avoids ambiguity. If your SaaS operates across regions, storing everything in UTC is essential for accurate time-series analysis.

Recap

Effective time-series design relies on three pillars: immutability, lean row width, and targeted indexing. By separating event logs from core entity tables, we protect the performance of our primary business operations while gaining the ability to analyze user behavior over time.

Up next: Database Normalization vs Denormalization — We will learn how to strategically break normalization rules to boost read performance for complex dashboard queries.