database connections Archives

Tech Optimizer

October 31, 2025

AI-powered tuning tools for Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL databases: PI Reporter

AWS offers services like Amazon CloudWatch and CloudWatch Database Insights for monitoring Amazon RDS performance metrics. Users can create custom dashboards for Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition. Essential metrics to monitor include Database Connections, CPU Utilization, Freeable Memory, and various I/O metrics. PI Reporter is an open-source tool designed to capture performance metrics and workload snapshots for Amazon Aurora PostgreSQL-Compatible Edition. It integrates with Amazon Bedrock to analyze data and provide actionable recommendations. Users can generate detailed HTML reports comparing performance metrics over time, assess workload changes, and identify performance issues. To use PI Reporter, prerequisites include enabling Amazon CloudWatch Database Insights and the pg_stat_statements extension. Installation involves cloning the repository, configuring IAM policies, and executing commands to create snapshots and reports. The tool is designed for ease of use and can be run on Amazon EC2 or on-premises. The report generated by PI Reporter includes a summary of findings, root causes, and recommendations for performance improvement. It also identifies resource utilization issues during specific operations, such as bulk data inserts or idle transactions. Users are advised to validate AI-generated recommendations with database experts and to monitor for idle transactions to optimize performance. Costs may be incurred from resources created by Amazon Bedrock, and users should clean up these resources when no longer needed.

Tech Optimizer

September 25, 2025

Building a Video Game Recommender System with FastAPI, PostgreSQL, and Render: Part 2

The project involves deploying a board game recommendation system using FastAPI and PostgreSQL on Render. Key tasks include configuring a PostgreSQL database on Render, populating it with data, Dockerizing the FastAPI application, and deploying it as a Render Web Application. The PostgreSQL database is created on Render with the name “fastapi-database” using the free tier. The database connection is tested using the External Database URL, and tables are populated using a script. The FastAPI application is containerized with Docker, which involves creating a Dockerfile that specifies the base image, working directory, dependencies, and commands to run the application. The Docker image is built, tagged, and pushed to DockerHub. The image is then pulled into Render, where a personal access token is created for secure access. Environmental variables are configured for the FastAPI application, and upon successful deployment, the application can be accessed via a provided link, with Swagger documentation available for testing database connections. The project repository is available on GitHub, along with additional resources for FastAPI and Docker.

Tech Optimizer

September 25, 2025

Partnering to make full-stack fast: deploy PlanetScale databases directly from Workers

Cloudflare has introduced a new feature that allows direct connections between Cloudflare Workers and PlanetScale databases, enabling developers to create full-stack applications using Postgres or MySQL. This integration is designed to enhance performance, reliability, and developer experience. The connection is optimized through Hyperdrive, which provides rapid access to databases regardless of their location. Developers can now link their Cloudflare and PlanetScale accounts directly from the Cloudflare dashboard without needing to manage API keys manually. The integration includes automatic generation of Hyperdrive configurations for optimal database access, minimizing latency and CPU load through efficient connection pooling and caching of frequently queried results. Developers can connect their PlanetScale accounts via the Cloudflare dashboard or vice versa and deploy a Worker easily.

Tech Optimizer

June 9, 2025

Streamline Amazon Aurora database operations at scale: Introducing the AWS Database Acceleration Toolkit

The AWS Database Acceleration Toolkit (DAT) is an open-source solution that enhances database performance, particularly for Amazon Aurora, by utilizing Terraform for setup, provisioning, and maintenance. DAT helps organizations reduce time to market, improve customer satisfaction, and optimize costs. It is beneficial for DevOps teams, SaaS providers, and enterprises transitioning from commercial databases to Aurora. One example of its effectiveness includes a customer reducing database migration time from 12 months to 4 weeks for over 100 databases, achieving zero outages over six months, increasing database team productivity by 60-70%, and lowering infrastructure costs by 42%. DAT's architecture includes automated resource provisioning and security through Terraform, with source code available in a Git repository. It offers multiple provisioning options for Aurora clusters, including the Terraform CLI and Jenkins pipelines. Key modules include: - Aurora cluster module for creating new clusters with customization options. - Amazon RDS Proxy for enhancing application performance and security. - Aurora GlobalDB for creating clusters across AWS Regions for disaster recovery. - Aurora Monitoring for configuring CloudWatch dashboards to monitor database performance. Use case examples are provided in the Git repository for both Aurora PostgreSQL-Compatible and Aurora MySQL-Compatible engines. Deployment options include using Terraform directly, setting up a new Jenkins instance, or integrating with an existing Jenkins setup. To provision an Aurora PostgreSQL cluster, users must clone the DAT repository, navigate to the appropriate folder, configure the Terraform variable definition file, initialize the working directory, execute a plan, and apply the changes. Security features include customer-managed keys, integration with AWS Secrets Manager for password management, and enhanced monitoring through CloudWatch. To clean up, users can destroy the Aurora cluster with a Terraform command.

Tech Optimizer

May 24, 2025

Connect Amazon Bedrock Agents with Amazon Aurora PostgreSQL using Amazon RDS Data API

