# Demonstration Database
> Explore the Airlines demonstration database: its schema, bookings, tickets, flights, routes, and the data used throughout the PostgreSQL 18 examples.
---
LLMS index: [llms.txt](/en/llms.txt)
---
## 1. General Information {#qpt-section-1-1}
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:
```sql
SELECT bookings.version();
version
-----------------------------------
PostgresPro 2025-09-01 (181 days)
(1 row)
```
{#qpt-fig-1-1 num="1.1" caption="Airlines Database Schema" .book-original-figure .d-flex .flex-column .align-items-center width=681 height=421}
## 2. Main Entities {#qpt-section-1-2}
The central entity is a **booking** (`bookings`).
```sql
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:
```sql
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:
```sql
SELECT bookings.now();
now
------------------------
2026-03-01 03:00:00+03
(1 row)
```
Relative to that clock, the booking is much younger:
```sql
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:
```sql
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:
```sql
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:
```text
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.
```sql
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:
```sql
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:
```sql
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:
```sql
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`):
```sql
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:
```sql
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:
```sql
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:
```sql
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?
```sql
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:
```sql
SHOW bookings.lang;
bookings.lang
---------------
en
(1 row)
```
Switch the current session to Russian:
```sql
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:
```sql
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:
```text
\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)
```