August 2023 Summaries

To speed up the creation of backups for large PostgreSQL databases, the authors of the platform Timescale combined pgBackRest with EBS (Elastic Block Store) snapshots. This approach significantly improved the backup and restore process, reducing the time it takes to create full backups by over 100x and restoring from backups by a similar margin. By taking EBS snapshots at regular intervals, the authors were able to quickly restore their databases in case of failures, while still maintaining the reliability of pgBackRest for data integrity and validation. This approach has improved the customer experience and peace of mind for the Timescale team, making it an attractive solution for PostgreSQL users running in AWS environments.

The text explores best practices for selecting data types in PostgreSQL and TimescaleDB, focusing on their suitability for applications that store and analyze large volumes of data, such as time series and logs. It emphasizes the importance of compression to optimize storage and input/output operations, especially in cloud environments. The discussion includes recommendations for data types like integers, floating-point numbers, timestamps with time zones, and JSONB, while advising against less efficient types such as numeric, char(n), and JSON. The text underscores the need to balance query speed, compressibility, and data requirements, providing insights into optimizing data type choices to enhance performance and manageability in TimescaleDB.

TimescaleDB is a new open-source time-series database optimized for fast ingest and complex queries. It is built on PostgreSQL, packaged as an extension, and scales horizontally while supporting normal SQL and all the features expected from a relational database. The key benefits of TimescaleDB include its compatibility with existing clients, connectors, and BI tools; scalability through horizontal scale-out, automatic space/time partitioning, and distributed query optimizations for time-series data; and reliability due to its foundation on PostgreSQL's 20+ years of work in reliability and tooling. TimescaleDB is designed to be easy to use, especially for those familiar with SQL or PostgreSQL.

AWS IoT Core is a managed service that allows users to easily connect and manage Internet of Things (IoT) devices. It uses the MQTT protocol, which is lightweight and fault-tolerant, and can be used as a message broker or with message routing functionality to forward messages to other Amazon Web Services such as Kinesis, SQS, and Kafka. AWS IoT Core can be integrated with AWS Lambda functions, allowing users to trigger actions based on incoming MQTT messages. In this example, an AWS Lambda function is created to insert MQTT messages into a Timescale database, which is a time-series database that allows for fast ingestion and querying of data in milliseconds, even at terabyte scale. The function uses PostgreSQL's NOW() function to timestamp the inserted data, and the process is demonstrated by creating a message routing rule and testing it with a Python script.

The PostgreSQL community has grown and evolved over time, with a significant increase in its popularity as evidenced by the 2023 Stack Overflow Developer Survey. The community's practices have also become more diverse, with a notable emphasis on personal projects and contributions to the database. In 2022, the community shared that PostGIS is their most frequently used extension, while this year they are interested in learning about AI tools and their integration into PostgreSQL. The survey results will be published in a free report that provides valuable insights into the PostgreSQL ecosystem, helping to make the database better for everyone. The survey is open until August 31, 2023, and users can share their voice with the community by participating.