Customer address history in SQL: valid-from rows and the as-of join

Pilotbase showing database constraints used to protect customer history

An order shipped to 14 King Street in March should still say 14 King Street after the customer moves in June. If customers.address is overwritten, the old order silently changes meaning. The useful model is one immutable row per address version, with the time interval during which it was true.

This guide uses half-open intervals: valid_from is included and valid_to is excluded. A version ending at 10:00 and the next starting at 10:00 meet without overlapping, and an order at exactly 10:00 belongs to the new version.

The portable table

sql

create table customer_address_history (
  address_version_id bigint primary key,
  customer_id bigint not null,
  line1 varchar(200) not null,
  city varchar(100) not null,
  postal_code varchar(30) not null,
  valid_from timestamp not null,
  valid_to timestamp null,
  check (valid_to is null or valid_to > valid_from)
);

create index address_asof_idx
  on customer_address_history(customer_id, valid_from, valid_to);

NULL valid_to means current. The check catches a backwards or empty interval, but it cannot catch overlap with another row: that needs a multi-row constraint in PostgreSQL or a locked write procedure in MySQL and SQLite.

The as-of join

sql

select o.order_id, o.placed_at, a.line1, a.city, a.postal_code
from orders o
join customer_address_history a
  on a.customer_id = o.customer_id
 and a.valid_from <= o.placed_at
 and (a.valid_to > o.placed_at or a.valid_to is null);

That predicate is the whole meaning of an as-of join. Do not use BETWEEN: it includes both ends, so two adjacent versions both match the boundary timestamp. If this query returns two rows for one order, the history contains an overlap; if it returns none, it contains a gap or begins after the order.

PostgreSQL 18: make overlap impossible

sql

create extension if not exists btree_gist;

create table customer_address_history_pg (
  address_version_id bigint generated always as identity primary key,
  customer_id bigint not null,
  line1 text not null, city text not null, postal_code text not null,
  valid_at tstzrange not null,
  unique (customer_id, valid_at without overlaps)
);

-- the constraint's GiST index on (customer_id, valid_at) also serves
-- the as-of predicate: a.valid_at @> o.placed_at

PostgreSQL 18's WITHOUT OVERLAPS temporal unique constraint is equivalent to a GiST exclusion constraint on equality of customer_id and overlap of the range. The range must be non-empty. Use [) ranges such as tstzrange(start, finish, '[)'); an omitted upper bound is open-ended. An overlapping insert fails with SQLSTATE 23P01 (exclusion violation) and an empty range with 23514.

The constraint builds its own GiST index on (customer_id, valid_at), which is what @> lookups need; a second GiST index on the same columns added 150 MB in our test, so leave it out. For a backfill, load first and add the constraint after: 3,000,000 ranges took 156 s to insert into the constrained table, against 12 s to load an unconstrained copy plus 59 s to add the constraint, and the constraint's index came out at 187 MB instead of 301 MB. It still refused an overlapping range afterwards.

Change an address in one transaction

sql

begin;

-- lock the current version, then close it
select address_version_id
from customer_address_history
where customer_id = :customer_id and valid_to is null
for update;

update customer_address_history
set valid_to = :changed_at
where customer_id = :customer_id and valid_to is null;

insert into customer_address_history
  (address_version_id, customer_id, line1, city, postal_code, valid_from, valid_to)
values
  (:new_id, :customer_id, :line1, :city, :postal_code, :changed_at, null);

commit;

The lock serializes two changes for the same customer. In PostgreSQL, the range constraint is the final guard. In MySQL 8.4, run a SELECT ... FOR UPDATE overlap query inside the same transaction before inserting. A CHECK constraint cannot contain a subquery, so it cannot compare the new interval with existing rows. In PostgreSQL's default READ COMMITTED level, the second of two simultaneous changes can get zero rows back from that SELECT ... FOR UPDATE once the first commits: the old row no longer matches and the new one is outside its snapshot. The UPDATE that follows still sees and closes the new row, so don't treat an empty lock result as "no current address".

sql

-- MySQL 8.4: after locking this customer's rows
select address_version_id
from customer_address_history
where customer_id = ?
  and valid_from < coalesce(?, timestamp '9999-12-31 23:59:59')
  and coalesce(valid_to, timestamp '9999-12-31 23:59:59') > ?
for update;
-- refuse the insert if this returns a row

SQLite has one writer at a time. Start with BEGIN IMMEDIATE, run the same overlap predicate, close and insert, then commit. A trigger can package the check, but the transaction is still important: it makes the check and write one serialized operation. Store every timestamp in one text format: '2026-11-08 10:00:00' sorts before '2026-11-08T10:00:00Z', so mixing the space and T forms breaks the comparisons at equal times.

sql

