# Pagila: the Sakila sample database ported to PostgreSQL, with JSONB and vector extras

> Pagila is a port of MySQL's Sakila example schema to PostgreSQL, with real booleans, foreign keys, built-in full text search and JSONB, plus recent work on SQL/JSON query functions, pgvector and partitioning. The Docker compose stack starts a database and pgAdmin in one command.

**devrimgunduz/pagila** — PostgreSQL Sample Database

- Repository: https://github.com/devrimgunduz/pagila
- Stars: 1,142 · Forks: 378
- Language: PLpgSQL
- License: PostgreSQL
- Published: 2026-10-19 · Updated: 2026-10-19 · Language: en
- Canonical page: https://hysenlabs.com/projects/devrimgunduz-pagila

## A port, and what changed on the way over

Pagila started as a port of the Sakila example database for MySQL, originally developed by Mike Hillyer of the MySQL AB documentation team. The stated purpose is to provide a standard schema for examples in books, tutorials, articles and samples, which makes it one of the few sample databases where the schema itself is the product.

The port is faithful in structure and opinionated in detail, and the differences are the interesting part. The char(1) true and false fields became real boolean fields. The last_update columns are set with triggers rather than by the application. Foreign keys were added, and the DEFAULT 0 on those foreign keys was removed since it is pointless with real constraints. Full text search uses the PostgreSQL built-in, which removed the need for the separate film_text table that Sakila carries. The rewards_report function was ported to a simple set-returning function. And JSONB data was added, which is the one addition that goes beyond making Sakila idiomatic.

That combination is the argument for using it over the original. A tutorial written against Sakila teaches you MySQL's string-as-boolean idiom and its missing constraints. The same tutorial against Pagila teaches you the behaviour you would actually ship.

The database is made available under the PostgreSQL licence, and the project states it has been tested against PostgreSQL 18 and above. The repository is not archived, the last push was on 2026-10-08, and the release history shows active development: 4.1.1 removing SQL/PGQ in September 2026, 4.1.0 adding more SQL features in August, and a 4.0.0 major overhaul in July.

## Running it with Docker and pgAdmin side by side

The compose file is the fastest way to a working database, and it wires the schema and data in as init scripts so the database is populated on first start.

```yaml
services:
  pagila:
    build: .
    container_name: pagila
    environment:
      POSTGRES_PASSWORD: 123456
      POSTGRES_USER: postgres
      POSTGRES_DB: pagila
    volumes:
      - ./pagila-schema.sql:/docker-entrypoint-initdb.d/1-pagila-schema.sql
      - ./pagila-schema-jsonb.sql:/docker-entrypoint-initdb.d/1-pagila-schema-jsonb.sql
      - ./pagila-data.sql:/docker-entrypoint-initdb.d/2-pagila-data.sql
      - ./restore-pagila-data-jsonb.sh:/docker-entrypoint-initdb.d/3-restore-pagila-data-jsonb.sh
      - ./pg_partman-setup.sql:/docker-entrypoint-initdb.d/4-pg-partman-setup.sql
    ports:
      - 5432:5432
```

Port 5432 is published, so the database is reachable from the host. The volumes named pgdata and the mount to /var/lib/postgresql hold the data, and there is a scripts directory in the repository alongside the SQL files.

pgAdmin is included as a second service in the same compose file, on port 5050, with the default credentials admin@admin.com and root, and a servers.json mounted in from the pgadmin directory so the server is pre-registered. The project documentation points at http://localhost:5050/ for the admin interface, which is a genuinely useful inclusion: you get a GUI to inspect the schema and run the example queries without setting up a client.

The Dockerfile shows what the base is. It starts from postgres:18 and installs two extensions from the PGDG apt repository, which the base image already has configured, so they are installed as packages rather than built from source: postgresql-18-partman and postgresql-18-pgvector.

## The example query and the full text search that replaced film_text

The example query in the documentation joins five tables to find late rentals, which is the classic Sakila exercise and doubles as a demonstration of the schema's relationships.

