Historical Data

One of Metadb’s core strengths is its ability to maintain complete history of all data changes. Every modification to a record is tracked with timestamps, enabling powerful temporal analysis and historical queries.

How Historical Tracking Works

Metadb uses temporal columns to track the lifecycle of each record:

__start: When this version of the record became active
__end: When this version was superseded (or 9999-12-31 00:00:00+00 if still current)
__current: Boolean flag indicating if this is the current version

This approach is known as “temporal table” design or “slowly changing dimension type 2” in data warehousing terminology.

Understanding Record History

Let’s look at a practical example of how changes are tracked:

select __start, __end, __current, id, groupname, description 
    from library.patrongroup__;

`__start`	`__end`	`__current`	`id`	`groupname`	`description`
2022-04-17 21:42:25-00	2022-04-18 19:27:18-00	false	15	undergrad	Student
2022-04-18 19:27:18-00	9999-12-31 00:00:00+00	true	15	undergrad	Undergraduate Student
2022-04-17 21:42:25-00	9999-12-31 00:00:00+00	true	10	graduate	Graduate Student
2022-04-17 21:52:53-00	9999-12-31 00:00:00+00	true	9	faculty	Faculty Member

Reading the History

In this example:

Initial state

The “undergrad” group was created on April 17 with description “Student”

Change event

On April 18 at 19:27:18, the description changed to “Undergraduate Student”

Historical record

The old version’s __end was set to the change timestamp and __current became false

Current record

A new row was created with the updated description, with __end set to the far future

The __end value of 9999-12-31 00:00:00+00 represents “infinity” - meaning this version is still active.

Querying Historical Data

Current State Only

For most operational queries, you only need current records:

-- Using the current table (recommended)
select id, groupname, description 
    from library.patrongroup;

-- Or filter the main table explicitly
select id, groupname, description 
    from library.patrongroup__ 
    where __current;

All History

To see every version of every record:

select __start, __end, id, groupname, description 
    from library.patrongroup__
    order by id, __start;

Point-in-Time Queries

Find out what the data looked like at a specific moment:

select id, groupname, description
    from library.patrongroup__
    where '2022-04-18 00:00:00' >= __start 
      and '2022-04-18 00:00:00' < __end;

This returns the state of all records as they existed on April 18, 2022 at midnight.

Point-in-time queries use the pattern target_time >= __start AND target_time < __end to find records that were active at that moment.

Date Range Queries

Find all records that changed within a time period:

select __start, id, groupname, description
    from library.patrongroup__
    where '2022-04-01' <= __start 
      and __start < '2022-05-01'
    order by __start;

Change Tracking

Identify records that have been modified:

-- Records that have been updated (have historical versions)
select id, groupname, count(*) as version_count
    from library.patrongroup__
    group by id, groupname
    having count(*) > 1;

Most Recent Changes

Find the most recently modified records:

select __start, id, groupname, description
    from library.patrongroup__
    where __current
    order by __start desc
    limit 10;

Temporal Analysis Use Cases

Audit & Compliance
Trend Analysis
Comparative Analysis
Data Quality

Track who changed what and when for audit trails:

-- View all changes to a specific record
select __start, __end, __current, 
       groupname, description
    from library.patrongroup__
    where id = 15
    order by __start;

This shows the complete audit trail for a single entity.

Analyze how data evolved over time:

-- Count active patron groups by month
select date_trunc('month', __start) as month,
       count(*) as new_groups
    from library.patrongroup__
    where __current
    group by month
    order by month;

Compare states at different points in time:

-- Compare January vs December
with january as (
    select id, groupname, description
        from library.patrongroup__
        where '2023-01-01' >= __start 
          and '2023-01-01' < __end
),
december as (
    select id, groupname, description
        from library.patrongroup__
        where '2023-12-01' >= __start 
          and '2023-12-01' < __end
)
select j.id, j.description as jan_desc, 
       d.description as dec_desc
    from january j
    full outer join december d on j.id = d.id
    where j.description != d.description
       or j.description is null
       or d.description is null;