begin immediate;
select address_version_id
from customer_address_history
where customer_id = :customer_id
  and valid_from < coalesce(:new_to, '9999-12-31T23:59:59Z')
  and coalesce(valid_to, '9999-12-31T23:59:59Z') > :new_from;
-- abort in the application if a row was returned; otherwise close + insert
commit;

Four checks before trusting the history

sql

-- 1. more than one current row
select customer_id, count(*) from customer_address_history
where valid_to is null group by customer_id having count(*) > 1;

-- 2. adjacent rows that overlap or leave a gap
with x as (
  select customer_id, valid_from, valid_to,
         lead(valid_from) over (partition by customer_id order by valid_from) next_from
  from customer_address_history
)
select * from x where valid_to is not null and valid_to <> next_from;

-- 3. orders with no historical address
select o.order_id from orders o left join customer_address_history a
  on a.customer_id=o.customer_id and a.valid_from<=o.placed_at
 and (a.valid_to>o.placed_at or a.valid_to is null)
where a.address_version_id is null;

The second query reports both overlaps and gaps; compare with < or > if gaps are allowed. Also test the exact boundary, a date before the first version, and two simultaneous address changes. Those cases find more bugs than a large happy-path load. We ran those cases on all three engines: after a change at exactly 10:00:00, 09:59:59 resolved to the old version and 10:00:00 to the new one; two simultaneous changes for one customer were serialized (the second waited about 1.5 s for the first's 2-second transaction) and left one current row with no gap. A plain insert of an overlapping version, without the write-path check, was accepted by the portable table in every engine and caught by check 1.

What it costs at 3,000,000 versions

We tested this schema on 8 November 2026 with 1,000,000 customers, exactly 3,000,000 address versions (200,000 customers each with one to five) and 1,000,000 orders, on a tport.cloud Server 4 GB (2 vCPU, 4 GB RAM, no swap, Ubuntu 24.04) in the India region. One engine ran at a time with a 1 GB cache each (shared_buffers, innodb_buffer_pool_size, PRAGMA cache_size) and the client on the same server. Cold is the first run after a restart with nothing cached; warm is the median of five runs after one warm-up.

3,000,000 versions	PostgreSQL 18.6, portable table	PostgreSQL 18.6, range + WITHOUT OVERLAPS	MySQL 8.4.13	SQLite 3.45.1
Load 3,000,000 versions	14.9 s + 4.6 s index	156.4 s	32.7 s + 8.6 s index	12.5 s + 1.9 s index
History table + as-of index on disk	294 MB + 122 MB	306 MB + 301 MB	288 MB + 101 MB	259 MB + 143 MB
As-of address for 10,000 orders, warm	95 ms	151 ms	145 ms	81 ms
Same, cold	10.5 s	8.2 s	10.7 s	13.4 s
As-of join over all 1,000,000 orders (count), warm	1.98 s	1.79 s	6.94 s	3.05 s
Same, cold	2.18 s	2.31 s	13.0 s	25.7 s
One order by id, median, warm	0.83 ms	0.77 ms	1.14 ms	0.02 ms*

* SQLite runs inside the client process; the PostgreSQL and MySQL figures include a round trip over a local socket.

For a slice of orders, every engine walked the (customer_id, valid_from) index once per order and filtered on valid_to. For the whole table, PostgreSQL switched to a parallel hash join and read the history sequentially, while MySQL and SQLite still made 1,000,000 index lookups, which is why their cold full-table runs were slower. Cold lookups are random reads: 10,000 orders took 8 to 13 s cold against 81 to 151 ms warm, so if as-of lookups sit on a request path, size the cache to hold the history index.

Keep the order's operational address too

History answers ‘what did the customer profile say then?’ It is not a substitute for an order snapshot. Checkout may normalize an address, the customer may choose a one-off delivery address, or support may correct the label after purchase. Store the actual ship-to fields (or an immutable snapshot id) on the order, and use customer history for profile auditing and reconstruction.

For slowly changing analytical dimensions, this is the same type-2 pattern. The difference is purpose: an operational table should enforce overlap at write time, while a warehouse load often stages rows and rejects bad intervals before the dimension swap.

Open the schema, run the boundary queries and inspect each version in one grid. Pilotbase works with PostgreSQL, MySQL, SQLite and more on Windows, macOS, Linux or Docker.

Get Pilotbase

Cleaning current addresses before you create the first history version? The address-normalisation guide handles punctuation, casing and comparable keys without overwriting the source.

Normalise addresses in SQL

Related: Merging duplicate customers without breaking orders. Sources checked and figures measured 8 November 2026: PostgreSQL 18 range types, CREATE TABLE and WITHOUT OVERLAPS, and constraints; MySQL 8.4 CHECK constraints, locking reads, and triggers; SQLite transactions, CREATE TRIGGER, and query planning.