CQL for Cassandra 2.x

Table properties

A list of CQL table properties and their syntax.

CQL supports Cassandra table properties, such as comments and compaction options, listed in the following table.

In CQL commands, such as CREATE TABLE, you format properties in either the name-value pair or collection map format. The name-value pair property syntax is:

name = value AND name = value

The collection map format, used by compaction and compression properties, is:

{ name : value, name : value, name : value ... }

Enclose properties that are strings in single quotation marks.

See CREATE TABLE for examples.

CQL properties
CQL property Default Description
bloom_filter_fp_chance 0.01 for SizeTieredCompactionStrategyand DateTieredCompactionStrategy, 0.1 for LeveledCompactionStrategy Desired false-positive probability for SSTable Bloom filters. See Bloom filter below.
caching Cassandra 2.1:

ALL for keys

NONE for rows_per_partition

Cassandra 2.0.x: keys_only

Optimizes the use of cache memory without manual tuning. See caching below.
comment N/A A human readable comment describing the table. See comments below.
compaction SizeTieredCompactionStrategy Sets the compaction strategy for the table. See compaction below.
compression LZ4Compressor The compression algorithm to use. Valid values are LZ4Compressor), SnappyCompressor, and DeflateCompressor. See compression below.
dclocal_read_repair_chance 0.1 (Cassandra 2.1, Cassandra 2.0.9 and later) 0.0 (Cassandra 2.0.8 and earlier) Specifies the probability of read repairs being invoked over all replicas in the current data center.
default_time_to_live 0 The default expiration time in seconds for a table. Used in MapReduce scenarios when you have no control of TTL.
gc_grace_seconds 864000 [10 days] Specifies the time to wait before garbage collecting tombstones (deletion markers). The default value allows a great deal of time for consistency to be achieved prior to deletion. In many deployments this interval can be reduced, and in a single-node cluster it can be safely set to zero.
min_index_interval, max_index_interval (Cassandra 2.1.x) or index_interval (Cassandra 2.0.x) min_index_interval 128 and max_index_interval 2048, or index_interval 128 To control the sampling of entries from the partition index, configure the sample frequency of the partition summary by changing these properties. See min_index_interval and max_index_interval below.
memtable_flush_period_in_ms 0 Forces flushing of the memtable after the specified time in milliseconds elapses.
populate_io_cache_on_flush (Cassandra 2.0.x only) false Adds newly flushed or compacted sstables to the operating system page cache, potentially evicting other cached data to make room. Enable when all data in the table is expected to fit in memory. You can also configure the global compaction_preheat_key_cache option in the cassandra.yaml file.
read_repair_chance 0.0 (Cassandra 2.1, Cassandra 2.0.9 and later) 0.1 (Cassandra 2.0.8 and earlier) Specifies the basis for invoking read repairs on reads in clusters. The value must be between 0 and 1.
replicate_on_write (Cassandra 2.0.x only) true Removed in Cassandra 2.1. Applies only to counter tables. When set to true, replicates writes to all affected replicas regardless of the consistency level specified by the client for a write request. For counter tables, this should always be set to true.
speculative_retry 99percentile Cassandra 2.0.2 and later Overrides normal read timeout when read_repair_chance is not 1.0, sending another request to read. See speculative retry below.

Bloom filter

The Bloom filter property is the desired false-positive probability for SSTable Bloom filters. When data is requested, the Bloom filter checks if the row exists before doing disk I/O. Bloom filter property value ranges from 0 to 1.0. The effects of the minimum and maximum values are:

0 Enables the unmodified, effectively the largest possible, Bloom filter

1.0 Disables the Bloom Filter

The recommended setting is 0.1. A higher value yields diminishing returns.


Caching optimizes the use of cache memory without manual tuning. You set table properties to configure caching when you create or alter the table. Cassandra weights the cached data by size and access frequency. After configuring the caching table property, configure the global caching properties in the cassandra.yaml file. For information about global caching properties, see Cassandra 2.1 documentation or Cassandra 2.0 documentation.

Cassandra 2.1

Configure the cache by creating a property map of values for the caching property. Options are:

  • keys: ALL or NONE
  • rows_per_partition: number of CQL rows, NONE, or ALL
For example:
  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
) WITH caching = { 'keys' : 'NONE', 'rows_per_partition' : '120' };

Cassandra 2.0

Configure the cache using one of these caching property options:

  • all
  • keys_only
  • rows_only
  • none
You can specify a key or row cache, or specify both key and row caches using the options. For example:
// Cassandra 2.0.x only

  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
) WITH caching = 'keys_only';
Important: In Cassandra 2.0.x, use row caching with caution.


