1 Demonstration Database

Explore the Airlines demonstration database: its schema, bookings, tickets, flights, routes, and the data used throughout the PostgreSQL 18 examples.

1. General Information

To work with more complex queries, we need a substantial database: nine tables filled with realistic data. We chose airline travel as the subject area. The database contains information about flights operated by an imaginary airline over a certain period of time.

The database schema is shown below. We tried to make it neither too complicated nor too simple: simple enough to avoid unnecessary details, but rich enough to support meaningful and interesting queries.

The database is available on the website in four sizes. This book on query optimization uses the September 1, 2025 version, about 2.7 GB in size, containing six months of flight data.

You can check the version with the following function:

SELECT bookings.version();
              version
-----------------------------------
 PostgresPro 2025-09-01 (181 days)
(1 row)

Figure 1.1 Airlines Database Schema

2. Main Entities

The central entity is a booking (bookings).

SELECT * FROM bookings b WHERE b.book_ref = '03RUKL';
 book_ref |           book_date           | total_amount
----------+-------------------------------+--------------
 03RUKL   | 2026-01-04 08:01:43.367932+03 |     84850.00
(1 row)

The row gives us the booking date and the total amount. Against the real clock, the booking gets older every day:

SELECT now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';
         ?column?
--------------------------
 264 days 09:13:04.888436
(1 row)

The demo database, however, has a fixed “current” time:

SELECT bookings.now();
          now
------------------------
 2026-03-01 03:00:00+03
(1 row)

Relative to that clock, the booking is much younger:

SELECT bookings.now() - b.book_date
FROM bookings b
WHERE b.book_ref = '03RUKL';
        ?column?
-------------------------
 55 days 18:58:16.632068
(1 row)

A booking can include several passengers. Each passenger is issued a separate ticket (tickets) for the outbound journey and, if needed, another one for the return journey.

The selected booking has these tickets:

SELECT t.ticket_no, t.passenger_id, t.passenger_name
FROM bookings b
  JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_ref = '03RUKL';
   ticket_no   |   passenger_id   |   passenger_name
---------------+------------------+--------------------
 0005435998767 | RU 3761090132061 | Evgeniy Kondratyev
 0005435998770 | RU 3761090132061 | Evgeniy Kondratyev
 0005435998774 | RU 5595520101762 | Oleg Golubev
 0005435998776 | RU 5595520101762 | Oleg Golubev
(4 rows)

A ticket contains one segment (segments) if there is a direct flight between the departure point and the destination, or several segments if the journey requires connections.

What segments do our passengers travel? It is enough to look at one passenger, because all passengers in the same booking follow the same itinerary:

SELECT s.*
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
WHERE t.ticket_no = '0005435998767';
   ticket_no   | flight_id | fare_conditions |  price
---------------+-----------+-----------------+----------
 0005435998767 |     17386 | Economy         |  6750.00
 0005435998767 |     17606 | Economy         | 14500.00
(2 rows)

This was the outbound ticket. Compare it with the return ticket:

   ticket_no   | flight_id | fare_conditions |  price
---------------+-----------+-----------------+---------
 0005435998770 |     26693 | Comfort         | 3575.00
 0005435998770 |     27013 | Comfort         | 5525.00
 0005435998770 |     27375 | Economy         | 2750.00
 0005435998770 |     27537 | Economy         | 8250.00
(4 rows)

The outbound and return itineraries do not have to match: on the way back, Evgeniy has three connections, and two of the flights (flights) are in Comfort class.

Each flight follows a route (routes) from one airport to another. Routes with the same route number have the same departure and arrival airports, while individual flights differ by departure date.

To find out which routes are behind the selected flights, we have to account for the temporal relationship between these tables. The schedule changes from time to time, so the join must match not only the route number but also the route’s validity interval to the flight’s departure time.

SELECT r.route_no, f.scheduled_departure, f.status,
  r.departure_airport dep, r.arrival_airport arr
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN flights f ON f.flight_id = s.flight_id
  JOIN routes r ON r.route_no = f.route_no
               AND r.validity @> f.scheduled_departure
WHERE t.ticket_no = '0005435998767'
ORDER BY f.scheduled_departure;
 route_no |  scheduled_departure   | status  | dep | arr
----------+------------------------+---------+-----+-----
 PG0177   | 2026-01-05 15:05:00+03 | Arrived | SVO | AER
 PG0587   | 2026-01-06 19:00:00+03 | Arrived | AER | LHR
(2 rows)

The timetable (timetable) view hides this complexity. Like a flight information board, it shows the information we need while hiding the join between the two tables:

SELECT tt.route_no, tt.scheduled_departure, tt.status,
  tt.departure_airport dep, tt.arrival_airport arr
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN timetable tt ON tt.flight_id = s.flight_id
WHERE t.ticket_no = '0005435998770'
ORDER BY tt.scheduled_departure;
 route_no |  scheduled_departure   |  status   | dep | arr
----------+------------------------+-----------+-----+-----
 PG0272   | 2026-02-27 06:25:00+03 | Arrived   | LHR | CDG
 PG0781   | 2026-03-01 02:50:00+03 | Departed  | CDG | MUC
 PG0358   | 2026-03-03 03:50:00+03 | Scheduled | MUC | VIE
 PG0165   | 2026-03-04 02:20:00+03 | Scheduled | VIE | SVO
(4 rows)

Both outbound flights and one return flight have already arrived (status Arrived). At the demo database’s current time, the passengers are flying from CDG to MUC (status Departed). Check-in has not yet opened for the remaining flights (status Scheduled).

Airports (airports) use the standard three-letter airport code as their key. Here is the full record for one airport:

SELECT * FROM airports WHERE airport_code = 'CDG' \gx
-[ RECORD 1 ]+------------------
airport_code | CDG
airport_name | Charles de Gaulle
city         | Paris
country      | France
coordinates  | (2.55,49.0128)
timezone     | Europe/Paris

Here is the route we are interested in:

SELECT lower(r.validity) valid_from,
  upper(r.validity) valid_to, r.airplane_code,
  r.days_of_week, r.scheduled_time, r.duration
FROM routes r
WHERE r.route_no = 'PG0781'
  AND r.validity @> '2026-03-01 02:50:00+03'::timestamptz \gx
-[ RECORD 1 ]--+-----------------------
valid_from     | 2026-02-01 03:00:00+03
valid_to       | 2026-03-01 03:00:00+03
airplane_code  | CR7
days_of_week   | {1,2,3,4,5,6,7}
scheduled_time | 00:50:00
duration       | 01:25:00

During this validity period, the route operates seven days a week and uses a CR7 airplane (airplanes):

SELECT * FROM airplanes WHERE airplane_code = 'CR7';
 airplane_code |       model       | range | speed
---------------+-------------------+-------+-------
 CR7           | Bombardier CRJ700 |  3100 |   829
(1 row)

The number of seats (seats) and their distribution among fare classes depend on the airplane model used for the flight. In the demo database, all airplanes of the same model have the same cabin configuration.

For example, here is the first row:

SELECT s.*
FROM airplanes a
  JOIN seats s ON s.airplane_code = a.airplane_code
WHERE a.airplane_code = 'CR7'
  AND s.seat_no ~ '^1.$';
 airplane_code | seat_no | fare_conditions
---------------+---------+-----------------
 CR7           | 1A      | Business
 CR7           | 1C      | Business
 CR7           | 1D      | Business
(3 rows)

These are Business-class seats. Here is the total number of seats in each fare class on this airplane:

SELECT s.fare_conditions, count(*)
FROM seats s
WHERE s.airplane_code = 'CR7'
GROUP BY s.fare_conditions;
 fare_conditions | count
-----------------+-------
 Business        |     6
 Comfort         |    12
 Economy         |    52
(3 rows)

Here is the complete record for one flight on the selected route. Actual times may differ from the schedule: usually not by much (the departure in this case was delayed by six minutes), but sometimes by several hours:

SELECT * FROM flights f
WHERE f.flight_id = 27013 \gx
-[ RECORD 1 ]-------+----------------------------
flight_id           | 27013
route_no            | PG0781
status              | Departed
scheduled_departure | 2026-03-01 02:50:00+03
scheduled_arrival   | 2026-03-01 04:15:00+03
actual_departure    | 2026-03-01 02:56:02.8521+03
actual_arrival      |

When a passenger checks in for the first flight, boarding passes (boarding_passes) are issued for all flights on the ticket. A boarding pass specifies a seat. The combination of flight and seat is unique: two passengers cannot occupy the same seat. At boarding, the pass is stamped with the passenger’s boarding sequence number and the time they passed through the gate.

Which seats has our passenger occupied, or will occupy?

SELECT f.status, f.flight_id, bp.seat_no, bp.boarding_no
FROM tickets t
  JOIN segments s ON s.ticket_no = t.ticket_no
  JOIN flights f ON f.flight_id = s.flight_id
  LEFT JOIN boarding_passes bp
       ON bp.ticket_no = s.ticket_no
      AND bp.flight_id = s.flight_id
WHERE t.ticket_no IN ('0005435998767','0005435998770')
ORDER BY f.scheduled_departure;
  status   | flight_id | seat_no | boarding_no
-----------+-----------+---------+-------------
 Arrived   |     17386 | 50A     |          41
 Arrived   |     17606 | 21D     |          16
 Arrived   |     26693 | 11A     |         176
 Departed  |     27013 | 7C      |          49
 Scheduled |     27375 | 45D     |
 Scheduled |     27537 | 13B     |
(6 rows)

The demo database can display airport, city, country, and airplane names in different languages. The display language is controlled by a configuration parameter set at the database level:

SHOW bookings.lang;
 bookings.lang
---------------
 en
(1 row)

Switch the current session to Russian:

SET bookings.lang = 'ru';
SELECT * FROM airports a WHERE a.airport_code = 'SVO' \gx
-[ RECORD 1 ]+------------------
airport_code | SVO
airport_name | Шереметьево
city         | Москва
country      | Россия
coordinates  | (37.4146,55.9726)
timezone     | Europe/Moscow

The implementation uses views over base tables that store translations as JSON:

SELECT ml.airport_name, ml.country, ml.city
FROM airports_data ml
WHERE ml.airport_code = 'SVO' \gx
-[ RECORD 1 ]+--------------------------------------------
airport_name | {"en": "Sheremetyevo", "ru": "Шереметьево"}
country      | {"en": "Russia", "ru": "Россия"}
city         | {"en": "Moscow", "ru": "Москва"}

To summarize, here are the tables and views in the demo database:

\dtv
               List of relations
  Schema  |      Name       | Type  |  Owner
----------+-----------------+-------+----------
 bookings | airplanes       | view  | postgres
 bookings | airplanes_data  | table | postgres
 bookings | airports        | view  | postgres
 bookings | airports_data   | table | postgres
 bookings | boarding_passes | table | postgres
 bookings | bookings        | table | postgres
 bookings | flights         | table | postgres
 bookings | routes          | table | postgres
 bookings | seats           | table | postgres
 bookings | segments        | table | postgres
 bookings | tickets         | table | postgres
 bookings | timetable       | view  | postgres
(12 rows)