Data Modeling for Performance

begin;

-- f1db has no FK constraints: invalid
-- foreign keys insert silently.
-- This is an insertion anomaly.
insert into f1db.results
  (resultid, raceid, driverid,
   constructorid, number,
   grid, positionorder,
   points, laps, statusid)
values
  (999999, 99999, 99999,
   99999, 0, 0, 0, 0, 0, 1);

select resultid, raceid, driverid
  from f1db.results
 where resultid = 999999;

rollback;

An INSERT with nonexistent foreign key values succeeds silently in f1db, which has no FK constraints.

begin;

-- Confirm existing contracts:
select driver_name, team, valid
  from lab.driver_contracts
 order by driver_name, lower(valid);

-- Attempt an overlapping contract.
-- The exclusion constraint rejects it.
\set ON_ERROR_STOP off
insert into lab.driver_contracts
  (driver_name, team, valid)
values
  ('Hamilton', 'RedBull',
   '[2020-01-01, 2026-01-01)');

rollback;

An overlapping driver contract insert is rejected by a tsrange exclusion constraint.

select case
         when length(t.name) < 54
         then t.name
         else substring(t.name from 1 for 54)
              || '...'
       end                    as track,
       ar.name                as artist,
       count(pt.playlist_id)  as playlists
  from chinook.playlist_track pt
       join chinook.track  t  using(track_id)
       join chinook.album  al using(album_id)
       join chinook.artist ar using(artist_id)
 group by t.track_id, t.name, ar.name
 order by playlists desc
 limit 5;

Top 5 Chinook tracks by playlist count, with artist name.

begin;

-- A partial index covers only US invoices —
-- smaller and faster for US-only queries.
create index chinook_invoice_usa_idx
    on chinook.invoice (customer_id)
 where billing_country = 'USA';

explain (analyze, buffers)
  select i.invoice_id, i.total
    from chinook.invoice i
   where i.billing_country = 'USA'
     and i.customer_id = 17;

rollback;

A partial index covers only the subset of rows a query targets, making it smaller and faster than a full index.

select g.name            as genre,
       c.country,
       date_trunc(
         'year', i.invoice_date
       )::date             as year,
       count(distinct i.invoice_id)
                           as invoices,
       sum(il.unit_price
           * il.quantity)
         ::numeric(10,2)   as revenue
  from chinook.invoice_line il
       join chinook.invoice  i using(invoice_id)
       join chinook.customer c using(customer_id)
       join chinook.track    t using(track_id)
       join chinook.genre    g using(genre_id)
 group by g.name, c.country,
          date_trunc('year', i.invoice_date)
 order by revenue desc
 limit 15;

A star join across one fact table and four dimension tables produces a multi-dimensional revenue breakdown in one query.

begin;

-- NOT VALID: skip scanning existing rows.
-- New inserts/updates are checked immediately.
alter table chinook.track
  add constraint track_ms_positive
  check (milliseconds > 0)
  not valid;

-- Second pass: validate existing rows
-- with a weaker lock (safe on large tables).
alter table chinook.track
  validate constraint track_ms_positive;

-- Verify new rows are now rejected:
do $$
begin
  insert into chinook.track
    (track_id, name, media_type_id,
     milliseconds, unit_price)
  values (99999, 'Silent', 1, -1, 0.99);
exception
  when check_violation then
    raise notice 'Caught: %', sqlerrm;
end;
$$;

rollback;

NOT VALID + VALIDATE CONSTRAINT adds a check constraint on a live table in two phases, each holding a weaker lock.