Best of PostgreSQL — December 2024

1. 1
   Article

   Hacker News·1y

   outerbase/studio: A lightweight Database GUI in your browser. It supports connecting to Postgres, MySQL, and SQLite.

   Outerbase Studio is a lightweight, browser-based GUI for managing SQL databases, initially built for LibSQL and SQLite but now supporting a range of databases including MySQL and PostgreSQL. It features a user-friendly query editor, a powerful data editor, a schema editor, and a flexible connection manager. The desktop apps for Windows and Mac offer additional support for drivers not feasible in a browser environment.

   220

   3
2. 2
   Article

   Lobsters·1y

   7 Databases in 7 Weeks for 2025

   This post discusses seven databases that are worth exploring in 2025, each offering unique capabilities and use cases. It covers PostgreSQL, SQLite, DuckDB, ClickHouse, FoundationDB, TigerBeetle, and CockroachDB, highlighting their key features and suggesting practical experiments to deepen understanding. This mix includes both well-established and emerging technologies, offering insights into client-server models, embedded databases, analytics, financial transaction processing, and globally distributed databases.

   126

   1
3. 3
   Article

   Community Picks·1y

   leerob/next-saas-starter: Get started quickly with Next.js, Postgres, Stripe, and shadcn/ui.

   A starter template for building a SaaS application using Next.js, Postgres, and Stripe. It offers features like authentication, Stripe payment integration, a marketing landing page, subscription management, and a user dashboard. The template includes instructions for setting up the environment, migrating and seeding the database, and deploying to Vercel.

   98

   9
4. 4
   Article

   gitconnected·1y

   PostgreSQL Shared Buffer, What Backend Developers Should Know

   For backend developers, understanding and configuring PostgreSQL shared_buffers is critical. Shared buffers are a key part of PostgreSQL's architecture, acting as an intermediary between the database and the disk rather than a simple cache. Proper configuration typically involves allocating around 25% of RAM to shared_buffers and monitoring their performance using tools like pgbuffercache and pginspect. Optimizing shared_buffers can significantly enhance read and write operations, reducing the need for frequent disk access.

   64
