February 2024 Summaries

Postgres Support Recap: Investigating Postgres Query Performance` is a comprehensive guide to troubleshooting Postgres query performance issues. The article outlines three strategies for investigating query performance problems: analyzing query performance with `pg_stat_statements`, checking cache hit ratios, and identifying table or index bloat. It also introduces the free and open-source monitoring tool `PgHero`, which provides a graphical interface for monitoring Postgres query performance. By following these strategies, developers can identify and address common causes of poor query performance in their Postgres databases.

Neon Support frequently addresses Postgres query performance issues, which can arise from various factors such as missing indexes, lack of database maintenance, or system resource limitations. To tackle these problems, the Postgres Support Recap suggests three key strategies: using the pg_stat_statements extension to analyze query performance, checking cache hit ratios to ensure queries are efficiently served from memory, and monitoring for table or index bloat that could degrade performance. Pg_stat_statements helps users gather detailed query statistics, while adjusting cache settings can optimize memory usage. To identify and address bloat, commands like VACUUM and REINDEX are recommended, albeit with caution due to their potential impact on database operations. Additionally, the post introduces PgHero, a free open-source tool that offers a performance dashboard for Postgres, providing an alternative to command-line SQL queries for monitoring and analysis.

Apache Kafka is used with Postgres to create real-time materialized views that update based on database changes. This approach enables scalable and flexible architectures for change data capture and streaming database events. The guide assumes the use of Confluent Cloud to host Apache Kafka, Kafka Connect, and ksqlDB components. It covers creating a Debezium connector to stream Postgres database changes to an Apache Kafka cluster, processing those changes using ksqlDB to create materialized views that update in response to database changes, and consuming these updates from the materialized view. The approach avoids the dual-write problem when integrating with message brokers like Apache Kafka.

The article outlines a detailed process for creating a real-time materialized view from PostgreSQL database changes using Confluent's ksqlDB, leveraging Apache Kafka for data streaming. It highlights the advantages of using Kafka's ecosystem for change data capture, which allows for real-time processing and flexibility in handling large volumes of data. By streaming changes from a PostgreSQL database to Kafka and utilizing ksqlDB, users can maintain an up-to-date materialized view without the performance drawbacks of traditional methods. The guide provides a step-by-step approach, including setting up a Neon PostgreSQL database, configuring logical replication, and using Debezium connectors on Confluent Cloud to handle database changes. Additionally, it discusses integrating Kafka with ksqlDB to create streams and aggregate data for a leaderboard application, offering a scalable solution for applications requiring dynamic data updates.

Mistral Large is a cloud-native serverless Postgres solution that adapts to application loads, offering unparalleled reasoning capabilities across multiple languages. It has been deployed on Azure and is available for use with LangChain, allowing developers to create AI-powered applications that are more intelligent, interactive, and impactful. The model has been shown to rank high in benchmarks such as the MassiveText Benchmarks for Programming Problems (MBPP) and GSM8K, measuring its capabilities in educational contexts and reasoning in mathematics.

Neon is offering a cloud-native serverless Postgres solution designed to scale AI applications using pgvector, and it aims to enhance database experiences for RAG apps. Mistral AI has launched its advanced open-source large language model, Mistral Large, known for its reasoning skills across multiple languages and high performance in programming and mathematical benchmarks. This guide outlines the steps to deploy Mistral Large on Azure, facilitated by Mistral AI's partnership with Microsoft, enabling immediate use with LangChain. The deployment process involves setting up an Azure AI Studio project and accessing the model through an API endpoint. Mistral Large can be integrated into LangChain to create conversational AI applications, and developers are encouraged to utilize Neon's autoscaling capabilities to support scalable AI-driven applications.

In this article, the author demonstrates Neon autoscaling in action by running a load test using Postgres' popular benchmarking tool, pgbench. The test simulates 30 clients running a heavy query with high computational overhead to signal the autoscaler-agent to allocate more resources to the VM. The effectiveness and efficiency of autoscaling are often assumed without thorough testing, so this demonstration showcases its utility in real-time adjustment of resources allocated to a database based on its current workload. The author also explains how to enable autoscaling by defining the minimum and maximum compute units (CU) for a Postgres instance.

Neon demonstrates the effectiveness of its autoscaling feature for Postgres databases by conducting a load test with pgbench, a popular benchmarking tool. The test involves simulating 30 clients executing a computationally intensive SQL query to trigger autoscaling, which dynamically adjusts CPU and memory resources based on real-time workload demands. The query, designed to stress the database, calculates the logarithms of large factorials, resulting in significant planning time and computational overhead. By leveraging pgbench and the EXPLAIN ANALYZE command, the test provides insights into database performance under load, while autoscaling ensures the system can handle variable demands without manual intervention. The results indicate that while the initial connection time is substantial, the database manages nearly five transactions per second on average, showcasing the utility of autoscaling in maintaining performance during fluctuating workloads.

Neon, a serverless Postgres solution, enables Point-In-Time Restore (PITR) by separating storage and compute. PITR is made possible through Write-Ahead Logging (WAL), which records all changes to the database, and base backups, snapshots of the database at specific moments in time. Neon's storage engine consists of Safekeepers for data persistence, Pageservers for multi-tenant storage, and another persistence layer for WAL storage in AWS S3. This design allows Postgres to become a stateless compute instance that can start quickly, making Neon serverless. The Pageservers create image and delta layers to store snapshots of key ranges at specific LSNs, allowing developers to access different timelines through ephemeral branches for Time Travel queries.

OpusFlow, a leading ERP tool that simplifies sustainable energy installations, has switched from Amazon RDS to Neon for managing its Postgres databases. The company needed an architecture with one database per customer to ensure maximum data isolation and security. However, implementing this design in Amazon RDS was complex and costly. By using Neon's serverless Postgres, OpusFlow can now manage hundreds of isolated databases without the need for managing servers or overprovisioning resources, resulting in reduced costs and operational overhead.

Neon, a serverless Postgres solution, has introduced Point-In-Time Recovery (PITR) to enable developers to restore their databases to any previous state using Time Travel queries. This feature is facilitated by Neon’s unique architecture, which separates storage and compute, using a custom Rust-based storage engine that streams Write-Ahead Logging (WAL) records to a multi-tenant system. Neon's storage engine allows for the reconstruction of data at any point in history without the need for traditional base backups and lengthy restore processes. The system utilizes concepts like safekeepers and Pageservers, which store and manage WAL data in AWS S3, making Postgres instances stateless and capable of rapid recovery. Furthermore, Neon introduces ephemeral branches, which are temporary, copy-on-write clones of databases, allowing developers to easily access and review historical data states. By enhancing PITR with Time Travel Assist, Neon offers a seamless, efficient, and integrated approach to disaster recovery and database management, surpassing traditional methods that rely on tools like Barman.

OpusFlow transitioned from using Amazon RDS to Neon for managing its Postgres databases to achieve cost-effective and efficient tenant isolation, which is crucial for handling sensitive data for its European customers. Originally a specialized software agency, OpusFlow has evolved into a leading ERP tool for simplifying sustainable energy installations. The move to Neon, a serverless Postgres solution, allows OpusFlow to implement a single tenancy system where each customer has an isolated database within separate Neon projects. This setup reduces operational overhead and costs as each database can scale independently, and the serverless nature eliminates the need for managing servers. Neon’s branching capabilities facilitate efficient development and testing by allowing isolated environments for each feature or bug fix, mirroring OpusFlow's Git workflow. Additionally, OpusFlow leverages a serverless tech stack including TypeScript, Next.js, and AWS Lambda, aligning with their commitment to sustainability by turning off resources when not in use, thus minimizing waste and enhancing flexibility.

The Pageserver in Neon's system experienced a severe regression in cold start latency after making changes to improve stability. The root cause was found to be the inefficient closing of file descriptors as part of the defense-in-depth strategy against compromise of walredo processes. To fix this, the code switched from using the `close_fds` crate to manually issuing the `close_range` system call, which allowed for faster process creation and reduced latency. This highlights the importance of dynamic tracing at runtime and the need to carefully consider the trade-offs in process creation primitives.

In November, an engineering team faced a regression in cold start latency due to changes made to improve stability and tenant density in their Neon Pageserver system. The issue arose from a decision to shut down idle walredo processes to conserve DRAM capacity, which inadvertently increased latency for cold starts and first queries after idling. Initially, the team suspected inefficient process creation using fork and exec instead of posix_spawn as the root cause, due to the need for a defense-in-depth strategy to close file descriptors before executing untrusted code. Upon further investigation, it was discovered that the real latency culprit was the inefficiency in closing file descriptors, which was resolved by moving the file descriptor closing logic to the walredo subprocess. This change allowed the use of posix_spawn, significantly reducing latency by preventing the fallback to a slower method of closing file descriptors. The experience highlighted the importance of dynamic tracing and proper monitoring to identify and resolve performance issues effectively.

Supergood unlocked its Postgres developer productivity by leveraging Neon's database branching feature, which allows for the creation of development, staging, and testing environments in seconds with full security. This feature enabled the company to ship features faster, improve development velocity, and optimize performance for time series data. With Neon's branches, Supergood can spin up multiple read replicas, which are available immediately and only incur compute costs when running, further improving performance and reducing expenses. The use of Neon has transformed the company's experience with Postgres, making it feel like a different database altogether.

Neon's Point-in-Time Restore feature allows developers to restore their Postgres database to a previous state, giving them peace of mind when shipping products with minimal risk of unintended consequences. This feature uses Neon's unique architecture to retain the history of changes, including data DDL changes, for all database branches. With the new Branch Restore functionality, users can restore a branch from history with time-travel assistance, streamlining the previously manual process of performing point-in-time restore operations. The feature offers up to 30 days of retention with LSN-level granularity and takes just seconds to perform, minimizing RPO and RTO without implementing complex backup infrastructure.

Neon, a company aiming to make Postgres accessible to more developers, has expanded its partner program with two tiers: Standard Partners and Premier Partners. The program is designed for any developer platform interested in offering scalable databases without worrying about infrastructure. Neon's partners can seamlessly integrate the serverless Postgres into their platforms via the Neon API and leverage security enhancements like IP allow. The company offers two partner plans, Standard Partner and Premier Partner, with varying features and pricing options. Neon has grown to serve over 500K databases and is looking forward to working with more partners to bring serverless Postgres to every developer.

Supergood, a company focused on enhancing the management of third-party APIs, significantly improved its developer productivity by adopting Neon, a serverless Postgres database platform. Neon’s database branching feature allowed Supergood to expedite feature development and testing by providing immediate access to development, staging, and testing environments, eliminating the need for synthetic datasets, and maintaining security by using actual production data without leaving their VPC. This approach also facilitated resource isolation, reduced costs, and improved onboarding processes. Additionally, Neon's serverless architecture enabled Supergood to efficiently use read replicas, further optimizing performance without incurring high costs. CEO Alex Klarfeld credits Neon for the substantial boost in engineering velocity and encourages others to explore its benefits, emphasizing its superior capabilities compared to traditional Postgres setups.

Neon has introduced a Point-in-Time Restore (PITR) feature that allows users to revert their databases to a previous state with precision using its unique architecture, which separates storage and compute for Postgres. This feature, facilitated by the Branch Restore capability available in the Neon Console, employs time-travel assistance to streamline PITR operations and offers up to 30 days of data retention. Users can restore a database to a specific log sequence number (LSN) or timestamp, providing flexibility and minimizing data loss risks. While traditional Postgres tools like pg_dump and pgBaseBackup exist for backup and restoration, Neon's solution enhances simplicity and reduces Recovery Point Objective (RPO) and Recovery Time Objective (RTO) without the need for complex backup infrastructure.

Neon, a company focused on providing serverless Postgres solutions, is expanding its partner program to enhance its reach and support for developers. The program now includes two tiers: Standard Partners, which offer easy onboarding for developers looking to integrate serverless Postgres into their platforms, and Premier Partners, which provide custom features and dedicated resources for larger enterprises. Neon aims to simplify infrastructure management and offer scalable databases without requiring developer platforms to manage the backend. With over 500,000 databases served, Neon is favored for its features like database branching and autoscaling, and its API integration allows partners to generate revenue efficiently. Existing partners like Vercel and Replit have successfully integrated Neon's solutions, benefiting from features such as automatic scaling and enhanced security. New partners will have access to technical support and marketing efforts to expand their user base.

We created Neon with the goal of providing developers with a cloud-native Postgres that separates storage and compute to make the database more automated, scalable, and durable. After listening to user feedback from over half a million databases, we learned that predictable pricing is crucial for developers, particularly in terms of easy-to-understand billing and management. To address this, we simplified our pricing model by removing unnecessary complexity, introducing Launch and Scale plans with clearer usage metrics, and dropping written data and data transfer fees. These changes aim to provide more predictability and transparency for our customers, while maintaining a generous free tier to support project development until launch.

Neon, a company offering a cloud-native Postgres solution, has introduced new pricing plans called Launch and Scale to address developers' demand for predictable pricing and to simplify the billing process. After gaining insights from over half a million databases and user feedback, Neon recognized that the complexity of their previous pricing model, particularly regarding data transfer fees, led to customer confusion and unexpected costs. The new Launch plan caters to developers with initial or development workloads, while the Scale plan is designed for larger production environments, offering scalable compute and storage resources. To further ease the billing process, Neon has eliminated written data and data transfer fees, aiming to pass cost savings to customers. Additionally, Neon maintains a generous free tier to support projects until they are ready for launch and has incorporated community feedback to enhance free-tier offerings.

Neon now supports PgBouncer 1.22.0, which increases query throughput by up to 250% and includes support for DEALLOCATE ALL, DISCARD ALL, and protocol-level prepared statements. Prepared statements in Postgres allow you to create an SQL query once and execute it multiple times with different parameters, improving performance and security. PgBouncer is a lightweight connection pooler that helps manage and maintain a pool of database connections to overcome Postgres' connection limitations. Leveraging prepared statements can significantly boost your Postgres query performance and add a layer of security against potential SQL injection attacks when using pooled connections.

Neon has announced support for PgBouncer 1.22.0, a lightweight connection pooler that helps manage and maintain database connections to address Postgres' connection limitations. This release enhances query throughput significantly and includes support for protocol-level prepared statements. Prepared statements in Postgres allow SQL queries to be optimized for repeated executions, improving performance and security by reducing the risk of SQL injection attacks. Although the SQL-level prepared statements using PREPARE and EXECUTE aren't directly compatible with PgBouncer, client libraries can still utilize prepared statements with pooled connections. PgBouncer manages prepared statements by assigning internal names and rewriting commands before forwarding them to Postgres, which allows for efficient query execution. This integration enables users to leverage the benefits of prepared statements alongside PgBouncer's connection pooling capabilities, facilitating higher concurrency in modern applications.

Neon's support for PostgreSQL's Logical Replication enables development teams to leverage Event-Driven Architectures to create robust, real-time applications with Postgres at the core. Change Data Capture platforms and technologies like Debezium provide low-code open-source solutions to reliably stream changes from Postgres to messaging systems such as Redis for further processing and analysis. By using Debezium Server with Neon's serverless Postgres and Upstash Redis, teams can implement a fan-out pattern that enables loosely coupled components to process database events in real-time, avoiding the dual write problem associated with naive approaches to notification and update of downstream systems.

Modal, Neon, and Unkey are used to build and deploy an AI API that can transcribe and summarize text using the Whisper model from OpenAI. The process involves setting up a new FastAPI application with Modal context loaded, creating a transcription route, adding data persistence with Neon, and adding authentication with Unkey. Unkey provides API keys a service so that developers can safely open up access to their API in minutes. With Unkey, developers can add authentication to their APIs in just a few lines of code, making it easy to control access to the API and ensure secure deployment.

The guide provides a comprehensive walkthrough for developers on building and authenticating an AI API using OpenAI’s Whisper model, Modal for deployment, Neon for database management, and Unkey for security. It addresses key challenges such as secure API access using API keys, implementing rate limiting, gaining analytics insights, controlling access permissions, and minimizing latency. The tutorial emphasizes using Unkey to quickly establish a secure API, with Whisper handling speech recognition to transcribe and summarize audio files. Developers are guided through setting up a FastAPI application with Modal, adding data persistence with Neon’s serverless Postgres database, and incorporating authentication using Unkey. The integration of these technologies allows for a fully secured and functional API, with the potential for further development such as creating a web UI for user-friendly result display, exemplified by a Next.js implementation.

The blog post provides a comprehensive guide on implementing Change Data Capture (CDC) with Neon's serverless Postgres, Debezium, and Upstash Redis to facilitate event-driven architecture through a fan-out messaging pattern. It highlights the benefits of using CDC to monitor database changes in real-time and the challenges of the dual write problem, where changes need to be consistent across different systems. The article explains how Debezium consumes changes from Postgres' write-ahead log (WAL) and streams them to a message broker, ensuring at-least-once delivery semantics to avoid inconsistencies. The author details the steps to set up logical replication in Neon, configure Debezium as a data source, and use Upstash Redis as a data sink. Additionally, it offers instructions for deploying a Debezium Server container and demonstrates how to use a Node.js application to consume streamed data. The guide emphasizes the value of CDC platforms like Debezium in creating reliable, real-time applications by connecting Postgres with messaging systems for downstream processing.

In this tutorial, we will guide you through setting up a scalable serverless API using FastAPI, deployed on AWS App Runner and powered by Neon Postgres as the serverless database. FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.8+ based on standard Python type hints. The key features of FastAPI are its speed and ease of use, making it an excellent choice for building robust APIs. AWS App Runner is a fully managed service that makes it easy for developers to quickly deploy containerized web applications and APIs, at scale. These services will automatically scale the instances up or down for your App Runner application in accordance to incoming traffic volume. Neon complements this setup by providing a serverless Postgres database that scales compute resources automatically, optimizing performance based on demand.

The blog post outlines a comprehensive guide to deploying a serverless FastAPI application using Neon Postgres and AWS App Runner, providing a scalable architecture for Python-based APIs. It highlights the use of FastAPI for its speed and ease of use, AWS App Runner for managing scalable containerized applications, and Neon Postgres for its serverless database capabilities that automatically scale based on demand. The guide walks through the setup process, including setting up a local environment with Poetry, connecting the application to a Neon Postgres database, configuring AWS App Runner, and managing secure database connections via AWS Systems Manager Parameter Store. Additionally, it details the creation of necessary configuration files and instance roles to facilitate seamless integration and deployment, ensuring the application can efficiently handle varying traffic volumes while maintaining cost efficiency and optimal performance.

The Postgres vector search extension, pgvector, has improved its index build time by a factor of 30 with the introduction of parallel index builds for the Hierarchical Navigable Small World (HNSW) index in version 0.6.0. This feature is particularly useful for large datasets and applications that require efficient vector search capabilities, such as semantic search and Retrieval Augmented Generation (RAG). With Neon's serverless Postgres offering, developers can scale their database resources according to their needs, allocating more resources for index builds when necessary and scaling down to optimize costs. The parallel index build process has negligible impact on recall performance, making pgvector 0.6.0 a significant leap forward in the vector search space.

Neon is a serverless Postgres service that makes it easy to host scalable and fully-managed instances of Postgres, while Glide is a front-end tool that allows developers to quickly build and deploy progressive web apps without code. With Glide, users can access and manage Postgres data without needing to learn a complex tool or spend time writing code. Users can connect to multiple data sources in a single app, customize their layout using an interactive Layout Editor, add actions and workflows with branching and conditional logic, publish and share the app with a custom domain, and more.

Glide and Neon provide a seamless solution for building and deploying progressive web apps (PWAs) that allow non-technical users to manage Postgres data without requiring coding skills. Neon offers scalable, fully-managed Postgres instances, and when combined with Glide, it enables users to create custom interfaces and dashboards quickly. Glide allows users to connect to Neon-hosted databases, add tables with live two-way sync or run custom queries, and combine data from multiple sources like Excel or Google Sheets using computed columns. Users can customize their app layout with Glide's components, which facilitate data display and user interaction. These components can trigger actions and workflows, including integrations with AI and third-party services. Once the app is ready, it can be published and shared with automatic updates or manual publishing options, providing a user-friendly experience similar to native apps. This integration offers dev teams a way to deliver fully managed PWAs with familiar UIs to users while eliminating DevOps overhead.

Neon, a serverless Postgres provider, offers enhanced scalability for AI applications using the pgvector extension, which recently introduced a parallel index build feature. This feature significantly accelerates the creation of Hierarchical Navigable Small World (HNSW) indexes by a factor of 30, thanks to its ability to utilize multiple CPU resources. The pgvector extension is pivotal for vector similarity searches in semantic search and Retrieval Augmented Generation (RAG) applications. Despite the challenge of high memory requirements and lengthy build times for HNSW indexes, the new parallel index build functionality in pgvector 0.6.0 addresses these issues effectively, maintaining high recall rates with minimal impact on query accuracy. Neon's architecture, which separates storage and compute, allows users to dynamically allocate resources for index building and subsequently scale down to optimize costs. This flexibility makes Neon and pgvector an ideal combination for developers aiming to efficiently manage and scale database resources for AI applications.

This guide provides an overview of building a Django application that uses Neon's serverless Postgres, covering topics such as configuring a Python development environment, creating a Django project, connecting to Neon's serverless Postgres, managing models and database schemas, rendering data using views and templates, deploying and scaling the application with Gunicorn, and optimizing performance through persistent connections.

Topo.io, an AI-powered sales assistant company, switched from Amazon RDS to Neon due to its branching feature that allows for parallel development and testing without impacting the production environment. The team at Topo.io found it difficult to maintain consistency between their production and development environments while using RDS, leading to longer debugging times. With Neon's serverless Postgres, developers can create branches from the main database, providing them with isolated environments for feature building and testing. This streamlined process has improved the overall development experience at Topo.io, allowing them to iterate faster without compromising reliability.

Topo.io transitioned from Amazon RDS to Neon due to the latter's branching feature, which significantly enhanced their development workflow by allowing parallel work with up-to-date production data. Initially using Amazon RDS for its reliability, Topo.io faced challenges with managing multiple instances for separate development and production environments, leading to increased complexity and costs. Neon's serverless Postgres, with its branching capabilities, provided a solution by enabling isolated development environments that mirror production, facilitating more efficient testing and debugging. This transition allowed developers to work collaboratively without impacting the main database, and the integration with Prisma further streamlined schema migrations, offering the ability to revert changes easily. By centralizing their tech stack around TypeScript, Node.js, and Next.js, Topo.io leveraged Neon's features to maintain high engineering velocity and a reliable user experience in their AI-powered sales assistant platform.

Building a Django application with Neon's serverless Postgres allows for quick and efficient setup of development environments that incur compute fees only when active, suitable for both serverless and long-lived applications. The process involves creating a Django project, configuring it to connect to Neon's Postgres database, and using Python tools like pyenv and venv to manage environments. Essential steps include setting up a virtual environment, installing Django, and configuring the database connection using environment variables. The application development process includes creating data models, defining views, and rendering data as web pages, followed by deploying the application with Gunicorn for production scalability. Performance optimizations, such as enabling persistent database connections, can significantly enhance application throughput. Neon's compatibility with Django and its autoscaling capabilities make it a practical choice for developers seeking a flexible and efficient database solution, with the added support of community resources for collaborative problem-solving.