Summary of PostgreSQL Syntax and Features Not Supported by KlustronDB

KlustronDB supports most PostgreSQL DDL SQL syntax and almost all DML SQL syntax, except for the features listed below. For the SQL syntax and feature usage supported by KlustronDB, you can refer to the PostgreSQL documentation.

1. Unsupported features

1. Foreign Key

The DDL statements for creating foreign keys will be ignored, and a warning message will be returned to the client instead of an error message. This allows data import to proceed normally, but in KlustronDB, there is no foreign key constraint. The lack of support for foreign keys is due to performance considerations — since the actual application system's functional logic can completely ensure referential integrity, foreign keys are often disabled in centralized databases due to their significant performance overhead.

2. Array type and Range type

Starting from KlustronDB version 1.3, arrays of arbitrary dimensions with numeric element types are supported.

3. Streaming replication, including WAL-based physical replication and logical replication

Executing the DDL statements corresponding to these functions will result in errors in KlustronDB. KlustronDB has a unique Fullsync high-availability mechanism in its storage shard, based on MySQL's binlog replication.

4. Storage-related options for tables and indexes

• All CREATE TABLE statements

storage params

(storage parameters), as well as the tablespace (TABLESPACE) option. These options will be ignored and a warning message will be returned to the client. Added storage options adapted for KlustronDB. *

exclude

Constraint: Not supported; if used, an error will be reported.

• indexed

include

Field: Ignore. The Include field is used to achieve the effect of a covering index. After ignoring, the index functions can work normally.

• System columns (OID, CTID, etc.). Note that the tableOID system column is still valid and can be used normally.

These operations cannot be performed on user data tables, but they can be performed on metadata tables stored locally on the Kluscomp instances. 5. Storage management commands for data tables

including

cluster

,

vacuum

,

reindex

。

These commands are supported from version 2.0 onwards; earlier versions will report an error. Supported methods:

• CLUSTER command: Directly ignore it, because KlustronDB's data storage method is b tree rather than heap, so this operation is not required.
• VACUUM and REINDEX commands: send the OPTIMIZE TABLE command to the target table.

The autovacuum, which is automatically executed periodically by PostgreSQL background processes, will still run automatically in the background. Since only metadata tables are stored in the Kluscomp instances, occupying very little space (usually a few MB), unlike PostgreSQL, the periodically automatic VACUUM in KlustronDB does not consume a lot of IO bandwidth and does not affect system performance.

'create table as select from'

Sentence

This statement is supported starting from KlustronDB-1.2 version.

CREATE/ALTER/DROP TABLESPACE

Statement and tablespace management functions

KlustronDB does not use PostgreSQL's storage engine to store user data, and therefore does not support PostgreSQL's tablespace management feature.

9. Advanced features of create index KlustronDB does not support these index options and features:

• Expression as index field

partial index

Option: Ignore. This option is used to ignore certain data rows and not insert index rows for them. *

CONCURRENT

Option: Ignore. Parallel index building in KlustronDB is automatic and default. *

include

Clause: Ignore. Removing the INCLUDE field does not affect the correctness of the query results at all, and can improve index efficiency, possibly only having a slight impact on query performance. *

'COLLATE'

Clause: Prohibited, you cannot use other COLLATIONS for string comparison and sorting, you can only use the COLLATION of this column. *

'nulls first' / 'nulls last' ：

If specified, it will be automatically ignored, always using MySQL's default method (nulls first), which is exactly the opposite of PostgreSQL's default method (nulls last). *

opclass

Prohibited, an error will be reported if specified *

hash

Index type: Since MySQL does not support hash indexes, it will be automatically ignored even if specified, and the index will still use a B-tree.

rtree

Index type: Not supported; however, KlustronDB supports the use of spatial index types, in conjunction with the PostGIS extension component. *

exclude

Clause: Not supported, an error will occur if used.

Starting from KlustronDB-1.3 and KlustronDB-1.2.2 versions, it is possible to successfully create partial indexes. The partial option is ignored, meaning that all data rows will have index entries. This does not affect the correctness of query results, and only theoretically may have some impact on query performance.

create / alter database

The TABLESPACE option, these options will be ignored.

CREATE/ALTER TABLE

Unsupported feature KlustronDB automatically ignores these options: *

SET { WITH | WITHOUT } OIDS

SET { LOGGED | UNLOGGED }

• Column-level and table-level foreign key constraints
• CLUSTER options: CLUSTER ON index_name; SET WITHOUT CLUSTER
• Tablespace (TABLESPACE) Options
• Storage Parameters
• Stream replication settings (including

REPLICA

Clause of the keyword)

13. DDL statements cannot be executed in an explicit transaction

This requirement is consistent with MySQL. For PostgreSQL users, if this situation occurs, the SQL code needs to be modified: each DDL cannot be executed within an explicitly started transaction.

14. KlustronDB-1.1 does not support, but from KlustronDB-1.2 version onwards, the feature is supported Stored procedures, triggers, materialized views, policy (i.e., row level security), domain, JSON data management

15. Starting from KlustronDB version 1.3, PostGIS and PGVector plugins are supported.

You must use the PostGIS and PGVector plugins that come with KlustronDB, and you cannot use the PostgreSQL community version. Other extension plugins from the PostgreSQL community can be mounted on KlustronDB Kluscomp instances as long as they support PostgreSQL-11 (which is almost all of them), but you need to compile the plugin using KlustronDB's header files.

2. Features and characteristics that differ from PostgreSQL

1. ALTER TABLE ADD COLUMN

For newly added columns to an existing table, if there is a default value, the default value cannot be an expression that MySQL cannot execute; it can only be a constant or an expression that MySQL can execute. For example, the functions and operators in the expression must exist and be supported in MySQL. In addition, if this new column is of the sequence type, then the table can only add one sequence column, because KlustronDB will define this column as an auto-increment column in the Klustore instance to automatically assign values to the new column for existing rows. And MySQL allows only one auto-increment column per table. 2. indexed

'nulls first' / 'nulls last'

Option

No matter which option is specified, it always uses MySQL's default method (nulls first), which is exactly the opposite of PostgreSQL's default method (nulls last).

3. Data type support

• The valid range of the timestamp has been narrowed to

[ '0000-01-01 00:00:00.000000' , '9999-12-31 23:59:59.999999' ].

• The valid range of the date type has been narrowed to

[ '0000-01-01' , '9999-12-31' ].

4. The question mark (?) operator in JSON queries In KlustronDB-1.2 and earlier versions, using the question mark (?) operator in JSON queries is not supported, for example:

   SELECT jsonb '{"a":null, "b":"qq"}' ? 'a';

That's because the question mark (?) is used as a parameter placeholder in MySQL syntax prepare statement queries. Users can use json_exists() to perform an equivalent check.

Starting from KlustronDB version 1.3, the question mark (?) operator still serves as a parameter placeholder for prepared statements in MySQL connections, but in PostgreSQL connections, it can be used as a regular operator, including as a JSON data lookup operator, thereby providing better compatibility with PostgreSQL.

END