3. What are the differences between Tantor SE 15 and PostgreSQL 15#
3. What are the differences between Tantor SE 15 and PostgreSQL 15
3.1. Core improvements
64-bit transaction counter.
Autonomous transactions.
Increased database management system (DBMS) performance with a large number of simultaneous users.
Increased number of partitions in the shared buffers.
Optimization (~1.4 times) of the pglz data compression algorithm.
Reduction in the number of data page locks in the shared buffers.
libpq compression.
Allow logical replication subscribers to process only changes that have no origin (bidirectional logical replication).
Optimization and acceleration of the cascade replication recovery process (standby fadvise).
Ability to end a transaction based on a preset timeout.
Ability to add new reserved connections , the number of which is predefined in the reserved_connections parameter in the configuration file. This allows reserving connection slots for users who are not superusers. Slots reserved through this parameter are only available to users who can have a new predefined role pg_use_reserved_connections.
Increased data stream processing speed, in particular for speeding up operations when working with jsontext for ARM architecture processors.
Optimization for linear search for ARM 64 architecture processors.
When sending the WAL to the standby server, a checksum of the data blocks is verified.
3.2. Additional Supplied Utilities
pgcompacttable - a tool designed to reduce the size of bloated tables and indexes without using heavy locks. It is developed to reorganize data in tables and rebuild indexes in order to reclaim disk space without impacting database performance.
3.3. Additionally supplied modules (extensions)
credcheck - provides few general credential checks, which will be evaluated during the user creation, during the password change and user renaming. By using this extension, we can define a set of rules to allow a specific set of credentials, and a set of rules to reject a certain type of credentials. This extension is developed based on the PostgreSQL's check_password_hook hook.
hypopg - adds support for hypothetical_indexes. A hypothetical or virtual index is an index that does not actually exist. Thus, no CPU, disk, or any other resource is required to create. It is useful to know if certain indexes can improve performance for problematic queries, as you can know if Tantor will use those indexes or not without having to spend resources creating them.
orafce - represents a set of functions that provide compatibility with Oracle databases.These functions allow simplifying of the migration to Tantor and reducing the expenses on applications' reconfiguration.
Optimized Row Columnar(ORC) - provides column method of data storage with a possibility of compression to decrease in-out volume and assure high performance. Applicable to append-only, e.g. data time series and displays of corporate storages.
page_repair - designed to repair separate corrupted pages using backup data from the replication server. It allows saving reparation time as it does not require reparation of all data, but separate pages.
pg_background - allows executing commands in the background mode such as VACUUM and CREATE INDEX CONCURRENTLY from SQL along with launching autonomous transactions.
pg_cron - represents a cron-based job scheduler working within the database as an extension. It uses the same syntax as regular cron, but it allows scheduling Tantor DBMS commands directly from the database.
pg_hint_plan - allows configuring plans of SQL queries execution using so called "hints" in SQL comments. This gives an opportunity to compensate the scheduler errors occurring in critical cases.
pg_partman - allows creating and managing both time-based and serial-based table partition sets.
pg_qualstats - saves statistic data on found predicates in WHERE operators and JOIN operations. This allows analysing the most frequent qualifiers (predicates) in the database, and also to identify correlated columns by defining which columns are most frequently requested together.
pg_repack - removes bloat from tables and indexes, and optionally restores the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing.
pg_store_plans - provides the means to keep track of the execution plan statistics for all SQL statements executed by the Tantor server.
pg_variables - provides functionality to work with variables of different types in the current user session.
pg_wait_sampling - provides information about current waiting event for a certain process. However, to collect a descriptive statistics of server behaviour a user must repeatedly collect the current waiting event. This module represents an extension for collecting sample statistics of waiting events that prevent needless sampling of the current event waiting.
pgaudit - provides detailed session and/or object audit logging via the standard Tantor DBMS logging facility. The goal of the pgAudit is to provide Tantor DBMS users with capability to produce audit logs often required to comply with government, financial, or ISO certifications. An audit is an official inspection of an individual’s or organization’s accounts, typically by an independent body. The information gathered by pgAudit is properly called an audit trail or audit log. The term audit log is used in this documentation.
pgq - provides generic, high-performance lockless queue with simple API based on SQL functions.
pgsql-http - allows making a trigger that calls a web service available to return a result, or makes the service update according to the new state of the database.
pgauditlogtofile - is an addon to pgAudit than will redirect audit log lines to an independent file, instead of using PostgreSQL server logger. This will allow us to have an audit file that we can easily rotate without polluting server logs with those messages. Audit logs in heavily used systems can grow very fast. This extension allows to automatically rotate the files based in a number of minutes.
3.4. Additionally supplied programs
Tantor Platform: full-featured modular platform for administration and monitoring of Tantor RDMS and most PostgreSQL based clusters. The platform simplifies daily RDBMS operation with significant impact on the RDBMS management simplicity. The software functionality includes: smart and simple user interface, cluster automatic adaptive tuning, system overview, alerts and monitoring, automatic health checks and schema audit, maintenance tasks resolution through the GUI, advanced query profiling, and administering multiple servers in one place;
pg_anon: is a standalone Python program to work with Tantor RDMS or PostgreSQL based DB to perform data masking and anonymization during data migration between production and test environments;
wal-g: is a tool for making encrypted, compressed Tantor SE backups (full and incremental) and push/fetch them to/from storage without saving it on your filesystem;
pg_configurator: is a PostgreSQL configuration tool;
pg_cluster: is a Ansible playbook for PostgreSQL high availability cluster on Patroni.