Sample dataset: Pagila

Aiven provides a sample database you can import in your Aiven for PostgreSQL service. This page covers information about the database and the procedure to get it up and running.

Pagila is a PostgreSQL port of the Sakila Sample Database. The examples here use one from devrimgunduz, version 2.1.0.

Sakila (and thus, Pagila) is a database representing a DVD rental store (remember those?!), containing information about films (like title, category, actresses), rental stores (like address, staff members, customers) and rentals, where a customer rents a film from a store through its staff.

With all these relational information, Pagila is a perfect fit to play around with PostgreSQL and the SQL language.

Load Pagila via the web console

Before exploring the Pagila database, follow the create new service article to spin up a PostgreSQL instance.

With your new PostgreSQL service, you can quickly load the sample dataset by clicking on the “Load sample dataset” button.

A screenshot of the Aiven Console showing the "Load sample dataset" button.

After loading the data, connect to the PostgreSQL instance using the command below. The SERVICE_URI value can be found in the Aiven Console dashboard.

psql 'SERVICE_URI'

Load Pagila manually

  1. Download the pagila-data.sql from our GitHub repository.

Tip

You may use the following command on your terminal:

wget https://raw.githubusercontent.com/aiven/devportal/main/code/products/postgresql/pagila/pagila-data.sql
  1. Connect to the PostgreSQL instance using the following command. The SERVICE_URI value can be found in the Aiven Console dashboard.

psql 'SERVICE_URI'
  1. Within the psql shell, create a database named pagila and connect to it with the command below:

CREATE DATABASE pagila;
\c pagila;
  1. Populate the database with the command below. This might take some time.

\i pagila-data.sql;
  1. Once the command finishes, make sure to reconnect to the database to access the imported data:

\c pagila;

You are ready to go! You can use the Sample queries section below to explore the database. Have fun!

Entity-relationship model diagram

The image below shows an overview of the Pagila database tables and views, generated by DBeaver.

Here you can see how the tables relate to each other, through each fields. For example, the film table has string columns like title and description. It also relates to the table language with the columns language_id and original_language_id. With that information, you know that you can join both tables to get the language of each film, or to list all films for a specific language.

A entity-relation model diagram for the Pagila databases, containing all the tables, fields and views.

Sample queries

Let’s explore the dataset with a few queries. All the queries results were limited by the first 10 items.

Ready for a challenge?

After playing around with the sample queries, can you use SQL statements to answer some of these questions?

  1. What is the total revenue of each rental store?

  1. Can you list the top 5 film genres by their gross revenue?

  1. The film.description has the text type, allowing for full text search queries, what will you search for?

Clean up

To clean up the environment and destroy the database, run the following commands:

\c defaultdb;
DROP DATABASE pagila;

Source

The source code for the Pagila database is available from our repository.