November 2023 Summaries

Timescale compression offers a more modern and effective solution to data compression in PostgreSQL, outperforming TOAST by up to 10x in reducing storage size and improving query performance. Timescale's columnar compression works by consolidating multiple records into a single row with an array-like structure, followed by compression algorithms tailored to each column based on their data types. The compression process can be fine-tuned through parameters such as `compress_orderby` and `compress_segmentby`, which allow users to organize data effectively and group similar values together for better compression outcomes. By leveraging these best practices and implementing Timescale compression, users can significantly reduce the size of their PostgreSQL databases and improve query performance, making it an ideal solution for modern applications struggling with storage demands.

Creating a Fast Time-Series Graph With Postgres Materialized Views` discusses how to efficiently analyze and visualize time-series data using PostgreSQL materialized views and Timescale's continuous aggregates. The article highlights the limitations of traditional materialized views, such as requiring manual refreshes, which can be time-consuming and performance-intensive. In contrast, continuous aggregates automatically update and materialize new data, eliminating the need for manual refreshes while maintaining query performance over time. By leveraging these techniques, developers can create fast and efficient time-series graphs and real-time analytics visualizations, even with large datasets.

Amazon Aurora and PostgreSQL are both popular cloud database options, but they have different strengths and weaknesses. Aurora is marketed as being faster than RDS and highly scalable, while Timescale extends PostgreSQL for new workloads such as time series with TimescaleDB. In a benchmark comparison, Timescale was found to be 35% faster in ingesting data, between 1.15x and 16x faster in query performance, and 95% more efficient at storing data compared to Aurora Serverless v2 I/O-Optimized. Additionally, Timescale is 52% cheaper per hour for compute and 78% cheaper per month to store the data created. While Aurora does replace PostgreSQL's storage backend with newer technology, Timescale outperforms it in large workloads across all dimensions.

Timescale has launched three consecutive weeks of new features, demonstrating how it simplifies, scales, and supercharges PostgreSQL database experience. The company aims to empower developers to start on Postgres, scale with Postgres, and stay with Postgres. Timescale specializes and supercharges PostgreSQL for specific use cases, offering capabilities via extensions "inside" the PostgreSQL software and cloud services "around" the software. Recent launches include Cloud Week, which focused on mature cloud platform, migration tooling, enterprise tier, insights, connection pooling, user-initiated point-in-time recovery, dynamic infrastructure week, which introduced Dynamic PostgreSQL with usage-based storage pricing, Terraform provider, more regions on cloud, and a partnership with Cloudflare. The third launch week featured Tiered Storage, columnar compression, vectorized query execution, performance improvements, and AI bonuses, including LangChain templates for building production-ready Retrieval Augmented Generation (RAG) applications. Timescale's goal is to be the go-to database for any use case that requires the best of Postgres, offering scalability, cost-effectiveness, and reliability.

TimescaleDB has introduced several performance improvements in recent releases, including enhancements to query analytics. These improvements include vectorized query execution, compressed columnar storage, continuous aggregates, partial aggregates at the chunk level, optimized chunk exclusion, lighter locks during continuous aggregate refresh procedures, more decompression parallel workers, and faster real-time continuous aggregates. Additionally, TimescaleDB has made optimizations to its columnar mini-batches sorting process, which benefits queries that limit the number of returned tuples. These improvements aim to increase the performance and scalability of TimescaleDB for demanding workloads like time series, events, and analytics.

TimescaleDB has added a new feature that enables Single Instruction, Multiple Data (SIMD) vectorization on top of its hybrid row columnar storage, making analytical queries significantly faster. The new vectorized query pipeline is designed to work seamlessly with PostgreSQL's existing architecture, allowing developers to scale and stay with PostgreSQL for their analytics workloads. The feature includes decompression speedup, filter optimizations, vectorized expressions, and aggregation improvements, resulting in performance increases of up to an order of magnitude on some queries.

The text discusses how developers can use Amazon Web Services (AWS) with Timescale, a cloud-native PostgreSQL solution for time series, events, and analytics. It highlights eight AWS services that Timescale customers love using, including Amazon VPC, AWS Lambda, AWS IoT Tools, Amazon QuickSight, Amazon CloudWatch, AWS Managed Service for Apache Kafka, and Amazon S3. The text provides detailed information on how to use these services with Timescale and offers resources for further learning. It also emphasizes the benefits of using Timescale with AWS, such as seamless integration, flexibility, and cost efficiency.

Scaling PostgreSQL for Cheap: Introducing Tiered Storage in Timescale` introduces a new storage architecture, `Tiered Storage`, designed to enable infinite, low-cost scalability for time series and analytical databases in the Timescale platform. This multi-tiered storage system allows developers to store older, infrequently accessed data in a low-cost storage tier while still maintaining performance for frequently accessed data. The high-performance storage tier is optimized for fast queries and high ingests, while the low-cost storage tier uses Amazon S3 with a flat price of $0.021 per GB/month. This architecture eliminates storage limitations and provides transparent, cost-effective data management without sacrificing performance. It also offers savings through compression and no extra charges for queries or data reads. With Tiered Storage, developers can scale their large PostgreSQL databases in AWS without compromising performance, making it a great solution for storing historical data at an affordable price.

The system designed by the authors is a resilient vector embedding creation system for PostgreSQL data that ensures simplicity, resilience, and high performance. The system uses a trigger to track changes in the blog table and inserts jobs into a work queue when changes are detected. An embeddings creator job then polls the work queue, creates an embedding for each row, and writes it to the database. The system is designed to be highly concurrent and performant, with measures taken to prevent duplicate entries and ensure that the system can handle failures and downtimes of the external service used to create embeddings. The system's design allows users to have control over how they embed their data and where they host their database, making it a flexible solution for various use cases.