Generative AI applications are being integrated with relational databases, allowing organizations to utilize structured data for training AI models. This integration involves using the RDS Data API with Amazon Aurora PostgreSQL-Compatible Edition and Amazon Bedrock for AI model access and automation. The solution enables natural language queries to be converted into SQL statements, executed against the database, and returns results in a user-friendly format. The architecture includes several steps: invoking the Amazon Bedrock agent with natural language input, generating SQL queries using large language models (LLMs), executing those queries via the Data API, and returning formatted results. Security measures are in place to restrict operations to read-only, preventing modifications that could compromise data integrity. To implement this solution, prerequisites include deploying an Aurora PostgreSQL cluster using AWS CDK and setting up the necessary Lambda functions and IAM roles. The agent is designed to convert natural language prompts into SQL queries and execute them securely. Testing can be conducted through the Amazon Bedrock console or the InvokeAgent API, with options for tracing the agent's steps. Key considerations for this integration include limiting it to read-only workloads, implementing parameter validation to prevent SQL injection, and ensuring comprehensive logging and auditing. For multi-tenant applications, appropriate isolation controls should be established. To avoid future charges, all resources created through CDK should be deleted after use.

Tech Optimizer

April 8, 2025

Pools across the sea: how Hyperdrive speeds up access to databases and why we’re making it free

Cloudflare has made Hyperdrive available on the free plan of Cloudflare Workers, allowing developers to create high-performance global applications that connect to SQL databases. Hyperdrive simplifies database connectivity by using existing drivers and connection strings, reducing the need for extensive refactoring. It has been adopted by Cloudflare's engineering teams for various functions, demonstrating its effectiveness in addressing common challenges in application development. Hyperdrive significantly improves performance, with a benchmark showing latency reduction from 1200 ms to 500 ms when using Hyperdrive instead of a direct connection, and further to 320 ms with caching enabled. It employs transaction-mode connection pooling to efficiently manage database connections, minimizing overhead and ensuring optimal performance for serverless applications. Hyperdrive's architecture includes a split connection approach that reduces latency by conducting necessary round trips over shorter distances. It also features a regional pool strategy for selecting data centers based on the inferred location of the Worker, optimizing connection latency. The system includes a dual-layer caching strategy to enhance query performance and reduce load on the origin database. Developers can easily start using Hyperdrive by executing a simple command or using a dashboard to set up a sample Worker application with their existing Postgres database.

Tech Optimizer

December 8, 2024

Project X #9 ~ Blend PostgreSQL with Python for Better Scalability

The text discusses the integration of Python and PostgreSQL for developing scalable applications, transitioning from SQLite to PostgreSQL. It highlights the use of the psycopg2 library for connecting Python to PostgreSQL and Postgres.app for setup. The project will cover establishing secure database connections, performing bulk inserts, and safely updating records using parameterized queries to prevent SQL injection. By the end of the project, participants will be able to create tables, insert data, execute queries, update records, and delete information while following best practices for database management. The project also includes implementing logging and managing environment variables for database credentials. A limited-time lifetime discount on annual subscriptions is mentioned, offering immediate access to content.

Tech Optimizer

October 2, 2024

How a large financial AWS customer implemented high availability and fast disaster recovery for Amazon Aurora PostgreSQL using Global Database and Amazon RDS Proxy | Amazon Web Services

A financial institution collaborated with AWS to create a solution that enables sub-minute failover between Availability Zones and single-digit minute recovery times across AWS Regions for their wealth management customer portal. The solution utilizes automation for failure detection and failover, along with AWS-managed data replication, specifically employing the Amazon Aurora PostgreSQL-Compatible Edition and Amazon Aurora Global Database for cross-Region replication. Key components include canary outage detection via AWS Lambda, DNS redirection through Amazon Route 53, and control plane resilience using the Amazon Route 53 Application Recovery Controller. The architecture is based on a three-tier model, with in-Region failovers expected to occur in seconds and cross-Region recoveries within minutes. The architecture team aimed to reduce the Recovery Time Objective (RTO) from tens of minutes to seconds and established a Recovery Point Objective (RPO) of under one minute. The failover process prioritizes speed, opting for immediate failover rather than controlled switchover. Testing confirmed that in-Region failover resulted in minimal disruption, while cross-Region failover could be completed in single-digit minutes. Recent enhancements to Amazon RDS Proxy and Aurora PostgreSQL have improved the architecture's efficiency and user experience.

Tech Optimizer

October 2, 2024

How BCM One migrated data from an unencrypted Amazon RDS for PostgreSQL database instance to a new encrypted instance using AWS DMS | Amazon Web Services

BCM One is a global provider of NextGen Communications and Managed Services, with Flowroute as a brand specializing in SIP trunking and business messaging. Flowroute encrypted its Amazon RDS for PostgreSQL database instances using AWS DMS. Storage encryption is essential for protecting disk data and involves a key from AWS KMS. Existing RDS instances cannot be directly encrypted, so a new encrypted instance must be created using RDS snapshots and AWS DMS. The migration strategy includes creating an empty RDS instance, initiating a CDC-only AWS DMS task, creating a snapshot of the unencrypted instance, generating an encrypted snapshot, and restoring a new encrypted RDS instance. RDS Proxy is used to manage connections and minimize downtime during the transition. The process involves creating a DMS login role, configuring RDS parameters, creating DMS endpoints, and verifying replication success. After the migration, the proxy authentication must be updated to connect to the encrypted database. The migration process also highlights the importance of primary keys and unique constraints to prevent duplicate entries during replication.

Tech Optimizer

July 5, 2024

New – Rightsizing Recommendations for Amazon RDS MySQL and RDS PostgreSQL in AWS Compute Optimizer | Amazon Web Services

AWS Compute Optimizer now delivers recommendations for Amazon RDS MySQL and PostgreSQL databases to help detect idle instances, identify optimal instance types, and provisioned IOPS settings.