What's new in Greenplum 7. Conclusion
Hi all! Here is the final, third part of the review of changes in Greenplum 7. If for some reason you missed previous publications, they can be found on our blog here and here. Today we will talk about changes in table partitioning and resource groups, JIT expression compilation and much more. Let's start!
Migration to partitioning from Postgres
Previous versions of Greenplum used their own original partitioning scheme, which was implemented for versions of Postgres that did not have such support. Later, Postgres 10 also added its own partitioning (f0e4475).
Greenplum 7 has completely migrated to the Postgres partitioning infrastructure. This means that many familiar tools have been removed and are no longer available (for example, the harvester view pg_partitionscatalog relations pg_partition, pg_partition_rule and so on). The syntax for defining tables, familiar from the Postgres documentation, is considered basic. When dumping a circuit, it will be used to describe it.
However, support for the classic syntax, familiar from previous versions of Greenplum, has been retained to the maximum, except in cases where the expressions it defines have no analogues in the Postgres version.
For example, range partitions now always include the smaller boundary and exclude the larger one. Using classic syntax, you can define another option, but only for those data types that allow you to express non-strict equality through strict:
New features are only available when using Postgres syntax. This includes the ability to split a table into ranges based on several columns or based on the remainder of a hash value division, and heterogeneous multi-level partitioning. Another question is that only the scheduler inherited from Postgres supports working with them, but not ORCA.
Row Level Security
Starting with Postgres 9.5, in addition to the access model defined by the standard at the level of types of operations on a table, it became possible to set data access policies within it. Each policy is defined using a condition. A condition defines a set of rows that the associated role can read or modify.
In the example below we set the role to alice a policy that grants access only to rows containing an even value in a column b.
The expression specified in the policy definition is used to filter rows when scanning or to check for consistency when inserting:
Thanks to the implementation of this functionality abstracted from the storage format, the application of policies is also available for AO tables. On the other hand, the ORCA scheduler in the Greenplum versions current at the time of writing does not support queries to tables for which access policies are activated:
Default permissions for schema and role
Continuing the topic of distribution of access rights, I will note the ability introduced by Postgres 10 to set default permissions for objects that will be created later within the schema:
Or a specific role:
WALs are only held for the last checkpoint
Postgres version 10 and up (and therefore Greenplum < 7) has been holding write-ahead log segments for disaster recovery since the last two checkpoints. This was motivated by increasing the reliability of recovery in cases where the most recent checkpoint was not found in the write-ahead log. However, in some cases such recovery would still not be possible, for example because the tablespace directories were permanently deleted at the time of the last checkpoint. Therefore, we decided to abandon this practice (4b0d28d), at the same time significantly reducing the amount of disk space occupied by write-ahead logs.
Just-in-Time (JIT) compilation
In my opinion, another significant innovation for the analytical workload is Just-in-Time compilation of universal code sections for processing a specific user request. Procedures for checking user-specified conditions, “disassembling” rows from a representation on disk into a representation for subsequent processing – these operations are performed for each retrieved row, of which there will be at least millions in a self-respecting query. As a consequence, the cost of converting an interpreted implementation into a specialized implementation will be offset by the performance gain.
Default jit off, the state is controlled by the GUC of the same name. Statistics output jit For EXPLAIN ANALYZE also closed with a separate configuration variable gp_explain_jit. ORCA supports jithowever, to support differences in the cost model of this scheduler, duplicates of all configuration parameters have been introduced with the prefix optimizer_ (For example, optimizer_jit_above_cost).
Generated Columns
Postgres 12 introduced a standard-compliant syntax for generated columns. Currently there is only support for stored generated columns (fc22b66). Support for virtual, or scan-calculated, columns was planned to be implemented at a later date. What distinguishes this syntax from attributes with a default value is the impossibility of overriding the value by the user, as well as the possibility of using other string attributes in the expression (except for the same generated ones). Below is a synthetic example of using this functionality:
Only immutable functions can be used in an expression (for example, converting a point in time to a string is not suitable, since it is a stable function).
The value of the generated column is not exported or imported using
COPY.In Greenplum, the generated column cannot be used as a distribution key, which is logical, since the calculation of the value of such a column occurs in the node
Modify Tablealready after a potential redirection to the segment that will store this string.
SQL/JSON
Along with the Postgres 12 commits, support for the JSON path language became available. Along with functions and operators, JSON processing capabilities have expanded significantly. More details about the possibilities can be found at page official documentation. As an example, I will give a simple query that allows you to retrieve only those flights where the destination is Murmansk airport.
Autovacuum and autoanalyze
Clearing tables of deleted rows and keeping statistics up to date has always been a headache for Greenplum administrators. Routine tasks that do not fit within the limitations of maintenance windows, searching for criteria for launching – that’s all. What has changed in the new version?
Alas, automatic VACUUM for user tables did not happen. autovacuum processes will only automatically serve tables in the schema pg_catalog and, optionally, auxiliary Append Optimized relationship tables (gp_autovacuum_scope). Also, judging by the code, heap tables that are dangerously close to wraparound should be vacuumed, although I don’t see this in the documentation. In addition, autovacuum processes operate independently on each segment, distributed VACUUM VMware team postponed on the back burner.
But there is automatic collection of statistics. It will be initiated only from the coordinator – statistics on segments, as before, are not used. To make a decision, the Postgres approach will be used. For this purpose, the collection of statistics after performing DML operations from writing processes to the coordinator was improved.
Here anl_base_treshhold — threshold value (default — 50 tuples), anl_scale_factor — percentage of the number of tuples stored in the table (default — 10%). Specific values can be configured for each table individually.
However, how this miracle works in battle still needs to be assessed. Utility analyzedb has not disappeared from the supply.
Optimizing ANALYZE for AO tables
Another problem associated with collecting statistics was performance. ANALYZE for AO tables. The reason is the inability to use the approach used for heap tables, where we, based on a fixed page size, can sample pages first (function scan_analyze_next_block table accessor) and then tuples in them (scan_analyze_next_tuple). As a result, the table was scanned completely. This, in turn, gave rise to attempts to store the number of updates in a table in external tools (analyzedb) to make a decision on the advisability of the next collection of statistics.
Our colleague, @darthunix, after researching the issue offered (the link to the discussion disappeared into oblivion when the vanilla source code was closed) rework the approach by introducing virtual sampling units, which assume a fixed number of lines in them. The performance gain was achieved by skipping blocks of arbitrary length without wasting resources on decompression. However, discussion of the patch has died down.
As a result, in 2023 the developers rolled out their patch (2939f9a), which abandoned two-level sampling, adding optimization for the case when there is at least one index on the table (and therefore a directory of blocks – Block Directory) and received a 20-fold increase in productivity. To implement this approach, the interface of table access methods was extended with the function relation_acquire_sample_rows.
External tables
Support for external table protocols survived the update to the major version of Greenplum, but working with them is now hidden behind the FDW API. For this purpose, a built-in “remote” server was implemented gp_exttable_server, which is responsible for working with the selected protocol. Full syntax support declared CREATE EXTERNAL TABLE, however, the expression is converted on the fly into a foreign table definition. In the query plan there will also be Foreign Scan:
Resource groups
The resource limitation mechanism has undergone a significant overhaul. Let's start with the fact that GUC gp_resource_manager now has four meanings: none (default), group, group-v2, queue. And yes, the queues are the most lively.
In mode group-v2 cgroup v2 support has been implemented, which is so relevant for modern Linux distributions. Controller ioavailable for this version, made it possible to implement bandwidth limitation when accessing disk devices (resource groups parameter IO_LIMIT, read/write, MB/s or IOPS). It can be configured individually for different tablespaces. It is worth considering that with write restrictions, this mechanism will only work well for Append Optimized tables, since heap table pages are normally reset by a separate background process background writerwhich will not be subject to role restrictions.
But this process is subject to the restrictions of the new built-in resource group system_group. Initially, the postmaster process and its child processes (for example, autovacuum worker processes) that have not been assigned another group or allowed to run without restrictions are placed in this group.
Constraints for different versions of cgroup are implemented using different controllers. For example, for the CPU limit for the first version, cpu.cfs_quota_us in combination with cpu.sharesfor the second – cpu.max And cpu.weight. The only option left is to set a hard ceiling, similar to the combination CPU_RATE_LIMIT and GUC gp_resource_group_cpu_ceiling_enforcementset to true in Greenplum 6. The GUC mentioned has been removed. CPU_RATE_LIMIT divided into two separate options CPU_MAX_PERCENT And CPU_WEIGHTcontrolling the above cgroup parameters.
Perhaps the most controversial changes occurred in the memory consumption limiting subsystem. The developers present this as a simplification of the configuration. In fact, MEMORY_LIMIT now only affects calculations statement_mem for queries running in a group and can be easily overridden by the user using GUC gp_resgroup_memory_query_fixed_mem. There is no further control over memory consumption during execution by resource groups. At the moment when the memory consumption on the segment exceeds gp_vmem_protect_limit * runaway_detector_activation_percent, Greenplum will begin to interrupt requests starting with the heaviest ones in the segment, and not in the group, as was before. Options MEMORY_SPILL_RATIO And MEMORY_SHARED_QUOTAG.U.C. gp_resource_group_enable_recalculate_query_mem And gp_resource_group_memory_limit were deleted.
Let's sum it up
As we have seen, Greenplum 7 brings many new capabilities to the analytics workload. For myself, I noted the modern partitioning scheme, BRIN indexes, JIT compilation, expanding the capabilities of FDW, as well as numerous optimizations. On the other hand, some of the functionality of Postgres 12 will not be available in its original form for objective reasons. The ORCA scheduler will also require improvements to accommodate new features. But it so happens that radical changes require a period of stabilization. I hope that my article helped you answer the question – why do we need a new Greenplum.
I thank our designers @kawaii_anya and @nastoika_lera for their help in preparing this article.
