Hot minor update in Postgres Pro 13

In this presentation, we'll explain how we do minor version update for Postgres Pro DBMS without instance restart and/or active backend termination.

Postgres Architectures in Production

When using PostgreSQL in production it is important to implement a strategy for High Availability. With a database service, the HA properties apply to both the service itself and of course to the data set.

In this talk we learn how to think about specific HA needs of your production environment and how to achieve your requirements with Open Source tooling when it comes to your database of choice, PostgreSQL. In particular, we dive in many options that could be implemented for Postgres to evolve its offering from being a toolset to being “batteries included”. What does it mean in the context of HA? How to achieve it?

How query execution history can help with re-execution

Postgres is able to build optimal query plans for most practical cases. However, sometimes, for objective reasons, for complex queries or because of open issues in the planner itself, it can make mistakes and produce a suboptimal plan. Because of this, the execution time of such a request can increase tenfold. If the query is executed frequently, then from time to time this query takes longer than it could, and the DBMS as a whole produces a lower TPS. If the planner is able to record his mistakes and take them into account in the subsequent planning of the same query, then this will improve the characteristics of the DBMS during its operation. We present the results of the development of a PostgreSQL DBMS extension that stores the query execution history and implements the planner recommendation mechanism. We show how knowledge about previously executed queries can improve the performance of subsequent ones.

Updating Materialized Views Automatically and Incrementally

Materialized view is a feature to store the results of view definition queries in DB in order to achieve faster query response. However, the data in the view gets stale after underlying tables are modified. Therefore, view maintenance is needed to keep the contents up to date. PostgreSQL has REFRESH MATERIALIZED VIEW command for updating a materialized view, but this command needs to recompute the contents from scratch, so this is not efficient in cases where only a small part of a base table is modified.

Incremental View Maintenance (IVM) is a technique to maintain materialized views efficiently, which computes and applies only the incremental changes to the materialized views instead of recomputing. This feature is required for updating materialized views rapidly but not implemented on PostgreSQL yet.

Therefore, we developed IVM on PostgreSQL and are proposing to implement this as a core feature. The patch is now under discussion on the hackers mailing list. Our implementation allows materialized views to be updated automatically and incrementally when a underlying table is modified. You don't need to write your own trigger function for updating views. As a result of continuous development, the current implementation supports some aggregates, subqueries, self-join, outer joins, and CTEs (WITH clauses) in a view definition query. The result of performance evaluation using TPC-H queries shows that our IVM implementation can update a materialized view more than 200 times faster than re-computation by REFRESH command.

In this talk, we will describe our IVM implementation and its features.