Support and Constraints

A hybrid data warehouse is compatible with all column-store syntax.

**Table 1** Supported syntax
Syntax	Supported
CREATE TABLE	Yes
CREATE TABLE LIKE	Yes
DROP TABLE	Yes
INSERT	Yes
COPY	Yes
SELECT	Yes
TRUNCATE	Yes
EXPLAIN	Yes
ANALYZE	Yes
VACUUM	Yes
ALTER TABLE DROP PARTITION	Yes
ALTER TABLE ADD PARTITION	Yes
ALTER TABLE SET WITH OPTION	Yes
ALTER TABLE DROP COLUMN	Yes
ALTER TABLE ADD COLUMN	Yes
ALTER TABLE ADD NODELIST	Yes
ALTER TABLE CHANGE OWNER	Yes
ALTER TABLE RENAME COLUMN	Yes
ALTER TABLE TRUNCATE PARTITION	Yes
CREATE INDEX	Yes
DROP INDEX	Yes
DELETE	Yes
Other ALTER TABLE syntax	Yes
ALTER INDEX	Yes
MERGE	Yes
SELECT INTO	Yes
UPDATE	Yes
CREATE TABLE AS	Yes

To use HStore tables, use the following parameter settings, or the performance of HStore tables will deteriorate significantly:
In clusters of version 9.1.1.100 and later versions, the recommended parameter settings are as follows: autovacuum_max_workers_hstore=3, autovacuum_max_workers=2, autovacuum_max_workers_col=2, autovacuum=true.
In version 8.2.1 and later, you can now clear the dirty data from column-store indexes. This is especially beneficial when dealing with frequent data updates and imports into the database. By efficiently managing the index space, it improves both the import and query performance.
In clusters of version 9.1.1.100, dirty page clearing in hstore tables depends on the column-store vacuum and asynchronous sorting mechanisms. By default, a CU with less than 1,000 rows is regarded as a small CU. Small CUs on dirty pages are merged through asynchronous sorting, and non-small CUs are rewritten to new files through column-store vacuum. Column-store vacuum is controlled by the colvacuum_threshold_scale_factor parameter.
When using HStore asynchronous sorting, pay attention to the following:
DML operations on certain data may be blocked during asynchronous sorting. The maximum blocking granularity is the row threshold for asynchronous sorting. This function is not recommended for frequent DML operations.