Revitalizing Outdated Data Models with PostgreSQL Views

Newvick Lee

Software developer, Careteam Technologies

​

POSETTE | June 11-13, 2024

Newvick Lee

You’re using a complicated and/or outdated data model.

​

What do you do?

You’re using a complicated and/or outdated data model.

​

What do you do?

1. Refactor the data model and related code

​

You’re using a complicated and/or outdated data model.

​

What do you do?

• Refactor the data model and related code
• Give up and continue using it grudgingly

​

You’re using a complicated and/or outdated data model.

​

What do you do?

• Refactor the data model and related code
• Give up and continue using it grudgingly
• Something else?

Goal 🎯: Learn about PostgreSQL Views � and how to use them � to improve your data models

Outline

☐ Review an example case

☐ What is a PostgreSQL View and how does it help?

☐ Creating a View

☐ Updatable Views

☐ Using triggers to augment updates

☐ Useful parameters

☐ Views vs Materialized Views

Case study: A complicated data model� with multiple polymorphic � associations

🦆

🐓

🦆

🦆

🦆

🦆

Polymorphic

association

Polymorphic

association

How would you query for a chicken in a large barn?

Table chicken.id

Table large_barn.id

How would you query for a duck in a small barn?

Table duck.id

Table small_barn.id

Complications?

• Example of a potentially more complicated data model that could exist in practice�
• Ways it could be more difficult to work with:
• Many more animal types: Goat 🐐, Cow 🐄, Horse 🐎, …
• Many more barn types: Wood 🛖, Extra Large, …
• Other polymorphic associations in the same table

🐐

🐎

🐄

🦆

🐄

🐓

🦆

🦆

🐎

🐑

🐑

🐑

🐑

🐑

🦆

🦆

What is a PostgreSQL View?

• A virtual table that doesn’t store data (not physically materialized)
• A predefined SQL query that acts like a regular table
• Like a “shortcut”

What is a PostgreSQL View?

Application: Using PostgreSQL Views to� help with our example case

Querying for a chicken in a large barn

Creating a view for chicken_in_large_barn

Querying for chicken in the large barn

★

★

Original

Using a View

🆚

Querying for chicken in the large barn

Querying for chicken in the large barn

Outcome: Querying is much simpler now!

But… What about other actions?

insert/ update/ delete

Views are updatable

Automatically updatable, if they satisfy all of these conditions:

• Has exactly one entry in its FROM list
• Must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses
• Must not contain set operations (UNION, INTERSECT, EXCERPT)
• Select list must not contain any aggregates, window functions or set-returning functions

This is possible…

This is possible…

But animal_type and barn_type will be NULL

We can use a trigger to replace the insert on the view

We can use a trigger to replace the insert on the view

We can use a trigger to replace the insert on the view

We can use a trigger to replace the insert on the view

We can use a trigger to replace the insert on the View

Insight: What is the trigger function doing?

Insert now fills animal_type and barn_type columns

chicken_id

Insert now fills animal_type and barn_type columns

large_barn_id

Insight: The same method will work � for update and delete

Insight: Views can easily improve � a data model without affecting� the underlying tables

Useful View parameters

Useful View parameters

WITH [ CASCADED | LOCAL ] CHECK OPTION

• Controls behaviour of automatically updatable views
• If option is specified, INSERT and UPDATE commands on the view�are checked to ensure that new rows are visible through the view
• Example: Preventing insertion of chicken from Small_Barn into chicken_large_barn view

Useful View parameters

WITH ( view_option_name [= view_option_value] [, ... ] )

• security_barrier (boolean): allows view to provide row-level security
• security_invoker (boolean): checks the base relations against the privileges of the user of the view rather than the view owner

Lastly, Views vs Materialized Views

Goal 🎯: Learn about PostgreSQL Views and how � to use them to improve your data models

✅ Views are virtual

✅ Example case: create view as chicken_large_barn

✅ Can be automatically updatable (insert/update/delete)

✅ Can use trigger functions to augment updates

✅ Useful view parameters

✅ Views vs Materialized Views

You’re using a complicated and/or outdated data model.

​

What do you do now?

• Refactor the data model and related code
• Give up and continue using it grudgingly
• Use PostgreSQL Views and prosper!