Udemy – PostgreSQL High Performance Tuning Guide 2022-4 – Download

Familiar with PostgreSQL server architecture

• In this section, we will examine the PostgreSQL architecture.
• Shared buffers work with the operating system cache rather than replacing it. These caches improve performance by reducing the physical I/O required.
• Why should we set checkpoints carefully to limit crash recovery time, while not affecting the rest of the system’s performance?
• This section will tell you all about why we need wall writer as well as background writer.
• See what steps the query processor must go through to reach the result.
• The subsystem utility provides methods for maintaining the database, such as claiming storage, updating statistics, and logging logs.

Vacuum Configuration for Performance

• We will notice that whenever we do an update or delete, we leave behind a dead row (and possibly a dead index entry) that needs to be cleaned up later with some sort of vacuum.
• We will learn that performance degrades when tables become very large due to dead tuples. Therefore, the VACUUM process should never be avoided.
• This section explains why it is better to perform a continuous low-intensity vacuum job using the database autovacuum feature rather than disabling that feature and cleaning in large blocks.

How to use indexes efficiently

• Keep in mind that adding indexes increases overhead whenever you add or modify rows in a table. Each index must satisfy enough queries to justify the cost of its maintenance.
• In this section, we will explain why the execution plan of a query depends on the data in the table. If the materiality of the data is low, PostgreSQL will probably ignore this index
• In this section, we will learn why an index is useful only when it is selective. It can be used to return only a small portion of the rows of a table.
• In this section, we will explore how to use bitmap scanning effectively

Index Optimization Tips

• In addition to using indexes, you can implement custom strategies for your specific application to speed things up.
• How to answer questions using a cover profile using only the profile’s data.
• This section discusses why it is a good idea to define indexes on foreign keys.
• In this section, we will examine partial indexes and how to benefit from a small and efficient index.
• Indexes may need to be rebuilt periodically to get them back to optimal performance, and clustering the underlying data against the index order can also help improve their speed for lookups.
• We will explain when it is useful to change the table fill factor parameter.
• In this section, we will look at in which cases it is better to use a composite index than several independent indexes.

Use Statistics

• In this section, you’ll explore statistics that can help you find and sort the queries that are responsible for the most system load.
• PostgreSQL provides a large set of statistics. In this section, we make it easier to use their insights.
• We find the quickest way to identify missing profiles, but we also check when we need to remove listings.

Diagnose query issues

• We show how to read query plans and explain how each built-in node type works.
• We’ll look at how queries are executed as a collection of nodes, each of which performs a small task, such as fetching or sorting a data set.
• We will look at the parts of the query that actually had the longest execution time and see if they have a reasonable matching cost.
• Differences between estimated and actual rows can cause major scheduling problems. We will check what we can do in such cases.

Query Optimization Tips

• We’ll see why it’s important to question the semantic correctness of an expression before attacking the performance problem
• We’ll look at why we should avoid SELECT *, ORDER BY, and DISTINCT unless we really need them and there’s no other way.
• We will explore PostgreSQL features such as CASE syntax and parallel queries to reduce query execution time.

How to adjust shared_buffers for better performance

• We will see that PostgreSQL’s default allocation for shared_buffers is too low, and we need to increase it to allow a reasonable amount of shared memory for the database.
• We look at how shared_buffers works in collaboration with the operating system cache rather than replacing it, and we should measure it as an average percentage of total RAM.
• We will see that if we want to allocate a better percentage to shared_buffers than to the operating system cache, we need to analyze the contents of the cache buffers.

Scaling and iteration

• We will look at how replication can be used to improve the performance of a software system by allowing the load to be distributed across multiple database servers.
• In some cases, the replication functionality provided by PostgreSQL is not enough. There are third-party solutions that work around PostgreSQL and provide additional features, such as Pgpool-II which can act as a load balancer and Postgres-XL which is a multi-distributed database solution. It implements a server that can run and manage large amounts of data over a large period of time.

installation Guide

After extract, watch with your favorite player.

English subtitle

Quality: 720p