Comments can be used to document CQL statements in your application code. Single line comments can begin with a double dash (--) or a double slash (//) and extend to the end of the line. Multi-line comments can be enclosed in /* and */ characters.


The compaction property defines the compaction strategy class to use. Choose the compaction strategy that best fits your data and environment:
  • SizeTieredCompactionStrategy (STCS): The default compaction strategy. This strategy triggers a minor compaction when there are a number of similar sized SSTables on disk as configured by the table subproperty, min_threshold. A minor compaction does not involve all the tables in a keyspace. Also see STCS compaction subproperties.
  • DateTieredCompactionStrategy (DTCS): Available in Cassandra 2.0.11 and 2.1.1 and later. This strategy is particularly useful for time series data. DateTieredCompactionStrategy stores data written within a certain period of time in the same SSTable. For example, Cassandra can store your last hour of data in one SSTable time window, and the next 4 hours of data in another time window, and so on. Compactions are triggered when the min_threshold (4 by default) for SSTables in those windows is reached. The most common queries for time series workloads retrieve the last hour/day/month of data. Cassandra can limit SSTables returned to those having the relevant data. Also, Cassandra can store data that has been set to expire using TTL in an SSTable with other data scheduled to expire at approximately the same time. Cassandra can then drop the SSTable without doing any compaction. Also see DTCS compaction subproperties.
  • LeveledCompactionStrategy (LCS): The leveled compaction strategy creates SSTables of a fixed, relatively small size (160 MB by default) that are grouped into levels. Within each level, SSTables are guaranteed to be non-overlapping. Each level (L0, L1, L2 and so on) is 10 times as large as the previous. Disk I/O is more uniform and predictable on higher than on lower levels as SSTables are continuously being compacted into progressively larger levels. At each level, row keys are merged into non-overlapping SSTables. This can improve performance for reads, because Cassandra can determine which SSTables in each level to check for the existence of row key data. This compaction strategy is modeled after Google's leveldb implementation. Also see LCS compaction subproperties.

Hybrid (leveled and size-tiered) compaction improvements to the leveled compaction strategy reduce the performance overhead on read operations when compaction cannot keep pace with write-heavy workload. When using the LCS, if Cassandra cannot keep pace with the workload, the compaction strategy switches to STCS until Cassandra catches up. For this reason, it is a best practice to configure the max_threshold subproperty for a table to use when the switch occurs.

You can specify a custom strategy. Use the full class name as a string constant.


To configure compression, choose the LZ4Compressor, SnappyCompressor, or DeflateCompressor property to use in creating or altering a table. Use an empty string ('') to disable compression, as shown in the example of how to use subproperties. Choosing the right compressor depends on your requirements for space savings over read performance. LZ4 is fastest to decompress, followed by Snappy, then by Deflate. Compression effectiveness is inversely correlated with decompression speed. The extra compression from Deflate or Snappy is not enough to make up for the decreased performance for general-purpose workloads, but for archival data they may be worth considering. Developers can also implement custom compression classes using the org.apache.cassandra.io.compress.ICompressor interface. Specify the full class name enclosed in single quotation marks. Also use the compression subproperties.

min_index_interval and max_index_interval

The index_interval (Cassandra 2.0.x) property or the min_index_interval and max_index_interval (Cassandra 2.1) properties control the sampling of entries from the primary row index, configure sample frequency of the partition summary by changing the index interval. After changing the index interval, SSTables are written to disk with new information. The interval corresponds to the number of index entries that are skipped between taking each sample. By default Cassandra samples one row key out of every 128. The larger the interval, the smaller and less effective the sampling. The larger the sampling, the more effective the index, but with increased memory usage. In Cassandra 2.0.x, generally, the best trade off between memory usage and performance is a value between 128 and 512 in combination with a large table key cache. However, if you have small rows (many to an OS page), you may want to increase the sample size, which often lowers memory usage without an impact on performance. For large rows, decreasing the sample size may improve read performance.

In Cassandra 2.1, the name index_interval is replaced by min_index_interval and max_index_interval. The max_index_interval is 2048 by default. The default would be reached only when SSTables are infrequently-read and the index summary memory pool is full. When upgrading from earlier releases, Cassandra uses the old index_interval value for the min_index_interval.

speculative retry

To override normal read timeout when read_repair_chance is not 1.0, sending another request to read, choose one of these values and use the property to create or alter the table:
  • ALWAYS: Retry reads of all replicas.
  • Xpercentile: Retry reads based on the effect on throughput and latency.
  • Yms: Retry reads after specified milliseconds.
  • NONE: Do not retry reads.
Using the speculative retry property, you can configure rapid read protection in Cassandra 2.0.2 and later. Use this property to retry a request after some milliseconds have passed or after a percentile of the typical read latency has been reached, which is tracked per table. For example:
ALTER TABLE users WITH speculative_retry = '10ms';
ALTER TABLE users WITH speculative_retry = '99percentile';

Attention: Be sure this document version matches your product version