← All schemas
Schema Library

Pagila Database Schema Diagram

Pagila is the PostgreSQL port of MySQL's Sakila DVD-rental sample database, designed to showcase Postgres features. Films describe titles with a full-text search column; inventory holds physical copies per store; rentals link inventory to customers and staff; payments record each rental's charge. Sixteen tables are mapped above. Pagila v4.1.1 partitions payment by month into 55 official partitions from payment_p2022_01 through payment_p2026_07; the 16-table diagram omits them for readability. The film table also carries a film_embedding vector column plus a tsvector fulltext column - the Postgres-specific additions that distinguish Pagila from Sakila.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Pagila pagila-v4.1.1, pagila-schema.sql (devrimgunduz/pagila) - a PostgreSQL port of MySQL's Sakila sample · License: MIT

Lineage: Pagila is a PostgreSQL port of MySQL's Sakila sample database, originally developed by Mike Hillyer of the MySQL AB documentation team. MySQL Sakila documentation · License: New BSD.

Tables
16
Core flow
Film -> Inventory -> Rental -> Payment
Version
4.1.1
Partitions
55 monthly

Pagila pagila-v4.1.1 (commit 9baf49c, 2026-09-20). Payment is range-partitioned by payment_date into 55 monthly partitions from payment_p2022_01 through payment_p2026_07; names and bounds match pagila-schema.sql.

This diagram omits those partitions, views, functions, and the source's unused bıgınt domain. For PostgreSQL 16, the exported DDL uses gen_random_uuid() and STORED instead of the PG18 uuidv7() and VIRTUAL, and includes CREATE EXTENSION vector for vector(20). pagila-schema.sql (pagila-v4.1.1).

Pagila database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

SQL export notes

  • Adapted for PostgreSQL 16 from a source that targets PostgreSQL 18: uuidv7() -> gen_random_uuid(), VIRTUAL -> STORED generated columns. Requires the pgvector extension (CREATE EXTENSION vector).
  • The official 55 monthly payment partitions (payment_p2022_01 through payment_p2026_07) are omitted from this 16-table diagram; names and bounds match pagila-schema.sql v4.1.1.

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/pagila">
  <img src="https://www.dbdiagramr.space/schemas/pagila-er-diagram.svg" alt="Pagila Database Schema Diagram (PostgreSQL)" width="2544" height="2022" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/pagila">Pagila Database Schema Diagram (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Pagila schema

ColumnTypeNullableKey
customer
customer_idintegerNoPK
store_idintegerNoFK → store.store_id
first_nametextNo-
last_nametextNo-
emailtextYes-
address_idintegerNoFK → address.address_id
activeboolbooleanNo-
create_datedateNo-
last_updatetimestamp with time zoneYes-
activeintegerYes-
uuiduuidNo-
actor
actor_idintegerNoPK
first_nametextNo-
last_nametextNo-
last_updatetimestamp with time zoneNo-
category
category_idintegerNoPK
nametextNo-
last_updatetimestamp with time zoneNo-
film
film_idintegerNoPK
titletextNo-
descriptiontextYes-
release_yearpublic.yearYes-
language_idintegerNoFK → language.language_id
original_language_idintegerYesFK → language.language_id
rental_durationsmallintNo-
rental_ratenumeric(4,2)No-
lengthsmallintYes-
replacement_costnumeric(5,2)No-
ratingpublic.mpaa_ratingYes-
last_updatetimestamp with time zoneNo-
special_featurestext[]Yes-
fulltexttsvectorNo-
length_hoursnumeric(4,2) GENERATED ALWAYS AS (round(length / 60.0, 2)) STOREDYes-
film_actor
actor_idintegerNoPK, FK → actor.actor_id
film_idintegerNoPK, FK → film.film_id
last_updatetimestamp with time zoneNo-
film_category
film_idintegerNoPK, FK → film.film_id
category_idintegerNoPK, FK → category.category_id
last_updatetimestamp with time zoneNo-
film_embedding
film_idintegerNoPK, FK → film.film_id
embeddingpublic.vector(20)No-
last_updatetimestamp with time zoneNo-
address
address_idintegerNoPK
addresstextNo-
address2textYes-
districttextNo-
city_idintegerNoFK → city.city_id
postal_codetextYes-
phonetextNo-
last_updatetimestamp with time zoneNo-
city
city_idintegerNoPK
citytextNo-
country_idintegerNoFK → country.country_id
last_updatetimestamp with time zoneNo-
country
country_idintegerNoPK
countrytextNo-
last_updatetimestamp with time zoneNo-
inventory
inventory_idintegerNoPK
film_idintegerNoFK → film.film_id
store_idintegerNoFK → store.store_id
last_updatetimestamp with time zoneNo-
language
language_idintegerNoPK
nametextNo-
last_updatetimestamp with time zoneNo-
payment
payment_idintegerNoPK
customer_idintegerNoFK → customer.customer_id
staff_idintegerNoFK → staff.staff_id
rental_idintegerNoFK → rental.rental_id
amountnumeric(5,2)No-
payment_datetimestamp with time zoneNoPK
uuiduuidNo-
rental
rental_idintegerNoPK
rental_datetimestamp with time zoneNo-
inventory_idintegerNoFK → inventory.inventory_id
customer_idintegerNoFK → customer.customer_id
return_datetimestamp with time zoneYes-
staff_idintegerNoFK → staff.staff_id
last_updatetimestamp with time zoneNo-
uuiduuidNo-
staff
staff_idintegerNoPK
first_nametextNo-
last_nametextNo-
address_idintegerNoFK → address.address_id
emailtextYes-
store_idintegerNoFK → store.store_id
activebooleanNo-
usernametextNo-
passwordtextYes-
last_updatetimestamp with time zoneNo-
picturebyteaYes-
store
store_idintegerNoPK
manager_staff_idintegerNo-
address_idintegerNoFK → address.address_id
last_updatetimestamp with time zoneNo-

