Mar 10, 2022 · Databases

How to Speed Up Latest‑Record Queries in Large PostgreSQL Time‑Series Tables

This article explains why querying the most recent record for a specific device in massive time‑series tables can be painfully slow in PostgreSQL, demonstrates the impact of using only a timestamp index, and presents several indexing and query‑design strategies—including composite indexes, lateral joins, SkipScan, recursive CTEs, and logging tables with triggers—to dramatically improve performance.

PostgreSQLSQLTime-Series

0 likes · 22 min read

How to Speed Up Latest‑Record Queries in Large PostgreSQL Time‑Series Tables

ITPUB

Sep 20, 2016 · Databases

Mastering Billion-Row Time-Series Data in SQL Server: Bulk Insert, Partitioning, Index Tuning

To meet a demanding monitoring project that required storing up to 400 million records per day in SQL Server 2012, the author details a step‑by‑step journey involving bulk‑copy insertion, removing indexes during load, hourly partitioned tables, strategic index creation, and query optimizations to achieve sub‑second query times.

Bulk InsertIndex TuningLarge Data

0 likes · 17 min read

Mastering Billion-Row Time-Series Data in SQL Server: Bulk Insert, Partitioning, Index Tuning