```sql
SELECT
	CONCAT(customer.last_name, ', ', customer.first_name) AS customer,
	address.phone,
	film.title
FROM
	rental
	INNER JOIN customer ON rental.customer_id = customer.customer_id
	INNER JOIN address ON customer.address_id = address.address_id
	INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
	INNER JOIN film ON inventory.film_id = film.film_id
WHERE
	rental.return_date IS NULL
	AND rental_date < CURRENT_DATE
ORDER BY
	title
LIMIT 5;
```

The rental to inventory to film path is the part worth noting, because Sakila models copies of a film in inventory and rentals of those copies. A query written against a naive film table would get the row count wrong.

Full text search is built into PostgreSQL, so the relevant parts of the schema live in the main schema file rather than in a separate table:

```sql
SELECT * FROM film WHERE fulltext @@ to_tsquery('fate&india');
```

The operator is the @@ one, and the query string uses the ampersand to require both terms. The removal of the Sakila film_text table is a real structural difference, and it means the search column lives on the film row itself rather than in a shadow table, so joins are simpler than the MySQL original.

There is also a schema diagram, pagila-schema-diagram.png, at the top level, which is the fastest way to see the whole shape of the database before running anything.

## Two different JSONB examples, and they are not interchangeable

Pagila carries two separate JSONB examples and the documentation is careful to say they are unrelated.

The first is the actor_info view, which lives inside the DVD rental domain itself. For each actor it aggregates their films into a JSONB object keyed by category, so a single row holds something like a Games key with a list of film titles, a Drama key, and so on. It is presented as a good place to get familiar with the JSONB operators:

```sql
SELECT actor_id, first_name, last_name, film_info -> 'Games' AS games
FROM actor_info
WHERE film_info -> 'Games' ? 'ACADEMY DINOSAUR';
```

The -> operator extracts a value, and ? checks whether a key or string is present. Because the categories become keys of one object, the question you can ask is whether this actor has a film in this category, which is a lookup the relational version of the schema would need a join for.

The second example is a set of separate files, pagila-schema-jsonb.sql along with pagila-data-yum-jsonb.backup and pagila-data-apt-jsonb.backup, holding real package metadata from apt.postgresql.org and yum.postgresql.org. The documentation describes it as a separate, unrelated JSONB example, two tables with real metadata on a different shape of data, useful for practising JSONB on something that is not the rental domain. The load script is restore-pagila-data-jsonb.sh, and the compose file mounts it into the init directory.

So when you read about a JSONB example in Pagila, check which one is meant. The actor_info view is a single aggregated object per row, and the package metadata tables are a different schema entirely.

## SQL/JSON query functions, and a documented bug in JSON_SERIALIZE

PostgreSQL 17 added the SQL-standard SQL/JSON query functions, and Pagila has been extended to exercise them. The documentation says all of the example queries were run against actor_info.film_info, which makes this section a practical test harness for the new syntax rather than a separate toy schema.

JSON_EXISTS checks whether a path matches anything without pulling the data back, JSON_VALUE pulls out a single scalar, and JSON_QUERY pulls out an object or array:

```sql
SELECT actor_id, first_name, last_name,
       JSON_VALUE(film_info, '$.Games[0]') AS first_game_film,
       JSON_QUERY(film_info, '$.Games') AS games_films
FROM actor_info
WHERE JSON_EXISTS(film_info, '$.Games');
```

JSON_TABLE turns JSON into rows, and the documentation singles out NESTED PATH as where it earns its keep, because it flattens every category for an actor into category and title rows in a single query without needing a jsonb_each plus jsonb_array_elements combination. The constructor functions go the other way, building or validating JSON rather than querying it, with JSON_SCALAR wrapping a plain SQL value, JSON_SERIALIZE rendering JSON back out as text, and JSON parsing a text payload with the option to reject duplicate keys, which raises an error on a duplicate JSON object key.

The most useful thing in the section is a bug note. As tested against 17.10, 18.4 and 19beta2, JSON_SERIALIZE mishandles a jsonb-typed argument passed directly and silently returns 1 byte of garbage. Casting to json first avoids it. 

