Book Image

PostgreSQL Development Essentials

By : Baji Shaik
Book Image

PostgreSQL Development Essentials

By: Baji Shaik

Overview of this book

PostgreSQL is the most advanced open source database in the world. It is easy to install, configure, and maintain by following the documentation; however, it’s difficult to develop applications using programming languages and design databases accordingly. This book is what you need to get the most out of PostgreSQL You will begin with advanced SQL topics such as views, materialized views, and cursors, and learn about performing data type conversions. You will then perform trigger operations and use trigger functions in PostgreSQL. Next we walk through data modeling, normalization concepts, and the effect of transactions and locking on the database. The next half of the book covers the types of indexes, constrains, and the concepts of table partitioning, as well as the different mechanisms and approaches available to write efficient queries or code. Later, we explore PostgreSQL Extensions and Large Object Support in PostgreSQL. Finally, you will perform database operations in PostgreSQL using PHP and Java. By the end of this book, you will have mastered all the aspects of PostgreSQL development. You will be able to build efficient enterprise-grade applications with PostgreSQL by making use of these concepts
Table of Contents (12 chapters)

Materialized views

A materialized view is a table that actually contains rows but behaves like a view. This has been added in the PostgreSQL 9.3 version. A materialized view cannot subsequently be directly updated, and the query used to create the materialized view is stored in exactly the same way as the view's query is stored. As it holds the actual data, it occupies space as per the filters that we applied while creating the materialized view.

Why materialized views?

Before we get too deep into how to implement materialized views, let's first examine why we may want to use materialized views.

You may notice that certain queries are very slow. You may have exhausted all the techniques in the standard bag of techniques to speed up those queries. In the end, you will realize that getting queries to run as fast as you want simply isn't possible without completely restructuring the data.

Now, if you have an environment where you run the same type of SELECT query multiple times against the same set of tables, then you can create a materialized view for SELECT so that, on every run, this view does not go to the actual tables to fetch the data, which will obviously reduce the load on them as you might be running a Data Manipulation Language (DML) against your actual tables at the same time. So, basically, you take a view and turn it into a real table that holds real data rather than a gateway to a SELECT query.

Read-only, updatable, and writeable materialized views

A materialized view can be read-only, updatable, or writeable. Users cannot perform DML statements on read-only materialized views, but they can perform them on updatable and writeable materialized views.

Read-only materialized views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or by disabling the equivalent option in the database management tool. Read-only materialized views use many mechanisms similar to updatable materialized views, except they do not need to belong to a materialized view group.

In a replication environment, a materialized table holds the table data and resides in a different database. A table that has a materialized view on it is called a master table. The master table resides on a master site and the materialized view resides on a materialized-view site.

In addition, using read-only materialized views eliminates the possibility of introducing data conflicts on the master site or the master materialized view site, although this convenience means that updates cannot be made on the remote materialized view site.

The syntax to create a materialized view is as follows:

CREATE MATERIALIZED VIEW  view_name AS SELECT  columns FROM table;

The CREATE MATERIALIZED VIEW command helps us create a materialized view. The command acts in way similar to the CREATE VIEW command, which was explained in the previous section.

Let's make a read-only materialized view for a supplier table:

CREATE MATERIALIZED VIEW suppliers_matview AS
SELECT * FROM suppliers;

This view is a read-only materialized view and will not reflect the changes to the master site.

Updatable materialized views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the database management tool. In order for changes that have been made to an updatable materialized view to be reflected in the master site during refresh, the updatable materialized view must belong to a materialized view group.

When we say "refreshing the materialized view," we mean synchronizing the data in the materialized view with data in its master table.

An updatable materialized view enables you to decrease the load on master sites because users can make changes to data on the materialized view site.

The syntax to create an updatable materialized view is as follows:

CREATE MATERIALIZED VIEW  view_name  FOR UPDATE 
AS 
SELECT columns FROM table;

Let's make an updatable materialized view for a supplier table:

CREATE MATERIALIZED VIEW suppliers_matview FOR UPDATE
AS
SELECT * FROM suppliers;

Whenever changes are made in the suppliers_matview clause, it will reflect the changes to the master sites during refresh.

Writeable materialized views

A writeable materialized view is one that is created using the FOR UPDATE clause like an updatable materialized view is, but it is not a part of a materialized view group. Users can perform DML operations on a writeable materialized view; however, if you refresh the materialized view, then these changes are not pushed back to the master site and are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable, read-only materialized views are allowed.