cosmo/controlplane/clickhouse/migrations/20260427120000_create_gql_cache_events.sql at d1cfe52ab955bf9946462ffbd53437214546a4fb · wundergraph/cosmo · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
-- migrate:up

CREATE TABLE IF NOT EXISTS gql_cache_events_raw
(
    -- See https://github.com/PostHog/posthog/issues/10616 why ZSTD(3) is used
    Timestamp DateTime64(9, 'UTC') CODEC(Delta, ZSTD(3)),

    -- Tenant
    OrganizationID LowCardinality(String) CODEC(ZSTD(3)),
    FederatedGraphID LowCardinality(String) CODEC(ZSTD(3)),
    RouterConfigVersion LowCardinality(String) CODEC(ZSTD(3)),

    -- Event discriminator. Canonical lowercase string. Values:
    --   'l1_read','l2_read','l1_write','l2_write','fetch_timing',
    --   'subgraph_error','shadow_comparison','mutation','header_impact',
    --   'cache_op_error'
    EventType LowCardinality(String) CODEC(ZSTD(3)),

    -- Operation context
    OperationHash LowCardinality(String) CODEC(ZSTD(3)),
    OperationName LowCardinality(String) CODEC(ZSTD(3)),
    OperationType LowCardinality(String) CODEC(ZSTD(3)),
    ClientName LowCardinality(String) CODEC(ZSTD(3)),
    ClientVersion LowCardinality(String) CODEC(ZSTD(3)),
    TraceID String CODEC(ZSTD(3)),
    IsShadow Bool CODEC(ZSTD(3)),

    -- Cache identity
    EntityType LowCardinality(String) CODEC(ZSTD(3)),
    SubgraphID LowCardinality(String) CODEC(ZSTD(3)),
    KeyHash UInt64 CODEC(ZSTD(3)),

    -- Field-level identity (root field for entity fetches; nested fields for value-type traversal)
    FieldName LowCardinality(String) CODEC(ZSTD(3)),
    FieldHash UInt64 CODEC(ZSTD(3)),
    FieldPath Array(LowCardinality(String)) CODEC(ZSTD(3)),
    EntityCount UInt32 CODEC(ZSTD(3)),
    EntityUniqueKeys UInt32 CODEC(ZSTD(3)),

    -- Read events (l1_read, l2_read)
    Verdict LowCardinality(String) CODEC(ZSTD(3)),
    ByteSize UInt32 CODEC(ZSTD(3)),
    CacheAgeMs UInt32 CODEC(ZSTD(3)),

    -- Write events (l1_write, l2_write)
    TTLMs UInt32 CODEC(ZSTD(3)),
    WriteReason LowCardinality(String) CODEC(ZSTD(3)),
    Source LowCardinality(String) CODEC(ZSTD(3)),

    -- Fetch timing
    FetchSource LowCardinality(String) CODEC(ZSTD(3)),
    DurationMs Float64 CODEC(ZSTD(3)),
    TTFBMs Float64 CODEC(ZSTD(3)),
    ItemCount UInt32 CODEC(ZSTD(3)),
    IsEntityFetch Bool CODEC(ZSTD(3)),
    HttpStatusCode UInt16 CODEC(ZSTD(3)),
    ResponseBytes UInt32 CODEC(ZSTD(3)),

    -- Errors (subgraph_error, cache_op_error)
    ErrorMessage String CODEC(ZSTD(3)),
    ErrorCode LowCardinality(String) CODEC(ZSTD(3)),
    CacheOp LowCardinality(String) CODEC(ZSTD(3)),
    CacheName LowCardinality(String) CODEC(ZSTD(3)),

    -- Shadow + mutation share these columns
    ShadowIsFresh Bool CODEC(ZSTD(3)),
    CachedHash UInt64 CODEC(ZSTD(3)),
    FreshHash UInt64 CODEC(ZSTD(3)),
    CachedBytes UInt32 CODEC(ZSTD(3)),
    FreshBytes UInt32 CODEC(ZSTD(3)),
    ConfiguredTTLMs UInt32 CODEC(ZSTD(3)),

    -- Mutation
    MutationRootField LowCardinality(String) CODEC(ZSTD(3)),
    HadCachedValue Bool CODEC(ZSTD(3)),
    IsStale Bool CODEC(ZSTD(3)),

    -- Header impact
    BaseKeyHash UInt64 CODEC(ZSTD(3)),
    HeaderHash UInt64 CODEC(ZSTD(3)),
    ResponseHash UInt64 CODEC(ZSTD(3)),

    INDEX idx_op_hash OperationHash TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_entity EntityType TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_subgraph SubgraphID TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_key_hash KeyHash TYPE bloom_filter(0.001) GRANULARITY 1
)
    engine = MergeTree PARTITION BY toDate(Timestamp)
        ORDER BY (OrganizationID, FederatedGraphID, EventType, OperationHash, EntityType, SubgraphID, toUnixTimestamp(Timestamp))
        TTL toDateTime(Timestamp) + toIntervalDay(7)
        SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1, non_replicated_deduplication_window = 1000;

