Optimizing Database Performance with Views Functions and Triggers in PostgreSQL

Introduction

Views, Functions, and Triggers are important tools that are used to maintain databases and ensure that information is kept under wraps without interference.

Let's start with the definition of each term.

Views

Views are a virtual table that is created from a saved query. They prevent tampering or interference with data and ensure that any adjustment made is done with permission from the database administrator. They also reduce repetition and increase the simplicity of your code.

The syntax of Views are as follows:

create or replace view view_name as
select column1,column_2
from your_dataset_in_your_table

For example:
Let's say we want to change the information of a particular sales department in our 'sales dataset' containing the following columns:

      - ✔️ Sales_name
      - ✔️ Sales_id
      - ✔️ Sales_company

For example if we want to change only the sales_id of a particular company without interfering with other rows in the dataset we can do the following

create or replace view sales_view_name_you_want
select sales_name,
sales company
from sales_dataset
where sales_id =1
with local check option

with local check option restricts any update done on the View above.

Let's see what changes we can implement on other CRUD operations.

UPDATE

## We can decide to update the particular view 
update sales_view_name_you_want
set company_name = 'Bobbyaxelrod99'
where sales_id = 1

INSERT

Insert into sales_view_name_you_want (sales_name,sales_company,sales_id)
values ('jordan','Apple',1)

insert into sales_view_name_you_want(sales_name,sales_company,sales_id)
values ('John','' Amazon',2)

The first query in the insert operation above runs while the second query doesn't because of our with local check option that we had inserted earlier in the first code. This restricts our changes to only sales_id = 1.

The same logic applies to other CRUD operations that we might want to introduce.

Advantages of Views

• Data Security - Views restrict access to certain columns or rows thus allowing users to see only what the database administrator intends for them to see.
• Code Reusability - Views can be used across all queries thus reducing the need for duplicate code
• Version Control - Views can be used to control changes made to a particular set of queries without making changes to other code.
• Query Simplification - Views make it easier for users to manipulate data without needing to understand the underlying data schema

Disadvantages of Views

• Limited Portability - Views may not always be portable across different database management systems (DBMS). While SQL standards define views, different DBMS implementations may have variations in their support for certain view features or syntax, limiting portability between systems.
• Complexity Maintenance - Managing and maintaining views, especially in a dynamic database environment where underlying tables may change, can become challenging and error-prone.
• Debugging and Troubleshooting - Identifying issues, such as performance bottlenecks or incorrect query results, may require tracing through multiple layers of abstraction introduced by views, making troubleshooting more challenging

Let's move on to functions.

Functions

Functions are blocks of code that accept parameters, perform a series of operations, and return a value.
Examples of Functions in PostgreSQL include:

• sum()

• lower()

• upper()

We have to first understand the syntax of a Function in PostgreSQL.

create or replace function 
function_name( column_name1 numeric,column_name2 numeric,
              places_of_decimals integer default 1)
returns numeric as
' select ( column_name1 - column_name2)::,places_of_decimals);'
language sql
immutable
returns null or null input

The function above can be broken down into:

• function_name- this is the name of the function which contains a list of arguments.Each argument in the parenthensis has a data type.
• default 1 - indicates that we want only one value to be displayed.
• returns numeric - returns the function as numeric
• language sql - it pecifies that it is written in sql
• immutable - it indicates that the function won't be making any changes to the database.

So how do we know it works?

We can check the Function if it works by simply

select function_name(argument1,argument2)

Advantages of Functions

• Performance Optimization- Functions can improve performance by allowing you to pre-compile and optimize frequently executed code segments.
• Reusability- Once defined, functions can be reused in multiple queries, reducing code duplication and promoting consistency across your database application.
• Encapsulation- Functions allow you to encapsulate and modularize complex or frequently used logic, making your code more organized and easier to maintain.

Disadvantages of Functions

• Deployment Challenges - Managing versions and deployments of functions can be challenging, particularly in environments with multiple database instances or distributed systems.
• Dependency Management - Functions can create dependencies between different parts of the database schema. Changes to a function's signature or behavior may require updates to dependent objects, such as views, and triggers.
• Debugging Complexity - Errors in function logic or parameter handling may be harder to identify and troubleshoot, particularly if the function is part of a larger application or system.

After Views and functions, we can now move on to triggers.

Triggers

Triggers are used to execute a function when a specified event occurs like a CRUD operation. We can set a trigger right before, after, or instead of an event occurring. It is used as a shortcut particularly when we have a large query that executes a given operation one by one.

The power of Triggers is that it helps us to execute a given instruction all at once.

Types of Triggers

1. Row-level Triggers

• Before Triggers: These triggers fire before the triggering event (e.g., Insert, Update, Delete) occurs on each affected row. They can be used to modify data before it is written to the table.

• After Triggers: These triggers fire after the triggering event has occurred and after any associated constraints have been checked. They can be used for logging or auditing purposes.

2. Statement Triggers

• Before Triggers: These triggers fire once for each SQL statement that triggers them, regardless of the number of affected rows. They can be used for validation or to modify the query's behavior.

• After Triggers: These triggers also fire once for each SQL statement but after the statement has been executed. They are commonly used for actions that don't need to be performed for each affected row individually.

How to create Triggers

Step 1

Create a Function

create or replace function _trigger_name_()
returns trigger as
$$
begin
(your statement depending on the CRUD operation you are performing)

Return NEW;
END;
$$ LANGUAGE plpgsql

Step 2

Create a trigger depending on your CRUD operation

create trigger _trigger_you_want_
after (CRUD Operation you want to perform)
ON _interested_row_you_want_to_be_manipulated_
for each row
execute procedure _trigger_name_ ()

Step 3
Test the trigger to ensure it is working.

• This can be done by running the query check if the instruction you have given has been compiled in the code.

Advantages of Triggers

• Data Validation and Integrity - Triggers allow you to validate data before it is inserted, updated, or deleted from a table.
• Data Maintenance - Triggers allow you to maintain derived or calculated data fields automatically. For example, you can use triggers to update summary statistics, generate denormalized views.
• Constraint Enforcement - Triggers provide a mechanism for enforcing complex constraints that cannot be expressed using standard SQL constraints alone. For example, you can use Triggers to enforce referential integrity across multiple tables or to implement custom validation logic.

Disadvantages of Triggers

• Transaction Control - Triggers may interfere with transaction control mechanisms, such as commit and rollback operations. Depending on when triggers are fired and their behavior, they may cause unexpected side effects.
• Maintenance Complexity - Triggers add complexity to the database schema and codebase, making it harder to understand and maintain the system over time.
• Debugging Challenges - Debugging Triggers can be challenging, as they execute automatically in response to database events. Tracking down errors or unexpected behavior in trigger logic may require specialized debugging tools or techniques.

We can summarize the Views, Functions, and Triggers through a cheat sheet.

Feature	View	Function	Trigger
Definition	Virtual table that you can create by using a saved query	Blocks of code that perform a specific task and can be invoked by SQL queries, procedural languages, or other functions	These are Procedural code blocks that automatically execute in response to specified events occurring on database tables or views
Performance	Have minimal performance overhead when querying them, as they are essentially saved SQL queries that are executed when the view is accessed	Have varying performance characteristics depending on factors such as the complexity of the function logic, the volume of data processed	Have a significant performance impact, especially if they are invoked frequently or involve complex logic
Suitability	They are used for complex queries	They are used for encapsulating complex computations	Enforcing data integrity constraints, implementing complex business rules, and automating tasks that need to be executed in response to database events

Conclusion

All in all Views, Functions, and Triggers are important tools to use when you are scrutinizing data which requires time and patience to master.