PostgreSQL Archives

Tech Optimizer

August 4, 2026

Automated PII redaction for Amazon RDS for PostgreSQL audit logs

The article discusses a serverless pipeline for creating an irreversibly redacted and queryable archive of Amazon RDS for PostgreSQL audit logs. It sanitizes sensitive information, such as Social Security numbers, credit card details, and email addresses, before storing the clean logs in Amazon S3, which can be queried using Amazon Athena. The deployment process is facilitated by a single AWS CloudFormation template. The challenge is that pgAudit logs sensitive data in cleartext to Amazon CloudWatch Logs, which poses compliance risks under regulations like GDPR and HIPAA. While CloudWatch Logs Data Protection offers reversible masking of PII, this is insufficient for compliance needs requiring permanent separation of PII from audit evidence. The proposed pipeline creates an irreversibly redacted copy stored in Amazon S3, ensuring original values cannot be restored. The deployment requires an active AWS account with specific permissions, AWS CLI v2, a compatible AWS region, and confirmation of service quotas. The CloudFormation template has four main parameters for deployment, including ProjectName, DBInstanceClass, DBAllocatedStorage, and EnvironmentType, with sensible defaults provided. Three deployment options are available: via the AWS console, AWS CLI, or a deploy script. After deployment, users can generate test data, trigger the redaction pipeline, and verify results in Athena. The process includes steps to create a PostgreSQL table, insert records with PII, run queries, and monitor the execution of the redaction pipeline. The article emphasizes that unredacted PII remains in the source CloudWatch Logs, recommending enabling CloudWatch Logs Data Protection for added security. To clean up resources and avoid charges, users must empty the S3 buckets and delete the stack. Cost considerations for the pipeline include infrastructure costs and the expense of achieving irreversibility.

Tech Optimizer

July 31, 2026

Someone turned PostgreSQL into a city you can walk around in

Nik Samokhvalov created PGSimCity, a visualizer for the open-source database PostgreSQL, inspired by the game SimCity. It allows users to navigate a city-like environment using gaming controls, where database transactions are represented as animations. The project originated from a prompt to Claude and involved 3.86 billion tokens of AI-assisted coding. PGSimCity is open source and has potential for adaptation to explain other complex technologies.

Tech Optimizer

July 30, 2026

Generating Referentially Consistent Synthetic Test Data for PostgreSQL in .NET Application Modernization

Satyajit Gokhale, Aiswaryaa Venugopalan, Ruchi Agarwal, Nits Jeganathan, Khurram Khawaja, Vijay Mandadi, and Luke Huan contributed to this article. AWS Transform offers a .NET application modernization service that allows enterprise customers to migrate from Microsoft SQL Server to Amazon Aurora PostgreSQL Compatible Edition. This migration reduces licensing costs and enhances scalability while utilizing an open-source ecosystem. Schema conversion tools can assist in translating DDL to PostgreSQL-compatible definitions, but correctness must be verified by teams to ensure performance under real-world workloads. Traditional testing methods, such as using production data or manual checks, are not scalable and raise privacy concerns. Therefore, there is a need for realistic synthetic data generated at scale that adheres to the migrated schema's constraints. AWS Transform for SQL Server modernization employs an AI agent to generate constraint-respecting synthetic data at scale during the migration workflow. This process involves loading the PostgreSQL schema into a live database, constructing a dependency graph for data generation, and distributing tasks across multiple sub-agents. The agent validates each generated INSERT against the live database, ensuring compliance with constraints and providing error diagnostics for any violations. The synthetic data generation workflow consists of four stages: schema analysis, establishing generation order, distributing tasks across sub-agents, and validation with error recovery. The agent extracts metadata from the PostgreSQL schema, determines a valid table generation sequence, parallelizes data generation while maintaining referential integrity, and generates and validates data row-by-row against the live database. Each successful insertion enriches the context for subsequent generations, while errors are handled through structured diagnostics and iterative regeneration. The process ensures referential integrity and preserves the generation state, allowing for recovery from interruptions without data loss.

Tech Optimizer

July 30, 2026

EDB Says Postgres AI Tops Enterprise AI Data Platform Benchmarks

EnterpriseDB (EDB) has released benchmark results for its EDB Postgres AI (EDB PG AI), showing superior performance compared to various AI data platforms, including vector databases and managed Postgres platforms. The independent testing by McKnight Consulting Group indicated that EDB PG AI outperformed competitors in all evaluated metrics, including query speed, accuracy, and cost efficiency. Key findings include: - EDB PG AI achieved a median query latency of 50 milliseconds at 50 million vectors, making it the fastest platform tested, 80 times faster than Databricks and 21 times faster than MongoDB Atlas. - It demonstrated the highest accuracy for core vector search with a recall rate of 0.911, outperforming MongoDB by 26% and Databricks by 17%. - In a concurrent agent loop test, EDB PG AI completed tasks in 27 milliseconds, significantly faster than MongoDB Atlas (699 milliseconds) and Databricks (5.1 seconds). - EDB PG AI provided 76 times better price performance than Databricks and 34 times better than MongoDB. - The platform integrates vectors, structured data, and analytics within a single operational database, enhancing efficiency and reducing the risk of data inconsistency. The benchmark study assessed the performance of EDB PG AI against Amazon Aurora PostgreSQL, Databricks, Crunchy Bridge, MongoDB Atlas, and open source PostgreSQL, all standardized on equivalent enterprise hardware.

Tech Optimizer

July 29, 2026

After rewriting SQLite in Rust, Turso turns its sights on Postgres

Cloud database provider Turso is developing a Postgres-compatible implementation based on its SQLite-compatible database, which was created from scratch in Rust. CEO Glauber Costa believes Postgres can benefit from modernization for cloud-native applications. Turso's SQLite reimplementation, now called Turso, uses a virtual machine architecture that may eventually support other database frontends, including MySQL and Redis. The company initially forked SQLite into libSQL but later pivoted to a cloud service named Turso. They focused on a virtual machine architecture for their rewrite, which translates SQL queries into a custom bytecode language. Turso has developed a Postgres-compatible prototype named pgmicro, which aims to run existing applications with minimal modifications. The company is also working on a database-as-a-cloud service that will allow customers to use various database types on the Turso platform.

Tech Optimizer

July 29, 2026

EDB Postgres® AI Outperforms Vector Databases, Lakehouses, and Document Stores for Agentic AI on Speed, Accuracy, and Cost

EDB Postgres AI is a pioneering solution that integrates intelligence and data on a unified sovereign foundation, eliminating the need for ETL processes, data duplication, and separate vector stores. Independent benchmarks show that EDB Postgres AI outperforms competing platforms in speed, accuracy, and cost-effectiveness. A study by McKnight Consulting Group indicates that EDB Postgres AI excels in key performance metrics, including query latency, accuracy, cost, and data freshness, outperforming specialized vector databases and other managed Postgres platforms. EDB Postgres AI achieves median query latencies of 50 milliseconds at a scale of 50 million vectors, making it 80x faster than Databricks, 21x faster than MongoDB Atlas, and up to 2x faster than alternatives like Aurora and Crunchy Bridge. It also delivers the highest recall rates for core vector searches, surpassing competitors like MongoDB and Databricks. In tests involving concurrent retrievals, EDB Postgres AI completed a three-arm agent loop in 27 milliseconds, significantly faster than other platforms. In terms of cost efficiency, EDB Postgres AI offers 76x better price performance than Databricks, 34x better than MongoDB, and 23%–28% better than the nearest managed Postgres competitors when considering query speed. The architecture of EDB Postgres AI allows for the integration of vector, filtered, and full-text retrieval in a single query path, ensuring real-time data access and eliminating the need for separate systems.

Tech Optimizer

July 28, 2026

Migrate self-managed PostgreSQL to Amazon RDS using the RDS console

Running PostgreSQL on Amazon EC2 requires users to manage operational tasks such as patching, backups, and security. Migrating to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition offers a fully managed service that automates these tasks, allowing development teams to focus on application development. The migration process uses the auto-migration feature in the Amazon RDS console, supported by AWS Database Migration Service (AWS DMS), which facilitates PostgreSQL-to-PostgreSQL migrations using native tools for accurate schema mapping and faster migration. The migration can be executed using various methods, including pg_dump/pg_restore, manual AWS DMS tasks, or third-party tools, with the RDS console approach being preferred for its reliability and minimal setup effort. AWS DMS supports three replication modes: full load, CDC only, and full load + CDC, with the migration duration depending on database size and network throughput. Prerequisites for migration include having a source PostgreSQL 10.4+ on Amazon EC2, a target RDS for PostgreSQL or Aurora PostgreSQL, proper IAM roles, stored credentials in Secrets Manager, and appropriate networking configurations. Considerations include unsupported objects, potential issues with sequence values, and the need to avoid schema changes during CDC. The migration involves preparing the source database, creating necessary users and permissions, and executing the migration through the RDS console. Post-migration, it is essential to verify data integrity, optimize performance by updating statistics and rebuilding indexes, and implement security best practices such as disabling the DMS user and enforcing SSL/TLS connections. Common troubleshooting errors during migration include permission issues, incorrect WAL levels, and connection refusals, each with specific resolutions. Finally, to avoid ongoing charges, it is recommended to clean up resources created during the migration process, including deleting the DMS migration task, logical replication publications, Secrets Manager secrets, and the target RDS/Aurora instance.

