Site icon Vinsguru

Materialized View PostgreSQL

vIns

Overview:

In this tutorial, I would like to demo Materialized View PostgreSQL with Spring Boot  to increase the read performance of the application.

Materialized View:

Most of the web based applications are CRUD in nature with simple CREATE, READ, UPDATE and DELETE operations. It is also true that in the most of the applications, we do more READ operations than other INSERT, DELETE and UPDATE transactions. Sometimes the READ operations could be very heavy in such a way that we would join multiple tables with aggregate functions. It cloud slow down the performance of the read operation.

The goal of this article to show Materialized View Pattern to demo how we can retrieve the prepoluated views of data when the source data is NOT easy to query every time & to improve the performance of your Microservices.

Sample Application:

Lets consider a simple application in which we have 3 services as shown below. (Ideally all these services should have different databases. Here just for this article, I am using same db)

Our order-service is responsible for placing an order for the user. It also exposes an end point which provides sale statistics. To understand that better, lets first see the DB table structure.

CREATE TABLE users(
   id serial PRIMARY KEY,
   firstname VARCHAR (50),
   lastname VARCHAR (50),
   state VARCHAR(10)
);

CREATE TABLE product(
   id serial PRIMARY KEY,
   description VARCHAR (500),
   price numeric (10,2) NOT NULL
);

CREATE TABLE purchase_order(
    id serial PRIMARY KEY,
    user_id integer references users (id),
    product_id integer references product (id)
);

Order-service exposes an end point which provides the total sale values by users state.

select 
    u.state,
    sum(p.price) as total_sale
from 
    users u,
    product p,
    purchase_order po
where 
    u.id = po.user_id
    and p.id = po.product_id
group by u.state
order by u.state

We could create a view to get the results we are interested in as shown here.

create view purchase_order_summary
as
select u.state,
       sum(p.price) as total_sale
from users u,
     product p,
     purchase_order po
where u.id = po.user_id
  and p.id = po.product_id
group by u.state
order by u.state

So executing below query provides the total_sale by state

select * from purchase_order_summary;

Spring Boot Application:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstname;
    private String lastname;
    private String state;

    // getters & setters

}
@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String description;
    private double price;

    // getters & setters

}
@Entity
public class PurchaseOrder {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Long userId;
    private Long productId;

    // getters & setters

}
@Entity
public class PurchaseOrderSummary {

    @Id
    private String state;
    private Double totalSale;

    // getters & setters

}
public class PurchaseOrderSummaryDto {
    private String state;
    private double totalSale;
   
     // getters and setters

}
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
}

@Repository
public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {
}

@Repository
public interface PurchaseOrderSummaryRepository extends JpaRepository<PurchaseOrderSummary, String> {
}
public interface PurchaseOrderService {
    void placeOrder(int userIndex, int productIndex);
    List<PurchaseOrderSummaryDto> getSaleSummary();
}

@Service
public class PurchaseOrderServiceImpl implements PurchaseOrderService {

    @Autowired
    private PurchaseOrderSummaryRepository purchaseOrderSummaryRepository;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private ProductRepository productRepository;

    @Autowired
    private PurchaseOrderRepository purchaseOrderRepository;

    private List<User> users;
    private List<Product> products;

    @PostConstruct
    private void init(){
        this.users = this.userRepository.findAll();
        this.products = this.productRepository.findAll();
    }
    
    @Override
    public void placeOrder(int userIndex, int productIndex) {
        PurchaseOrder purchaseOrder = new PurchaseOrder();
        purchaseOrder.setProductId(this.products.get(productIndex).getId());
        purchaseOrder.setUserId(this.users.get(userIndex).getId());
        this.purchaseOrderRepository.save(purchaseOrder);
    }

    @Override
    public List<PurchaseOrderSummaryDto> getSaleSummary() {
        return this.purchaseOrderSummaryRepository.findAll()
                        .stream()
                        .map(pos -> {
                            PurchaseOrderSummaryDto dto = new PurchaseOrderSummaryDto();
                            dto.setState(pos.getState());
                            dto.setTotalSale(pos.getTotalSale());
                            return dto;
                        })
                        .collect(Collectors.toList());
    }
}
@RestController
@RequestMapping("po")
public class PurchaseOrderController {

    @Autowired
    private PurchaseOrderService purchaseOrderService;

    @GetMapping("/sale/{userIndex}/{prodIndex}")
    public void placeOrder(@PathVariable final int userIndex,
                           @PathVariable final int prodIndex){
        this.purchaseOrderService.placeOrder(userIndex, prodIndex);
    }

    @GetMapping("/summary")
    public List<PurchaseOrderSummaryDto> getSummary(){
        return this.purchaseOrderService.getSaleSummary();
    }

}

Performance Test – DB View:

Problem With Views:

Materialized View PostgreSQL:

Materialized Views are most likely views in a DB. But they are not virtual tables. Instead the data is actually calculated / retrieved using the query and the result is stored in the hard disk as a separate table. So when we execute below query, the underlying query is not executed every time. Instead the data is fetched directly from the table. This is something like using the cached data. So it improves the performance.

select * from purchase_order_summary;


CREATE MATERIALIZED VIEW purchase_order_summary
AS
select 
    u.state,
    sum(p.price) as total_sale
from 
    users u,
    product p,
    purchase_order po
where 
    u.id = po.user_id
    and p.id = po.product_id
group by u.state
order by u.state
WITH NO DATA;
CREATE UNIQUE INDEX state_category ON purchase_order_summary (state);

-- to load into the purchase_order_summary
REFRESH MATERIALIZED VIEW CONCURRENTLY purchase_order_summary;

The obvious question would be what if the source data is updated. That is, if we make new entry into the purchase_order table, how the purchase_order_summary table will be updated!? It will not automatically update. We need to make some actions to do that.

Materialized View PostgreSQL – Auto Update With Triggers:

CREATE OR REPLACE FUNCTION refresh_mat_view()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
  BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY purchase_order_summary;
  RETURN NULL;
  END $$;
CREATE TRIGGER refresh_mat_view_after_po_insert
  AFTER INSERT 
  ON purchase_order
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_mat_view();

Materialized View – Performance Test:

But do we really need to update summary for every order. Instead, we could update the materialized view certain interval like 5 seconds. The data might not be very accurate for few seconds. It will eventually be refreshed in 5 seconds. This could be a nice solution to avoid the new order performance issue which we saw above.

-- drop trigger

drop trigger refresh_mat_view_after_po_insert ON purchase_order;

-- drop function
drop function refresh_mat_view();

-- create procedure
CREATE OR REPLACE PROCEDURE refresh_mat_view()
LANGUAGE plpgsql    
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY purchase_order_summary;
END;
$$;

Materialized View With Spring Boot:

@Component
public class MaterializedViewRefresher {

    @Autowired
    private EntityManager entityManager;

    @Transactional
    @Scheduled(fixedRate = 5000L)
    public void refresh(){
        this.entityManager.createNativeQuery("call refresh_mat_view();").executeUpdate();
    }

}

Summary:

We were able to demonstrate the usage of Materialized View PostgreSQL with Spring Boot  to improve the performance of the read heavy operations for the Microservices architecture. Implementing this pattern will also enable us implementing CQRS pattern to improve the performance further of our microservices.

Read more about Microservice Design Patterns.

The source code is available here.

Share This:

Exit mobile version