Best of PostgreSQL — November 2024

1. 1
   Article

   Community Picks·2y

   7+ Free PostgreSQL Hosting Platforms for Developers in 2024

   Several cloud providers offer free tiers for PostgreSQL hosting, ideal for developers working on small projects, personal experiments, or startup MVPs. Notable platforms include Neon, Supabase, EdgeDB, Xata, Tembo, Vercel Postgres, Koyeb, Aiven, and Nhost. These options provide scalable, fully managed databases with features like autoscaling, database branching, and integrated authentication, allowing developers to focus on development without worrying about infrastructure costs.

   138

   4
2. 2
   Article

   Neon·1y

   WTF Are JWTs?

   JSON Web Tokens (JWTs) are compact, self-contained tokens used for transmitting information securely. They emerged as a solution to the limitations of session-based authentication, offering stateless authentication, cross-domain compatibility, scalability, and security. JWTs are commonly used in modern web applications, especially with services like Neon Authorize, to manage authorization at the database level through row-level security. The process includes signing the tokens to ensure authenticity, though they are not encrypted by default.

   103

   2
3. 3
   Article

   Community Picks·2y

   PostgreSQL Performance Optimization Techniques Quiz

   Test your skills and enhance your knowledge of PostgreSQL performance tuning through this quiz. It covers crucial topics like optimizing queries, selecting appropriate indexing strategies, configuring parameters, and improving efficiency for high-performance workloads. Suitable for both aspiring database specialists and experienced DBAs.

   83

   1
4. 4
   Article

   Quastor Daily·2y

   How Reddit built a Metadata Store that Handles 100k Reads per Second

   Reddit developed a media metadata store that handles 100k reads per second to accommodate their rapidly growing user base. They opted for Sharded Postgres over Cassandra due to easier management and fewer denormalization issues. The migration involved dual writes, backfill data, and dual reads. Their scaling strategies include table partitioning using the pg_partman extension and data denormalization to optimize query efficiency.

   49

   1
5. 5
   Article

   Deno·2y

   Build a Database App with Drizzle ORM and Deno

   This tutorial guides you through setting up Drizzle ORM with Deno and PostgreSQL to manage dinosaur data. It covers installing dependencies, configuring Drizzle ORM, defining schemas, and interacting with the database using helper functions. The post concludes with suggestions for enhancing your application with complex database relationships, REST API implementation, validation, and error handling.

   46

   2
6. 6
   Article

   ByteByteGo·2y

   Storing 200 Billion Entities: Notion’s Data Lake Project

   Notion experienced a 10-fold increase in data growth since 2021, reaching over 200 billion blocks stored in their Postgres database by 2024. This exponential growth led to the development of a new data lake infrastructure to manage the heavy load and improve scalability, performance, and cost-efficiency. The new setup includes using S3 for storage, Kafka for data ingestion, and Apache Hudi for managing updates. This overhaul has resulted in significant cost savings, reduced ingestion times, and enabled new features such as Notion AI.

   33
7. 7
   Article

   Dev Squad·1y

   🚀 Automate Your PostgreSQL Backups with Ease! 🐳

   Simplify database management with a robust system for backup and restore using Deno, Docker, and Cloudflare R2. Features include automated backups via cron or on-demand, seamless restores from local files or R2, and easy cloud integration. To get started, clone the appropriate repo, configure your environment file, and run the specified Deno tasks.

   32
8. 8
   Video

   Deno·1y

   Build a Database App in 3 Minutes

   Building a database application with Deno 2 and Drizzle ORM has been made easier with npm compatibility. The post guides you through installing necessary dependencies, setting up configurations, and defining schemas for a PostgreSQL database. It covers both inferring existing tables and creating new ones, and explains how to use Drizzle to manage data with TypeScript types. The tutorial concludes with performing CRUD operations on the database.

   31
9. 9
   Article

   Lobsters·2y

   What I Wish Someone Told Me About Postgres

   Learn about essential tips and practices for working with Postgres that aren't always clear from the documentation. Covers topics like data normalization, useful psql shortcuts, handling NULLs, the importance of indexing, and managing locks. Also addresses the use of JSONB for flexible data storage and the potential pitfalls of long-running transactions.

   27

   1
10. 10
    Article

    Hacker News·2y

    Stream, transform, and route PostgreSQL data in real-time.

    Stream data changes in near real-time using PostgreSQL Logical Replication with pg_flo. Perform parallelizable bulk copies for fast initial synchronization, apply regex-based transformations to mask sensitive data, and route data between differently named tables or the same table with custom column mappings. Deploy easily using Docker, and leverage pg_flo for secure production to staging synchronization, and data archiving and analytics.

    25

    1
11. 11
    Article

    Community Picks·2y

    Prisma + Postgres

    This guide provides step-by-step instructions on how to set up Prisma with Postgres using Supabase. It covers prerequisites, setting up a new Supabase project, configuring Prisma in a Node.js project, defining data models, and using Prisma Client to interact with the database. The tutorial uses real-world examples involving books and authors to illustrate the process.

    22