Tech Optimizer

July 28, 2026

‘PGSimCity’ shows how the PostgreSQL database actually works internally in a SimCity-like 3D environment.

PGSimCity is a web application that provides a 3D visualization of PostgreSQL's architecture, allowing users to explore its internal operations during SQL query execution. The application features an interactive interface that guides users through the connection process to PostgreSQL, illustrating how client connections reach the Postmaster and backend processes. It highlights the significance of connection pooling and the impact of increasing connections on backend processes. In the 'Query Lab' section, SQL statements are transformed into execution plans, showcasing processes like Parser, Rewriter, Planner, and Executor, along with execution paths such as Seq Scan and Index Scan. The shared_buffers area represents PostgreSQL's buffer cache, optimizing performance by reading data in 8KiB pages. Visualizations depict data pages stored on disk, with pathways showing the movement of pages to shared_buffers. The Write-Ahead Log (WAL) mechanism records changes for recovery, and transaction commits wait for WAL records to be flushed. Checkpointing writes dirty pages to storage in batches, which can cause latency spikes. PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage updated rows and dead tuples, which are cleaned up by autovacuum processes. The application also illustrates replication, where WAL from the primary server is sent to the standby server to maintain data consistency. Overall, PGSimCity provides a comprehensive view of PostgreSQL's components and potential performance issues related to caching, WAL management, checkpointing, vacuuming, and replication.

Tech Optimizer

July 24, 2026

How we pushed CDC into Postgres, and turned replication into clockwork

Making data from transactional databases accessible to analytical databases is essential in modern data architecture, but it faces challenges such as fragile tooling, high costs, and complex operations. Snowflake has developed a Postgres service that addresses these issues by reimagining Postgres replication. Postgres is a strong operational database, but its change data capture (CDC) capabilities need improvement. Many data pipelines are fragile due to complexities in managing continuous data flow, schema changes, snapshots, and failures. To enhance user experience with Snowflake Postgres, a complete reinvention of the replication process was necessary. A new feature called data mirroring is in public preview, allowing resilient data replication into Snowflake with low cost, minimal lag, and transactional consistency. This feature directly pushes changes from Postgres into Apache Iceberg™ tables in transactional batches, which are then automatically applied to Snowflake tables without extra infrastructure. Change data capture records changes from a transactional database for replay on another system, primarily using logical decoding in Postgres. This method decodes WAL records into logical operations, but the client bears the responsibility for subsequent steps like backfilling, managing schema changes, and handling failures. Built-in logical replication in Postgres only addresses some complexities. A limitation of logical decoding is that the external system does not recognize the state of Postgres, making it difficult to detect schema changes or correlate snapshots with changes. The solution is to push changes from Postgres into a data lake, specifically into Iceberg tables using compressed Parquet format, with object stores like Amazon S3 as the destination. The mirroring process uses a new Postgres extension called snowflake_cdc, which continuously pushes batches of changes into per-table change logs and a meta log. This extension understands the Postgres environment, coordinating schema changes and complex transactions while ensuring alignment between snapshots and changes.

Tech Optimizer

July 23, 2026

Optimizing PostgreSQL on Azure directly in Visual Studio Code

Poor database performance can lead to missed SLAs, delayed releases, customer dissatisfaction, and lost revenue. Microsoft has enhanced PostgreSQL on Azure, transforming it into a fully managed platform that meets enterprise demands. Azure Database for PostgreSQL and the new Azure HorizonDB offer significant performance improvements, with HorizonDB being three times faster than self-managed PostgreSQL. The PostgreSQL extension for Visual Studio Code integrates performance management into development workflows, providing tools for managing PostgreSQL throughout its lifecycle. Key features include a server metrics dashboard that displays performance indicators, Azure Advisor recommendations for actionable insights, improved query plan visualization, and AI-assisted query analysis. The extension also enhances schema design and query formulation experiences, ensuring secure and governed interactions with PostgreSQL. Azure HorizonDB is in public preview, designed for AI-native workloads. These advancements aim to reduce friction, enhance clarity, and enable faster actions for enterprise teams managing PostgreSQL at scale.