Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the new highlights of PostgreSQL 10, which have certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

Some time ago a major new version of PostgreSQL 10 was released! It is highly recommended to read the announcements, release notes and "new features" overview available here, here and here. As usual, there have been quite a few blogs covering all the new stuff, but I guess everyone has their own perspective that matters, so as with version 9.6 I'm listing here again the most interesting/relevant features in my impressions.

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

upgrade considerations

First there are some tips for upgrading from existing settings-there are small things that cause problems when migrating from 9.6 or older, so be sure to test the upgrade on a separate copy and go through all possible problems in the release notes before you actually upgrade. 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 you are using any third-party backup/replication/HA tools, check if they are version ***.

The pg_log folder where the server logs (error messages/warnings, etc.) are stored has been renamed to "log."

Make sure to verify that your log parsing or grep scripts (if any) work.

By default, queries will use up to 2 daemons.

If you use the default value of 10 in the postgresql.conf setting on machines with a low CPU count, you might see resource usage spikes because parallel processing is enabled by default-which is a good thing because it should mean faster queries. If you want the old behavior, set max_parallel_workers_per_gather to 0.

Replication connections for localhost are enabled by default.

To simplify things like testing, local host and local Unix socket replication connections are now enabled in pg_hba.conf in "trust" mode (no password)! So if other non-DBA users can also access the real production machine, make sure you change the configuration.

From a DBA perspective, my ***

logical replication

This long-awaited feature requires simple setup with minimal performance loss when you want to copy only a single table, some tables, or all tables, which also means that major versions can be upgraded with zero downtime later! Historically (Postgres 9.4+ required), this can be achieved by using third-party extensions or slow trigger-based solutions. For me, this is a 10 *** function.

Declared partition

The previous approach to managing partitions by inheriting and creating triggers to reroute inserts to the correct tables was annoying, not to mention the performance impact. Currently supported are "range" and "list" partition schemes. If someone is missing a "hash" partition in some database engines, you can use a "list" partition with expressions to achieve the same functionality.

Available hash indexes

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

Cross-column optimizer statistics

Such statistics need to be manually created on a set of table columns to indicate that the values are actually interdependent in some way. This will be able to cope with slow query problems where the planner believes that very little data is returned (the product of probabilities usually yields very small numbers) and thus results in poor performance on large amounts of data (e.g. selecting a "nested loop" join).

Parallel snapshots on replicas

Multiple processes (-jobs flag) can now be used in pg_dump to greatly speed up backups on standby servers.

Better tune parallel processing worker behavior

Refer to max_parallel_workers and min_parallel_table_scan_size/min_parallel_index_scan_size parameters. I suggest adding a little more default for the latter two (8MB, 512KB).

New built-in monitoring roles for ease of tool use

The 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-previously you had to use a superuser account or some SECURITY DEFINER wrapper function.

Temporary (per session) replication slots for more secure replica generation

A New Contrib Extension for Checking the Validity of B-Tree Indexes

These two smart checks detect structural inconsistencies and page-level verification of uncovered content. I hope to go deeper in the near future.

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

For example, the following enables a single maintenance/monitoring script with a version-specific branch (different column names for pg_stat* views, etc.) rather than 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 Thank you for reading this article carefully. I hope that Xiaobian's "What are the new highlights of PostgreSQL 10" will be helpful to everyone. At the same time, I hope that everyone will support you a lot. Pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.