DEV Community: Jordan Gamba

Optimizing Database Performance with Views Functions and Triggers in PostgreSQL

Jordan Gamba — Tue, 12 Mar 2024 04:34:25 +0000

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.

A simple Mall Analysis project using Postgresql 👨‍💻

Jordan Gamba — Sat, 06 Jan 2024 19:42:03 +0000

Introduction

I created a simple data analytics project that analyzes data from mall dataset I got from Kaggle.The goal of this project was to qury simple business questions that would arise when client wants to build mall in a particular geographic areas.

Here are simple questions I queried the data:

a. Performance Metrics :

What are the top-performing malls based on the rank or other performance indicators?
How does the gross leasable area (GLA) correlate with the overall performance of the malls?
Are there trends or patterns in the ranking of malls over the years?

b. Geographic Analysis :

Which countries and cities have the highest concentration of malls in the dataset?
Is there a correlation between the location (country or city) and the performance or size of the malls?

c. Market Penetration :

How many shops, on average, are there in each mall?
Is there a correlation between the number of shops and the success of a mall?

d. Temporal Analysis :

Are there trends in the year of mall opening? Does the opening year correlate with performance?
How has the gross leasable area (GLA) changed over the years in newly opened malls?

e. Expansion Opportunities :

Can you identify trends in the opening of new malls over the years?
Are there regions or cities where the client could potentially open new malls based on market demand?

f. Customer Experience :

Can you estimate the average shopping area per shop to understand customer experience?
How might the distribution of shops influence the overall shopping experience for customers?

I will attempt to go step by step to answer the questions above.

We must first load the dataset into our database
I used the largest mall dataset from kaggle

create table largest_malls(
Rank smallint,
Mall varchar(50),
Country varchar(50),
City varchar(60),
Year_Opened smallint,
Gross_leasable_area varchar(100),
Shops bigint
);

drop table largest_malls

COPY largest_malls
from 'D:\Data Science e-books\EDA  SQL Project\Largest-Malls.csv'
WITH (Format csv,header);

Performance Metrics :

--  Ranking the top 10 malls 
select rank,Mall
from largest_malls
limit 10

-- #Ranking the Country with the highest no.of malls
select Country,count(*)
from largest_malls
group by Country
order by count(*) desc
limit 10

-- How does the gross leasable area (GLA) correlate with the overall performance of the malls?
select rank, Mall, Gross_leasable_area
from largest_malls
limit 10
-- ###The malls with the highest GLA are the ones with the highest rank.

-- Are there trends or patterns in the ranking of malls over the years?
select Country,count(*)
from largest_malls
group by Country
order by count(*) desc
limit 5

Geographic Analysis :

In the previous code I discovered this:

-- Phillipines has the highest amount of malls in the world
-- Phillipines has 10 malls.
-- Compared to the rest of the countries the Philippines has a smaller land mass as US,China. 
-- The reason that keeps popping up is because Philippines has a hot and humid climate. 
-- Therefore it makes sense to build malls in order ffor people to chill wwhen the Temperature goes up

count of no of malls in each country

Market Penetration:

-- I used median to depict the number of shops in each mall in the dataset
-- The answer was 300
select round(avg(shops),2) as "Average_Shops",
percentile_cont(.5)
within group (order by shops) as "Median_shops "
from largest_malls

-- Is there a correlation between the number of shops and the success of a mall?

-- There are so many determinants of mall success. 
-- In our data set we have a few columns that we can’t actually have any metric to measure the healthiness of a  mall . 
-- However here are a few factors that are used to measure the healthiness of a mall.

-- This include:
-- Retail mix
-- -Entertainment
--  Dining Options

Temporal Analysis:


-- Are there trends in the year of mall opening? Does the opening year correlate with performance?
-- I could not find an appropriate answer to Qn 1.
-- However using our mall success based on the number of shops we can make an assumption on the performance.

-- How has the gross leasable area (GLA) changed over the years in newly opened malls?
-- I was able to find the answer on Qn 2.
-- I exported the query to Excel

copy(
select Mall,Country,City,Year_opened,Gross_leasable_area
from largest_malls
order by Year_opened asc
TO 'C:\Data Science e-books\EDA  SQL Project\MallS_over_the_Years.csv'
WITH(FORMAT CSV,HEADER,DELIMITER ',');

-- There is generally a flat trend over the years the except when the Iran mall was built in 2018.
-- The years have been arranged in ascending order.

Expansion Opportunities:

select Rank,Country,Year_Opened,shops
from largest_malls
where Year_opened >2010
order by shops desc

-- I would advise to open malls in the Asian market but specifically in the middle East. 
-- Because as much as these countries are oil dependent they would be moving away from fossil fuels in the future thus focusing on tourism and Renewables.
-- These economies have also shown to loose their social and restrictive rules towards their own. This Liberisation of women could open up new opportunities
-- Therefore, Opening up more shops would be a smart move also because the local purchasing power is not weak and will continue to grow

Customer Experience:

-- Can you estimate the average shopping area per shop to understand customer experience?
-- How might the distribution of shops influence the overall shopping experience for customers?

-- We exported a query into an excel file and therefore performed data cleaning for the Gross leasable area from being a character to an integer

copy(
select Country,City,Gross_leasable_Area,Year_Opened,shops
from largest_malls
order by Year_Opened asc)
TO 'C:\Data Science e-books\EDA  SQL Project\Malls_Opened_Over_the_Years.csv'
WITH(FORMAT CSV,DELIMITER',')

-- We then created another Table in the Postgresql Database
Create table Malls_Opened_over_the_Years(
   Country varchar(50),
   City varchar(60),
   Gross_leasable_Area_in_m2 bigint,
   Year_Opened smallint,
   Shops smallint
)
-- Imported the data that we had

COPY Malls_Opened_Over_the_Years
from 'C:\Data Science e-books\EDA  SQL Project\Malls_Opened_Over_the_Years.csv'
WITH(FORMAT CSV,HEADER)

-- The average shopping area per shop per country

select Country,avg(round((Shops/Gross_leasable_Area_in_m2::numeric(10,1))*1000,4)) as avg_shop_per_Country  
from Malls_Opened_Over_the_Years
Group by Country 

-- How might the distribution of shops affect the customer experience?
-- Customer Experience in a shopping mall is affected by various factors:
-- •  Quality of service
-- •  Product costs
-- •  Distribution of shops to customers
-- All these affect the customer experience. It should be noted all these factors have to be harmonized in order for the customer experience to be positive. 
-- All negative deviation of one of this factors can jeopardize the customer experience and it will require for the rest of these other factors to read just in order for the customer experience to remain positive

Conclusion
This project is limited in that it is not too complex to conduct analysis.However,it is beginner friendly and it is easy to understand for anyone starting out.