5. 5
   Article

   Community Picks·1y

   How Redis Solved Our Challenges with Dynamic Task Scheduling and Concurrent Execution? [Developer's Guide]

   The post discusses migrating a task scheduling system from a simple Go service using SQLite to a more complex SaaS platform. The new challenges of dynamic scheduling and concurrent execution were solved using Redis's sorted set for task prioritization and Postgres for handling distributed writes. Evaluations of various scheduling libraries indicated rq_scheduler in Python as the most suitable. The result was an efficient architecture capable of scalable and reliable task scheduling and execution.

   53
6. 6
   Article

   Neon·1y

   psql from the browser

   Neon has developed a tool to run psql, the popular Postgres command-line client, directly in the browser. This innovation emerged from an internal hackathon and utilizes a WebSocket proxy, allowing seamless connection to databases in browser environments where the TCP stack is inaccessible. The app includes features like database instantiation from templates, SQL query execution, and slash commands for database schema inspection. It also provides a unique, isolated connection string for each session. The project is open-source and available for contributions.

   48
7. 7
   Article

   Hacker News·1y

   Electric BETA release

   Electric has released its BETA version 1.0.0-beta.1 of their Postgres sync engine, allowing real-time partial replication of Postgres data into local apps and services. It simplifies building apps with instant, real-time, local data and includes support for PGlite, a lightweight WASM Postgres for the browser. Electric has been tested in production with companies like Supabase, Trigger.dev, Otto, and Doorboost. The platform scales to millions of concurrent users with low latency and offers easy integration with existing APIs. Documentation and interactive demos are available to help users get started.

   46
8. 8
   Article

   Community Picks·1y

   Web Software Development

   The free online course covers the development and testing of web applications with a comprehensive introduction to both server-side and client-side technologies. Key technologies include Deno, Hono, Deno KV, PostgreSQL, Svelte, Playwright, TailwindCSS, HTML, CSS, and JavaScript. The course platform improves continuously using data gathered from user interactions for better learning experiences.

   36

   1
9. 9
   Article

   Hacker News·1y

   pgroll

   pgroll simplifies zero-downtime schema migrations for PostgreSQL by automating data backfills and using PostgreSQL views to support simultaneous old and new schemas. It handles the complex steps of schema changes with its expand-and-contract pattern and integrates seamlessly with development and deployment pipelines. The tool offers enhanced functionality in its cloud version and ensures safe, reversible schema changes with instant rollbacks.

   33
10. 10
    Article

    Hacker News·1y

    Olshansk/postgres_for_everything: How to reduce complexity and move faster? Just Postgres for everything.

    Using PostgreSQL simplifies development by handling a wide variety of tasks including cron jobs, message queues, GIS & mapping, search, caching, and more. It shows PostgreSQL’s versatility and provides a repository of resources to use PostgreSQL for various purposes. Contributors are encouraged to submit new examples to enrich this collection.

    33

    1
11. 11
    Article

    Community Picks·1y

    DELETEs are difficult

    DELETE operations in databases, particularly PostgreSQL, can pose significant challenges and are often overlooked compared to SELECT and INSERT operations. DELETE commands involve several steps, including row identification, lock acquisition, trigger execution, marking rows for deletion, index updates, and more. This process may lead to bloat, necessitating autovacuum processes to reclaim space. Strategies such as batching DELETE operations, using partitioning, and managing autovacuum settings are essential for maintaining database performance and efficiency.

    26

    4
12. 12
    Article

    Towards Data Science·1y

    PostgreSQL: Query Optimization for Mere Humans

    Learn how to optimize PostgreSQL queries using the EXPLAIN and EXPLAIN ANALYZE commands. Understand the structure of execution plans to identify bottlenecks, and apply practical tips for improving performance through indexing and various scanning methods. Explore tools like eversql, metis, and QueryFlow for advanced optimization.

    23
13. 13
    Article

    Community Picks·1y

    Document Store using JSONB in Postgres

    JSONB in Postgres allows the storage and querying of nested JSON-like data. This guide explains how to set up a table with a JSONB column, insert and query JSONB data, use Sequelize ORM, index JSONB fields, and perform type casting and updates. GIN indexes can improve query performance on JSONB properties.

    22
14. 14
    Article

    Supabase·1y

    Running Durable Workflows in Postgres using DBOS

    DBOS, a joint research project between Stanford and MIT, integrates with Postgres to create a durable workflow engine. It leverages Postgres's features to offer lightweight and efficient performance, ensuring robust management of workflow steps and state recovery. The platform supports serverless functions in Python and TypeScript, using Postgres to maintain state, handle errors, and provide features like idempotency and transactional guarantees, offering significant performance advantages over other workflow engines.

    22
15. 15
    Article

    strongdm·1y

    How to List All Databases in PostgreSQL (6 Methods)

    Learn six different methods to list all databases in PostgreSQL: using the psql command-line interface, SQL queries, pgAdmin, third-party tools like DBeaver, automation with scripts, and Docker containers. The guide provides step-by-step instructions and troubleshooting tips for common issues.

    21
16. 16
    Article

    strongdm·1y

    How to Create a Database in PostgreSQL

    Managing large datasets efficiently can be challenging. PostgreSQL is an advanced database system known for its reliability and performance. This guide covers installing PostgreSQL, connecting via psql or pgAdmin, creating databases, and managing permissions. Common issues such as permission errors and encoding conflicts are also addressed. Additionally, StrongDM enhances database security and management through centralized dashboards, audit logging, tool integration, and role-based access control.

    18

    1
17. 17
    Article

    Crunchy Data·1y

    Postgres Partitioning with a Default Partition

    Effective partitioning in PostgreSQL can be crucial for maintaining a database with growing application data. Default partitions serve as a catch-all for data that doesn't fit existing partitions and help manage unexpected or incorrect data entries. It's essential not to leave data in default partitions and regularly monitor and move valid data to appropriate child partitions. Tools like pg_partman can assist in managing this process, automatically creating child partitions and providing functions to check and handle data in default partitions.

    17
18. 18
    Article

    RUBYLAND·1y

    Kamal Database Backups

    The post provides details on how to manage PostgreSQL database backups using Kamal. It includes commands for starting, restarting, rebooting, and viewing logs for the backup process. The post also explains how to configure PostgreSQL credentials using Rails and set up prompts for different Rails environments. Additionally, it provides an example configuration for scheduling backups with environment variables and aliases for common commands.

    16
19. 19
    Article

    Ardan Labs·1y

    Optimizing Databases on Kubernetes Ep.2: Automating Database Maintenance with Kubernetes and CNPG

    Jérôme Petazzoni explains how Kubernetes and CNPG (Cloud Native PostgreSQL) automate database maintenance, showcasing features like automated failover mechanisms and Pod Disruption Budgets to ensure minimal downtime and reliability. He also covers connection handling strategies to mitigate application disruptions during switchovers.

    15
20. 20
    Article

    strongdm·1y

    How to Connect to a Remote PostgreSQL Database

    Learn how to connect to a remote PostgreSQL database by configuring necessary prerequisites, adjusting PostgreSQL and firewall settings, and using different tools like psql, pgAdmin, DBeaver, and various programming languages. Ensure secure connections using best practices and consider using tools like StrongDM for enhanced security and management.

    14
21. 21
    Article

    Neon·1y

    What To Know About Postgres if You’re Coming From SQL Server

    Microsoft SQL Server and Postgres both share their origins in 1989, but they have grown in different directions. SQL Server uses T-SQL, while Postgres uses PL/pgSQL, each with distinct syntax and features for procedural programming, string delimiters, date functions, and more. Postgres, an open-source community-driven database, offers flexibility and earlier access to innovative features compared to the company-driven SQL Server. Switching from SQL Server to Postgres includes adapting to different syntactical nuances but opens up a broader range of tools and hosting options.

    12