Pstgre Archives

Tech Optimizer

May 21, 2025

Achieve up to 1.7 times higher write throughput and 1.38 times better price performance with Amazon Aurora PostgreSQL on AWS Graviton4-based R8g instances

Upgrading to Graviton4-based R8g instances with Aurora PostgreSQL-Compatible 17.4 in an Aurora I/O-Optimized cluster configuration results in significant performance improvements. The new instances provide up to 1.7 times higher write throughput, 1.38 times better price-performance, and reduce commit latency by up to 46% on r8g.16xlarge instances and 38% on r8g.2xlarge instances compared to Graviton2-based R6g instances. The Amazon Aurora PostgreSQL-Compatible Edition now supports AWS Graviton4-based R8g instances and PostgreSQL 17.4, which introduces performance enhancements for I/O-Optimized configurations, optimizing write operations and batch processing. R8g instances offer up to 192 vCPUs and 1.5 TB of memory, supporting larger configurations and providing up to 50 Gbps of network bandwidth. PostgreSQL 17 includes vacuum improvements, eliminates the need to drop logical replication slots during upgrades, and expands SQL/JSON standards. Aurora PostgreSQL-Compatible separates compute from storage, enabling independent scaling and maintaining six-way replication for durability, while processing changes as log records to reduce I/O operations. Performance benchmarks using HammerDB show improvements in throughput and commit latency across various workloads. For small workloads on 2xlarge instance size, throughput increased by 50.25% and commit latency improved by 33.87%. For medium workloads on 16xlarge instance size, throughput increased by 30% and commit latency improved by 17.44%. The most significant performance benefits arise from combining hardware upgrades from Graviton2 to Graviton4 with database engine upgrades from PostgreSQL 15.10 to 17.4. For small workloads, throughput increased by 70% and commit latency improved by 38.71%. For medium workloads, throughput increased by 70% and commit latency improved by 46.67%. Cost efficiency is also enhanced, with a 38% improvement in price performance and a 61.26% improvement in price-performance ratio when comparing Graviton2 and Graviton4 instances. Reserved Instances for Graviton4-based R8g instances offer additional cost-optimization opportunities.

Tech Optimizer

May 21, 2025

YugabyteDB Extends Support for Document Databases With Postgres Extension DocumentDB

Yugabyte has integrated support for the DocumentDB extension, a document database-compatible Postgres extension released by Microsoft, enhancing its multi-modal database capabilities. This integration allows developers to combine SQL and NoSQL functionalities within a single database environment, facilitating the transition from MongoDB workloads to YugabyteDB and enabling vector search queries through the pg_vector Postgres extension. The DocumentDB extension is open-source and aims to provide a unified, vendor-agnostic solution based on Postgres, which is rapidly gaining adoption. Since its launch, DocumentDB has received significant attention on GitHub, with over 1.6k stars and more than 80 forks. YugabyteDB is designed for high-performance, distributed SQL database applications and is backed by various investors.

Tech Optimizer

May 20, 2025

Modernizing Public Sector IT with PostgreSQL

On June 24, 2025, a webinar will discuss how PostgreSQL can help government agencies modernize their IT infrastructures while maintaining security and managing costs. The session will cover PostgreSQL's capabilities in addressing legacy system migration, regulatory compliance, and its certifications like FedRAMP and DISA STIG that ensure it meets federal security standards. Gianni Cioli, a PostgreSQL consultant with over 15 years of experience, will be the speaker.

Tech Optimizer

May 20, 2025

How to Set Up PostgreSQL with NestJS and Docker for Fast Local Development: A Quick Guide

Launching a new project with PostgreSQL for NestJS development can be efficiently managed by running a local PostgreSQL instance in Docker. This setup includes a Dockerfile for the NestJS application, a docker-compose.yml file to connect Node and PostgreSQL, and an .env file for environment variables. The Dockerfile specifies a Node environment, while the docker-compose.yml file configures services for both the database and the API. The database service uses the PostgreSQL image, and the API service builds from the Dockerfile, ensuring that the database persists through reboots. An .env file is created to store configuration details such as database user, password, and database name. Additionally, package.json scripts are enhanced for quick access to the database and application containers. In the NestJS application, the main startup file initializes the app, and a TypeORM configuration file is provided for database connection settings. Common development commands include starting the services, viewing logs, tearing down containers, and accessing the database and application shells.

Tech Optimizer

May 20, 2025

Running I/O intensive workloads on PostgreSQL with Amazon EBS io2 Block Express

