为何Snowflake、Redshift等列存数据库无法调整列顺序？

Great question—this is a common point of confusion when moving from row-oriented to columnar databases, and it boils down to how columnar storage and MPP architectures prioritize performance over schema flexibility. Let’s break it down step by step:

1. Row vs. Columnar Storage: The Core Difference #

Row-oriented databases (like PostgreSQL, MySQL) store entire rows as contiguous data blocks. When you reorder columns, you’re only changing how the database interprets existing physical data—since each row’s columns are stored side-by-side, updating metadata (like the ordinal in information_schema) is enough to map the new order to the same underlying bytes. No need to rewrite actual data.

Columnar databases (Redshift, Snowflake) do the opposite: each column lives in its own independent set of compressed files/blocks. For example, in Snowflake’s micro-partitions or Redshift’s columnar storage chunks, every column has its own sorted, optimized data. The "column order" you see in SELECT * is purely a logical construct—physically, columns have no inherent order relative to each other.

2. Why "Simple" Metadata Changes Don’t Work #

You might wonder, "Why not just tweak the ordinal value in the system catalog?" Here’s why that’s not feasible:

• System catalogs are more than information_schema: information_schema is a read-only view built on internal system tables (like Redshift’s pg_attribute or Snowflake’s INFORMATION_SCHEMA.COLUMNS). Modifying these views directly isn’t supported, and even if you could, it wouldn’t sync with critical metadata like column permissions, constraints, and query statistics.
• MPP distribution adds complexity: In MPP databases, data is split across dozens of nodes. Reordering columns would require syncing metadata across all nodes, which is non-trivial. If your table uses a distribution or sort key tied to column positions, reordering could invalidate these keys, forcing a full data redistribution (exactly what a deep copy does).
• Optimizer dependencies: Query optimizers rely on cached metadata about column layouts and co-location patterns. A manual ordinal change would break this cache, leading to incorrect query plans or even crashes.

3. Why Columns Can Only Be Added to the End #

When you add a column to a columnar database, it’s appended as a new set of physical blocks (one per node/partition). Since columns are stored independently, this is a fast, lightweight operation—no need to rewrite existing column data. Inserting a column between existing ones would require:

• Updating every query that references column positions (like SELECT col1, col2 instead of SELECT *), which is error-prone.
• Rebuilding dependent objects (views, stored procedures) that rely on column order.
• Forcing the optimizer to re-learn column co-location patterns (many columnar databases optimize for frequently queried column groups), which could hurt performance until statistics are refreshed.

Vendors restrict column additions to the end to keep operations fast and avoid unintended side effects.

4. Why Deep Copies Are the Only Safe Option #

When you need to reorder columns, a deep copy (create a new table with the desired order, migrate data, rename) is the only reliable method because:

• It lets you re-optimize the table’s physical layout: you can update distribution keys, sort keys, or clustering keys at the same time, boosting query performance.
• It ensures all metadata (statistics, permissions, constraints) aligns correctly with the new column order.
• It avoids corrupting the database’s internal state, which could happen if you tried to modify system tables directly.

Wrap-Up #

Columnar databases are built for large-scale analytics speed, and that design comes with tradeoffs. The same architecture that lets you scan billions of rows in seconds (by only reading relevant columns) makes lightweight schema rearrangements impractical. While it’s less convenient than row-oriented databases, this tradeoff is intentional to keep query performance consistent at scale.

内容的提问来源于stack exchange，提问作者iDataEngX