Using advanced SQL features in KunlunBase

KlustronDB supports a wide range of advanced SQL features, including views, materialized views, triggers, stored procedures, domains, CHECK constraints, Row Level Security (RLS), multi-level and multi-granularity access control, and more. These features of KlustronDB are inherited from PostgreSQL and have been extended and enhanced by us to ensure that they continue to work properly in the KlustronDB distributed database system.

These advanced SQL features already existed in the era of classic databases. In today's era of distributed databases, are these features still useful? What impact do they have on database system performance? Are there better alternatives? These are questions that technical personnel with some database experience would consider. Below, we will introduce the use cases for each feature, as well as the value and pros and cons of these features in KlustronDB.

Access Control

In this era of informatization and intelligence, data is widely likened to gold, so a database is like a treasury. Only when precise Access Control Rules (ACR) are defined at the source of the data, that is, in the database system, ensuring that only users with the relevant permissions for specific data can retrieve data from the DBMS or perform insert, delete, and update operations on the data, can data security be guaranteed. Implementing ACR outside the database system is like imagining opening the doors of a treasury, allowing gold to be taken out by anyone without any loss—it is very naive and irresponsible. Therefore, managing access control rules for data is a key task of a professional DBA.

KlustronDB inherits the full access control system of PostgreSQL, including the management of roles and users, connection authorization (pg_hba.conf), ACR management and execution, and its functions and usage are exactly the same as PostgreSQL. Therefore, this article focuses only on several features that are easily overlooked by users.

KlustronDB supports multi-level and multi-granularity access control, allowing ACR to be set at the granularity of database, schema, table, row, column, domain (which can be understood as a column type), and sequence. ACR can control all operation types that are meaningful for a specific type of database object.

For every user created on a Kluscomp instance, KlustronDB also creates a user with the same name on the Klustore instance and uses a username identical to the currently logged-in user to connect to the Klustore instance. Therefore, the owner of each table shard created by a user on the Klustore instance is the username of the user logged in at the time of the operation. Although Klustore instances also have access control functions, KlustronDB performs access control checks on the Kluscomp instances because, when executing an SQL statement, it only enters the query optimization and execution phase after passing the access control check. KlustronDB Klustore instances still require username and password verification to connect, except in special cases where the DBA does not need to connect directly to the Klustore instances. Application software usernames should never directly connect to the Klustore instances (and application software cannot connect to the Klustore instance using the username and password it created on the Kluscomp instance). Otherwise, it would not only bypass the access control mechanism on the Kluscomp instance but also, if the user performs an ALTER on the data tables, the Kluscomp instance would be unaware, leading to serious metadata mismatches.

KlustronDB also supports defining ACR (Access Control Rules) in views, materialized views, and stored procedures. Through views, very fine-grained and flexible ACR can be defined for combinations of specific rows and columns across multiple tables, which is introduced in the views section. By defining ACR through stored procedures, the types of operations controlled by ACR are extended from basic operations such as insert, delete, update, and select to an entire set of basic operations combined in a user-defined manner. At the same time, the data controlled by ACR is defined by the procedure logic, allowing for maximum flexibility and customizability.

Row Level Security

Row-level ACR in KlustronDB is implemented based on the Row Level Security, RLS feature. RLS, combined with column-level ACR in GRANT statements, allows users to define access control rules at the finest granularity, that is, at the field level. Users need to execute CREATE POLICY to specify which rows of a particular table a certain user can perform specific operations on (SELECT, INSERT, UPDATE, DELETE). The 'which rows' is an expression that references certain columns of the table, calculating a boolean value based on the corresponding fields of each referenced column in the row. Only when this value is true can the user perform the operation specified by the policy on that row. Multiple POLICIES can be defined for a table to separately manage the permissions of multiple users for each operation on that table.

RLS will have a slight impact on the performance of add, delete, update, and query operations, but it provides very fine-grained data access control capabilities and data security protection. In practice, a POLICY can be created for individual tables containing critical data. For example, in an employee information table, the salary column should not be visible even to the DBA account; only senior executives such as the HR manager can see the salaries of all employees, department managers can see the salaries of employees in their department, and individual employees can see their own salaries. Contract prices with suppliers, product prices for specific customers, and other customized details are highly confidential information for many companies. These pieces of information are not allowed to be seen by the DBA; only accounts of designated senior executives can access them. This is the only way to fully protect the company's confidential information.

At the same time, application software needs to allow users with specific advanced permissions to connect to the database using their respective dedicated database accounts, rather than using a unified database account to connect. The unified database account only has the most basic permissions and cannot access any data that requires controlled access.

Domain