Databases are essential for organizations, and their performance is critical to user experience and operational efficiency. AWS introduced Amazon Elastic Block Store (Amazon EBS) io2 Block Express in December 2020 to address performance issues related to storage latency during database operations. This solution enhances storage management for high-performance database servers, supporting up to 256,000 IOPS and 4,000 MiB/s throughput per volume, with a maximum volume size of 64 TiB and sub-millisecond disk I/O latency. A performance comparison was conducted between PostgreSQL databases on Amazon EBS gp3 and io2 Block Express, utilizing the HammerDB benchmarking tool. The analysis involved four tests: OLTP Load test, OLTP Stress test, OLAP Load test, and OLAP Stress test, using r5b.24xlarge EC2 instances. Results indicated that io2 Block Express outperformed gp3 by 2.08 times in transactions per minute (TPM) and provided significantly lower latency for both OLTP and OLAP workloads. Key differences between gp3 and io2 Block Express include durability, with io2 offering 99.999% durability compared to gp3's 99.8% – 99.9%. The maximum IOPS for gp3 is 16,000, while io2 supports up to 64,000 IOPS. Pricing for gp3 is approximately ,375.36/month, while io2 Block Express costs around ,408/month, reflecting its premium performance capabilities. In benchmarking tests, io2 Block Express demonstrated superior performance, achieving 3.95 times faster average performance than gp3 across all scenarios. It is particularly suitable for mission-critical applications requiring consistent sub-millisecond latency and high durability.

Tech Optimizer

May 19, 2025

Databricks To Buy Serverless Postgres Company Neon

Databricks has agreed to acquire Neon, a significant player in the serverless Postgres market, to enhance its database capabilities and developer experience. Over 80 percent of databases on Neon are automatically created by AI agents, indicating a shift towards agent-driven workflows. Neon's architecture allows for rapid deployment of Postgres instances in under 500 milliseconds, features a cost structure that scales with usage through the separation of compute and storage, and is fully compatible with the Postgres ecosystem. The acquisition aims to eliminate inefficiencies in database scaling, improve performance for concurrent AI agents, and streamline infrastructure while maintaining security and governance. Following the acquisition, Neon's team will join Databricks to support the development of responsive and reliable AI agent systems.

Tech Optimizer

May 19, 2025

How to catch and optimize problematic queries in PostgreSQL

Performance issues in PostgreSQL can arise as databases grow, leading to declines in query performance. To identify problematic queries, one can log long-running queries by setting the `log_min_duration_statement` parameter in the `postgresql.conf` file. The `pg_stat_statements` module can be enabled to monitor execution statistics of SQL statements, providing insights into query performance, including execution time and the number of calls. Queries with high standard deviation in execution time may indicate inconsistency, while sorting query statistics by total execution time can reveal excessive load from multiple fast queries. Real-time monitoring can be done using the `pg_stat_activity` view to check active queries and their states, and the `pg_locks` view can help identify blocked processes. The `EXPLAIN` command can analyze query execution plans, and using the `auto_explain` module can log plans for slow queries. Understanding cost parameters in query plans helps in optimizing performance, and different join methods (Nested Loop, Merge Join, Hash Join) have varying complexities and costs. To influence the planner's choice of scanning methods, configuration parameters can be adjusted, such as disabling sequential scanning. Extensions like `sr_plan`, `pg_hint_plan`, and `AQO` can further optimize query execution. Monitoring query progress can be done using dynamic views like `pg_stat_progress_*` for various commands.

Tech Optimizer

May 17, 2025

Databricks to Acquire Database Startup Neon for $1 Billion

Databricks plans to acquire Neon, a serverless Postgres provider, for billion. Neon's platform supports AI agents, with over 80% of databases provisioned automatically by them. The architecture allows rapid database spin-up in under 500 milliseconds and is compatible with the open-source Postgres ecosystem. Neon was founded in 2021 by Nikita Shamgunov, Heikki Linnakangas, and Stas Kelvich, and features a design that separates storage and compute for instant provisioning and auto-scaling. Databricks aims to integrate Neon’s technology into its Data Intelligence Platform to enhance AI workloads. Following the acquisition, Neon’s team will join Databricks while continuing to support the existing community. This acquisition is expected to impact the 0 billion database market. Databricks has also acquired Fennel and Tabular to enhance its capabilities in the tech landscape.

Tech Optimizer

May 17, 2025

Supercharging your data experience: PostgreSQL 17 is now available on IBM Cloud

PostgreSQL Version 17 has launched on IBM Cloud, featuring optimized memory management, faster query execution, and accelerated bulk operations. These enhancements improve performance, scalability, security, and developer productivity. IBM Cloud's ecosystem of extensions and plugins supports PostgreSQL 17, enabling efficient management of complex applications.

Tech Optimizer

May 16, 2025

WebDevPro #90: GSAP Goes Free, PostgreSQL 18 Beta Launches, Node 24 Overhauls Windows Support

The integration of system design principles in software development is increasingly critical, influenced by the rise of artificial intelligence (AI) in development workflows. The demand for programmers who can quickly write code is diminishing, with organizations seeking individuals who possess a strong understanding of programming fundamentals and can oversee AI-generated outputs. Developers are now expected to leverage AI to enhance their work and maintain quality by recognizing when AI is not performing optimally. A holistic view of software architecture is encouraged, emphasizing collaboration and the importance of each code contribution to the overall project. The industry is shifting away from evaluating candidates based solely on syntax proficiency and coding speed, recognizing the value of strategic thinking and architectural awareness in developers.