Best of PostgreSQL — June 2025

  1. 1
    Article
    Avatar of programmingdigestProgramming Digest·49w

    How Databases Store Your Tables on Disk

    Databases store tables using a hierarchical structure of pages and heap files. Data is organized in fixed-size pages (typically 8KB-16KB) that contain multiple rows, with databases reading entire pages rather than individual rows for efficiency. Tables are stored in heap files where pages can be unordered, making full table scans expensive. Indexes solve this by creating separate B-tree structures that point to specific pages in the heap, dramatically reducing I/O operations. Clustered indexes physically organize table data based on the index key, with databases like MySQL's InnoDB storing all tables as clustered indexes by default. The choice of primary key affects performance significantly, as random values like UUIDs can cause page splits and fragmentation.

  2. 2
    Video
    Avatar of asaprogrammerAs a Programmer·48w

    Build a Full Stack Mobile App with React Native & Expo - React Native Tutorial

    A comprehensive tutorial covering the development of a full-stack recipe search mobile app using React Native and Expo for the frontend, with a Node.js/Express backend connected to a PostgreSQL database. The tutorial walks through building authentication with Clerk, implementing CRUD operations with Drizzle ORM, deploying the API to Render, and setting up cron jobs to prevent server inactivity. The mobile app features user authentication, recipe search, favorites functionality, and multiple color themes, demonstrating cross-platform development principles and best practices for production deployment.

  3. 3
    Article
    Avatar of supabaseSupabase·47w

    Build a Personalized AI Assistant with Postgres

    A comprehensive guide to building a personalized AI assistant using PostgreSQL as the backbone for long-term memory and data management. The system combines LLMs with a scoped database schema, scheduled tasks via pg_cron, vector search using pgvector, and external integrations through Zapier MCP. Key features include three-layer memory architecture (message history, semantic search, structured data), autonomous scheduling capabilities, and secure database access controls. The tutorial covers practical use cases like run tracking, meal planning, and feedback analysis, with complete implementation steps using Supabase, OpenAI, and Telegram. Total monthly operating costs are estimated at around $0.54 for moderate usage.

  4. 4
    Article
    Avatar of habrhabr·48w

    What May Surprise You About UUIDv7

    UUIDv7 is a 128-bit identifier inspired by ULID that combines timestamps with random data, offering superior performance and functionality compared to traditional auto-increment IDs and other identifier types. Key advantages include cryptographically secure generation, monotonicity guarantees, equivalent performance to bigint while eliminating key collision issues, and the ability to hide creation timestamps through offset options. The format supports various implementation strategies across databases like PostgreSQL and ClickHouse, with optimal storage achieved through binary format rather than string representation.

  5. 5
    Article
    Avatar of supabaseSupabase·49w

    Announcing Multigres: Vitess for Postgres

    Supabase announces Multigres, a new database clustering system for PostgreSQL inspired by Vitess. The project is led by Sugu, co-creator of Vitess, and aims to provide sharding, connection pooling, query routing, and high availability for Postgres databases. Multigres will be open source under Apache 2 license and focuses on compatibility with the Postgres ecosystem while providing a gradual scaling path from simple connection pooling to petabyte-scale sharded solutions.

  6. 6
    Video
    Avatar of wdsWeb Dev Simplified·48w

    Build an AI-Powered Job Board From Scratch with Next.js 15, Drizzle, Tailwind

    A comprehensive tutorial covering the development of a full-stack AI-powered job board application using Next.js 15, Drizzle ORM, and Tailwind CSS. The project includes user authentication with Clerk, subscription billing, file uploads for resumes, AI-powered job matching and candidate rating, email notifications, and a complete database schema with PostgreSQL. The tutorial demonstrates advanced features like background job processing with Ingest, responsive design with mobile support, and proper TypeScript implementation with environment variable validation using Zod.

  7. 7
    Article
    Avatar of appsignalAppSignal·49w

    Using SQL in Node.js with Sequelize

    A comprehensive guide to integrating Sequelize ORM with Node.js applications for PostgreSQL database operations. Covers setting up database connections, defining models, and implementing complete CRUD functionality through a practical blog post management system. Includes Docker setup for PostgreSQL, model synchronization, pagination considerations, and best practices for production environments.

  8. 8
    Article
    Avatar of testdrivenTestDriven.io·47w

    Building a Multi-tenant App with Django

    A comprehensive guide to implementing multi-tenant architecture in Django using django-tenants and django-tenant-users packages. Covers three multi-tenancy approaches (isolated, semi-isolated, shared), with focus on the semi-isolated approach using PostgreSQL schemas. Includes practical implementation steps: setting up tenant models, configuring middleware, separating shared and tenant-specific apps, provisioning tenants, and managing user permissions across tenants. The tutorial transforms a single-tenant project management app into a multi-tenant SaaS application with isolated tenant data and shared public content.

  9. 9
    Article
    Avatar of communityCommunity Picks·48w

    Database Schema Design Patterns for Building Scalable E-commerce Applications

    Essential database schema design patterns for e-commerce applications using SQLAlchemy. Covers relational modeling patterns including one-to-many and many-to-many relationships, hierarchical data structures like adjacency lists and path enumeration, temporal data patterns for versioning and audit trails, embedding vs referencing strategies, and polymorphic associations. Emphasizes normalization principles, data integrity constraints, and designing schemas that support both current requirements and future evolution.

  10. 10
    Article
    Avatar of awegoAwesome Go·49w

    Best Database Migration Tools for Golang

    A comprehensive guide comparing five database migration tools for Go applications: Goose (lightweight and SQL-focused), Migrate (CLI-first with broad database support), Gormigrate (GORM-integrated), SQLx (custom migration workflows), and Flyway (enterprise-grade). Each tool is explained with practical code examples, use cases, and a comparison table to help developers choose based on project size, team needs, and database requirements. The guide includes best practices for versioning, testing, and implementing transactional migrations.

  11. 11
    Article
    Avatar of baeldungBaeldung·49w

    Event-Driven LISTEN/NOTIFY Support in Java using PostgreSQL

    PostgreSQL's LISTEN and NOTIFY commands enable asynchronous communication between database server and clients, creating a simple messaging system. The LISTEN command registers interest in receiving events on specific channels, while NOTIFY broadcasts messages to all listeners. Java applications can raise notifications using standard JDBC with either NOTIFY commands or pg_notify() function. Listening for notifications requires driver-specific functionality - the official PostgreSQL JDBC driver requires polling with getNotifications(), while PGJDBC-NG provides callback-based listeners for more efficient event handling. This mechanism supports real-time dashboards, cache invalidation, and data auditing use cases.

  12. 12
    Article
    Avatar of collectionsCollections·49w

    Introducing the PostgreSQL Extension for VS Code

    Microsoft released a comprehensive PostgreSQL extension for Visual Studio Code that brings database management capabilities directly into the IDE. The extension features schema visualization, natural language database queries through GitHub Copilot integration, migration script generation, SQL IntelliSense, local Docker server creation, connection management, and query history tracking. This tool aims to replace traditional database management applications by providing a unified development environment for PostgreSQL workflows.

  13. 13
    Video
    Avatar of dreamsofcodeDreams of Code·50w

    This is perhaps my favorite thing I've built with A.I. so far...

    A developer shares their experience building an AI-powered system to automatically convert video content into formatted blog posts. The solution uses Whisper CPP for local transcription, integrates with PostgreSQL via Neon's MCP (Model Context Protocol) server, and allows interactive refinement through Claude Desktop. The system saved approximately 120 hours of manual work by automating the conversion of 120 course lessons into written guides, while maintaining data safety through database branching.

  14. 14
    Article
    Avatar of p99confP99 Conf·50w

    Migrating from Postgres to ScyllaDB, with 349X Faster Query Processing

    Coralogix migrated their metadata store from PostgreSQL to ScyllaDB, achieving a 349x performance improvement by reducing query processing time from 30 seconds to 86 milliseconds. The migration involved redesigning data models for NoSQL, implementing bloom filter chunking strategies, and optimizing partition keys. Despite challenges with data modeling and EBS storage decisions, the team successfully deployed a 3-node ScyllaDB cluster handling 10K writes per second with sub-millisecond P99 latency, processing terabytes of Parquet metadata for their observability platform.

  15. 15
    Article
    Avatar of phProduct Hunt·50w

    SchemaFlow: Real-time database schemas for AI-IDEs via MCP protocol

    SchemaFlow is a tool that enables AI-powered IDEs to access PostgreSQL and Supabase database schemas in real-time through the Model Context Protocol. It offers live schema synchronization, interactive visualizations, and supports exporting schemas in multiple formats including JSON, Markdown, SQL, and Mermaid diagrams to enhance AI-driven development workflows.

  16. 16
    Article
    Avatar of neontechNeon·49w

    Comparing Native Postgres, ElasticSearch, and pg_search for Full-Text Search

    PostgreSQL's native full-text search works for small datasets but struggles with performance and search quality at scale. ElasticSearch provides superior search capabilities but introduces operational complexity with dual data stores and sync challenges. pg_search offers a middle ground by embedding a modern search engine (powered by Tantivy) directly into PostgreSQL, delivering BM25 ranking, fuzzy matching, and real-time indexing without the operational overhead of managing separate systems.

  17. 17
    Article
    Avatar of dbconvertDBconvert·47w

    MySQL ↔ PostgreSQL Schema Conversion: The Real-World Guide

    A comprehensive guide covering bidirectional schema conversion between MySQL and PostgreSQL databases. Details complete type mapping tables for both directions, handling of constraints, foreign keys, comments, and edge cases like zero dates and identifier length limits. Includes comparison with other migration tools and covers advanced features like deferred foreign key creation and universal constraint handling across different database environments.

  18. 18
    Article
    Avatar of communityCommunity Picks·48w

    This Open-Source Project Turns Text Into Instant AI Answers

    CocoIndex is an open-source project that enables building AI-powered search tools using text embeddings and PostgreSQL. The tutorial demonstrates creating an indexing flow that reads text files, chunks documents, generates embeddings using SentenceTransformer models, and stores them in a vector database. The system supports natural language queries through cosine similarity search, providing a minimalistic approach to semantic search with full data lineage tracking.

See all PostgreSQL archives