CREATE TABLE IF NOT EXISTS gql_cache_events_5m_90d
(
    Timestamp DateTime('UTC') CODEC(Delta, ZSTD(3)),

    OrganizationID LowCardinality(String) CODEC(ZSTD(3)),
    FederatedGraphID LowCardinality(String) CODEC(ZSTD(3)),
    RouterConfigVersion LowCardinality(String) CODEC(ZSTD(3)),

    EventType LowCardinality(String) CODEC(ZSTD(3)),
    OperationHash LowCardinality(String) CODEC(ZSTD(3)),
    OperationName LowCardinality(String) CODEC(ZSTD(3)),
    OperationType LowCardinality(String) CODEC(ZSTD(3)),
    ClientName LowCardinality(String) CODEC(ZSTD(3)),
    ClientVersion LowCardinality(String) CODEC(ZSTD(3)),
    EntityType LowCardinality(String) CODEC(ZSTD(3)),
    SubgraphID LowCardinality(String) CODEC(ZSTD(3)),
    Verdict LowCardinality(String) CODEC(ZSTD(3)),
    FieldName LowCardinality(String) CODEC(ZSTD(3)),
    FetchSource LowCardinality(String) CODEC(ZSTD(3)),
    IsShadow Bool CODEC(ZSTD(3)),

    Events UInt64 CODEC(ZSTD(3)),
    SumByteSize UInt64 CODEC(ZSTD(3)),
    SumDurationMs Float64 CODEC(ZSTD(3)),
    SumCacheAgeMs UInt64 CODEC(ZSTD(3)),
    SumStale UInt64 CODEC(ZSTD(3)),
    SumEntityCount UInt64 CODEC(ZSTD(3))
)
    engine = SummingMergeTree PARTITION BY toDate(Timestamp)
        ORDER BY (OrganizationID, FederatedGraphID, EventType, OperationHash, EntityType, SubgraphID, ClientName, ClientVersion, RouterConfigVersion, OperationName, OperationType, Verdict, FetchSource, IsShadow, toUnixTimestamp(Timestamp))
        TTL toDateTime(Timestamp) + toIntervalDay(90)
        SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

CREATE MATERIALIZED VIEW IF NOT EXISTS gql_cache_events_5m_90d_mv TO gql_cache_events_5m_90d AS
SELECT
    toStartOfFiveMinute(Timestamp) as Timestamp,
    toLowCardinality(OrganizationID) as OrganizationID,
    toLowCardinality(FederatedGraphID) as FederatedGraphID,
    toLowCardinality(RouterConfigVersion) as RouterConfigVersion,
    toLowCardinality(EventType) as EventType,
    toLowCardinality(OperationHash) as OperationHash,
    toLowCardinality(OperationName) as OperationName,
    toLowCardinality(OperationType) as OperationType,
    toLowCardinality(ClientName) as ClientName,
    toLowCardinality(ClientVersion) as ClientVersion,
    toLowCardinality(EntityType) as EntityType,
    toLowCardinality(SubgraphID) as SubgraphID,
    toLowCardinality(Verdict) as Verdict,
    toLowCardinality(FieldName) as FieldName,
    toLowCardinality(FetchSource) as FetchSource,
    IsShadow as IsShadow,
    count() as Events,
    sum(ByteSize) as SumByteSize,
    sum(DurationMs) as SumDurationMs,
    sum(CacheAgeMs) as SumCacheAgeMs,
    sumIf(1, IsStale) as SumStale,
    sum(EntityCount) as SumEntityCount
FROM gql_cache_events_raw
GROUP BY
    Timestamp,
    OrganizationID,
    FederatedGraphID,
    RouterConfigVersion,
    EventType,
    OperationHash,
    OperationName,
    OperationType,
    ClientName,
    ClientVersion,
    EntityType,
    SubgraphID,
    Verdict,
    FieldName,
    FetchSource,
    IsShadow
ORDER BY Timestamp;

CREATE TABLE IF NOT EXISTS gql_cache_events_1d_90d
(
    Timestamp DateTime('UTC') CODEC(Delta, ZSTD(3)),

    OrganizationID LowCardinality(String) CODEC(ZSTD(3)),
    FederatedGraphID LowCardinality(String) CODEC(ZSTD(3)),

    EventType LowCardinality(String) CODEC(ZSTD(3)),
    EntityType LowCardinality(String) CODEC(ZSTD(3)),
    SubgraphID LowCardinality(String) CODEC(ZSTD(3)),
    Verdict LowCardinality(String) CODEC(ZSTD(3)),

    Events UInt64 CODEC(ZSTD(3)),
    SumByteSize UInt64 CODEC(ZSTD(3)),
    SumDurationMs Float64 CODEC(ZSTD(3))
)
    engine = SummingMergeTree PARTITION BY toDate(Timestamp)
        ORDER BY (OrganizationID, FederatedGraphID, EventType, EntityType, SubgraphID, Verdict, toUnixTimestamp(Timestamp))
        TTL toDateTime(Timestamp) + toIntervalDay(90)
        SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

CREATE MATERIALIZED VIEW IF NOT EXISTS gql_cache_events_1d_90d_mv TO gql_cache_events_1d_90d AS
SELECT
    toStartOfDay(Timestamp) as Timestamp,
    toLowCardinality(OrganizationID) as OrganizationID,
    toLowCardinality(FederatedGraphID) as FederatedGraphID,
    toLowCardinality(EventType) as EventType,
    toLowCardinality(EntityType) as EntityType,
    toLowCardinality(SubgraphID) as SubgraphID,
    toLowCardinality(Verdict) as Verdict,
    count() as Events,
    sum(ByteSize) as SumByteSize,
    sum(DurationMs) as SumDurationMs
FROM gql_cache_events_raw
GROUP BY
    Timestamp,
    OrganizationID,
    FederatedGraphID,
    EventType,
    EntityType,
    SubgraphID,
    Verdict
ORDER BY Timestamp;

-- migrate:down

DROP VIEW IF EXISTS gql_cache_events_1d_90d_mv;
DROP TABLE IF EXISTS gql_cache_events_1d_90d;
DROP VIEW IF EXISTS gql_cache_events_5m_90d_mv;
DROP TABLE IF EXISTS gql_cache_events_5m_90d;
DROP TABLE IF EXISTS gql_cache_events_raw;