Identify data quality issues by examining change patterns:

-- Find records that change frequently (potential data issues)
select id, groupname, count(*) as change_count
    from library.patrongroup__
    group by id, groupname
    having count(*) > 10
    order by change_count desc;

Working with Date Ranges

When querying date ranges, use the recommended inequality pattern:

select *
    from folio_circulation.loan__t
    where '2023-01-01' <= loan_date and loan_date < '2024-01-01';

The pattern start <= t < end is the most reliable method for date range queries in Metadb.

This approach:

Includes the start date
Excludes the end date (making ranges easier to reason about)
Works correctly across time zones
Handles edge cases consistently

Historical Data and Transformations

Transformed tables also maintain full history:

-- Historical transformed data
select __start, __end, __current, id, groupname, description
    from library.patrongroup__t__
    order by id, __start;

-- Current transformed data
select id, groupname, description
    from library.patrongroup__t;

The naming convention:

Main transformed: table__t__ (with history)
Current transformed: table__t (current only)

Performance Considerations

Index on Temporal Columns

Metadb automatically creates indexes on __start, __end, and __current columns for efficient temporal queries.

Current Tables for Speed

When you don’t need history, use current tables for better performance. They’re optimized for current-state queries.

Partition Awareness

Main tables are partitioned internally by Metadb. The query planner automatically uses the most efficient partitions based on your temporal filters.

Best Practices

Default to current tables

Use current tables (table) for operational queries unless you specifically need historical data.

Use proper date range syntax

Always use the start <= t < end pattern for date range queries.

Filter early on temporal columns

When querying main tables, add __start and __end filters early in your WHERE clause to leverage indexes.

Consider query complexity

Historical queries can be complex. Test with small date ranges first, then expand once you verify the logic.

Monitoring Data Updates

Check when tables were last updated:

select schema_name, table_name, last_update, elapsed_real_time
    from metadb.table_update
    order by last_update desc;

This helps you understand data freshness and ensure your historical analysis includes the latest changes.

Limitations

Database views are not supported in Metadb. Views can create dependencies that block schema changes during data updates. Use database functions instead to encapsulate query logic.

Instead of views, create functions:

create function get_patron_groups_at(
    target_date timestamp with time zone
)
returns table(
    id integer,
    groupname text,
    description text
)
as $$
    select id, groupname, description
        from library.patrongroup__
        where target_date >= __start 
          and target_date < __end
$$
language sql
stable
parallel safe;

Then use it:

select * from get_patron_groups_at('2022-04-18 00:00:00');

Get Started

Core Concepts

User Guide

Administration

Historical Data

How Historical Tracking Works

Understanding Record History

Reading the History

Querying Historical Data

Current State Only

All History

Point-in-Time Queries

Date Range Queries

Change Tracking

Most Recent Changes

Temporal Analysis Use Cases

Working with Date Ranges

Historical Data and Transformations

Performance Considerations

Index on Temporal Columns

Current Tables for Speed

Partition Awareness

Best Practices

Monitoring Data Updates

Limitations

Build docs developers (and LLMs) love

Get Started

Core Concepts

User Guide

Administration

​How Historical Tracking Works

​Understanding Record History

​Reading the History

​Querying Historical Data

​Current State Only

​All History

​Point-in-Time Queries

​Date Range Queries

​Change Tracking

​Most Recent Changes

​Temporal Analysis Use Cases

​Working with Date Ranges

​Historical Data and Transformations

​Performance Considerations

Index on Temporal Columns

Current Tables for Speed

Partition Awareness

​Best Practices

​Monitoring Data Updates

​Limitations

Build docs developers (and LLMs) love

How Historical Tracking Works

Understanding Record History

Reading the History

Querying Historical Data

Current State Only

All History

Point-in-Time Queries

Date Range Queries

Change Tracking

Most Recent Changes

Temporal Analysis Use Cases

Working with Date Ranges

Historical Data and Transformations

Performance Considerations

Best Practices

Monitoring Data Updates

Limitations