12. 12
    Article

    Quastor Daily·2y

    How Reddit built a Metadata Store that Handles 100k Reads per Second

    Reddit built a scalable metadata store to handle over 100k reads per second, choosing Postgres over Cassandra due to manageability and flexibility challenges with the latter. They implemented dual writes, backfill data, dual reads, and other strategies for data migration. To achieve high performance, they utilized table partitioning and denormalization. Reddit's current setup on AWS Aurora Postgres delivers low latency without the need for a read-through cache.

    21
13. 13
    Article

    Data Engineer Things·1y

    Change Data Capture (CDC): Comprehensive Guide-PostgreSQL To S3(MinIO) Using NiFi

    Change Data Capture (CDC) technology is essential for real-time database updates, ensuring data integrity and quick access. This guide provides a step-by-step approach to using CDC with PostgreSQL, Debezium, Apache NiFi, and storing data in MinIO. The process involves setting up Docker Compose, configuring Debezium to monitor PostgreSQL, using Kafka as a message broker, and employing NiFi for data flow management to transfer data to MinIO for real-time analysis.

    19
14. 14
    Article

    Hacker News·1y

    frectonz/pglite-fusion: Embed an SQLite database in your PostgreSQL table. AKA multitenancy has been solved.

    Introducing pglite-fusion, a PostgreSQL extension that allows embedding SQLite databases within PostgreSQL tables, providing a solution for multitenancy. The post demonstrates setup via Docker, SQL queries for creating and manipulating tables storing SQLite databases, and functions for initializing, updating, and querying SQLite data within PostgreSQL.

    19
15. 15
    Article

    DEV·2y

    AI-Driven Content Retrieval with PostgreSQL, Ollama and Open-Source models

    The platform utilizes advanced semantic search algorithms such as Ollama LLM, Langchain, and LangGraph to analyze and retrieve relevant data from various content types, including images, YouTube links, and PDF files. It performs a semantic search using pgvector, summarizes video metadata through the YouTube API, and generates images using the Stable Diffusion algorithm. The system stores generated images securely and maintains chat history, enhancing user experience with robust, multimedia processing and seamless interaction continuity.

    19
16. 16
    Article

    Community Picks·2y

    The future of Postgres?

    The future of Postgres lies significantly in its extensions, which allow users to modify and extend standard behavior. Notable extensions include pg_stat_statements for query logging, auto_explain for performance debugging, Citus for sharding and distribution, and pg_search for full-text search capabilities. These extensions enhance the functionality, scalability, and performance of Postgres, offering solutions for various needs from scheduled jobs with pg_cron to efficient file handling with pg_parquet.

    17

    1
17. 17
    Article

    Hacker News·2y

    BemiHQ/BemiDB: Postgres read replica optimized for analytics

    BemiDB is a Postgres read replica optimized for analytics, featuring a single binary that replicates data in a compressed columnar format. It boasts up to 2000x faster query performance than Postgres, supports seamless integration with Postgres-compatible tools, and stores data either locally or on S3. BemiDB utilizes the DuckDB query engine and the Iceberg table format for efficient data handling and is available as an open-source project under the AGPL-3.0 License.

    15
18. 18
    Article

    Crunchy Data·2y

    8 Steps in Writing Analytical SQL Queries

    Writing complex SQL queries involves starting with simple queries and progressively adding complexity while verifying accuracy at each step. Key steps include defining desired data, investigating and sampling data, confirming simplicity, adding joins cautiously, performing summations, and rigorously debugging. SQL's power lies in its ability to utilize simple, standardized logic blocks to extract accurate data from complex structures.

    14
19. 19
    Article

    neo4j·2y

    🐘 Relational Database-to-Graph Querying in Less Than 5 Minutes

    Learn how to import data from a PostgreSQL database to a Neo4j graph database using the updated import capabilities in Aura. The post guides you through setting up a PostgreSQL data source, generating a graph model, and running the import job. It also provides examples of queries to explore the imported data, using the Chinook music dataset as a case study for practical application.

    13
20. 20
    Article

    Community Picks·1y

    Testing with Go and PostgreSQL: ephemeral DBs

    Using ephemeral PostgreSQL instances can simplify testing for Go programs by avoiding configuration and setup problems. This method ensures that tests start with an empty database, making them reliable and environment-independent. By utilizing packages like `postgrestest`, developers can streamline the setup process, optimize test runtime, and reduce system resource usage.

    12

    2
21. 21
    Video

    Community Picks·2y

    SQLite vs PostgreSQL

    The post compares SQLite and PostgreSQL regarding their performance in various operations such as inserts, updates, deletes, and joins. It highlights the differences in architecture, with PostgreSQL being a traditional client-server database and SQLite functioning as a library integrated into the application. Tests are conducted to measure latency, throughput, CPU usage, and disk write operations, emphasizing how network interactions impact PostgreSQL's performance compared to SQLite's local file interactions.

    12
22. 22
    Article

    Hacker News·2y

    Dr-Nekoma/lyceum: An MMO game written in Erlang (+ PostgreSQL) + Zig (+ Raylib)

    Lyceum is an MMO game with its server developed in Erlang and the client in Zig, enhanced with raylib and Zerl. The project utilizes Nix for development and deployment, including PostgreSQL initialization and server-client setup. Comprehensive instructions are provided for local development, database management, and building server images. The game's narrative features a blend of mythic lore and strategic challenges, urging players to restore balance in a world plagued by internal decay and external threats.

    12