Best of PostgreSQL — July 2024

1. 1
   Article

   Community Picks·2y

   Don't query your PostgreSQL db anymore, prefer PostgREST

   The post discusses the benefits of using PostgREST to replace traditional SQL queries in PostgreSQL databases. It outlines the process of setting up PostgREST, a standalone web server that converts PostgreSQL databases into RESTful APIs, simplifying database interactions. The author shares their experience migrating from MySQL to PostgreSQL and how PostgREST helped streamline their PHP code by eliminating direct SQL queries. The post includes a tutorial on setting up and using PostgREST with Docker, illustrating various query examples and security considerations.

   272

   20
2. 2
   Article

   ByteByteGo·2y

   EP119: What do Amazon, Netflix, and Uber have in common?

   Amazon, Netflix, and Uber excel at scaling their systems using strategies such as stateless services, horizontal scaling, load balancing, auto-scaling, caching, database replication, sharding, and asynchronous processing. Figma achieved 100X Postgres scaling by implementing vertical and horizontal partitioning, and using tools like PgBouncer and a custom DBProxy service. Additionally, the post highlights the importance of robust testing techniques like unit, integration, system, load, and error testing to ensure software functionality and performance.

   269

   7
3. 3
   Article

   Community Picks·2y

   Can Postgres replace Redis as a cache?

   The post discusses the possibility of using Postgres as a cache instead of Redis, highlighting that while Postgres can simplify the technology stack and reduce costs, it lacks the performance and specialized features of Redis. Unlogged tables in Postgres improve write performance but not read performance. Moreover, Redis offers built-in features like expiration and eviction policies that are complex to implement in Postgres. Ultimately, Redis is recommended for its superior performance and simplicity in use as a caching solution.

   121

   2
4. 4
   Article

   Community Picks·2y

   Building a RAG application with Llama 3.1 and pgvector

   Tech giants are in an AI arms race, with OpenAI's closed models competing against Meta's open-source Llama 3.1. The post demonstrates how to build a Retrieval-Augmented Generation (RAG) application using Llama 3.1 and pgvector, leveraging Postgres for vector databases. It covers creating embeddings, setting up a vector database with Neon, and deploying an AI app that provides inspirational quotes, highlighting the power and cost-effectiveness of open-source AI models.

   106

   2
5. 5
   Article

   Machine Learning News·2y

   Korvus: An All-in-One Open-Source RAG (Retrieval-Augmented Generation) Pipeline Built for Postgres

   Korvus aims to simplify the Retrieval-Augmented Generation (RAG) pipeline by executing the entire process within a Postgres database using PostgresML. This approach eliminates the need for multiple external tools, reduces development complexity, and improves efficiency by leveraging in-database machine learning for tasks like embedding generation and data retrieval. Korvus supports multiple programming languages, facilitating easier integration and maintenance of search applications, although its performance metrics are yet to be quantified.

   89
6. 6
   Article

   Supabase·2y

   Postgres Realtime location sharing with MapLibre

   Learn how to build a Telegram Bot to capture live location data and use a Supabase Edge Function to insert it into a Postgres database. The tutorial covers using Supabase Realtime to broadcast database changes and integrating MapLibre GL JS in React to visualize the live data on a map. Key topics include creating Edge Functions, using RPC for database operations, and setting up Realtime subscriptions in React.

   85
7. 7
   Video

   Jack Herrington·2y

   Payload: The Complete Backend for NextJS

   Build a movie voting application using Payload 3.0, an open-source backend framework that pairs seamlessly with NextJS. The app includes a detailed admin panel without writing code, supports Postgres and MongoDB, and showcases easy deployment. The tutorial covers creating collections, setting up a database, configuring environment variables, building the front-end with Tailwind CSS, adding server actions, and deploying via Vercel.

   84

   2
8. 8
   Article

   System Design Codex·2y

   How Reddit Serves 100K Metadata Requests Per Second

   Reddit faced challenges handling scattered metadata across multiple systems. To address this, they built a unified media metadata store using AWS Aurora Postgres. This solution supports over 100K read requests per second with low latency. The setup included dual writes, data backfill, and robust data validation using Kafka for Change Data Capture (CDC). They also implemented range-based partitioning to ensure performance and scalability, enabling Reddit to handle expected volume growth efficiently.

   71

   2
