:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import datetime
from collections.abc import Iterable
from datetime import timedelta
from time import sleep
import pytest
import time_machine
from posthog.test.base import BaseTest, ClickhouseTestMixin, _create_event, get_index_from_explain
from unittest import TestCase
from unittest.mock import patch
from parameterized import parameterized
from tenacity import retry, stop_after_attempt, wait_exponential
from posthog.clickhouse.client import sync_execute
from posthog.clickhouse.materialized_columns import TablesWithMaterializedColumns
from posthog.conftest import create_clickhouse_tables
from posthog.constants import GROUP_TYPES_LIMIT
from posthog.models.event.sql import EVENTS_DATA_TABLE
from posthog.models.property import PropertyName, TableColumn
from posthog.settings import CLICKHOUSE_DATABASE
from ee.clickhouse.materialized_columns.columns import (
MATERIALIZATION_VALID_TABLES,
MaterializedColumn,
MaterializedColumnDetails,
_clear_materialized_columns_cache,
backfill_materialized_columns,
drop_column,
get_bloom_filter_index_name,
get_bloom_filter_lower_index_name,
get_enabled_materialized_columns,
get_materialized_columns,
get_minmax_index_name,
get_ngram_lower_index_name,
materialize,
update_column_is_disabled,
)
EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS = [f"$group_{i}" for i in range(GROUP_TYPES_LIMIT)] + [
"$session_id",
"$window_id",
]
class TestMaterializedColumnDetails(TestCase):
def test_column_comment_formats(self):
old_format_comment = "column_materializer::foo"
old_format_details = MaterializedColumnDetails.from_column_comment(old_format_comment)
assert old_format_details == MaterializedColumnDetails(
"properties", # the default
"foo",
is_disabled=False,
)
# old comment format is implicitly upgraded to the newer format when serializing
assert old_format_details.as_column_comment() == "column_materializer::properties::foo"
new_format_comment = "column_materializer::person_properties::bar"
new_format_details = MaterializedColumnDetails.from_column_comment(new_format_comment)
assert new_format_details == MaterializedColumnDetails(
"person_properties",
"bar",
is_disabled=False,
)
assert new_format_details.as_column_comment() == new_format_comment
new_format_disabled_comment = "column_materializer::person_properties::bar::disabled"
new_format_disabled_details = MaterializedColumnDetails.from_column_comment(new_format_disabled_comment)
assert new_format_disabled_details == MaterializedColumnDetails(
"person_properties",
"bar",
is_disabled=True,
)
assert new_format_disabled_details.as_column_comment() == new_format_disabled_comment
with pytest.raises(ValueError):
MaterializedColumnDetails.from_column_comment("bad-prefix::property")
with pytest.raises(ValueError):
MaterializedColumnDetails.from_column_comment("bad-prefix::column::property")
with pytest.raises(ValueError):
MaterializedColumnDetails.from_column_comment("column_materializer::column::property::enabled")
class TestMaterializedColumns(ClickhouseTestMixin, BaseTest):
@classmethod
def setUpClass(cls) -> None:
cls.recreate_database()
super().setUpClass()
def tearDown(self):
self.recreate_database()
super().tearDown()
@staticmethod
def recreate_database() -> None:
# Dropping the database removes materialized columns behind the metadata cache's back.
for table in MATERIALIZATION_VALID_TABLES:
_clear_materialized_columns_cache(table)
sync_execute(f"DROP DATABASE {CLICKHOUSE_DATABASE} SYNC")
sync_execute(f"CREATE DATABASE {CLICKHOUSE_DATABASE}")
create_clickhouse_tables()
def test_unknown_table(self):
assert {} == get_materialized_columns("some weird string")
def test_get_columns_default(self):
assert sorted([property_name for property_name, _ in get_materialized_columns("events")]) == sorted(
EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS
)
assert sorted(get_materialized_columns("person")) == sorted([])
def test_caching_and_materializing(self):
base_time = datetime.datetime.fromisoformat("2020-01-04T13:01:01Z")
with time_machine.travel(base_time, tick=False):
materialize("events", "$foo", create_minmax_index=True)
materialize("events", "$bar", create_minmax_index=True)
materialize("person", "$zeta", create_minmax_index=True)
assert sorted(
[
property_name
for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
]
) == sorted(["$foo", "$bar", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])
assert sorted(get_enabled_materialized_columns("person", use_cache=True).keys()) == sorted(
[("$zeta", "properties")]
)
materialize("events", "abc", create_minmax_index=True)
assert sorted(
[
property_name
for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
]
) == sorted(["$foo", "$bar", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])
# The cache is updated in the background, so we need to poll for cache update with retry
# otherwise this flakes
@retry(wait=wait_exponential(multiplier=0.5, min=0.5, max=2), stop=stop_after_attempt(5))
def check_cache_updated():
assert sorted(
[
property_name
for property_name, _ in get_enabled_materialized_columns("events", use_cache=True).keys()
]
) == sorted(["$foo", "$bar", "abc", *EVENTS_TABLE_DEFAULT_MATERIALIZED_COLUMNS])
with time_machine.travel(base_time + timedelta(minutes=59), tick=False):
check_cache_updated()
@patch("secrets.choice", return_value="X")
def test_materialized_column_naming(self, mock_choice):
assert materialize("events", "$foO();--sqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject"
mock_choice.return_value = "Y"
assert (
materialize("events", "$foO();ääsqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject_YYYY"
)
mock_choice.return_value = "Z"
assert (
materialize("events", "$foO_____sqlinject", create_minmax_index=True).name == "mat_$foO_____sqlinject_ZZZZ"
)
assert materialize("person", "SoMePrOp", create_minmax_index=True).name == "pmat_SoMePrOp"
def test_backfilling_data(self):
sync_execute("ALTER TABLE events DROP COLUMN IF EXISTS mat_prop")
sync_execute("ALTER TABLE events DROP COLUMN IF EXISTS mat_another")
_create_event(
event="some_event",
distinct_id="1",
team=self.team,
timestamp="2020-01-01 00:00:00",
properties={"prop": 1},
)
_create_event(
event="some_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-02 00:00:00",
properties={"prop": 2, "another": 5},
)
_create_event(
event="some_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-03 00:00:00",
properties={"prop": 3},
)
_create_event(
event="another_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-04 00:00:00",
)
_create_event(
event="third_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-05 00:00:00",
properties={"prop": 4},
)
_create_event(
event="fourth_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-06 00:00:00",
properties={"another": 6},
)
columns = [
materialize("events", "prop", create_minmax_index=True),
materialize("events", "another", create_minmax_index=True),
]
assert self._count_materialized_rows("mat_prop") == 0
assert self._count_materialized_rows("mat_another") == 0
with time_machine.travel("2021-05-10T14:00:01Z", tick=False):
backfill_materialized_columns(
"events",
columns,
timedelta(days=50),
test_settings={"mutations_sync": "0"},
)
_create_event(
event="fifth_event",
distinct_id="1",
team=self.team,
timestamp="2021-05-07 00:00:00",
properties={"another": 7},
)
iterations = 0
while self._get_count_of_mutations_running() > 0 and iterations < 100:
sleep(0.1)
iterations += 1
assert self._count_materialized_rows("mat_prop") >= 4
assert self._count_materialized_rows("mat_another") >= 4
assert sync_execute("SELECT mat_prop, mat_another FROM events ORDER BY timestamp") == [
("1", ""),
("2", "5"),
("3", ""),
("", ""),
("4", ""),
("", "6"),
("", "7"),
]
def test_column_types(self):
columns = [
materialize("events", "myprop", create_minmax_index=True),
materialize("events", "myprop_nullable", create_minmax_index=True, is_nullable=True),
materialize(
"events",
"myprop_float",
create_minmax_index=True,
is_nullable=True,
column_type="Nullable(Float64)",
),
]
expr_nonnullable = "replaceRegexpAll(JSONExtractRaw(properties, 'myprop'), '^\"|\"$', '')"
expr_nullable = "JSONExtract(properties, 'myprop_nullable', 'Nullable(String)')"
expr_float = "JSONExtract(properties, 'myprop_float', 'Nullable(Float64)')"
backfill_materialized_columns("events", columns, timedelta(days=50))
assert self._get_column_types("mat_myprop") == ("String", "DEFAULT", expr_nonnullable)
assert self._get_column_types("mat_myprop_nullable") == ("Nullable(String)", "DEFAULT", expr_nullable)
assert self._get_column_types("mat_myprop_float") == ("Nullable(Float64)", "DEFAULT", expr_float)
def _count_materialized_rows(self, column):
return sync_execute(
"""
SELECT sum(rows)
FROM system.parts_columns
WHERE database = %(database)s
AND table = %(table)s
AND column = %(column)s
""",
{
"database": CLICKHOUSE_DATABASE,
"table": EVENTS_DATA_TABLE(),
"column": column,
},
)[0][0]
def _get_count_of_mutations_running(self) -> int:
return sync_execute(
"""
SELECT count(*)
FROM system.mutations
WHERE is_done = 0
"""
)[0][0]
def _get_column_types(self, column: str):
return sync_execute(
"""
SELECT type, default_kind, default_expression
FROM system.columns
WHERE database = %(database)s AND table = %(table)s AND name = %(column)s
""",
{
"database": CLICKHOUSE_DATABASE,
"table": EVENTS_DATA_TABLE(),
"column": column,
},
)[0]
def test_lifecycle(self):
table: TablesWithMaterializedColumns = "events"
property_names = ["foo", "bar"]
source_column: TableColumn = "properties"
# create materialized columns
materialized_columns = {}
for property_name in property_names:
materialized_columns[property_name] = materialize(
table, property_name, table_column=source_column, create_minmax_index=True
).name
assert set(property_names) == materialized_columns.keys()
# ensure they exist everywhere
for property_name, destination_column in materialized_columns.items():
key = (property_name, source_column)
assert get_materialized_columns(table)[key].name == destination_column
assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
destination_column,
MaterializedColumnDetails(source_column, property_name, is_disabled=False),
is_nullable=False,
column_type="String",
has_minmax_index=True,
)
# disable them and ensure updates apply as needed
update_column_is_disabled(table, materialized_columns.values(), is_disabled=True)
for property_name, destination_column in materialized_columns.items():
key = (property_name, source_column)
assert get_materialized_columns(table)[key].name == destination_column
assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
destination_column,
MaterializedColumnDetails(source_column, property_name, is_disabled=True),
is_nullable=False,
column_type="String",
has_minmax_index=True,
)
# re-enable them and ensure updates apply as needed
update_column_is_disabled(table, materialized_columns.values(), is_disabled=False)
for property_name, destination_column in materialized_columns.items():
key = (property_name, source_column)
assert get_materialized_columns(table)[key].name == destination_column
assert MaterializedColumn.get(table, destination_column) == MaterializedColumn(
destination_column,
MaterializedColumnDetails(source_column, property_name, is_disabled=False),
is_nullable=False,
column_type="String",
has_minmax_index=True,
)
# drop them and ensure updates apply as needed
drop_column(table, materialized_columns.values())
for property_name, destination_column in materialized_columns.items():
key = (property_name, source_column)
assert key not in get_materialized_columns(table)
with pytest.raises(ValueError):
MaterializedColumn.get(table, destination_column)
def _get_latest_mutation_id(self, table: str) -> str:
[(mutation_id,)] = sync_execute(
"""
SELECT max(mutation_id)
FROM system.mutations
WHERE
database = currentDatabase()
AND table = %(table)s
""",
{"table": table},
)
return mutation_id
def _get_mutations_since_id(self, table: str, id: str) -> Iterable[str]:
return [
command
for (command,) in sync_execute(
"""
SELECT command
FROM system.mutations
WHERE
database = currentDatabase()
AND table = %(table)s
AND mutation_id > %(mutation_id)s
ORDER BY mutation_id
""",
{"table": table, "mutation_id": id},
)
]
def test_drop_optimized_no_index(self):
table: TablesWithMaterializedColumns = (
"person" # little bit easier than events because no shard awareness needed
)
property: PropertyName = "myprop"
source_column: TableColumn = "properties"
destination_column = materialize(table, property, table_column=source_column, create_minmax_index=False)
latest_mutation_id_before_drop = self._get_latest_mutation_id(table)
drop_column(table, destination_column.name)
mutations_ran = self._get_mutations_since_id(table, latest_mutation_id_before_drop)
assert not any("DROP INDEX" in mutation for mutation in mutations_ran)
def test_drop_optimized_no_column(self):
table: TablesWithMaterializedColumns = (
"person" # little bit easier than events because no shard awareness needed
)
property: PropertyName = "myprop"
source_column: TableColumn = "properties"
# create the materialized column
destination_column = materialize(table, property, table_column=source_column, create_minmax_index=False)
sync_execute(f"ALTER TABLE {table} DROP COLUMN {destination_column.name}", settings={"alter_sync": 1})
latest_mutation_id_before_drop = self._get_latest_mutation_id(table)
drop_column(table, destination_column.name)
mutations_ran = self._get_mutations_since_id(table, latest_mutation_id_before_drop)
assert not any("DROP COLUMN" in mutation for mutation in mutations_ran)
def test_minmax_index_usage(self):
property_name = "numeric_prop"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={property_name: "100"},
)
column = materialize("events", property_name, create_minmax_index=True)
index_name = get_minmax_index_name(column.name)
# Verify index appears in EXPLAIN for range query
query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {column.name} > '50'"
index_info = get_index_from_explain(query, index_name)
assert index_info is not None, f"MinMax index {index_name} should appear in EXPLAIN output"
def test_bloom_filter_index_usage(self):
property_name = "category_prop"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={property_name: "category_a"},
)
column = materialize("events", property_name, create_bloom_filter_index=True)
index_name = get_bloom_filter_index_name(column.name)
# Verify index appears in EXPLAIN for equality query
query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {column.name} = 'category_a'"
index_info = get_index_from_explain(query, index_name)
assert index_info is not None, f"Bloom filter index {index_name} should appear in EXPLAIN output"
def test_ngram_lower_index_usage_non_nullable(self):
property_name = "text_prop"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={property_name: "Hello World Text"},
)
column = materialize("events", property_name, create_ngram_lower_index=True, is_nullable=False)
index_name = get_ngram_lower_index_name(column.name)
# Verify index appears in EXPLAIN for case-insensitive LIKE query
query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE lower({column.name}) LIKE '%world%'"
index_info = get_index_from_explain(query, index_name)
assert index_info is not None, f"N-gram index {index_name} should appear in EXPLAIN output"
def test_ngram_lower_index_usage_nullable(self):
property_name = "text_prop"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={property_name: "Hello World Text"},
)
column = materialize("events", property_name, create_ngram_lower_index=True, is_nullable=True)
index_name = get_ngram_lower_index_name(column.name)
# Verify index appears in EXPLAIN for case-insensitive LIKE query
query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE lower(coalesce({column.name}, '')) LIKE '%world%'"
index_info = get_index_from_explain(query, index_name)
assert index_info is not None, f"N-gram index {index_name} should appear in EXPLAIN output"
@parameterized.expand([("non_nullable", False), ("nullable", True)])
def test_bloom_filter_lower_index_usage(self, _name, is_nullable):
property_name = "ci_category_prop"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={property_name: "Category_A"},
)
column = materialize("events", property_name, create_bloom_filter_lower_index=True, is_nullable=is_nullable)
index_name = get_bloom_filter_lower_index_name(column.name)
# The index expression is lower(coalesce(col, '')) for nullable columns, lower(col) otherwise
indexed_expr = f"lower(coalesce({column.name}, ''))" if is_nullable else f"lower({column.name})"
query = f"SELECT count() FROM {EVENTS_DATA_TABLE()} WHERE {indexed_expr} = 'category_a'"
index_info = get_index_from_explain(query, index_name)
assert index_info is not None, f"Bloom filter lower index {index_name} should appear in EXPLAIN output"
def test_get_all_returns_index_flags(self):
"""Test that get_materialized_columns returns correct index flags from ClickHouse."""
prop_all = "prop_all"
prop_minmax = "prop_minmax"
prop_bf = "prop_bf"
prop_ngram_lower = "prop_ngram_lower"
prop_bf_lower = "prop_bf_lower"
prop_none = "prop_none"
_create_event(
team=self.team,
event="test_event",
distinct_id="user1",
properties={
prop_all: prop_all,
prop_minmax: prop_minmax,
prop_bf: prop_bf,
prop_ngram_lower: prop_ngram_lower,
prop_bf_lower: prop_bf_lower,
prop_none: prop_none,
},
)
# Create column with all index types
materialize(
"events",
prop_all,
create_minmax_index=True,
create_bloom_filter_index=True,
create_ngram_lower_index=True,
create_bloom_filter_lower_index=True,
)
materialize(
"events",
prop_minmax,
create_minmax_index=True,
create_bloom_filter_index=False,
create_ngram_lower_index=False,
create_bloom_filter_lower_index=False,
)
materialize(
"events",
prop_bf,
create_bloom_filter_index=True,
create_ngram_lower_index=False,
create_minmax_index=False,
create_bloom_filter_lower_index=False,
)
materialize(
"events",
prop_ngram_lower,
create_bloom_filter_index=False,
create_ngram_lower_index=True,
create_minmax_index=False,
create_bloom_filter_lower_index=False,
)
materialize(
"events",
prop_bf_lower,
create_bloom_filter_index=False,
create_ngram_lower_index=False,
create_minmax_index=False,
create_bloom_filter_lower_index=True,
)
materialize(
"events",
prop_none,
create_bloom_filter_index=False,
create_ngram_lower_index=False,
create_minmax_index=False,
create_bloom_filter_lower_index=False,
)
cols = get_materialized_columns("events")
mat_col_all = cols.get((prop_all, "properties"))
mat_col_minmax = cols.get((prop_minmax, "properties"))
mat_col_bf = cols.get((prop_bf, "properties"))
mat_col_ngram_lower = cols.get((prop_ngram_lower, "properties"))
mat_col_bf_lower = cols.get((prop_bf_lower, "properties"))
mat_col_none = cols.get((prop_none, "properties"))
assert mat_col_all is not None
assert mat_col_minmax is not None
assert mat_col_bf is not None
assert mat_col_ngram_lower is not None
assert mat_col_bf_lower is not None
assert mat_col_none is not None
# Verify index flags: (has_minmax, has_bloom_filter, has_ngram_lower, has_bloom_filter_lower)
assert (
mat_col_all.has_minmax_index,
mat_col_all.has_bloom_filter_index,
mat_col_all.has_ngram_lower_index,
mat_col_all.has_bloom_filter_lower_index,
) == (True, True, True, True)
assert (
mat_col_minmax.has_minmax_index,
mat_col_minmax.has_bloom_filter_index,
mat_col_minmax.has_ngram_lower_index,
mat_col_minmax.has_bloom_filter_lower_index,
) == (True, False, False, False)
assert (
mat_col_bf.has_minmax_index,
mat_col_bf.has_bloom_filter_index,
mat_col_bf.has_ngram_lower_index,
mat_col_bf.has_bloom_filter_lower_index,
) == (False, True, False, False)
assert (
mat_col_ngram_lower.has_minmax_index,
mat_col_ngram_lower.has_bloom_filter_index,
mat_col_ngram_lower.has_ngram_lower_index,
mat_col_ngram_lower.has_bloom_filter_lower_index,
) == (False, False, True, False)
assert (
mat_col_bf_lower.has_minmax_index,
mat_col_bf_lower.has_bloom_filter_index,
mat_col_bf_lower.has_ngram_lower_index,
mat_col_bf_lower.has_bloom_filter_lower_index,
) == (False, False, False, True)
assert (
mat_col_none.has_minmax_index,
mat_col_none.has_bloom_filter_index,
mat_col_none.has_ngram_lower_index,
mat_col_none.has_bloom_filter_lower_index,
) == (False, False, False, False)