PostgreSQL Archives

Tech Optimizer

August 15, 2025

Wiz Achieves Near-Zero Downtime for Amazon Aurora PostgreSQL Major Version Upgrades with Aurora Blue/Green Deployments

Wiz has transitioned its Amazon Aurora PostgreSQL database from version 14 to version 16 with near-zero downtime using Aurora Blue/Green Deployments. The upgrade process is facilitated by the DB Upgrade Pilot, which features an automated eight-step flow, including automated validation steps, enhanced synchronization monitoring, and end-to-end orchestration. This has reduced the downtime for database upgrades from one hour to 30 seconds.

Tech Optimizer

August 15, 2025

How to Create a Foreign Data Wrapper in PostgreSQL and Aurora PostgreSQL on AWS RDS

Foreign data wrappers (FDWs) in PostgreSQL and Aurora PostgreSQL allow users to access and manipulate data from external sources without data migration. To set up a foreign data wrapper, the following steps are required: 1. **Install the Foreign Data Wrapper**: Use the command `CREATE EXTENSION postgres_fdw;` to install the FDW. 2. **Set Up Foreign Data Wrapper Access**: Grant usage permissions using `GRANT USAGE ON FOREIGN SERVER foreign_server_name TO PUBLIC;` or for specific users. 3. **Create a Foreign Server**: Use the command `CREATE SERVER foreign_server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'hostname', dbname 'dbname', port '5432');`. 4. **Create User Mapping**: Establish authentication details with `CREATE USER MAPPING FOR local_username SERVER foreign_server_name OPTIONS (user 'remote_username', password 'remote_password');`. 5. **Create Foreign Tables**: Reference remote tables using `CREATE FOREIGN TABLE remote_table ( id SERIAL PRIMARY KEY, data VARCHAR(50) ) SERVER foreign_server_name OPTIONS (table_name 'remote_table');`. An example use case is an online retailer using a PostgreSQL database for operations and an external SQL database for customer feedback, allowing real-time insights into customer satisfaction.

Tech Optimizer

August 12, 2025

Postgres for everything? not really..and here are some of the problems.

PostgreSQL can serve as a powerful all-in-one database, but real-world implementations often complicate its effectiveness due to complex corporate infrastructures. Accessing a production database may involve numerous network hops, firewalls, and antivirus software that can slow down performance. The lack of administrative privileges can hinder the use of extensions. Developers often turn to Kubernetes for more freedom, but this can introduce new challenges, such as unpredictable resource allocations and performance inconsistencies. Row-Level Security (RLS) can lead to performance overhead and complicate debugging. Centralizing business logic in stored procedures can enhance performance but complicates version control and tracking changes. In large organizations, policies governing technology use often create bottlenecks. PostgreSQL may not be optimal for portable setups (SQLite), simple caching (Redis), or specialized search functionalities (Elasticsearch, Meilisearch). It is most effective when developers have control over the environment, allowing it to handle various applications efficiently.

Tech Optimizer

August 12, 2025

How Wiz achieved near-zero downtime for Amazon Aurora PostgreSQL major version upgrades at scale using Aurora Blue/Green Deployments

Wiz upgraded its Aurora PostgreSQL database from version 14 to 16, achieving a significant reduction in downtime from approximately one hour to just 30 seconds. The upgrade was facilitated by an automated process called DB Upgrade Pilot, which utilized Amazon Aurora's Blue/Green Deployments. This process involved several steps: taking a cluster snapshot, provisioning a synchronized replica of the production database, executing a VACUUM ANALYZE for performance optimization, monitoring replication lag, and performing a switchover to the new environment. The upgrade included performance enhancements such as improved parallel query execution and faster aggregations, along with enhanced security features. The solution also incorporated safety measures like automatic rollback capability and robust validation steps to ensure a stable upgrade process.

Tech Optimizer

August 10, 2025

Why Oracle Thinks PostgreSQL Isn’t Enough

AIM Media House can be contacted via email at info@aimmediahouse.com. AIM India office address: 1st Floor, Sakti Statesman, Marathahalli – Sarjapur Outer Ring Rd, Green Glen Layout, Bellandur, Bengaluru, Karnataka 560103. AIM Americas office address: 166 Geary St STE 1500, Suite #634, San Francisco, California 94108, United States.

Tech Optimizer

August 9, 2025

PostgreSQL Revolutionizes Durable Execution with Database Workflow Storage

Developers and enterprises are increasingly turning to PostgreSQL for durable execution, which allows workflows to withstand failures without losing state. PostgreSQL's strong transactional guarantees, adherence to ACID principles, and extensibility make it suitable for this purpose. By using Write-Ahead Logging (WAL), PostgreSQL can reliably record every step of a process, facilitating smooth resumption after disruptions. This approach eliminates the need for separate state management layers, simplifying cloud-native environments. PostgreSQL can manage both data persistence and execution logic, allowing for seamless recovery from failures. Recent advancements, such as integrations from Neon and Inngest, enable real-time workflows based on database changes without requiring complex orchestration services. Updates in PostgreSQL 16 and beyond, including improved logical replication and the anticipated asynchronous I/O in PostgreSQL 18, enhance performance for durable tasks. Adoption of PostgreSQL for durable execution is rising among tech giants and startups, with examples like Figma's scalable Postgres database and AWS’s Aurora DSQL service. This trend reflects a movement towards disaggregated architectures, where databases become active participants in application logic. However, challenges remain in balancing speed and durability configurations. Overall, PostgreSQL is evolving as a resilient solution for durable execution in modern applications.

Tech Optimizer

August 5, 2025

