Will Continuous Top-N leak space forever?

[Ocramius]

I'm looking at this query:

flink-sql-cookbook/aggregations-and-analytics/05_top_n/05_top_n.md

Lines 30 to 47 in aa0bd97

	```sql
	CREATE TABLE spells_cast (
	wizard STRING,
	spell STRING
	) WITH (
	'connector' = 'faker',
	'fields.wizard.expression' = '#{harry_potter.characters}',
	'fields.spell.expression' = '#{harry_potter.spells}'
	);
	SELECT wizard, spell, times_cast
	FROM (
	SELECT *,
	ROW_NUMBER() OVER (PARTITION BY wizard ORDER BY times_cast DESC) AS row_num
	FROM (SELECT wizard, spell, COUNT(*) AS times_cast FROM spells_cast GROUP BY wizard, spell)
	)
	WHERE row_num <= 2;
	```

I'm wondering if this will leak space forever?

An example about data size

Excluding the GROUP BY bit, and simplifying:

SELECT wizard, spell, times_cast
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY wizard ORDER BY times_cast DESC) AS row_num
    FROM (SELECT wizard, spell, times_cast FROM spells_cast_source)
)
WHERE row_num <= 2; 

From a logical PoV, my brain tells me that Flink will have to forever keep a map of seen identifiers (wizard) somewhere, in order to track row_num continuously?

What happens if I have 300Gb of data? Will this leak that space?

Windowed aggregations and continuous streaming?

Looking at upstream docs for Flink 1.20:

https://nightlies.apache.org/flink/flink-docs-release-1.20/docs/dev/table/sql/queries/window-agg/

Unlike other aggregations on continuous tables, window aggregation do not emit intermediate results but only a final result, the total aggregation at the end of the window. Moreover, window aggregations purge all intermediate state when no longer needed.

So the tradeoff here is either "continuously, but leaking space", or "when window ends, but cleaning up after a window".

Is there a way to obtain continuous streaming over a window, whilst still cleaning up after a window is done?

[Ocramius]

To answer myself, I believe that the two things to tweak are:

SET 'table.exec.state.ttl' = '86400000ms';
SET 'table.exec.window-agg.buffer-size-limit' = '100000';

The first value effectively truncates the storage after a certain size, and therefore leads to a resolution loss.

That is something that requires careful consideration in environments where high consistency is needed, but is perfectly fine in environments where only the last hour worth of data is needed, and burning through an entire hard drive is not acceptable for it.