---
title: Analyze application events with UUIDv7 | Tiger Data Docs
description: Get started with TimescaleDB using application event data and UUIDv7 identifiers, with no separate timestamp column
---

Get started with TimescaleDB using application event data that leverages UUIDv7 identifiers. This example demonstrates how to handle event logging and analytics using time-embedded UUIDs for partitioning, with no separate timestamp column needed.

## What you learn

- Using UUIDv7 for time-ordered unique identifiers.
- Efficient time-range queries with `to_uuidv7_boundary()`.
- Session tracking and user analytics.
- Event funnel and conversion analysis.

## Prerequisites for this tutorial

To follow these steps, you'll need:

- Docker installed
- The `psql` PostgreSQL client

## Set up and query the data

1. **Start TimescaleDB**

   Start TimescaleDB using one of these options.

   - [One-line install (recommended)](#tab-panel-452)
   - [Manual Docker command](#tab-panel-453)

   The easiest way to get started:

   Important

   This script is intended for local development and testing only. Do not use it for production deployments. For production-ready installation options, see the [TimescaleDB installation guide](/get-started/choose-your-path/install-timescaledb/index.md).

   On Linux or Mac:

   Terminal window

   ```
   curl -sL https://tsdb.co/start-local | sh
   ```

   This command:

   - Downloads and starts TimescaleDB, if not already downloaded.
   - Exposes PostgreSQL on port `6543`, a non-standard port that avoids conflicts with other PostgreSQL instances on port 5432.
   - Automatically tunes settings for your environment using timescaledb-tune.
   - Sets up a persistent data volume.

   Run TimescaleDB directly with Docker. This option also works on Windows:

   Terminal window

   ```
   docker run -d --name timescaledb \
       -p 6543:5432 \
       -e POSTGRES_PASSWORD=password \
       timescale/timescaledb-ha:pg18
   ```

   This example uses port `6543`, mapped to container port 5432, to avoid conflicts if you have other PostgreSQL instances running on the standard port 5432.

   Wait about 1 to 2 minutes for TimescaleDB to download and initialize.

2. **Connect to TimescaleDB**

   Connect using `psql`:

   Terminal window

   ```
   psql -h localhost -p 6543 -U postgres
   # When prompted, enter password: password
   ```

   You should see the PostgreSQL prompt. Verify TimescaleDB is installed:

   ```
   SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';
   ```

   Expected output:

   ```
      extname   | extversion
   -------------+------------
    timescaledb | 2.x.x
   ```

   Tips

   Prefer a GUI? If you would rather use a graphical tool instead of the command line, you can download [pgAdmin](https://www.pgadmin.org/download/) and connect to TimescaleDB using the same connection details (host: `localhost`, port: `6543`, user: `postgres`, password: `password`).

3. **Create the schema**

   Create the optimized hypertable by running this SQL in your `psql` session:

   ```
   -- Create the app_events table with UUIDv7 partitioning
   -- Note: No separate timestamp column needed - the timestamp is embedded in the UUIDv7!
   -- Note: No PRIMARY KEY to allow direct compress during COPY
   CREATE TABLE IF NOT EXISTS app_events (
       event_id UUID NOT NULL,
       user_id UUID NOT NULL,
       session_id UUID NOT NULL,
       event_type TEXT NOT NULL,
       event_name TEXT,
       device_type TEXT,
       country_code TEXT,
       category TEXT,
       page_path TEXT,
       referrer TEXT,
       viewport_width INTEGER,
       element_id TEXT,
       position_x INTEGER,
       position_y INTEGER,
       product_id TEXT,
       quantity INTEGER,
       revenue_cents INTEGER
   ) WITH (
       tsdb.hypertable,
       tsdb.partition_column = 'event_id',
       tsdb.segmentby = 'user_id'
   );


   -- Create index on event_id for lookups (not unique to allow direct compress)
   CREATE INDEX idx_app_events_event_id ON app_events(event_id);
   ```

   This creates an `app_events` table with:

   - UUIDv7 partitioning on `event_id` (time is embedded in the UUID).
   - Segmentation by `user_id` for optimal compression.
   - No separate timestamp column needed.

4. **Load the sample data**

   First, download and decompress the sample data:

   Terminal window

   ```
   # Download the sample data
   wget https://assets.timescale.com/timescaledb-datasets/events_uuid.csv.gz


   # Decompress the CSV file
   gunzip events_uuid.csv.gz


   # This will create events_uuid.csv ready for loading
   ```

   Load the data using one of these approaches.

   - [Direct to columnstore (recommended)](#tab-panel-450)
   - [Standard COPY (fallback)](#tab-panel-451)

   This approach writes data directly to the columnstore, bypassing the rowstore entirely. You get instant analytical performance.

   From the command line:

   Terminal window

   ```
   psql -h localhost -p 6543 -U postgres \
     -v ON_ERROR_STOP=1 \
     -c "SET timescaledb.enable_direct_compress_copy = on;
         COPY app_events FROM STDIN WITH (FORMAT csv, HEADER true);" \
     < events_uuid.csv
   ```

   This command reads the CSV file from your local filesystem and pipes it to PostgreSQL, which loads it directly into the columnstore.

   Verify the data loaded:

   ```
   SELECT COUNT(*) FROM app_events;
   ```

   This approach loads data into the rowstore first. Data is converted to the columnstore by a background policy (12 to 24 hours) for faster querying.

   From the command line:

   Terminal window

   ```
   psql -h localhost -p 6543 -U postgres \
     -v ON_ERROR_STOP=1 \
     -c "COPY app_events FROM STDIN WITH (FORMAT csv, HEADER true);" \
     < events_uuid.csv
   ```

   Verify the data loaded:

   ```
   SELECT COUNT(*) FROM app_events;
   ```

   If you loaded data using standard copy, a background process converts your rowstore data to the columnstore in 12 to 24 hours. To get the best query performance immediately, you can convert it manually:

   ```
   DO $$
   DECLARE ch TEXT;
   BEGIN
       FOR ch IN SELECT show_chunks('app_events') LOOP
           CALL convert_to_columnstore(ch);
       END LOOP;
   END $$;
   ```

5. **Run sample queries**

   Now explore the data with some analytical queries. Run these in your `psql` session.

   Query 1: efficient time range query (chunk pruning).

   ```
   -- Uses the boundary function for chunk exclusion
   \timing on
   SELECT COUNT(*), event_type
   FROM app_events
   WHERE event_id >= to_uuidv7_boundary(now() - interval '7 days')
   GROUP BY event_type;
   ```

   Why this is fast: the [`to_uuidv7_boundary()`](/reference/timescaledb/uuid-functions/to_uuidv7_boundary/index.md) function creates a UUID boundary value that TimescaleDB can use to exclude entire chunks without scanning them.

   Query 2: inefficient query (anti-pattern).

   ```
   -- Scans ALL chunks, extracts timestamp from every row
   SELECT COUNT(*), event_type
   FROM app_events
   WHERE uuid_timestamp(event_id) >= now() - interval '7 days'
   GROUP BY event_type;
   ```

   Why this is slow: the `uuid_timestamp()` function must be evaluated for every row, which prevents chunk exclusion.

   Query 3: SkipScan on a single column (distinct users).

   ```
   -- Demonstrates SkipScan optimization: uses the compression index to skip repeated values
   \timing on
   SELECT DISTINCT ON (user_id)
       user_id,
       event_type,
       uuid_timestamp(event_id) as event_time
   FROM app_events
   WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
   ORDER BY user_id, event_id DESC
   LIMIT 50;
   ```

   Why this uses SkipScan: because `user_id` is the `segmentby` column, TimescaleDB automatically creates a compression index on it. SkipScan can jump directly to the next unique `user_id` value instead of scanning all rows. The `WHERE` clause ensures chunk exclusion, so it only scans chunks with recent data, making it even faster.

   To verify SkipScan is used, check the query plan with `EXPLAIN`. You should see `Custom Scan (SkipScan)` on the compressed chunks instead of a sequential scan.

   Query 4: funnel analysis.

   ```
   WITH funnel AS (
       SELECT
           user_id,
           session_id,
           MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as viewed,
           MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
           MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchased
       FROM app_events
       WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
       GROUP BY user_id, session_id
   )
   SELECT
       SUM(viewed) as sessions_with_view,
       SUM(added_to_cart) as sessions_with_cart,
       SUM(purchased) as sessions_with_purchase,
       ROUND(100.0 * SUM(added_to_cart) / NULLIF(SUM(viewed), 0), 2) as view_to_cart_pct,
       ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added_to_cart), 0), 2) as cart_to_purchase_pct
   FROM funnel;
   ```

   Query 5: revenue by country (last 30 days).

   ```
   SELECT
       country_code,
       COUNT(*) as purchase_count,
       SUM(revenue_cents) / 100.0 as total_revenue,
       ROUND(AVG(revenue_cents) / 100.0, 2) as avg_order_value
   FROM app_events
   WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
     AND event_type = 'purchase'
   GROUP BY country_code
   ORDER BY total_revenue DESC
   LIMIT 10;
   ```

## What's happening behind the scenes?

### UUIDv7 partitioning

When you create a table with `tsdb.partition_column = 'event_id'` where `event_id` is a UUIDv7:

- TimescaleDB automatically partitions your data by the time-embedded UUID.
- No separate timestamp column is needed, because the timestamp is embedded in the UUID itself.
- chunk exclusion works with `to_uuidv7_boundary()` for efficient time-range queries.
- Vectorized UUID compression provides 30% storage savings and 2x query performance.

### Columnstore compression

With `tsdb.enable_columnstore=true`:

- Data is stored in a hybrid row-columnar format.
- Analytical queries only scan the columns they need, for a large speedup.
- Typical compression ratios: 90% or more for time-series data.
- Compression happens transparently, with no changes to your queries.

### Direct to columnstore

When you use `SET timescaledb.enable_direct_compress_copy = on`:

- Data loads directly into compressed columnstore format.
- Bypasses the rowstore entirely.
- Provides instant analytical performance, with no waiting for background compression.

### Segmentation

The `tsdb.segmentby='user_id'` setting:

- Groups data by user within each chunk.
- Improves compression ratios by keeping similar data together.
- Speeds up queries that filter by `user_id`.
- Is better for user-based analytics.

### UUIDv7 functions

TimescaleDB provides comprehensive UUIDv7 functionality across all supported PostgreSQL versions (including PostgreSQL 15, 16, and 17), while PostgreSQL only provides UUIDv7 support in PostgreSQL 18.

For complete documentation on all available UUIDv7 functions, see the [UUIDv7 functions reference](/reference/timescaledb/uuid-functions/index.md).

## Continuous aggregates (Advanced)

For real-time dashboards, you can create continuous aggregates that automatically update:

```
-- Create a continuous aggregate for hourly event statistics
CREATE MATERIALIZED VIEW app_events_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', uuid_timestamp(event_id)) AS hour,
    event_type,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users,
    SUM(revenue_cents) / 100.0 as total_revenue
FROM app_events
GROUP BY hour, event_type;


-- Add a refresh policy to keep it updated
SELECT add_continuous_aggregate_policy('app_events_hourly',
    start_offset => INTERVAL '2 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');
```

Now you can query `app_events_hourly` for instant results on pre-aggregated data.

## Troubleshooting

### Data didn't load

- Check the CSV file path is correct.
- Ensure the CSV header matches the schema columns.
- Try loading a few rows first to test: `LIMIT 10` in your data file.

### Direct to columnstore not working

- Verify TimescaleDB version 2.24 or later: `SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';`.
- Ensure you ran `SET timescaledb.enable_direct_compress_copy = on;` in the same session.
- Check for error messages in the output.

### Queries seem slow

- Verify you're using `to_uuidv7_boundary()` for time-range queries, not `uuid_timestamp()`.
- Check if data is compressed: `SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'app_events';`.
- Ensure chunk exclusion is working: use `EXPLAIN ANALYZE` to see chunk pruning.

### UUIDv7 functions not found

- Verify your TimescaleDB version supports UUIDv7: `SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';`.
- UUIDv7 support requires TimescaleDB 2.24 or later with the uuidv7 extension enabled.

## Use cases

This application events example demonstrates patterns applicable to:

- **SaaS analytics**: user behavior tracking, feature usage, and conversion funnels.
- **E-commerce**: shopping cart analysis, purchase patterns, and product recommendations.
- **Application monitoring**: error tracking, performance metrics, and user sessions.
- **Audit logging**: security events, compliance tracking, and change history.
- **Event-driven architectures**: microservices event sourcing and message queues.
- **A/B testing**: experiment tracking, variant analysis, and statistical significance.

## Clean up

When you're done experimenting, remove the container and data.

If you used the one-line install:

Terminal window

```
# Stop the container
docker stop timescaledb-ha-pg18-quickstart


# Remove the container
docker rm timescaledb-ha-pg18-quickstart


# Remove the persistent data volume
docker volume rm timescaledb_data


# (Optional) Remove the Docker image
docker rmi timescale/timescaledb-ha:pg18
```

If you used the manual Docker command:

Terminal window

```
# Stop the container
docker stop timescaledb


# Remove the container
docker rm timescaledb


# (Optional) Remove the Docker image
docker rmi timescale/timescaledb-ha:pg18
```

If you created a named volume with the manual Docker command, you can remove it with `docker volume rm <volume_name>`.