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

This article introduces the relevant knowledge of "what are the new features of database PostgreSQL 6.0". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Aliyun AnalyticDB for PostgreSQL is a distributed cluster database based on MPP architecture, which fully supports SQL 2003, partially compatible with Oracle syntax, supports PL/SQL stored procedures, triggers, and supports standard database transaction ACID. Through row storage, column storage, multiple partitioned tables and indexes and other mechanisms, AnalyticDB PG can support the delivery analysis of massive data, as well as ETL batch tasks.

AnalyticDB PG version 6.0 greatly improves the ability of concurrent transaction processing, better meets the real-time warehouse scenario, and fully supports HTAP services through optimization such as transaction locks. The kernel of AnalyticDB PG 6.0has been upgraded from PostgreSQL 8.2 to 9.4, which is more compatible with PostgreSQL community ecology.

Key features of AnalyticDB for PG 6.0:

PostgreSQL kernel upgrade

Compared with the previous version 4.3, the PostgreSQL kernel has been upgraded from AnalyticDB for PG version 8.2 to version 9.4. A number of new PostgreSQL features include:

JSONB types: on existing JSON types, support JSONB storage format, achieve high-performance JSON data processing and richer JSON functions.

UUID type: UUID data type is supported.

GIN index and SP-GiST index: can support fuzzy matching and Chinese retrieval with higher performance.

Fine-grained permission control: supports schema level, as well as column column level permission control and authorization.

When doing space release, efficient Vacuum:Vacuum can skip the locked page temporarily, and then poll its Vacuum again later, thus reducing the blocking condition as a whole.

DBLink: supports query access across databases.

Recursive CTE: realize the recursive query function of SQL, which is used to deal with the data with hierarchical or tree structure logically, and facilitate the multi-level recursive query of this kind of data.

PL/SQL enhancements: support for RETURN QUERY EXECUTE statements, dynamic and immediate execution of SQL; supports Anonymous blocks anonymous block definitions.

HTAP (OLAP+OLTP) capability improvement

By introducing the global deadlock checking mechanism (global deadlock detection), the lock information will be dynamically collected and analyzed to check and release the global deadlock. Based on this, the update and modification operation of the HEAP table can only be completed with the help of fine-grained row locks, support large concurrent change and delete queries, and improve the concurrency and throughput of the whole system. At the same time, the transaction lock is optimized to reduce the lock competition when starting and ending the transaction. ADB PG 6.0provides high throughput transaction processing on the basis of high performance OLAP analysis.

Typical OLTP scenarios TPC-C up to 10w tpmc;Sysbench support select 15w tps, insert 5w tps,update 2w tps.

New features of OLAP

Support replication table (replicated table): for dimension tables in data warehouse, by establishing replication table (DISTRIBUTED REPLICATED clause), data transmission can be greatly reduced and query efficiency can be improved.

Support zstandard compression algorithm: ZSTD compression algorithm, compared with the previous zlib compression algorithm, improve the compression and decompression performance by three times.

This is the end of the content of "what are the new features of database PostgreSQL 6.0". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.