Preview —Spanner columnar engine

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

This page provides an overview of the columnar engine for Spannerand describes how to use it.

Operational databases commonly extract, transform, and load (ETL) data into anOLAP system for analytics. This system is often part of a data warehouse. WithData Boost for Spanner, Spanneralready separates analytical compute, which ensures transactional stability.

Columnar engine is a storage technique that analytics systems use to speedup scans when compared to batch-based scans. Spanner columnarengine lets you run analytics with significantly improved performance on thelatest operational data. Spanner columnar engine increases scanperformance by up to 200 times, eliminating the need for ETL while maintainingstrong consistency.

Spanner'sRessi formatuses a partition attributes across (PAX) column-wise layout for efficient scanswithin a data block. However, this format colocates all columns of a row withina given block for fast single-row lookups. Unlike Ressi,Spanner's columnar engine dedicates runs of blocks to a singlecolumn. This approach is more efficient for sequential scans, asSpanner only needs to read the columns referenced in the query.

Spanner builds the columnar representation in the background (aspart of compactions), and automatically merges the representation with thelatest updates at query time to provide strong consistency. Queries thatwouldn't benefit from columnar storage can continue to use PAX.

Workloads that would benefit from using columnar engine include the following:

• Operational reporting extract up-to-the-second business intelligence fromthe latest operational data.
• Served analytics power dashboards and custom drill-downs with interactivelatency.
• Federated analytics seamlessly combine data from Spanner andother sources in BigQuery.

Spanner instance backups don't includethe columnar format.

Best practices for using columnar engine

This section describes best practices when using columnar engine.

Large scan optimization

Columnar engine optimizes queries that scan large amounts of data. For smallerdata scans or queries with quickly satisfiedLIMIT clauses, row-based scansmight be more efficient.

Essential columns

If you useSELECT *, Spanner reads all columns from columnarstorage. To maximize performance, specify only necessary columns. For example,SELECT column1, column2 FROM ....

Performance bottleneck identification

Columnar engine is effective for scan-bound workloads. To identify a scan-boundworkload, check thequery plan for a highlatency level in theTable scan node. If your query isn't scan-bound,prioritize other optimizations first. Columnar engine can provide benefitslater if your optimizations make the query scan-bound.

Optimal columnar coverage

After youenable columnar engineon a database that already contains data, Spanner's automaticcompaction process converts data to columnar storage asynchronously in thebackground. To see how much your query benefits, check theColumnar read share percentagein the query plan.

High churn data management

High write rates from updates or random inserts can affect the columnar engine'sperformance. Append-only workloads experience minimal impact from using columnarengine. Compaction is a background process, which typically is spread out overmultiple days, but can happen sooner if the size of the database growssubstantially. Alternatively, design the schema to favor append-only writes atthe split level. For more information, seesharding of timestamp-ordered data in Spanner.

Workload isolation

There are two techniques that you can use to isolate analytical queries fromtransactions:

• Usedirected reads to route reads to read-onlyreplicas.
• UseData Boost for federated queries.

Pricing

Billing for the Spanner columnar engine is based on storageusage. After you enable the Spanner columnar engine andSpanner completes data compaction, storage usage increases toinclude the new columnar representation. Columnar engine provides storagemetrics that let you monitor the impact to storage. For more information, seeColumnar data storage metrics.

Spanner columnar engine isn't impacted by the8 bytes per celloverhead.

Preview limitations

• Columnar engine only supports the GoogleSQL interface.

What's next

• Learn how toenable columnar engine.
• Learn how toquery columnar data.
• Learn how tomonitor columnar engine.