Topographic features enable you to bypass PostgreSQL's limitations by adding extra functionality to your database through extensions that modify and enhance the execution of certain processes. These features help maintain good performance when tables grow up to billions of rows without switching to a new database. The `top 8` PostgreSQL extensions discussed in this article include PostGIS, pg_stat_statements, pgcrypto, pg_partman, postgres_fdw, pgvector, hstore, and pgpcre. TimescaleDB is also mentioned as another top extension that adds query and ingest performance boost to PostgreSQL via automatic partitioning, query planner enhancements, improved materialized views, columnar compression, and much more.

We are excited to partner with Cloudflare to offer Hyperdrive, a product that turns regional databases into globally distributed ones, allowing us to make Hypertables on Hyperdrive a thing. Timescale's hypertables work like PostgreSQL tables but automatically partition data for better performance. Hyperdrive acts as an edge proxy for PostgreSQL databases, reducing traffic and improving persistence. We have created a tutorial to help users deploy an edge app with Timescale for persistence and querying. Using Timescale provides speed and scalability advantages over vanilla PostgreSQL, especially for time-series data and large datasets. With our 30-day free trial, you can start speeding up your workflow today.

Terraform is a must-have tool in any software developer’s toolbox, allowing for declarative configuration of cloud resources. It increases visibility into deployments, helps with security, auditability, and accountability, and speeds up deployment processes. The Timescale Terraform provider enables users to deploy and destroy Timescale services using Terraform, providing a convenient and efficient way to manage their database. The provider allows users to configure their services with CPU, memory, storage, region, and prevent_destroy lifecycle rules. Users can retrieve the service password using outputs and synthesize the connection string as an output for easy deployment of other resources. When ready to destroy a service, users must remove or comment out the prevent_destroy lifecycle rule and then use the terraform destroy command. With the Timescale Terraform provider, developers can focus on their applications rather than their database, making it possible to achieve speedy deployments and reduce tedious configuration tasks.

Navigating a usage-based model for PostgreSQL requires understanding how pricing works and how well it will scale. Developers must consider various factors, including data storage, scaling, and performance, to ensure cost-efficiency and optimal performance. A usage-based model promotes a culture of continuous improvement, where developers focus on database health and efficiency rather than mere storage management. To effectively manage their PostgreSQL database size in a usage-based model, developers should adopt good data practices, such as reducing bloat, fine-tuning parameters, practicing indexing hygiene, using compression, setting up tiered storage, and implementing data retention policies. By adopting these strategies, developers can reduce their database size, improve performance, and scale their databases efficiently and effectively.

Boosting Postgres performance is achieved by adopting prepared statements in applications, which skips query parsing and analyzing, reducing overhead. Pairing with a connection pooler and transaction mode can dramatically improve database performance. Prepared statements are queries that accept parameters, pre-compiling the query on the server side, allowing for improved execution time. To use them effectively, developers should ensure their application uses the libpq version of creating prepared statements through the Object-Relational Mapping (ORM) implementation. The recommended configuration for Timescale allows 100 prepared statements to maximize efficiency in PgBouncer's cache. By adopting this approach, applications can achieve a performance win while boosting Postgres database performance.

Timescale has announced the availability of connection pooling on its platform, utilizing PgBouncer to address the longstanding issue of scaling database connections in PostgreSQL. This connection pooling service, hosted on dedicated infrastructure, allows users to improve database performance, especially in environments with numerous short-lived connections, such as serverless applications or those using AWS Lambda. The connection pooler offers two types of pools—session and transaction—to enhance throughput and performance while preserving resources. By maintaining persistent connections and managing the opening and closing of connections efficiently, PgBouncer reduces the overhead that PostgreSQL typically encounters with multiple concurrent connections. Timescale chose PgBouncer for its proven reliability and resource efficiency, while maintaining the flexibility to switch to other poolers like pgCat if needed. Additionally, Timescale provides users with access to PgBouncer's monitoring commands to ensure proper configuration and performance. This new feature is part of a series of launches during Timescale's Cloud Week, including live migrations, an enterprise tier, and enhanced database monitoring.

We scaled PostgreSQL to over 350 TB+ by leveraging TimescaleDB's features such as hypertables and continuous aggregates to handle massive amounts of query statistics data, ingesting tens of billions of records daily into a single database instance. We utilized columnar compression to store hundreds of TBs efficiently, enabling fast analytical queries on the users' side. The Insights database is powered by an "off-the-shelf" Timescale service with high-availability replicas, and we leveraged data tiering to keep hundreds of TBs accessible. Continuous aggregates simplified logic in constructing different time periods, streamlining data analysis and processing, while approximation algorithms included in Timescale's hyperfunctions simplified implementation. However, we faced challenges such as database observability limitations, snapshot-based views, and difficulties with schema modification operations at scale. Despite these pain points, the process of building Insights showed us how far our product can go, and we've added some things to our to-do list as engineers behind the product.

The newly released `Insights` feature by Timescale allows users to deeply observe the queries running in their database over a given time period, providing useful statistics on timing/latency, memory usage, and more. This complements existing tools like pg_stat_statements, which provides a complementary but limited form of database observability. Insights offers a scalable query collection system, storing sanitized statistics on every query running in the cloud service, and providing graphs and stats to power its functionality. The feature is available in all regions as part of Timescale's Cloud Week, and can be accessed through the service view of the Timescale service. It provides a user-friendly interface for users to explore relationships between system resources and query latency, identify problematic queries, and track performance over time. With Insights, users can save precious time and effort optimizing their queries, and unlock a new understanding of their database's performance.