9. 9
   Article

   Community Picks·2y

   Building an AI-powered ChatBot using Vercel, OpenAI, and Postgres

   This guide provides step-by-step instructions on building an AI-powered chatbot using Vercel, OpenAI, and Postgres. It explains the fundamentals and benefits of word embeddings, the use of the pgvector extension in Postgres for similarity analysis, and how to create and fine-tune a chatbot API using these technologies. Additionally, it covers optimization techniques to enhance model performance and reduce costs.

   69
10. 10
    Article

    freeCodeCamp·2y

    Full Stack Development with Next.js, Clerk, and Neon Postgres

    This tutorial provides a comprehensive guide on building full-stack applications using Next.js, Clerk, and Neon, a serverless PostgreSQL database. It covers setting up Neon, integrating it with Next.js, managing the database with Drizzle ORM, implementing user authentication with Clerk, and performing CRUD operations. The tutorial also highlights Neon's unique features like serverless architecture, auto-scaling, and instant branching.

    69
11. 11
    Article

    Hacker News·2y

    The Great Database Migration

    Shepherd successfully migrated its pricing engine database from SQLite to Postgres with zero downtime. The new architecture improves scalability, performance, and developer experience. The migration included converting synchronous functions to asynchronous, leveraging a serverless architecture with Neon, and automating ETL processes. The project highlighted performance optimizations, including caching strategies and connection pooling, resulting in significantly improved response times.

    56
12. 12
    Article

    Community Picks·2y

    Build a RAG chatbot with Astro, Postgres, and LlamaIndex

    A comprehensive guide to building a RAG chatbot using Astro, Postgres, and LlamaIndex. It covers generating OpenAI API tokens, creating and deploying an Astro app, setting up a serverless Postgres database, and configuring CI/CD with GitHub Actions. Key steps include Dockerizing the app, setting up React for the UI, adding Tailwind CSS, and deploying on Amazon ECS.

    49
13. 13
    Article

    Community Picks·2y

    How to copy large Postgres databases in seconds

    Neon introduces a revolutionary way to copy large Postgres databases instantly using database branching. This method uses a copy-on-write technique, making it both fast and resource-efficient. Traditional methods like physical file copies, logical backups, and logical replication are less efficient for large databases. With Neon branching, creating and managing separate database instances for development, testing, and migrations becomes much easier without significant additional storage requirements.

    44
14. 14
    Article

    Community Picks·2y

    Using Strapi CMS with Neon Postgres and Astro to build a blog

    This guide provides a detailed walkthrough on creating a blog using Strapi CMS, a serverless Postgres database powered by Neon, and Astro. It covers setting up the database, configuring Strapi, defining a blog schema, managing API permissions, integrating Tailwind CSS, and creating dynamic blog routes in Astro. The guide also highlights Neon's scale-to-zero feature for efficient resource management.

    44
15. 15
    Article

    Community Picks·2y

    Dealing with Race Conditions: A Practical Example

    The post describes a practical example of dealing with race conditions in an application managing on-call shifts for doctors. It explains how naive API implementations can lead to race conditions and demonstrates two PostgreSQL-based solutions—serializable transaction isolation and advisory locks—to handle these issues. The article includes SQL snippets and code examples for implementing these solutions and discusses the importance of addressing race conditions in various real-life scenarios.

    43
16. 16
    Article

    Hacker News·2y

    PostgreSQL and UUID as primary key

    UUIDs are often used as primary keys in databases due to their uniqueness and ease of generation. While not always the optimal choice due to size concerns, PostgreSQL offers a dedicated UUID type that is more efficient than storing UUIDs as text. Experiments show that using the `uuid` type significantly reduces table and index size compared to `text`. Furthermore, UUID v7, which generates time-sorted values, improves insert performance over the more common UUID v4. These optimizations are crucial for large datasets and high-traffic applications.

    40
