June 2022 Summaries

Downsampling in Grafana is a technique used to understand data quicker and easier by highlighting trends that otherwise wouldn’t stand out. Grafana, an open-source visualization tool, allows users to create graphs for time-series data with ease. However, problems arise when dealing with extremely large datasets, which can be slow to load and lead to frustrated users or unusable dashboards. To overcome this, two types of downsampling techniques are used: `Largest Triangle Three Buckets` (lttb) and `Automated Smoothing for Attention Prioritization` (ASAP). The lttb method reduces the number of data points while maintaining the visual appearance of a graph, whereas ASAP smooths away noise in the data to reveal underlying trends. Both methods can be implemented using TimescaleDB's hyperfunctions, making it easy to manipulate and analyze time-series data with fewer lines of SQL code.

SQL is the lingua franca for analytics`, with many NoSQL databases adding SQL interfaces to keep up. Most developers are familiar with SQL, along with data scientists, analysts, and other professionals who work with data. This makes it easier for teams to onboard new members and quickly extract value from the data. Time-series data is ubiquitous, generating millions of data points per second, making complex queries challenging even in SQL. TimescaleDB hyperfunctions simplify time-series analysis by providing purpose-built functions for common queries, such as time-based analysis, time-weighted averages, percentile approximation, frequency analysis, and more. These hyperfunctions are designed to improve productivity, readability, and maintainability of SQL code, and come pre-loaded on every hosted and managed database service in Timescale Cloud.

TimescaleDB has optimized its `now()` function to improve query performance, particularly in hypertables with many chunks. The optimization works by rewriting the constraint to make the `now()` expression constifiable during plan-time chunk exclusion, reducing planning time and speeding up queries. This improvement is available in TimescaleDB 2.7 and will be further expanded in version 2.8 to support intervals of months and years. The optimization significantly improves query performance, with performance improvements scaling with the total number of chunks in hypertables, resulting in speeds up to 401x faster than the previous version for a total of 20,000 chunks. Users can upgrade to TimescaleDB 2.7 automatically if using Timescale Cloud or manually if self-managing their instance.

TimescaleDB 2.7 introduces dramatic performance improvements in continuous aggregates, up to 44,000x faster than previous versions, as well as significant storage savings of 60% on average. The new version allows for more flexibility and easier management of materialized views, enabling users to use standard PostgreSQL aggregate functions with `FILTER`, `DISTINCT`, and other SQL features. Additionally, the performance improvements are accompanied by a reduction in storage requirements, making it an attractive solution for time-series data analysis. With these enhancements, TimescaleDB 2.7 is poised to become a leading relational database for time-series and analytics workloads.

This open-source relational database is designed to handle large amounts of time-series data, such as blockchain data, by leveraging the properties of timestamps in blockchains. The company Zondax uses TimescaleDB to analyze and process hundreds of terabytes of blockchain data, achieving significant performance improvements over traditional databases like PostgreSQL. By employing hypertables, a feature of TimescaleDB that allows for efficient storage and querying of large amounts of data, Zondax has been able to reduce the time it takes to process data from weeks to just a few days. The team at Zondax emphasizes the importance of safety, efficiency, and scalability in their work, and they welcome professionals from diverse backgrounds and experiences to join their remote work culture. With TimescaleDB, Zondax is able to provide fast and responsive services in real-time, stay synced with blockchain nodes, and protect data integrity through backups. The company's adoption of TimescaleDB has been instrumental in accelerating their progress and delivering quality results for their clients.

This post discusses how Prometheus queries data, focusing on its indexing strategies and query execution flow. It explains that the performance of a PromQL query slows down with an increased time range due to the need to evaluate more blocks and chunks. The process starts with identifying which blocks correspond to the query's time range, then using the postings index to find matching series, followed by identifying chunks for each series, and finally retrieving sample data from those chunks. Understanding this flow can help optimize Prometheus queries, such as avoiding unnecessary label values, keeping scrape intervals high, and modifying TimescaleDB chunk sizes if used with Promscale. The post concludes that knowing how Prometheus indexes work provides an intuitive understanding of query performance, making it easier to monitor parameters affecting performance.

Our love for PostgreSQL runs deep. We built our products on PostgreSQL, are proud members of the community, and wouldn’t exist without it and the extensibility it provides.In 2019, Timescale launched the first State of PostgreSQL report, advancing our desire to provide greater insights into the specificities and features useful to the PostgreSQL community. Following a one-year hiatus due to the pandemic and after the 2021 survey submissions, we released the 2021 report.We are pleased to announce that the 2022 survey is now open for submissions! We are keen to learn more about how you use PostgreSQL for work and personal projects, how you deploy it, and how we can collectively improve it.✨Help us give back to this awesome group: answer survey questions and share with other PostgreSQL users. We are excited to hear your thoughts and spark a conversation that will keep us moving forward and building better things together. 🙌 We will share our report (as well as give you full and free access to the survey’s anonymized raw data) in July. Thank you for being a part of the community! Take the 2022 State of PostgreSQL survey PostgreSQL usage is growing, with around 67% of developers saying they use it more or much more compared to 52% in 2021. The primary use case for PostgreSQL developers is building applications, which accounts for 70% in both 2019 and 2021. Community contribution is also increasing, with 11% of respondents claiming to contribute their code to the database in 2021. Developers cited reliability and SQL as the main reasons they use PostgreSQL. The way developers deploy PostgreSQL is changing, with a shift towards self-managed data centers and private clouds, particularly in 2021. Timescale aims to provide insights into the specificities and features of the PostgreSQL community and would like to hear from users about their experiences and suggestions for improvement.