Building a job search engine with PostgreSQL’s advanced search features

Job search platforms connect employers and candidates through advanced search engines that analyze structured and unstructured data. These platforms require robust database technologies for executing complex queries, full-text and semantic searches, and geospatial functionalities. A modern job search engine consists of a data repository that stores job listings and candidate profiles, and a search engine that facilitates bidirectional searches. Key features of an effective job search engine include: - **Full-text search**: Provides lexical matching for job titles and skills, supporting exact phrase matching and typo-tolerant searches. - **Semantic search**: Uses vector-based similarity to understand context and relationships beyond literal terms. - **Geospatial search**: Incorporates geographic considerations to find opportunities within specific distances. PostgreSQL serves as both a data repository and search engine, supporting full-text search, semantic search via the pgvector extension, and geospatial queries using the PostGIS extension. The job search engine utilizes PostgreSQL to manage job listings and candidate profiles, enabling real-time searches across millions of entries. The data model for a job search engine includes tables for jobs and resumes, with columns for full-text search vectors, semantic vectors, and geographical locations. PostgreSQL's full-text search capabilities include tokenization, dictionaries for language-aware parsing, and ranking functions for relevance. Advanced features such as proximity search, simple and weighted ranking, and fuzzy matching enhance search accuracy. Vector embeddings represent text in high-dimensional space, allowing for semantic searches that recognize similar roles or skills. PostgreSQL supports vector similarity searches with specialized indexing methods like IVFFlat and HNSW for efficient querying. Geospatial search capabilities in PostgreSQL enable location-aware job searches, allowing candidates to find jobs within commuting distances. Combining different search techniques, such as full-text and semantic searches, provides more relevant results. Performance optimization features in PostgreSQL address challenges related to computational complexity, indexing overhead, and concurrent query loads. The architecture discussed is applicable to various applications beyond job search platforms, including e-commerce, real estate, content recommendation systems, travel, and healthcare provider matching.

Tech Optimizer

August 4, 2025

Zero-Day Flaw in PostgreSQL Exploited to Target BeyondTrust Systems

A significant PostgreSQL vulnerability, CVE-2025–1094, was identified during the investigation of another vulnerability, CVE-2024–12356, which was exploited in the BeyondTrust breach in December 2024. The breach involved unauthorized access to BeyondTrust's systems and was linked to the state-sponsored hacking group Silk Typhoon from China. The U.S. Treasury Department confirmed its network was compromised through a stolen BeyondTrust API key. CVE-2025–1094 is an SQL injection vulnerability that allows attackers to execute arbitrary SQL commands due to improper handling of invalid UTF-8 byte sequences. Rapid7 found that CVE-2024–12356's exploitation relied on CVE-2025–1094, and that CVE-2025–1094 could be exploited independently. BeyondTrust issued patches for these vulnerabilities, but the patch for CVE-2024–12356 did not directly address the underlying cause of CVE-2025–1094. The exploitation of these vulnerabilities underscores the need for timely patching and proactive security measures in organizations using PostgreSQL.

Tech Optimizer

July 31, 2025

Cloudflare Chooses PostgreSQL Extension Over Specialized OLAP for 100K Row/Second Analytics

Cloudflare's team chose TimescaleDB over ClickHouse for its Zero Trust product suite to achieve a balance between managing analytical and configuration data and the performance of an OLAP system. They developed Digital Experience Monitoring (DEX), an observability platform that collects structured logs from WARP clients and presents them through a dashboard. Initially using ClickHouse since 2017, they faced limitations with its MergeTree engine, which struggled with numerous small writes from millions of devices. To expedite DEX's release, they used PostgreSQL, managing 200 inserts per second at launch, which scaled to 1,000 inserts/sec as usage grew, leading to performance degradation. They improved query performance by precomputing aggregates, achieving a 1000x improvement. Transitioning to TimescaleDB allowed for better management of time-stamped data with automated aggregation and data retention. Benchmarking showed performance enhancements of 5x to 35x compared to traditional PostgreSQL tables. Following DEX's success, TimescaleDB has been adopted for other Cloudflare initiatives, including Zero Trust Analytics & Reporting.

Tech Optimizer

July 30, 2025

Benchmark Reports Identify EDB Postgres® AI as the Sovereign Platform for Generative and Agentic AI

Recent benchmark studies by Everest Group and McKnight Consulting Group reveal that EDB Postgres AI significantly reduces development complexity by 67% and total cost of ownership (TCO) by 50% compared to traditional DIY solutions. Over 90% of major global enterprises plan to establish their own sovereign AI and data platforms within three years, yet only 13% are currently implementing agentic and generative AI. These early adopters achieve twice the volume of AI output and fivefold economic benefits by focusing on sovereignty in their AI infrastructures. EDB Postgres AI is noted for eliminating complexity, offering a fully integrated platform for modern AI workloads, and enabling faster development and lower costs. It is reported to be three times faster to production, 51% lower in total cost, and simpler to manage at scale. Key highlights from the benchmark study include: - 67% lower development effort and 38% lower maintenance complexity compared to DIY cloud approaches. - 40%-43% fewer build steps required in typical in-house builds. - More than 90% fewer steps in the development stage, leading to significant productivity gains. - Over 55% lower total complexity compared to in-house data and AI management solutions. - EDB Postgres AI enables delivery of AI-enabled applications three times faster than DIY cloud solutions, reducing time to production from 28 weeks to 9. - 65% faster time-to-value, compressing a year-long build into weeks. - Up to 51% lower TCO over three years due to reduced infrastructure, licensing, and personnel costs.