17. 17
    Article

    Community Picks·2y

    Real-Time Notifications using pg_notify with Neon Postgres

    Learn how to implement real-time notifications in PostgreSQL using pg_notify and Neon. This guide covers provisioning a Neon-powered PostgreSQL database, creating a Node.js application, setting up event triggers, listening for notifications, and sending notifications using triggers. Follow the detailed steps to build a webhook-like system that instantly notifies users on specific database operations.

    40

    1
18. 18
    Article

    Community Picks·2y

    Run your own analytics with Umami, Fly.io and Neon

    Learn how to self-host Umami analytics using Fly.io and Neon serverless Postgres. This guide walks you through the prerequisites, setting up Umami with Fly.io, provisioning a Postgres database with Neon, and configuring it for deployment. Follow these steps to ensure your analytics data is private and cost-effective.

    35

    3
19. 19
    Article

    Hacker News·2y

    Mongo but on Postgres and with strong consistency benefits

    Pongo allows you to use MongoDB-like syntax on a PostgreSQL backend, taking advantage of the strong consistency and advanced querying capabilities of PostgreSQL. It leverages the JSONB format for improved performance and storage efficiency. Pongo is installed via npm and can be used with explicit typing or a MongoDB-compliant shim, translating MongoDB operations into native PostgreSQL queries.

    34

    2
20. 20
    Article

    Community Picks·2y

    HTTP vs. WebSockets: Which protocol for your Postgres queries at the Edge

    SQL-over-HTTP has been added to the Neon driver, which previously only supported WebSockets, for querying Postgres databases closer to end-users. WebSockets excel in maintaining consistent connections, offering low latencies for sustained queries but perform slower for single-shot queries. By contrast, HTTP is quicker for single-shot queries but lacks features like session support and interactive transactions. To strike a balance, connection caching was introduced, speeding up HTTP queries by around 10ms. The choice between HTTP and WebSockets depends on query type, user location, and specific APIs used.

    33
21. 21
    Article

    Community Picks·2y

    dbcli/pgcli: Postgres CLI with autocompletion and syntax highlighting

    pgcli is a command-line interface for PostgreSQL that offers features like auto-completion and syntax highlighting. It supports installation via pip, apt-get (for Debian-based systems), and Homebrew (for macOS). It can also be used within Docker and IPython consoles. Configuration options are extensive and can be set through environment variables or configuration files. Pgcli is built on Python's Prompt Toolkit and supports many of the same environment variables as psql.

    28
22. 22
    Article

    Trunk.io·2y

    How it feels to keep it simple stupid

    The author discusses their shift from an over-engineered project with multiple microservices to a simpler setup using Next.js for the frontend, a monolith backend, and a single PostgreSQL database within a monorepo. They highlight improved development experience with tRPC and Drizzle, and outline current challenges like teaching trunk-based development and optimizing CI processes.

    26

    7
23. 23
    Article

    Collections·2y

    Building an E-commerce Store in Next.js with Payload CMS and Neon Postgres

    This guide details the process of building an e-commerce store with Next.js, Payload CMS, and Neon serverless Postgres. It covers setting up a Neon Postgres database, initializing a Next.js project, creating a Payload CMS application, implementing server actions, and testing the store locally. The setup leverages Neon's scale-to-zero feature to minimize compute costs and ensures secure handling of user inputs.

    25

    2
24. 24
    Article

    Community Picks·2y

    When LIMIT 9 works but LIMIT 10 hangs

    A debugging story details an issue with a PostgreSQL query that hangs when using LIMIT 10 but works with LIMIT 9. The problem is linked to WebSocket payload lengths and an underlying bug in the undici package's handling of WebSocket frames in Node.js. The story highlights the nuances of WebSocket framing, the importance of precise buffer management, and how a small code fix resolved the problem.

    24
25. 25
    Article

    Community Picks·2y

    Autoscaling in Action: Postgres Load Testing with pgbench

    Learn how to use pgbench to conduct a load test on a Postgres database to demonstrate autoscaling in action using the Neon platform. The load test includes simulating 30 clients running a high computational overhead query, which triggers dynamic resource allocation through autoscaling. Key steps, such as enabling autoscaling and monitoring performance metrics, are highlighted. Pgbench and EXPLAIN ANALYZE are used to understand the performance and execution plan of the query.

    24