The Domain in KlustronDB and PostgreSQL is the concept of a domain in relational algebra; it is equivalent to a column type. A Domain carries the column's data type, default value, and column constraints (including whether it can be NULL and CHECK constraints). Therefore, after defining a domain using CREATE DOMAIN, this domain can be used as the column type for any table, as long as it fits the business logic. You can use ALTER DOMAIN to modify a domain's definition, but you cannot change its data type. Meaningful modifications mainly include default values and constraints. After modification, the corresponding columns in all tables using this domain are updated accordingly.

Constraint

The common data validity constraints of a table include general rules such as data type, primary key, uniqueness, and nullability. These can regulate data validity to a certain extent, but they cannot specifically define the valid value range and constraint relationships of one or more fields in each row of a table. Only by using CHECK constraints can this be achieved, so CHECK constraints are extremely important for applications to correctly implement business logic.

Data validity verification rules must be defined at the data source, that is, in the database system, rather than relying on the application software layer to perform such checks, otherwise, in practice, illegal data will inevitably enter the database system and affect the normal operation of the application system. The reason for this is that as the application software continues to iterate and expand, those data validity rules may have been strictly followed in the initial system design, but later may be omitted due to changes in developers and the loss of technical documentation.

Illegal data is like poison and pollutants; it can contaminate valid data, affect the stability of application systems, and even mislead data analysis and decision-making. Once illegal data enters the database, the existing business logic may experience unpredictable abnormal behavior, impacting system stability or even preventing it from functioning correctly. This is because the modules using this data process it according to business requirements and do not anticipate the presence of illegal data. Therefore, the database's data validity verification mechanism acts like a filter, filtering out toxic, illegal, and incorrect data, ensuring that the data stored in the database is meaningful and legal.

CHECK constraint

Defining a CHECK constraint in the CREATE TABLE statement allows the validation of one or more fields of a row against user-defined validity rules during each INSERT and UPDATE. INSERT/UPDATE statements that do not comply with the table's validity rules will fail to execute and be rolled back, thereby preventing invalid data from entering the database.

Foreign Key

In the era of classic databases, foreign keys were a commonly used constraint. However, the referential integrity rules of foreign keys have a significant impact on the performance of INSERT, UPDATE, and DELETE operations. In distributed database systems, this performance overhead is even greater --- a row in table t1 that references a row in table t2 may be on another node, making referential integrity checks very expensive. Therefore, KlustronDB does not support foreign keys. This is the only commonly used classic SQL advanced feature that KlustronDB does not support.

The main reason why foreign keys were initially necessary is that when Edgar Codd first designed the relational model, he envisioned SQL as a language for users to directly manipulate data in the database. In other words, SQL was inputted by humans, much like computers at that time were used by entering commands for execution. This made it easy for human errors to cause mistakes. However, in modern practical applications, databases operate as backend servers for application software, and SQL is either written by application developers or automatically generated by ORM middleware, and then sent to the database by the application software. This eliminates human errors during actual operation. Such errors are addressed during the development and debugging stages.

At the same time, the application logic can also be correctly customized to implement referential integrity rules that meet the requirements. If a row in t2 is to be deleted, the row in t1 that references it can be explicitly deleted by the application software, or kept, or set to NULL or other values. This is safer and more flexible, and can also prevent accidental data loss caused by cascading deletes.

Special Advice for MySQL Users --- Use Appropriate Data Types

MySQL did not officially support CHECK constraints until version 8.0.16. Before that, user-defined CHECK constraints were directly ignored by the parser. The absence of CHECK constraints once posed significant risks for MySQL application development, as relying on application software to perform data validity checks could easily lead to validity rules being unintentionally or even intentionally bypassed. Therefore, it is strongly recommended that MySQL users quickly develop the good habit of setting validity check rules in CREATE TABLE statements.

MySQL supports very flexible data type conversions, and this flexibility is also a curse, causing some beginner MySQL users to lose their understanding of data types, resulting in the functionality of data type constraints, validity checks, and comparison methods being invalidated or misused.

Data types themselves are a basic form of data validity constraint, specifying the range of valid data and the operations that can be performed, particularly methods for comparison and data ordering. Not all data types can be converted into each other; for example, converting between dates, times, timestamps, and numeric values usually makes little sense. However, MySQL actually supports this and its conversion methods are quite imaginative — for instance, the date value '2023-07-16' can be converted to and from the integer 20230716.

This kind of arbitrary data type conversion not only easily causes illegal values to unexpectedly enter the data table, but also easily leads to indexes not working properly or not as expected, such as not finding values that should be in the table, which is common in range searches; or not using an index when it should have been used, among other issues.

For example, some users are accustomed to defining all numeric types in a table as strings. The problem with this is that when you need to perform a range query, the returned results are actually incorrect because the data is compared as strings. Unless you pad zeros on the left of the string-represented numbers, but in that case, you either face the risk of numeric overflow due to a small predefined width, or you end up using much more space than numeric types. At the same time, the CPU overhead of string comparison is also much higher than the comparison of numeric types other than NUMERIC/DECIMAL.

Some users are accustomed to defining timestamp columns as strings or datetime types, which results in the timezone information of the timestamp type not taking effect, and values cannot be displayed in the local timezone for users in different timezones. At the same time, defining any date, time, datetime, or timestamp column as a string will cause problems such as incorrect range queries, inconsistent data display formats, failure of localization (l10n) and internationalization (i18n), entry of invalid values into the data table, and it also consumes much more storage space compared to using the correct type.

Finally, let's talk about the character set and collation attributes of string types. MySQL supports specifying charset and collation at both the table and column levels. This is actually a very flexible feature, but at the same time, it can be a huge pitfall for many users. In practical applications, many users often encounter issues caused by this flexibility because the charset and collation of two columns do not match. For example, if table t1.a and t2.b are both of type varchar(64) and have UNIQUE constraints, but their collations are different, and then a query contains a condition like WHERE t1.a = t2.b AND t2.b='xxx', you might find via EXPLAIN that a full table scan is performed on t1 instead of using the unique index on t1.a, resulting in very poor performance. Another type of problem is the performance impact caused by collation conversion operations. If a statement performs collation conversion operations on fields of a large number of rows, the performance overhead can also be considerable.

Therefore, it is best to consistently use UTF8MB4 across a database. The UTF8MB4 character set includes all human languages' characters as well as emoji packs, making it very comprehensive and eliminating the need for other character sets. Some domestic users are accustomed to using character sets like GB2312, GB18030, or GBK, but these character sets do not include many foreign characters. If someday your business expands internationally, you will find that input data from overseas users cannot be correctly used in your system. So, it is still recommended to standardize on UTF8MB4.

Views and Materialized Views

Views are a very useful advanced SQL feature. They separate the storage details of the tables from the business-related data, the logical meaning of the data, and the application scenarios, similar to a data interface. If the storage structure of a table changes, you only need to modify the view definition, and usually, the SQL in the application layer does not need to be changed. For application developers, querying data from a view is more intuitive. It usually eliminates the need to write multi-table join statements or construct projection expressions, as these are already defined in the view definition. Typically, querying a view only requires adding a filter condition.

KlustronDB supports defining access control rules for views, that is, data access control rules are defined according to the application scenarios and logical meaning of the data, making them more intuitive and precise. Therefore, a well-designed application system should have a view layer, where application developers almost always query a view to obtain data, and rarely need to directly query the base tables.

A materialized view is based on a view and stores the query result data of the view in a data table file. This way, if a view is queried repeatedly, it can avoid repeatedly executing the query statements in the view definition (which are usually quite complex), thereby achieving better query performance. However, the cached results of a materialized view will gradually become outdated as the related base tables are continuously updated. Therefore, after creating a materialized view, it is necessary to regularly execute the REFRESH MATERIALIZED VIEW statement to update the cached data.

In KlustronDB, the data of materialized views is stored in Klustore instances. When a Kluscomp instance replays a CREATE MATERIALIZED VIEW statement, it does not refresh the data again, and the REFRESH MATERIALIZED VIEW statement does not enter the DDL log. In other words, when Kluscomp instances replay DDL, they will not repeatedly refresh the materialized view, preventing performance issues.

Trigger

A trigger is a mechanism that allows users to define operations to be executed before or after performing insert, update, or delete actions on each row of a table or on rows that meet certain filter conditions (row-level trigger), as well as before and after executing insert, update, or delete statements on a table (statement-level trigger).

So, why not perform these operations in the application code? For row-level triggers, it is really impossible to precisely capture this timing outside the database to perform such customized operations, unless the entire data update logic is implemented as a stored procedure, using a cursor to iterate over the rows that meet the conditions and performing the corresponding row-level customized operations before and after insert, delete, and update; therefore, row-level triggers do have certain usage value. KlustronDB supports row-level triggers.

As for statement-level triggers, they can actually be completely implemented in the application code itself, as long as the operations are executed within the same transaction before or after the statement. However, considering the maintainability and lifecycle of application software, in practice, many applications become very difficult to maintain after a few years. In such cases, if there is a need to add these custom operations but modifying the application code is not feasible, adding statement-level triggers to extend the behavior of statements becomes quite a good remedial measure and a means to prolong the life of the application software. DBAs can implement such changes at any time according to need, without relying on the application software vendor's release cycle, for example, to add some auditing operations.

Performance overhead

Whether it is a row-level or statement-level trigger, it increases the workload of query processing and may inadvertently have a serious impact on the performance of queries. In the era of classic databases, database systems could not scale horizontally, making this impact even more severe and difficult to resolve, and the only solution was to replace it with a more powerful and much more expensive server. For the KlustronDB distributed database, Kluscomp instances can be added as needed to increase computing capacity, so the performance overhead and pressure caused by triggers and stored procedures can be perfectly resolved by adding more Kluscomp instances.

Alternatives to row-level triggers

For some requirements, using a row-level trigger approach may be able to solve the problem by processing data update event streams afterward. For MySQL, this means handling the binlog stream; the KlustronDB CDC feature can output a data change stream for external plugins to consume. This approach is currently quite common and can also be used with related toolchains.

Stored procedure

Stored procedures allow users to define a set of operations and then call it, which is similar to defining and calling functions in software development languages. Stored procedures avoid the network delay and bandwidth consumption of transferring data between the database server and the application server, as the data is only operated on within the database cluster, which can sometimes achieve relatively good performance.

Stored procedures can achieve better and more flexible data access control. After sensitive data is calculated and processed through a stored procedure, the caller only receives the final processed result, allowing sensitive data to remain completely within the database. You can define user and role execution permissions for stored procedures to ensure authorized operations.

However, if there is a heavy computational load in the stored procedure, its actual execution performance is quite concerning, especially compared to most commonly used programming languages today. At this point, the time spent on computation may be greater than the time spent on data transfer, resulting in lower performance. Additionally, debugging stored procedures is also relatively troublesome because there is no debugger to assist, so the development and maintenance cost is relatively high.

Stored procedures also have relatively good effects on maintaining old systems. For functionality implemented by calling stored procedures, in theory, when application developers cannot quickly update the application system, end users still have the opportunity to partially update the application software by updating stored procedures and other related database objects such as tables, transactions, triggers, etc.

Stored procedures and triggers have similar performance overhead and scalability issues, but for KlustronDB, the solution is the same --- just add more Kluscomp instances.

KlustronDB inherits the powerful stored procedure capabilities of PostgreSQL. It not only supports writing stored procedures in PL/SQL, but also allows writing stored procedures using languages such as Python and Perl. Moreover, the PostgreSQL community has stored procedure plugins for languages like Lua, Java, and JavaScript. Once installed, these languages can be used to write stored procedures, and their execution efficiency is higher than PL/SQL, thus avoiding the performance weaknesses of PL/SQL stored procedures. Additionally, there are very rich function libraries and class libraries, such as Python's machine learning and data analysis libraries. This allows KlustronDB's data nodes to be used as data processing nodes, with advantages including at least two aspects: data analysis and privacy computing can be performed without leaving KlustronDB, and KlustronDB's Kluscomp instances can be added as needed to increase data analysis and processing capabilities, achieving horizontal scalability of computing power.

Summary

Regarding the advanced SQL features discussed in this article, our view is that, aside from foreign keys, the other features have certain practical value, especially multi-level and multi-granularity access control, as well as various constraints other than foreign keys, and features such as views and materialized views, which are very valuable for designing and implementing excellent application software systems.

In particular, it is important to accurately use data types and carefully consider NULL-ability and uniqueness, as these two aspects are often easily overlooked. If you use ALTER TABLE to change a column's nullability, or modify the data type even if it is the same base type but with increased width (e.g., changing int to bigint), it requires copying all the data in the table. It is ideal to define CHECK constraints when creating a table; for architects who understand the application's requirements and business logic, this is achievable. Later modifications may face the issue of dealing with existing invalid data in the table, and these remedial tasks are often ultimately abandoned due to the difficulty of maintenance, which can affect the stability, usability, and maintainability of the application system.

It is recommended to use stored procedures for data analysis scenarios, as this allows the use of rich data analysis and machine learning libraries from languages like Python to quickly develop related functions, and complete analysis and computation tasks without exporting data, enabling rapid iteration. Additionally, users can take advantage of KlustronDB's ability to scale Kluscomp instances horizontally and add nodes as needed, so analysis and computation tasks will not encounter performance bottlenecks. DBAs can allocate a separate set of Kluscomp instances for data analysts, allowing data analysis to be completed without affecting transaction processing load.

Triggers and stored procedures should be decided on a case-by-case basis, and in many cases, these two features may no longer be the optimal technical approach to implement relevant functional logic. They are more often used as a workaround for compatibility with older application systems.

END