Exploring Aurora serverlessV2 for MySQL Part 3
Author: Kabilesh P R Explore the powerful features of Aurora Serverless V2 for MySQL in this informative blog series. Learn about read-only scaling, parameter ...
Author: Mohammad Zaid Patel
PostgreSQL 16 is here, and it's packed with exciting new features and improvements! If you're eager to dive in and start using this latest version of one of the world's most powerful open-source databases, you're in the right place.
In this blog, we'll keep things simple and straightforward. Whether you're a PostgreSQL pro or a newcomer, we'll guide you through the essentials of PostgreSQL 16, so you can quickly get up to speed and make the most of what it has to offer. So, let's jump right in and explore what PostgreSQL 16 brings to the table!
Performance improvements in PostgreSQL 16
In PostgreSQL 16, there are significant performance improvements across several key areas:
New optimizations in query planner
In this latest release, the query planner gains the ability to parallelize FULL and RIGHT joins, generate more optimized plans for queries involving aggregate functions with DISTINCT or ORDER BY clauses, utilize incremental sorts for SELECT DISTINCT queries, and optimize the execution of window functions, leading to more efficient operation. Furthermore, PostgreSQL 16 refines RIGHT and OUTER anti-joins, empowering users to pinpoint rows that are absent in a joined table.
Bulk loading
This release also brings significant improvements to bulk loading, applicable to both single and concurrent operations using the COPY command. Tests have shown remarkable performance enhancements of up to 300% in certain scenarios.
Load balancing for libpq
Starting from version 16, tools that utilize libpq, such as psql, now have support for load balancing at the connection level. A new option has been added to the utility i.e. load_balance_hosts which can be used for load balancing. Detailed info and a demo for the utility is provided in our blog: PostgreSQL 16 brings Load Balancing Support in libpq
Improved vacuum
PostgreSQL 16 comes with enhancements to the vacuum strategy that reduce the necessity for full-table freezes.
Performance boosts
This version introduces CPU acceleration through SIMD technology for both x86 and ARM architectures, resulting in notable performance boosts during operations involving ASCII and JSON strings, as well as array and subtransaction searches.
Logical replication in PostgreSQL 16
Bidirectional Replication
This release initiates the process of adding support for bidirectional logical replication, introducing functionality for replicating data between two tables originating from different publishers.
This feature is a game-changer for high availability, Write Scalability, disaster recovery, and collaboration scenarios. While its implementation requires careful consideration, bidirectional replication opens exciting possibilities for seamless data exchange.
Detailed information can be found in our blog: Bidirectional Logical Replication in PostgreSQL 16
Logical replication from standby
In PostgreSQL 16, users gain the capability to engage in logical replication from a standby instance, signifying that a standby server can now publish logical changes to other servers. This offers users, possibilities for distributing workloads, such as opting to use a standby server instead of the more heavily utilized primary server to logically replicate changes to downstream systems.
Performance enhancements for logical replication
Subscribers are now able to apply substantial transactions using parallel workers. In cases where tables lack a primary key, subscribers can utilize B-tree indexes instead of sequential scans to locate rows
Logical replication control enhancements
Introduction of the new predefined role, pg_create_subscription, which grants users the authority to create new logical subscriptions.
Detailed information about predefined roles in PostgreSQL is explained in our blog: Overview of Predefined Roles in PostgreSQL
Monitoring
IO monitoring
PostgreSQL 16 introduces pg_stat_io, a fresh source of crucial I/O metrics, enabling in-depth inspection of I/O access patterns.
Structure of pg_stat_io
Column | Type | Description |
backend_type | text | Type of backend (e.g. background worker, autovacuum worker). |
object | text | Target object of an I/O operation. Possible values are relation: Permanent relations. temp relation: Temporary relations. |
context | text | The context of an I/O operation. Possible values are: normal: The default or standard context for a type of I/O operation. For example, by default, relation data is read into and written out from shared buffers. Thus, reads and writes of relation data to and from shared buffers are tracked in context normal. vacuum: I/O operations performed outside of shared buffers while vacuuming and analyzing permanent relations. Temporary table vacuums use the same local buffer pool as other temporary table IO operations and are tracked in context normal. bulkread: Certain large read I/O operations are done outside of shared buffers, for example, a sequential scan of a large table. bulkwrite: Certain large write I/O operations done outside of shared buffers, such as COPY. |
reads | bigint | Number of read operations, each of the size specified in op_bytes. |
read_time | double precision | Time spent in read operations in milliseconds (if track_io_timing is enabled, otherwise zero) |
writes | bigint | Number of write operations, each of the size specified in op_bytes. |
write_time | double precision | Time spent in write operations in milliseconds (if track_io_timing is enabled, otherwise zero) |
writebacks | bigint | Number of units of size op_bytes which the process requested the kernel write out to permanent storage. |
writeback_time | double precision | Time spent in writeback operations in milliseconds (if track_io_timing is enabled, otherwise zero). This includes the time spent queueing write-out requests and, potentially, the time spent to write out the dirty data. |
extends | bigint | Number of relation extend operations, each of the size specified in op_bytes. |
extend_time | double precision | Time spent in extend operations in milliseconds (if track_io_timing is enabled, otherwise zero) |
op_bytes | bigint | The number of bytes per unit of I/O read, written, or extended. Relation data reads, writes, and extends are done in block_size units, derived from the build-time parameter BLCKSZ, which is 8192 by default. |
hits | bigint | The number of times a desired block was found in a shared buffer. |
evictions | bigint | Number of times a block has been written out from a shared or local buffer in order to make it available for another use. In context normal, this counts the number of times a block was evicted from a buffer and replaced with another block. In contexts of bulkwrite, bulkread, and vacuum, this counts the number of times a block was evicted from shared buffers in order to add the shared buffer to a separate, size-limited ring buffer for use in a bulk I/O operation. |
reuses | bigint | The number of times an existing buffer in a size-limited ring buffer outside of shared buffers was reused as part of an I/O operation in the bulkread, bulkwrite, or vacuum contexts. |
fsyncs | bigint | Number of fsync calls. These are only tracked in context normal. |
fsync_time | double precision | Time spent in fsync operations in milliseconds (if track_io_timing is enabled, otherwise zero) |
stats_reset | timestamp with time zone | Time at which these statistics were last reset. |
pg_stat_all_tables
This view offers comprehensive statistics pertaining to every table within the present database. These statistics include the counts of sequential and index scans, updates, deletes, inserts, and more. Additionally, it furnishes details regarding the quantities of active and obsolete tuples, as well as statistics related to vacuum and analyze operations.
in this release, a new attribute is appended to the pg_stat_all_tables view. This attribute records a new timestamp column i.e. last_seq_scan indicating the time of the last scan table.
Latest structure for pg_stat_all_tables:
Column | Type | Description |
relid | oid | OID of a table |
schemaname | name | Name of the schema that this table is in |
relname | name | Name of this table |
seq_scan | bigint | Number of sequential scans initiated on this table |
last_seq_scan | timestamp | The time of the last sequential scan on this table, based on the most recent tran |
seq_tup_read | bigint | Number of live rows fetched by sequential scans |
idx_scan | bigint | Number of index scans initiated on this table |
last_idx_scan | timestamp | The time of the last index scan on this table, based on the most recent transaction stop time |
idx_tup_fetch | bigint | Number of live rows fetched by index scans |
n_tup_ins | bigint | Total number of rows inserted |
n_tup_upd | bigint | Total number of rows updated. (This includes row updates counted in n_tup_hot_upd and n_tup_newpage_upd, |
n_tup_del | bigint | Total number of rows deleted |
n_tup_hot_upd | bigint | Number of rows HOT updated. These are updates where no successor versions are required in indexes. |
n_tup_newpage_upd | bigint | Number of rows updated where the successor version goes onto a new heap page, leaving behind an original version with a t_ctid field that points to a different heap page. These are always non-HOT updates. |
n_live_tup | bigint | Estimated number of live rows |
n_dead_tup | bigint | Estimated number of dead rows |
n_mod_since_analyze | bigint | Estimated number of rows modified since this table was last analyzed |
n_ins_since_vacuum | bigint | Estimated number of rows inserted since this table was last vacuumed |
last_vacuum | timestamp | Last time at which this table was manually vacuumed (not counting VACUUM FULL) |
last_autovacuum | timestamp | Last time at which this table was vacuumed by the autovacuum daemon |
last_analyze | timestamp | Last time at which this table was manually analyzed |
last_autoanalyze | timestamp | Last time at which this table was analyzed by the autovacuum daemon |
vacuum_count | bigint | Number of times this table has been manually vacuumed (not counting VACUUM FULL) |
autovacuum_count | bigint | Number of times this table has been vacuumed by the autovacuum daemon |
analyze_count | bigint | Number of times this table has been manually analyzed |
autoanalyze_count | bigint | Number of times this table has been analyzed by the autovacuum daemon |
pg_stat_all_indexes
This view offers comprehensive statistics for all indexes within the present database. It encompasses details such as the count of index scans, the number of rows fetched by each specific index, etc.
In this release, a new column is appended to the pg_stat_all_indexes view, i.e. last_idx_scan which indicates the time of the last index scan for the particular index.
Latest structure for pg_stat_all_indexes
Column | Type | Description |
relid | oid | OID of the table for this index |
indexrelid | oid | OID of this index |
schemaname | name | Name of the schema this index is in |
relname | name | Name of the table for this index |
indexrelname | name | Name of this index |
idx_scan | bigint | Number of index scans initiated on this index |
last_idx_scan | timestamp | The time of the last scan on this index, based on the most recent transaction stop time |
idx_tup_read | bigint | Number of index entries returned by scans on this index |
idx_tup_fetch | bigint | Number of live table rows fetched by simple index scans using this index |
Improved ‘auto_explain’
The auto_explain extension offers an automated way to log execution plans for slow statements, eliminating the need to execute EXPLAIN manually.
In PostgreSQL 16, the auto_explain feature enhances readability by recording input values for parameterized statements
Improved query tracking
PostgreSQL 16 refines the precision of the query tracking algorithm utilized by pg_stat_statements and pg_stat_activity.
Security in PostgreSQL
In terms of security, PostgreSQL 16 introduces several enhancements and new features:
More Detailed Access Control Options
This update includes improvements to the management of pg_hba.conf and pg_ident.conf files. It enables regular expression matching for user and database names, providing more flexible and fine-grained access control. Additionally, it includes directives for external configuration files, allowing for better management of access control rules.
Security-Focused Client Connection Parameters
PostgreSQL 16 introduces the require_auth function, empowering clients to specify the authentication parameters they are willing to accept from a server. This feature adds an extra layer of control over the security of client-server interactions.
Moreover, the introduction of sslrootcert=system signifies that PostgreSQL should utilize the trusted certificate authority (CA) store provided by the client's operating system, enhancing the security of SSL/TLS connections.
Kerberos Credential Delegation
This release supports Kerberos credential delegation, allowing extensions like postgres_fdw and dblink to use authenticated credentials to establish connections with trusted services securely. This feature enhances the authentication and security capabilities of PostgreSQL 16.
These security enhancements in PostgreSQL 16 bolster the database system's capabilities to protect sensitive data and ensure secure connections, making it a reliable choice for various applications where security is paramount.
The release of PostgreSQL v16 reflects the PostgreSQL community's unwavering commitment to innovation. This latest version not only underscores the community's dedication but also demonstrates its agility in responding to the ever-evolving needs of developers and database administrators.
With a host of new features and enhanced functionalities, this release stands as a powerful and versatile tool for developers and database administrators, equipping them with the capabilities needed to tackle a wide range of database challenges.
