Support ALTER TABLE ADD COLUMN

[qinxingnet]

What happens?

I am creating a table SQL using the public.user_activity table stored in columnstore as follows:

CREATE TABLE user_activity(
user_id BIGINT,
activity_type TEXT,
activity_timestamp TIMESTAMP,
duration INT
) USING columnstore;

The above is just a demo. When I inserted 20 million production environment data, I need to add a "from source" column field to distinguish whether it is from H5 or PC or mobile app,.

ALTER TABLE "public"."user_activity" 
ADD COLUMN "from_source" varchar(120);

But the engine reported an error:
ERROR: ALTER TABLE on columnstore table is not supported

To Reproduce

I'm wondering if adding fields like this is not allowed in existing column storage fields? Alternatively, other SQL should be used to modify the table structure so that new fields can be added to meet the requirements?

OS:

CentOS 9.2

pg_mooncake Version:

0.1.2

Postgres Version:

17.0

Are you using pg_mooncake Docker, Neon, or the extension standalone?

pg_mooncake Docker Image

[dpxcc]

As the error message indicates, ALTER TABLE is not supported yet

[qinxingnet]

As the error message indicates, ALTER TABLE is not supported yet

thank. Is there a planned version and release time for this fetch?

[dpxcc]

No set date yet, but we do plan to support it in the future

For now, you can work around by creating a new columnstore table

CREATE TABLE new_user_activity USING columnstore AS SELECT *, CAST('' AS VARCHAR(120)) AS from_source FROM t;

[qinxingnet]

thanks, The above SQL can solve temporary problems, as the requirements change quickly and involve data migration and development.
I hope the next supported version arrives soon.
good lucky!

[qinxingnet]

when we are already using pg_mooncake.
At present, migrating data using the suggested methods mentioned above is time-consuming and carries high risks. And it is not very user-friendly for JSON data type support.
Do you have any plans to add fields function in v0.2 or 0.3 ?
We are eager to obtain this feature.

[dpxcc]

We plan to support ALTER TABLE ADD/DROP COLUMN in v0.2. v0.2-rc is planned for end of Aug

And it is not very user-friendly for JSON data type support

Can you elaborate on this?

[qinxingnet]

Is it feasible for me to solve the problem of adding columns using JsonB fields in pghmooncake and then create virtual columns on JSON fields with appropriate Gaso references as a temporary solution?

[dpxcc]

Not really. The reason is that pg_mooncake isn’t currently optimized for updating a single column - updating even one column rewrites the entire table. So in practice, it’s no better than just creating a new table. We do plan to optimize this case in the future.

[qinxingnet]

Not really. The reason is that pg_mooncake isn’t currently optimized for updating a single column - updating even one column rewrites the entire table. So in practice, it’s no better than just creating a new table. We do plan to optimize this case in the future.

I don't understand. Should meta info be stored separately? Can simply adding fields backwards meet the requirements? Moreover, the newly added fields in the original data are all empty at the beginning, and the column values will not be written until the new data is updated. Why rewrite the entire table?
by the way , PGSQL18 has already released a beta version, will we also upgrade accordingly?

[dpxcc]

If you need to both add a new column and populate its values, that effectively results in a full table rewrite

However, if you’re only adding the column and your application handles missing values using a default, then no - the table won’t be rewritten (The ALTER TABLE feature we are currently working for 0.2 will also support default values)

We will support PG18 when it's officially released

[qinxingnet]

As mentioned above, if an update/Insert is triggered to add new column field data, will it rewrite the full table?