What is a columnar table

Technical architecture

PolarDB overall architecture

PolarDB uses an architecture with separation of compute and storage, built on a distributed storage layer. The system uses the ParallelRaft protocol to ensure multi-replica consistency and transparently supports OSS. Compute nodes consist of one read-write node (RW) and one or more read-only nodes (RO), supporting both the InnoDB and X-Engine engines. A database proxy acts as a bridge between applications and compute nodes, providing read/write splitting and load balancing.

Comparison of table types

The following table compares the key differences between traditional row-oriented tables (such as InnoDB) and PolarDB columnar tables to help you choose the appropriate type:

Low-cost data archiving

• Pain point: As businesses grow, historical data such as past orders, logs, and transaction records can rapidly accumulate in core databases, consuming expensive storage space. While traditional data migration solutions can reduce costs, they often take data offline, making online access complex.

• Solution: Use columnar tables for online data archiving. You can migrate cold data from row-oriented tables (InnoDB) or entire historical data tables to columnar tables within the same PolarDB cluster.

• Core value:

  • Drastically lower costs: With a compression ratio of up to 10:1 and the use of low-cost storage media like OSS, storage costs can be reduced by up to 90%.

  • Online data availability: Archived data remains online and available. You can query and analyze it at any time using standard SQL without a complex data migration process.

  • Seamless application integration: From the application's perspective, a columnar table functions just like a regular MySQL table, requiring no code changes.

Dedicated data warehouse

• Pain point: When building a dedicated data warehouse, enterprises often face high hardware costs, complex data synchronization pipelines (ETL), and a steep operational learning curve, especially when introducing new technologies like ClickHouse.

• Solution: You can leverage the massive storage capacity of PolarStore to aggregate data from multiple upstream sources and store it uniformly in X-Engine columnar tables. This allows you to benefit from massive, low-cost storage and real-time aggregate analysis performance.

• Core value:

  • Lower cost and complexity: Build a data warehouse within the familiar MySQL ecosystem without purchasing expensive dedicated hardware or introducing heterogeneous analytical systems. This approach simplifies your tech stack and reduces operational overhead.

  • Real-time data analysis: Supports real-time consolidation of upstream data into columnar tables for analysis, avoiding the T+1 data latency common in traditional ETL solutions.

  • Massive data processing capacity: The X-Engine storage architecture and efficient compression enable low-cost storage and processing of petabyte-scale data.

Federated query analysis

• Pain point: Business data is often split across different systems. Some data resides in online databases like PolarDB, while other data is stored in open formats like Parquet or ORC on OSS. Analyzing these disparate datasets together typically requires a complex ETL process to import the OSS data into the database.

• Solution: Use the PolarDB foreign table feature to directly query data on OSS.

• Core value:

  • In-place analysis: Query data where it lives. Create a foreign table in PolarDB that points to your files on OSS, and then use standard SQL to query them directly without moving or importing the data.

  • Federated query: Easily perform JOIN operations between local tables in PolarDB (either row-oriented or columnar) and OSS foreign tables to run unified analysis across online and offline data.