Best of PostgreSQL — October 2024

  1. 1
    Article
    Avatar of collectionsCollections·2y

    Databases In-Depth – Complete Course

    A comprehensive course on database architecture and optimization, designed by Keerti Purswani and available on freeCodeCamp.org's YouTube channel. It covers transaction management, storage engines, advanced indexing techniques, an in-depth look at SQLite, and understanding MongoDB and PostgreSQL. Suitable for both beginners and experienced engineers, the course aims to equip learners with the skills to manage and optimize data systems efficiently.

  2. 2
    Article
    Avatar of freecodecampfreeCodeCamp·2y

    How to Create Database Documentation Using dbdocs with DBML

    Database documentation is crucial for maintaining and scaling systems. This guide demonstrates creating database documentation using DBML and dbdocs. The step-by-step process includes installing the necessary CLI tools, exporting a database schema to DBML, editing the DBML file, and generating documentation with dbdocs. Benefits include simplicity, automation, collaboration, version control, and visualization.

  3. 3
    Article
    Avatar of awegoAwesome Go·2y

    Go and Postgres Listen/Notify or: How I Learned to Stop Worrying and Love PubSub

    This post explains how to implement PubSub functionality using PostgreSQL's Listen/Notify feature in Golang applications. Unlike traditional PubSub systems that rely on message brokers like Redis, PostgreSQL's Listen/Notify allows for an efficient, single-system solution. The author discusses the use of pgx for connection management, introduces a custom Notifier package, and provides code examples illustrating how to set up a listener, notifier, and subscription system efficiently. Key points include handling notifications, managing subscriptions, and dealing with blocking calls.

  4. 4
    Article
    Avatar of awegoAwesome Go·2y

    Writing raw SQL easier with pgx and sqlc in Go

    Combine pgx, a PostgreSQL driver and toolkit for Golang, with sqlc, a tool that generates type-safe Go code from SQL queries, to streamline database interactions. While ORMs can be limiting for complex queries, pgx and sqlc offer more control and type safety. Using sqlc for CRUD operations and simple queries, and pgx for more complex queries, provides a balanced approach. The post outlines how to set up and use these tools in a Go project.

  5. 5
    Article
    Avatar of communityCommunity Picks·2y

    Building a Full-Stack Portfolio Website with a RAG Powered Chatbot

    Develop a portfolio website using React and FastAPI, featuring a chatbot powered by pgvector, Neon Postgres, and OpenAI embeddings. This guide walks you through setting up the backend, database, API endpoints, and front end, and optionally containerizing the project with Docker. You'll learn how to create a modern tech portfolio that demonstrates both front-end and back-end skills, while integrating an AI chatbot to answer questions about your experience.

  6. 6
    Article
    Avatar of communityCommunity Picks·2y

    A video course on PostgreSQL

    Mastering Postgres is a comprehensive video course designed to enhance your skills with PostgreSQL, covering fundamentals to advanced topics like indexes and data types. Learn to use full text search capabilities, create efficient indexes, and optimize your database performance. The course is taught by Aaron Francis, a seasoned software developer and database educator, and offers real-world examples to help you understand core principles. The course includes 4K resolution videos with both light and dark modes, and offers a satisfaction guarantee with refund options.

  7. 7
    Article
    Avatar of hnasrHussein Nasser·2y

    The Internals of a Primary Index

    A primary key uniquely identifies a row and is typically indexed for fast access. Primary indexes are often clustered, meaning they store full rows. Secondary indexes only store row identifiers, requiring an additional lookup of the primary key to retrieve full rows. Postgres is an exception as it does not use a real primary clustered index; all indexes, including primary, point to the row identifier or tuple id. The tuple id in Postgres is unique and allows direct row access with minimal I/O.

  8. 8
    Article
    Avatar of towardsdevTowards Dev·2y

    Building an API with Go, PostgreSQL, Google Cloud and CockroachDB

    The post outlines the process of building and deploying an API using Go, PostgreSQL, Google Cloud Run, and CockroachDB. It provides detailed steps for setting up a local PostgreSQL database, creating an API server with Go, managing middleware and routes, handling error structures, caching, and deploying on Google Cloud Run. Additionally, it includes instructions for integrating with CockroachDB and using Google Secret Manager for managing sensitive data like certificates.

  9. 9
    Video
    Avatar of communityCommunity Picks·2y

    MySQL vs PostgreSQL (Round 2) Performance Benchmark (Latency - Throughput - Saturation)

    Performance comparison between PostgreSQL and MySQL focusing on latency, throughput, and system resource usage. The benchmark simulates an e-commerce website managing customer and order data, with tests for INSERT, UPDATE, DELETE, and SELECT operations. Improvements include optimized settings for both databases and utilizing i3en EC2 instances. PostgreSQL outperforms MySQL in write-heavy operations, while read operations show smaller differences.

  10. 10
    Video
    Avatar of communityCommunity Picks·2y

    Building Change Data Capture (CDC) in .NET with Debezium + RabbitMQ

    Milan introduces Change Data Capture (CDC), which tracks and reacts to database changes in real-time or near-real-time. He covers setting up CDC with PostgreSQL, RabbitMQ, and MassTransit in a .NET application. The guide includes configuring Docker containers, setting environment variables, and using transformations in Debezium to route messages accurately. Despite initial challenges, the setup enables streaming database changes to RabbitMQ, which are then consumed by a .NET app.

  11. 11
    Article
    Avatar of communityCommunity Picks·2y

    Building a High-Performance Sensor Data API with FastAPI and Postgres' TimescaleDB Extension

    This guide helps you build a high-performance API for streaming, storing, and querying sensor data using FastAPI and TimescaleDB. It covers setting up the project, database, and creating API endpoints that efficiently manage and analyze sensor data, leveraging TimescaleDB's advanced time-series features for optimal performance.

  12. 12
    Article
    Avatar of threedotslabsThree Dots Labs·2y

    Watermill 1.4 Released (Event-Driven Go Library)

    Watermill 1.4, an event-driven Go library, has seen significant updates. Key highlights include support for a new universal requeuer component that works with all Pub/Subs (requiring PostgreSQL for now), a pq CLI tool for managing poison queues, and a delay package to add delay metadata to messages. There's also enhanced support for AWS SNS/SQS Pub/Subs and major updates to watermill-amqp and watermill-sql. The new release includes refreshed documentation, a new logo, and several new examples showcasing features like delayed messages and distributed transactions.

  13. 13
    Article
    Avatar of neontechNeon·2y

    How to create an AWS S3 bucket for Postgres backups

    Learn how to set up an AWS S3 bucket to store Postgres backups, a useful step for managing backups in a multi-tenant architecture. This guide includes setting up AWS Providers and Roles, creating an OIDC Identity Provider and Role, and configuring an S3 bucket with an updated policy. The process involves using GitHub Actions to automate transfers, ideal for handling numerous projects efficiently.

  14. 14
    Article
    Avatar of simplethreadSimple Thread·2y

    Seeding a Postgres Database in GitHub Actions Without Storing SQL in GitLFS

    Setting up a Postgres database in a test environment usually avoids preloading data, but sometimes budget constraints require a workaround. To avoid using GitLFS for a large seed data file, compress it with `tar` and `gzip` before committing it to the repository. During the GitHub Actions workflow, decompress the file before running tests. If you encounter a 'no space left on device' error, increase the shared memory size (`shm-size`) to resolve the issue.

  15. 15
    Video
    Avatar of communityCommunity Picks·2y

    Full Text Search in .NET With PostgreSQL and EF Core

    Learn how to implement full text search in a .NET application using PostgreSQL and EF Core, without relying on external services like Elasticsearch or Solr. This guide walks through creating a search endpoint, handling case sensitivity, improving performance with indexes, and ranking search results for relevance. Additionally, it covers optimizing database queries for performance with full text search capabilities.

  16. 16
    Article
    Avatar of golangnutsGo Developers·2y

    Go project to backup and restore PosgreSQL database to local, S3, SSH and FTP remote storage

    A Golang project for backing up, restoring, and migrating PostgreSQL databases is shared. It supports local storage, S3, SSH, and FTP. The project can be run using Docker, and a sample run command is provided along with links to the GitHub repository and documentation.

  17. 17
    Article
    Avatar of devopslifeDevOps Life·2y

    Docker backing up PosgreSQL dabatase to Local, AWS S3, FTP and SSH remote storage

    Learn how to easily backup a PostgreSQL database using Docker with the `jkaninda/pg-bkup` tool. This solution supports saving backups to local storage, AWS S3, FTP, and SSH remote storage. Key parameters include setting the database host, username, password, and scheduling the backup with a cron expression.

  18. 18
    Video
    Avatar of communityCommunity Picks·2y

    MySQL vs PostgreSQL Performance Benchmark (Latency - Throughput - Saturation)

    The post compares MySQL and PostgreSQL databases focusing on latency, throughput, and saturation during data ingestion and retrieval. PostgreSQL demonstrates lower latency, efficient CPU and memory usage, and better disk usage efficiency compared to MySQL. Detailed benchmark tests show PostgreSQL handling nearly twice the number of queries per second than MySQL. The author invites suggestions for test improvements and provides a breakdown of various performance metrics collected during the tests.

  19. 19
    Article
    Avatar of supabaseSupabase·2y

    Live Share: When your database escapes your browser

    Live Share is a new feature for database.build (formerly postgres.new) that allows connecting to in-browser PGlite databases from outside the browser. This is accomplished by generating a unique Postgres connection string for use with any Postgres client. Database.build offers an AI-assisted sandbox for creating and querying Postgres databases using natural language and standard SQL. Users can drag-and-drop CSVs to generate tables automatically. A websocket-to-tcp proxy facilitates the connection, using pg-gateway to relay messages. PGlite supports single-client connections and has some limitations in protocol support and connection concurrency.

  20. 20
    Video
    Avatar of codingwithlewisCoding with Lewis·2y

    How Uber Handles TRILLIONS of Transactions

    Uber handles trillions of transactions by evolving from a monolithic application to a microservices architecture using Postgres and MySQL. They developed their own immutable ledger system called Ledger Store, which sits above existing NoSQL databases, ensuring the integrity of financial transactions. Key features include sealing, manifest for security, and the use of Apache Kafka for real-time data streaming. They managed a complex database migration with minimal disruption by employing strategies like checkpointed data processing and the Shadow Rider system for simultaneous database operations.

  21. 21
    Article
    Avatar of communityCommunity Picks·2y

    Building a High-Performance API with FastAPI, Pydantic, and Neon Postgres

    Learn to create a high-performance API for managing a tech conference system using FastAPI, Pydantic for data validation, and Neon's serverless Postgres. The guide walks through setting up the project, connecting to Neon Postgres, defining models and schemas, creating API endpoints, running and testing the API, and Dockerizing the application. It also covers performance optimization techniques such as database indexing, pagination, dependency injection, and efficient data handling.

  22. 22
    Video
    Avatar of awesome-codingAwesome·2y

    What Happened in September?

    OpenAI released a new model aimed at providing more thoughtful and accurate responses, potentially impacting jobs. September saw the release of Postgres 17 with performance enhancements, and Deno 2, focusing on developer experience and interoperability. The Angular team is pushing for modularity with standalone components, while Vue 3.5 offers performance optimizations and improved reactivity without breaking changes. OpenAI's switch from Next.js to Remix is notable. Additionally, Caleb achieved $1 million in GitHub sponsors through valuable open-source projects.

  23. 23
    Article
    Avatar of communityCommunity Picks·2y

    Building an Async Product Management API with FastAPI, Pydantic, and PostgreSQL

    Learn to build an asynchronous product management API using FastAPI, Pydantic for data validation, and PostgreSQL with connection pooling. This guide covers setting up your project, managing database connections with asyncpg, defining Pydantic models, creating API endpoints, and running and testing your application using various tools.

See all PostgreSQL archives