https://attilatoth.dev/speaking/build-fast-dashboards/

fast_dashboard_caggs.sql

CREATE MATERIALIZED VIEW demo_collections_daily
WITH (timescaledb.continuous) AS
SELECT
collection_id,
time_bucket('1 day', time) AS bucket,
COUNT(*) AS volume,
SUM(total_price) AS volume_eth,
MAX(total_price) AS max_price,
MIN(total_price) AS min_price,
AVG(total_price) AS avg_price
FROM nft_sales_demo2
WHERE payment_symbol = 'ETH'
GROUP BY bucket, collection_id;

/* See what's inside tha cagg */
SELECT * FROM demo_collections_daily
ORDER BY bucket DESC 
LIMIT 10

/*********************************/
/* DASHBOARD VIEWS */
/*********************************/

/* JOIN the cagg with the collection table to get the slug */
SELECT c.slug, cagg.* FROM demo_collections_daily cagg
INNER JOIN collections c ON c.id = cagg.collection_id
LIMIT 10


/* Create view for superset */
CREATE VIEW demo_collections_daily_view AS 
SELECT c.slug, cagg.* FROM demo_collections_daily cagg
INNER JOIN collections c ON c.id = cagg.collection_id
WHERE bucket > NOW() - INTERVAL '3 months'

/* Check the view */
SELECT * FROM demo_collections_daily_view
LIMIT 10
/*********************************/
/* COMPRESSION */
/*********************************/

/* Enable compression (this will show an error!) */
ALTER TABLE nft_sales_demo2 SET (
	timescaledb.compress=true,
	timescaledb.compress_segmentby = 'collection_id'
)

/* Enable compression */
ALTER TABLE nft_sales_demo2 SET (
	timescaledb.compress=true,
	timescaledb.compress_segmentby = 'collection_id',
	timescaledb.compress_orderby = 'id' -- DEFAULT: 'time DESC'
)

/* Show all the chunks that we have in the hypertable */
SELECT show_chunks('nft_sales_demo2');

/* Show specific chunks that are newer than  3 months */
SELECT show_chunks('nft_sales_demo2', newer_than => NOW() - INTERVAL '3 months');

/* See more information about chunks */
SELECT hypertable_name, chunk_name, range_start, range_end, is_compressed FROM timescaledb_information.chunks 
WHERE hypertable_name = 'nft_sales_demo2' AND range_start > NOW() - INTERVAL '3 months'
ORDER BY range_start DESC 

/* Manually compress multiple chunks */
SELECT compress_chunk(i, if_not_compressed=>true) FROM show_chunks('nft_sales_demo2', newer_than => NOW() - INTERVAL '3 months') i;

/* Decompress chunks (SKIP!) */
SELECT decompress_chunk(i) from show_chunks('nft_sales_demo2', newer_than => NOW() - INTERVAL '3 months') i;

/* Compression stats */
SELECT chunk_name,
pg_size_pretty(before_compression_total_bytes) AS before_size, 
pg_size_pretty(after_compression_total_bytes) AS after_size
FROM chunk_compression_stats('nft_sales_demo2')
WHERE compression_status = 'Compressed'

/* Compression stats total */
SELECT 
pg_size_pretty(SUM(before_size)) AS before_total, 
pg_size_pretty(SUM(after_size)) AS after_total,
(SUM(before_size)-SUM(after_size))/SUM(before_size) AS ratio
FROM (
SELECT chunk_name, 
	before_compression_total_bytes AS before_size, 
	after_compression_total_bytes AS after_size
	FROM chunk_compression_stats('nft_sales_demo2')
	WHERE compression_status = 'Compressed'
) s