Table relationships

20 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

store.manager_staff_id points to staff.staff_id by name, but pagila-schema.sql declares no foreign-key constraint on that column.

One-to-many

customer.address_id → address.address_id

One address can have many customers (customer.address_id -> address.address_id, required).

One-to-many

customer.store_id → store.store_id

One store can have many customers (customer.store_id -> store.store_id, required).

One-to-many

film.language_id → language.language_id

One language can have many films (film.language_id -> language.language_id, required).

One-to-many

film.original_language_id → language.language_id

One language can have many films (film.original_language_id -> language.language_id, optional).

Many-to-many

film_actor (actor_id -> actor.actor_id, film_id -> film.film_id)

Many actors can have many films through film_actor (film_actor.actor_id -> actor.actor_id, film_actor.film_id -> film.film_id).

Many-to-many

film_category (category_id -> category.category_id, film_id -> film.film_id)

Many categories can have many films through film_category (film_category.category_id -> category.category_id, film_category.film_id -> film.film_id).

One-to-one

film_embedding.film_id → film.film_id

Each film embedding has one film (film_embedding.film_id -> film.film_id, required).

One-to-many

address.city_id → city.city_id

One city can have many addresses (address.city_id -> city.city_id, required).

One-to-many

city.country_id → country.country_id

One country can have many cities (city.country_id -> country.country_id, required).

One-to-many

inventory.film_id → film.film_id

One film can have many inventories (inventory.film_id -> film.film_id, required).

One-to-many

inventory.store_id → store.store_id

One store can have many inventories (inventory.store_id -> store.store_id, required).

One-to-many

payment.customer_id → customer.customer_id

One customer can have many payments (payment.customer_id -> customer.customer_id, required).

One-to-many

payment.staff_id → staff.staff_id

One staff can have many payments (payment.staff_id -> staff.staff_id, required).

One-to-many

payment.rental_id → rental.rental_id

One rental can have many payments (payment.rental_id -> rental.rental_id, required).

One-to-many

rental.customer_id → customer.customer_id

One customer can have many rentals (rental.customer_id -> customer.customer_id, required).

One-to-many

rental.inventory_id → inventory.inventory_id

One inventory can have many rentals (rental.inventory_id -> inventory.inventory_id, required).

One-to-many

rental.staff_id → staff.staff_id

One staff can have many rentals (rental.staff_id -> staff.staff_id, required).

One-to-many

staff.address_id → address.address_id

One address can have many staffs (staff.address_id -> address.address_id, required).

One-to-many

staff.store_id → store.store_id

One store can have many staffs (staff.store_id -> store.store_id, required).

One-to-many

store.address_id → address.address_id

One address can have many stores (store.address_id -> address.address_id, required).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Revenue by category

Sum payments through rentals and film categories.

select c.name, sum(p.amount) as revenue
from payment p
join rental r on r.rental_id = p.rental_id
join inventory i on i.inventory_id = r.inventory_id
join film_category fc on fc.film_id = i.film_id
join category c on c.category_id = fc.category_id
group by c.name
order by revenue desc;

Most featured actors

Count films for each actor.

select a.first_name || ' ' || a.last_name as actor,
       count(*) as films
from actor a
join film_actor fa on fa.actor_id = a.actor_id
group by a.actor_id, a.first_name, a.last_name
order by films desc, actor;

Overdue rentals

Unreturned rentals older than seven days.

select c.first_name || ' ' || c.last_name as customer,
       f.title, r.rental_date
from rental r
join customer c on c.customer_id = r.customer_id
join inventory i on i.inventory_id = r.inventory_id
join film f on f.film_id = i.film_id
where r.return_date is null
  and r.rental_date < now() - interval '7 days'
order by r.rental_date;

Revenue by store

Sum payments by the staff member's store.

select s.store_id, sum(p.amount) as revenue
from payment p
join staff st on st.staff_id = p.staff_id
join store s on s.store_id = st.store_id
group by s.store_id
order by s.store_id;

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →