Download Udemy – PostgreSQL High Performance Tuning Guide 2022-4

Familiarity with PostgreSQL server architecture

• In this section, we will examine the PostgreSQL architecture.
• Shared buffers work with, rather than replace, the operating system cache. These caches improve performance by reducing the physical I/O required.
• Why should we carefully set checkpoints to limit crash recovery time, while not affecting the rest of the system’s performance?
• This section will introduce you all why we need WAL Writer as well as Background Writer.
• See the steps the Query Processor must go through to reach the result.
• The Subsystem utility provides ways to maintain the database, such as claiming storage, updating statistics, and logging logs.

Vacuum configuration for performance

• We’ll see why 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 grow too large due to dead tuples. Therefore, the VACUUM process should never be avoided.
• This section explains why it’s better to have a constant low intensity vacuum job using the database autovacuum feature instead of disabling that feature and doing the cleanup in larger blocks.

How to use index efficiently

• Be aware that adding an index increases overhead every time you add or change rows in a table. Each index must satisfy enough queries to justify the cost of maintaining it.
• In this section, we will explain why the execution plan of a query depends on the data in the table. If the data has low physicality, PostgreSQL will likely ignore this index
• In this section, we will learn why an index is only useful when it is selective. It can be used to return only a small portion of the table’s rows.
• In this section, we will explore how to effectively use bitmap scanning

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 only the data of a profile using cover profiles.
• This section discusses why defining indexes on foreign keys is a good idea.
• In this section, we will examine partial indexes and how to benefit from a small and efficient index.
• Indexes can require periodic rebuilding to bring them back to optimal performance, and clustering the underlying data against 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 see in which cases it is better to use a composite index versus several independent indices.

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 look at the fastest way to identify missing profiles, but we also check when we need to remove listings.

Diagnose query problems

• We explain how to read query plans and explain how each of the underlying node types works.
• We’ll see how queries are executed as a collection of nodes, each of which performs a small task, such as fetching data aggregates or sorting.
• We’ll 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 planning problems. We will check what we can do in such cases.

Query optimization tips

• We will see why it is important to first question the semantic correctness of an expression before attacking the performance problem
• We’ll see 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’ll see that PostgreSQL’s default allocation for shared_buffers is too low, and we need to increase it to allow the appropriate amount of shared memory for the database.
• We see how shared_buffers work in cooperation with the operating system cache rather than replacing it, and we should measure it as an average percentage of total RAM.
• We’ll see that if we want to better allocate a percentage to shared_buffers than the operating system cache, we need to analyze the contents of the cache buffers.

Scaling and iteration

• We will see 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 sufficient. 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 huge amounts of data. A big time

Installation guide

After Extract, view with your favorite Player.

English subtitle

Quality: 720p