Best of PostgreSQL — September 2024

  1. 1
    Article
    Avatar of bytebytegoByteByteGo·2y

    EP128: The Ultimate Software Architect Knowledge Map

    Discover the ultimate knowledge map for software architects, exploring essential skills such as mastering programming languages, tools, design and architectural principles, platform knowledge, data analytics, networking, and supporting skills. Learn about the wide-ranging capabilities of PostgreSQL, from time-series data to federated querying and graph databases. Gain insights into advancing from a junior to a senior developer, covering collaboration tools, programming languages, API development, authentication, and system design.

  2. 2
    Article
    Avatar of communityCommunity Picks·2y

    The Ultimate Open Source Starter Kit

    The Ultimate Open Source Starter Kit provides a modern stack using Neon.tech for a serverless PostgreSQL database, Next.js, Lucia Auth, Bun, Vercel, ShadCN, Tailwind CSS, and Typescript. Designed for rapid SaaS deployment, the kit includes light/dark mode, and a blog feature with MDX for content management. It offers a robust and scalable foundation easily customizable for your specific needs.

  3. 3
    Article
    Avatar of communityCommunity Picks·2y

    PGlite - run Postgres everywhere

    PGlite is a WASM-based Postgres database that works in various JavaScript runtimes, including NodeJS, BUN, Deno, and browsers. It's lightweight, supports popular Postgres extensions, and is ideal for local-first applications, prototypes, local caches, and CI pipelines. The database can be easily installed via npm and used with regular SQL queries.

  4. 4
    Article
    Avatar of communityCommunity Picks·2y

    How I built my open-source Social media scheduling tool... 🤯

    Postiz, an open-source social media scheduling tool, leverages OAuth2 for authentication across different platforms. It uses Redis for queue management to schedule posts and Prisma ORM for database operations with PostgreSQL. The tool's architecture supports horizontal scaling with microservices called 'Workers' to handle post jobs efficiently.

  5. 5
    Article
    Avatar of planetscalePlanetScale·2y

    B-trees and database indexes

    B-trees and B+ trees are essential for efficient data lookups in database management systems like MySQL, Postgres, and MongoDB. They are structured to store key/value pairs in a way that optimizes search operations. MySQL's InnoDB engine relies heavily on B+ trees, where the choice of primary key significantly impacts performance. B-trees are well-suited for large data volumes that need persistent disk storage, and B+ trees offer advantages like storing all values at the leaf level and having linked lists for faster sequential access. Sequential keys generally improve performance over random or UUID keys.

  6. 6
    Article
    Avatar of postgresPostgreSQL·2y

    PostgreSQL 17 Released!

    PostgreSQL 17 has been released, offering significant performance boosts, including improved memory management, better I/O processing, enhanced query execution, and optimizations for high concurrency workloads. It introduces new developer features like the SQL/JSON `JSON_TABLE` command and enhancements in logical replication that streamline high availability management and major version upgrades. It also includes updates in partition management, foreign data wrappers, and built-in collation providers for consistent text-based queries across platforms.

  7. 7
    Video
    Avatar of youtubeYouTube·2y

    Coding a SAAS platform in 6 hours - NextJS, Tailwind, Prisma, Postgres

    This post outlines the process of developing a music SaaS platform using NextJS, Tailwind, Prisma, and PostgreSQL. It describes the motivations, design, and step-by-step coding practices. The application includes Google authentication with NextAuth, schema design with Prisma for users and streams, and deployment. Key elements of the design include user roles, stream types, and vote functionalities.

  8. 8
    Article
    Avatar of communityCommunity Picks·2y

    Creating a SaaS app with Vue and Nuxt

    Learn how to create a SaaS platform similar to LinkedIn using Vue, Nuxt, and Prisma. This guide walks you through setting up the project, scaffolding it with Nuxt, connecting to a PostgreSQL database via Vercel, and preparing the user schema.

  9. 9
    Article
    Avatar of communityCommunity Picks·2y

    Do You Really Need Redis? How to Get Away with Just PostgreSQL

    Redis is often used for job queuing, distributed locking, and pub/sub mechanisms in web services. However, PostgreSQL has features like `SKIP LOCKED`, advisory locks, and the `LISTEN/NOTIFY` statements that can achieve these tasks effectively. This can help simplify your architecture by using just PostgreSQL and reduce operational costs associated with maintaining multiple data services.

  10. 10
    Article
    Avatar of groctogroCTO·2y

    Why Next js failed for me...

    The author describes the struggles faced while working on a large-scale project using Next.js and Postgres. Major issues included slow rendering during development and challenges managing the full codebase with both backend and frontend developers. Additionally, setting up the CI/CD pipeline was difficult, and Next.js 14's lack of support for Socket.io due to server-side rendering (SSR) limitations posed problems. The author mitigated some issues by splitting the backend and frontend but still faces slow development rendering.

  11. 11
    Article
    Avatar of cybertec_postgresqlCYBERTEC PostgreSQL·2y

    Why "SELECT * FROM table" is NOT a read

    A SELECT operation might not always be a read-only action, which has significant implications for load balancing and replication strategies in databases. The application must be aware of whether it is performing read or write operations to correctly execute load balancing. Additionally, synchronous replication does not guarantee immediate visibility of data changes on replicas, highlighting the complexity in designing effective replication and load balancing solutions.

  12. 12
    Article
    Avatar of lnLaravel News·2y

    Prepare your Laravel app for the cloud

    Learn how to prepare a Laravel app for cloud deployment by mimicking the production environment using Docker. The guide covers setting up a local PostgreSQL database, configuring Dockerfiles, and deploying to the cloud provider Sevalla. Key steps include handling environment variables, creating Docker images, and setting up database migrations.

  13. 13
    Article
    Avatar of hnHacker News·2y

    Postgres webhooks with pgstream

    Set up and trigger webhooks in response to PostgreSQL data and schema changes using pgstream, a Change-Data-Capture (CDC) tool. The guide covers initial setup with Docker, webhook configuration, handling basic operations like inserts, updates, and deletes, as well as advanced features like sending old and new values during updates and managing schema changes. Detailed examples demonstrate how to automate workflows and trigger real-time notifications in your applications.

  14. 14
    Article
    Avatar of hnHacker News·2y

    pg-nano/pg-parser: Parse your Postgres queries into a 100% type-safe AST (powered by libpg_query)

    A fork of libpg-query, pg-nano/pg-parser offers advanced type definitions and AST utilities, ensuring type-safe parsing of PostgreSQL queries. The package includes functions for parsing both SQL and plpgsql, generating unique fingerprints for SQL strings, and traversing ASTs. It also provides tools for deep field access with dot-notation. The project aligns its versions with PostgreSQL and provides pre-compiled binaries from GitHub Releases. Windows support is currently incomplete.

  15. 15
    Article
    Avatar of infoworldInfoWorld·2y

    Using PostgreSQL as a vector database in RAG

    Learn how to build a local retrieval-augmented generation (RAG) application using PostgreSQL with the pgvector extension, Ollama, and the Llama 3 large language model. This guide describes how Postgres can store both vector and tabular data, making it a versatile option for medium-sized RAG applications. It covers setting up a vector database, ingesting text from multiple sources, conducting similarity searches, and querying a large language model to generate answers. Practical coding examples and step-by-step instructions are provided to help developers get started quickly.

  16. 16
    Article
    Avatar of communityCommunity Picks·2y

    🗄️🚀 SQL vs NoSQL: Viability and Comparison

    SQL databases like PostgreSQL offer rigid, table-based schemas and follow ACID principles, making them ideal for structured data and applications requiring consistent transactions. NoSQL databases like MongoDB and ElasticSearch provide flexible schemas, are horizontally scalable, and are suited for unstructured data, web apps, IoT, and real-time text searching. PostgreSQL is trusted in corporate sectors for reliability, MongoDB is favored by startups for scalability, and ElasticSearch is essential for real-time observability and log analysis.

  17. 17
    Article
    Avatar of rubyflowRuby Flow·2y

    Practical Guide for Database Scaling: how to use Postgres Logical Replication

    Postgres logical replication facilitates efficient and easy-to-implement database replication by allowing granular control over which tables are replicated. Through Docker Compose, the guide demonstrates setting up a main database and a replica, creating tables, populating data, and configuring the replication. Key considerations include the WAL settings and the limitations of logical replication, such as the necessity for identical schemas between the primary and replica databases. The guide also covers using PGAdmin for database management and offers tips for managing schema changes.

  18. 18
    Article
    Avatar of newstackThe New Stack·2y

    PostgreSQL 17 Gets Incremental Backup, SQL Queries for JSON

    PostgreSQL 17 introduces several major features including incremental backup, improved replication slots, and enhanced JSON query support. Incremental backup allows for faster recovery times and is more suitable for large databases. The update to replication slots ensures database consistency during failovers. Additionally, users can now query JSON data types using standard SQL statements, thanks to new JSON compatibility improvements.

  19. 19
    Article
    Avatar of postgresPostgreSQL·2y

    Coroot 1.4: Simplify PostgreSQL Monitoring (Open Source)

    Coroot 1.4 enhances PostgreSQL monitoring by offering seamless integration without extra configurations. Key features include L7 protocol decoding for operations, advanced SSL/TLS monitoring using eBPF, broad deployment support across various environments, and cloud-cost analysis. It also provides simple log analysis, low-impact query tracing, and in-depth performance and slow query identification.

  20. 20
    Video
    Avatar of vscodeVisual Studio Code·2y

    Building a RAG application with a PostgreSQL database

    Learn how to build a Retrieval Augmented Generation (RAG) application using PostgreSQL from scratch. RAG leverages large language models to answer questions accurately by providing relevant information from databases. The post walks through setting up PostgreSQL with the PG Vector extension, creating embeddings for semantic search, and combining full-text and vector search techniques for optimal results. It also highlights the importance of query rewriting and discusses options for using local and hosted models for embedding.

  21. 21
    Article
    Avatar of vladmihalceaVlad Mihalcea·2y

    High-Performance Java Persistence Newsletter, Issue 66

    Discover tools like Hypersistence Optimizer that automatically detect JPA and Hibernate performance issues. This newsletter covers articles on database interaction in Java, including PostgreSQL clauses, Spring Boot metrics, and the latest project releases like PostgreSQL 16.4 and Hibernate ORM 6.6. Public training events and trending StackOverflow answers are also featured.

See all PostgreSQL archives