Silent garbage is the kind of defect that will cost an afternoon if you hit it without the note, so it is worth reading the caveats in the documentation before building on these functions.

## pgvector, a partition manager, and files that are easy to miss

pgvector is not bundled with PostgreSQL, so the compose stack installs it, and the schema runs CREATE EXTENSION vector. The film_embedding table gives each film a vector(20) with a deliberately inspectable shape: dimensions 1 to 16 are a multi-hot encoding of the film's categories, and 17 to 20 are length, rental_rate, replacement_cost and rental_duration, each min-max normalized to the range zero to one.

The documentation is careful that these are not real text embeddings from a language model, because there is no plot-summary model in the dataset to run. They are engineered from data already in the film table, which is the point: nearest-neighbor search returns films that genuinely share categories and attributes, so the results can be sanity-checked by eye rather than trusted blindly.

pgvector adds three distance operators, <-> for Euclidean, <#> for negative inner product, and <=> for cosine distance, with cosine the natural fit here because it ignores magnitude and compares direction, so a cheap short film and an expensive long one in the same categories land close together.

Alongside that there is pg_partman, a partitioning extension, with its own setup file pg_partman-setup.sql mounted in the init directory, and a separate pagila-temporal-setup.sql for temporal tables. Those two files are easy to miss when you read the top level listing, and the compose file mounts pg_partman-setup.sql, so its contents land in your database whether or not you looked at it.

One limitation worth stating: the project says it has been tested against PostgreSQL 18 and above, so a team on an earlier major version should not assume the schema and the extension setup will apply unchanged.

## Conclusion

Pagila fits a developer who wants a realistic multi-table PostgreSQL schema to write tutorials, book examples, article queries or test code against, especially if the subject is PostgreSQL specific features such as JSONB, JSON_TABLE or pgvector. It is a poor fit as a schema to build on, since it exists to be queried, not extended, and it needs a recent PostgreSQL release, since the project states it has been tested against PostgreSQL 18 and above and the compose file builds on the postgres:18 image. Before loading it, check which of the extra files you actually want, since the compose file mounts the JSONB, pg_partman and vector pieces into the init directory alongside the base schema and data.

## FAQ

### What is Pagila and what is it based on?

Pagila is a PostgreSQL sample database ported from the Sakila example database for MySQL, which was originally developed by Mike Hillyer of the MySQL AB documentation team. It provides a standard schema for examples in books, tutorials, articles and samples.

### How do I run Pagila in Docker?

The repository ships a docker-compose.yml that builds the image from a Dockerfile based on postgres:18 and mounts the schema and data SQL files into the container init directory, so the database is populated on first start. It publishes port 5432 and includes a pgAdmin service on port 5050 with the credentials admin@admin.com and root.

### How does Pagila differ from the original MySQL Sakila schema?

The char(1) true and false fields were changed to real boolean fields, the last_update columns are set with triggers, foreign keys were added with the pointless DEFAULT 0 removed, PostgreSQL built-in full text search replaced the separate film_text table, the rewards_report function was ported to a simple set-returning function, and JSONB data was added.

### Does Pagila include pgvector and pg_partman?

Not by default in PostgreSQL itself. The Dockerfile installs postgresql-18-pgvector and postgresql-18-partman from the PGDG apt repository, and the schema runs CREATE EXTENSION vector, which gives the film_embedding table a vector(20) per film with the first 16 dimensions a multi-hot encoding of the categories.

### What is the actor_info view in Pagila used for?

It aggregates each actor's films into a JSONB object keyed by category, for example a Games key holding a list of film titles. It is a convenient place to practise the JSONB operators, since the -> operator extracts a value from the object and the ? operator checks whether a key or string is present.

## Sources

- [devrimgunduz/pagila on GitHub](https://github.com/devrimgunduz/pagila)
- [Issues](https://github.com/devrimgunduz/pagila/issues)
- [License: PostgreSQL](https://github.com/devrimgunduz/pagila/blob/master/LICENSE)
- [README](https://github.com/devrimgunduz/pagila/blob/master/README.md)
- [Releases](https://github.com/devrimgunduz/pagila/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/devrimgunduz-pagila