Best of PostgreSQL — August 2024

1
Article
Crunchy Data·2y
Announcing an Open Source Monitoring Extension...
Crunchy Data has introduced a new open-source pgMonitor extension aimed at improving PostgreSQL monitoring. The extension enhances query performance and simplifies metric collection by leveraging materialized views and background workers. It offers broad compatibility with various monitoring systems, ensures consistent metric updates, and adapts easily to changes in PostgreSQL versions. Users can also define custom metrics and refresh intervals to meet specific needs.
430
11
2
Article
Hacker News·2y
ChartDB
ChartDB is a free and open-source database design editor that simplifies the creation of database diagrams. It allows users to import, edit, and export database schemas using a single query, supporting popular relational DBMS like MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, and SQL Lite. Features include a visual query editor, the ability to add tables and notes, and exporting diagrams as SQL scripts or images.
335
12
3
Video
YouTube·2y
Build a Full Stack React Native App with Payments | PostgreSQL, TypeScript, Stripe, Tailwind
The post describes a comprehensive course on building a full-stack mobile app using React Native. Highlights include creating an Uber clone with 15 screens and advanced features like Expo router, global state management with Zustan, Expo Location, PostgreSQL database, email authentication with Clerk, and Stripe payments. The course aims to teach a wide range of essential mobile app development skills including splash screens, custom tabs, navigation models, Google Maps integration, and responsive design across devices. It also introduces useful development tools like WebStorm for enhanced productivity.
214
4
Article
freeCodeCamp·2y
How to Build an Invoice SaaS App with Next.js, Resend, Clerk and Neon Postgres
This tutorial guides you through building a complete invoicing web application using Next.js, Resend, Clerk, and Neon Postgres. Key functionalities include managing customers, creating invoices, printing and sending invoices via email, and handling real-time communication between the frontend and backend. You'll gain experience with setting up a Postgres database, implementing user authentication with Clerk, and using React-to-print for PDF generation.
167
5
Article
Supabase·2y
postgres.new: In-browser Postgres with an AI interface
Introducing postgres.new, an in-browser Postgres sandbox with AI assistance. It allows the creation and management of Postgres databases directly in the browser using PGlite, a WASM version of Postgres. Key features include drag-and-drop CSV import, report generation, chart creation, and database diagram construction. The platform leverages a large language model for autonomous database operations, and it supports semantic search with pgvector. Future enhancements include deploying databases to S3 and supporting more file types.
157
3
6
Article
Supabase·2y
Supabase + Vercel Partnership
Vercel has launched official First-Party Integrations, including Supabase, making it easier to manage Postgres databases directly from the Vercel dashboard. This integration supports Vercel Templates, unified billing, and offers dedicated Postgres instances, modular building blocks like AI and Auth, low latency with read replicas, and secure, scalable solutions. It's currently in Public Alpha as of August 28, 2024.
152
4
7
Article
Community Picks·2y
Simplifying Your Tech Stack with PostgreSQL
Simplifying your tech stack with PostgreSQL can streamline development, reduce operational complexity, and minimize moving parts. PostgreSQL can efficiently replace multiple technologies such as Kafka, RabbitMQ, MongoDB, and Redis, supporting functionalities like caching, message queuing, data warehousing, and full-text search. This approach enhances developer productivity, reduces cognitive load, and ensures robust performance and flexibility. Developers benefit from PostgreSQL's comprehensive support for JSON, geospatial queries, auditing, and more, providing a powerful and scalable backend solution.
136
5
8
Article
DEV·2y
Project Starter Kit: Neon Postgres Database + ExpressJs + TypeScript and TypeORM
A comprehensive starter kit incorporating TypeScript, TypeORM, Neon Postgres, and ExpressJs is available for quickly scaffolding backend projects. It provides CRUD operations, entity definitions, and database configurations. The kit includes a CLI tool for easy setup and is well-documented for both beginners and advanced developers. Find it on GitHub and use it to boost productivity and ensure best practices.
101
1
9
Article
Neon·2y
Top 3 features in Postgres 17
Postgres 17 introduces significant improvements aimed at enhancing developer experience and performance. Key features include MERGE command with RETURNING support for more efficient queries, enhanced JSON functions for better handling of semi-structured data, and several performance optimizations like improved parallel query processing and faster vacuum operations. Contributions from Neon engineers include detailed query analysis with EXPLAIN (SERIALIZE), parallel builds for BRIN indexes, and enhancements to B-tree index performance.
94
3
10
Video
Josh tried coding·2y
My Favorite Tech Stack for 2024
94
1
11
Article
Community Picks·2y
Next.js authentication using Clerk, Drizzle ORM, and Neon
Learn to build a Next.js application with Clerk for authentication and Neon’s Serverless Postgres using Drizzle ORM. The post guides you through setting up user management, database schema, migrations, and adding interactivity. Deploy a full-featured app with secure authentication and efficient data storage.
93
12
Article
Community Picks·2y
PostgreSQL databases under attack
Poorly protected PostgreSQL databases on Linux are being targeted by cryptojacking attackers. Aqua Security researchers observed attackers brute-forcing access credentials, creating new high-privilege user roles, removing superuser privileges from compromised roles, and downloading payloads that eliminate competing cryptomining malware. PostgreSQL databases, often found in cloud and on-premises environments, are frequent targets due to lax security or misconfigurations. To protect these databases, using firewalls, VPNs, SSH tunnels, strong passwords, audit logs, and intrusion detection systems is advised.
86
2
13
Article
Supabase·2y
In-Browser Semantic AI Search with PGlite and Transformers.js
The post introduces a method for building an in-browser semantic search experience using PGlite, pgvector, and Transformers.js. This setup could benefit eCommerce sites by quickly surfacing relevant products without server roundtrips. It includes instructions for setting up the database schema and code examples for a React application, enabling local storage and retrieval of text and embeddings using IndexedDB.
72
1
14
Article
Hacker News·2y
Just use Postgres
When developing a new application requiring persistent data storage, Postgres should be the default choice, especially for web applications. SQLite is suitable for desktop or mobile apps but not ideal for websites. NoSQL databases like DynamoDB, MongoDB, Cassandra, and others offer performance benefits if the access patterns are well-defined beforehand but are less flexible when handling evolving access needs. Redis is efficient for caching but can be used as a primary database with some trade-offs. Datomic and XTDB present innovative approaches with specific benefits and challenges. While advanced databases like Kafka and ElasticSearch excel in niche tasks, Postgres often suffices for general needs, potentially complemented by simpler built-in search functions.
62
13
15
Article
Community Picks·2y
How to Minimise the Impact of Database Latency
Learn how to minimize the impact of database latency on your application's response times by using Neon's Regional Latency Dashboard and implementing techniques like query optimization, batching queries, leveraging Postgres features, reusing connections, and application-level connection pooling. Understand the significance of placing your backend close to your database and solving the N+1 Problem to enhance performance.
59
2
16
Article
Bytes by ui.dev·2y
The final boss of build tools
Vite has rapidly become a key foundation for higher-level innovations in the web ecosystem, with many frameworks adopting it. The Vite team plans to create a unified toolchain for handling JavaScript and TypeScript from source to production. Meanwhile, Neon offers a managed Postgres service designed to simplify database management tasks. Additionally, recent tech updates include new JavaScript tools, TypeScript 5.6 beta features, and insights from the Stack Overflow 2024 Developer Survey.
53
17
Article
Dev Genius·2y
Why I Always Use PostgreSQL Functions For Everything
The author advocates for using PostgreSQL functions for all database interactions to improve maintainability, readability, security, and availability. They provide a detailed analysis of encapsulating SQL queries within functions to define clear data contracts. The article also discusses handling legacy systems, managing the N+1 query antipattern, migrating to Timescale DB, and optimizing query performance. Additionally, the approach allows easy implementation of least privilege security by limiting database access to executing specific functions only.
50
4
18
Article
Community Picks·2y
Bring Postgres relationships to light
Entity-relationship diagrams (ERDs) are invaluable for visualizing and managing complex Postgres databases. ERDs showcase entities, attributes, and relationships, making database structures more accessible, especially for non-technical team members. Tools like Outerbase simplify the creation and maintenance of ERDs by automatically generating and updating diagrams for Neon databases. This democratization of data aids developers in understanding, communicating, and scaling database schemas efficiently.
49
19
Article
SD Times·2y
SD Times Open-Source Project of the Week
Teable is a no-code database offering a spreadsheet-like interface for data entry and application creation, merged with the performance of a database. It surpasses Airtable in performance with no row limits and supports SQL querying. Additional view types and features like AI copilot and extensions are being developed.
38
20
Article
Neon·2y
Multi-tenancy and Database-per-User Design in Postgres
The post examines the multi-tenancy strategies in Postgres, emphasizing the database-per-user architecture. It discusses the evolution of multi-tenant architectures, highlights the benefits and challenges of the database-per-user strategy, and contrasts it with schema-per-user and shared schema approaches. The post also introduces the concept of control planes for managing and scaling multiple databases effectively, especially with Neon’s cloud-native capabilities.
36
21
Article
Hacker News·2y
Postgres as a search engine
Learn how to build a robust retrieval system using semantic, full-text, and fuzzy search techniques in Postgres. This guide covers setting up a search engine as a backbone for RAG pipelines, implementing full-text search with tsvector, semantic search with pgvector, and fuzzy matching with pg_trgm. Additionally, it provides tips on fine-tuning search results, debugging rankings, and boosting user experience through personalized and recent results. An overview of BM25 for advanced ranking is also included.
32
22
Article
Community Picks·2y
How Retool uses Retool (and the Neon API) to manage 300K+ Postgres databases
Retool leverages the serverless Neon API to manage over 300,000 Postgres databases with minimal engineering overhead. The platform simplifies internal app development with a drag-and-drop UI and automatic boilerplate handling. By partnering with Neon, Retool automates database tasks, scales resources efficiently, and offers dedicated databases to users without incurring high idle costs.
32
23
Article
selfh.st·2y
This Week in Self-Hosted (2 August 2024)
This week highlights updates in self-hosted software, including a new fork called Trilium Notes Next, and milestone updates for Immich's mobile photo editor. Noteworthy software updates include Forgejo, Git-Sync, and Memos among others. PG Back Web is showcased for its intuitive web interface and features for PostgreSQL backup. New software additions feature tools like Accent for pronunciation practice and ContainerCleaner for automated container management.
29
24
Article
Community Picks·2y
Join us for the Neon Open Source Starter Kit Challenge: $3,000 in Prizes!
Join the Neon Open Source Starter Kit Challenge for a chance to win $3,000 in prizes. Participants are tasked with building an open-source starter kit using Postgres on Neon and documenting its use. The challenge runs until September 1, with winners announced on September 3. Submissions will be judged on clarity, developer experience, applicability, and usability. All valid entries receive a completion badge.
25
3
25
Article
Community Picks·2y
Performance tips for Neon Postgres
The post provides performance tips for using Neon Postgres, a serverless Postgres solution that scales based on load. It covers essential practices such as connection pooling, autoscaling, and troubleshooting query performance using tools like pg_stat_statements and EXPLAIN ANALYZE. The article also discusses optimizing ingestion rates by ensuring sufficient compute resources, using read replicas, batch inserts, and cleaning up old indexes. Additionally, it emphasizes the importance of managing table bloat and effectively utilizing cache. Lastly, it suggests safely testing performance improvements using Neon’s database branching feature.
22

See all PostgreSQL archives