The Art of PostgreSQL/

...

/

More About Denormalization

More About Denormalization

Learn more about denormalization in PostgreSQL.

We'll cover the following...

Use case: Validity period as a range

A variant of the historic table requirement is when our application needs to process the data even after its date of validity. When doing financial analysis or accounting, it’s crucial to relate an invoice in a foreign currency to the valid exchange rate at the time of the invoice rather than the most current value of the currency.

Press + to interact
create table rates
 (
  currency text,
  validity daterange,
  rate     numeric,
  exclude using gist (currency with =,
                      validity with &&)
 );

An example of using this model follows:

Press + to interact
  select currency, validity, rate
    from rates
   where currency = 'Euro'
     and validity @> date '2017-05-18';

And here’s what the application would receive: a single line of data, of course, thanks to the exclude using constraint:

 currency │        validity         │   rate   
══════════╪═════════════════════════╪══════════
 Euro     │ [2017-05-18,2017-05-19) │ 1.240740
(1 row)

This query is kept fast thanks to the special GiST indexing, as we can see in the query plan:

Press + to interact
\pset format wrapped
\pset columns 57
 explain
  select currency, validity, rate
    from rates
   where currency = 'Euro'
     and validity @> date '2017-05-18';

Here is the output:

                       QUERY PLAN                        
------------------------------------
...