DBA discovers new problem with PostgreSQL

As always, users who upgrade or initialize a new cluster will see better performance (e.g., better parallel index scans, merge joins and unrelated subqueries, faster aggregations, more intelligence on remote servers joins and aggregations), these all work out of the box, but in this article I want to talk about something that doesn't work out of the box and actually requires you to take some steps to benefit from it. The features highlighted below are compiled from a DBA's perspective, and there will soon be an article covering the changes from a developer's perspective.

Upgrade Notes

First some tips for upgrading from an existing setup - there are some small things that can cause problems when migrating from 9.6 or older, so be sure to test the upgrade on a separate copy before doing the actual upgrade, and Go through the release notes for all possible issues. The most notable flaws are:

All functions containing "xlog" have been renamed to use "wal" instead of "xlog".

The latter naming may be confused with normal server logs, so this is a "just in case" change. If using any third-party backup/replication/HA tools, check that they are up to date.

The pg_log folder that holds server logs (error messages/warnings, etc.) has been renamed to "log".

Make sure to verify that your log parsing or grep script (if you have one) works.

By default, queries will use up to 2 background processes.

If you use the default value of 10 in the postgresql.conf setting on a machine with a low number of CPUs, you may see resource usage spikes because parallel processing is enabled by default - this is a good thing because it Should mean faster queries. If you want the old behavior, set max_parallel_workers_per_gather to 0.

By default, replication connections for localhost are enabled.

To simplify things like testing, localhost and local Unix socket replication connections are now enabled in "trust" mode (no password) in pg_hba.conf! Therefore, be sure to change the configuration if other non-DBA users have access to the real production machine.

My favorites from a DBA perspective

Logical replication

This long-awaited feature requires simple setup with minimal performance penalty when you only want to copy a single table, part of a table, or all tables, which also means that subsequent major versions can be upgraded with zero downtime! Historically (requires Postgres 9.4), this has been possible by using third-party extensions or slow trigger-based solutions. For me this is the 10 best features.

Declare partition

The previous method of managing partitions involved inheriting and creating triggers to reroute inserts to the correct table, which was annoying, not to mention the performance impact. Currently supported are the "range" and "list" partitioning schemes. If anyone is missing "hash" partitioning in some database engines, you can use "list" partitioning with expressions to achieve the same functionality.

Available hash indexes

Hash indexes are now WAL logged and therefore crash-safe, and have gained some performance improvements, making them faster than standard B-tree indexes on larger data for simple searches. Larger index sizes are also supported.

Cross-column optimizer statistics

Such statistics need to be created manually on a set of table columns to indicate that the values ​​are actually dependent on each other in some way. This will deal with slow queries where the planner thinks there is very little data to be returned (the product of probabilities often yields very small numbers), resulting in poor performance with large amounts of data (e.g. choosing a "nested loop" join).

Parallel snapshots on replicas

It is now possible to use multiple processes (-jobs flag) in pg_dump to greatly speed up backups on standby servers.

Better tune the behavior of parallel processing workers

Refer to max_parallel_workers and min_parallel_table_scan_size/min_parallel_index_scan_size parameters. I recommend increasing the default values ​​for the latter two (8MB, 512KB) a bit.

New built-in monitoring role for easier tool use

New roles pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables make it easier to perform various monitoring tasks - which previously had to be done using a superuser account or some SECURITY DEFINER wrapper function.

Temporary (per-session) replication slot for safer replica generation

A new Contrib extension for checking the validity of B-tree indexes

These two smart checks find structural inconsistencies and content not covered by page-level validation. Hopefully more in depth in the near future.

Psql query tool now supports basic branches (if/elif/else)

For example the following will enable a single maintenance/monitoring script with a specific version branch (with different column names for pg_stat* views etc.) instead of many version specific scripts.

SELECT :VERSION_NAME = '10.0' AS is_v10 \gset
\if :is_v10
SELECT 'yippee' AS msg;
\else
SELECT 'time to upgrade!' AS msg;
\endif

That’s it this time! There are of course a lot of other things that are not listed, so for a dedicated DBA, I would definitely recommend taking a more comprehensive look at release records. Many thanks to the 300+ people who contributed to this release!

The above is the detailed content of DBA discovers new problem with PostgreSQL. For more information, please follow other related articles on the PHP Chinese website!