What is a columnar table

Technical architecture

PolarDB overall architecture

PolarDB uses a compute-storage decoupled architecture built on a distributed storage layer. The ParallelRaft protocol ensures consistency across multiple replicas, and OSS is transparently supported. Compute nodes consist of one read/write node (RW) and one or more read-only nodes (RO), and support 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.

Solution comparison

To help you understand and select the appropriate table type, the following table compares the key differences between traditional row-oriented tables (such as InnoDB) and PolarDB columnar tables:

Low-cost archiving of massive historical data

• Pain point: As your business grows, historical data such as orders, logs, and transaction records in core databases expands rapidly and occupies a large amount of expensive storage space. Traditional data migration solutions can reduce costs, but they take data offline so that it cannot be queried online directly, which increases the complexity of data usage.

• 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:

  • Sharp cost reduction: A compression ratio of up to 10:1 and the use of low-cost storage media such as OSS reduce storage costs by 90%.

  • Data stays online: Archived data always remains online and available. You can query and analyze it with standard SQL at any time, without a complex data restoration process.

  • Transparent to your business: At the application layer, a columnar table is just a regular MySQL table, so no changes are required.

Build a dedicated data warehouse

• Pain point: When enterprises build a dedicated data warehouse, they often face high hardware costs, complex data synchronization pipelines (ETL), and a high operations barrier, especially when a new technology stack such as ClickHouse is introduced.

• Solution: Relying on the massive data storage capacity of PolarStore, you can consolidate data from multiple upstream data sources and store it uniformly in X-Engine columnar tables. This gives you massive, low-cost storage capacity together with real-time aggregate analytical performance.

• Core value:

  • Significantly lower cost and complexity: You can build a data warehouse within the familiar MySQL ecosystem without purchasing expensive specialized hardware or introducing a heterogeneous analytical system, which effectively simplifies your technology stack and operations management.

  • Real-time data analytics: Upstream data can be consolidated into columnar tables in real time for analysis, which avoids the T+1 data latency of traditional ETL solutions.

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

Federated query analysis

• Pain point: The business data of an enterprise is usually stored in a distributed manner. Part of the data resides in online databases such as PolarDB, while the rest is stored in open formats such as Parquet and ORC in Object Storage Service (OSS). To analyze these two parts of data together, a complex ETL process is usually required to import the OSS data into the database.

• Solution: Use the PolarDB foreign table feature to directly access data in OSS.

• Core value:

  • In-place analysis: Without moving or importing data, create a foreign table in PolarDB for the files in OSS, and then query them with SQL.

  • Federated queries: You can easily use PolarDB local tables (row-oriented or columnar) together with OSS foreign tables in JOIN operations to achieve unified analysis of online and offline data.