Best of SQL — January 2025

1. 1
   Article

   Tiger's Place·1y

   All database ACID isolation levels and race conditions explained

   This post provides a comprehensive explanation of database ACID isolation levels and the associated race conditions. It covers isolation levels from weakest to strongest, detailing their characteristics and issues, including dirty reads, non-repeatable reads, write skew, and phantoms. Solutions such as snapshot isolation, two-phase locking (2PL), and Serializable Snapshot Isolation (SSI) are also discussed along with practical examples.

   134

   2
2. 2
   Article

   Vlad Mihalcea·1y

   Why you should use compact table columns

   Using compact table columns can significantly enhance database performance by allowing more records and index entries to be stored in memory, thus speeding up SQL queries. This practice is effectively demonstrated by StackOverflow, which uses a highly efficient schema to serve millions of queries per second with minimal hardware. Tips include using smaller data types for keys and enforcing column length limits for VARCHAR fields.

   128

   2
3. 3
   Article

   Database Daily·1y

   Visualizing a SQL query

   122

   1
4. 4
   Article

   Neon·1y

   Build an end-to-end RAG pipeline entirely in psql using pgrag and DeepSeek

   Retrieval-Augmented Generation (RAG) pipelines can be built entirely within psql using the pgrag extension, which works alongside the pgvector extension to enable efficient semantic searches within Postgres. The pgrag extension simplifies both the preparation of document embeddings and the handling of questions to retrieve relevant information. Users can extract text, generate embeddings, rerank results, and prompt AI chat models like DeepSeek directly within their database environment.

   60
5. 5
   Article

   marmelab·1y

   How to build a CMS with React-Admin

   Learn how to build a CMS proof of concept using React-Admin for the admin interface and Supabase for backend support. The guide covers setting up a flexible data model, using SQL triggers for dynamic table updates, and creating dynamic CRUD views with React-Admin components.

   46

   1
6. 6
   Article

   Lonely Programmer·1y

   Great Idea! Lets implement it...

   46

   10
7. 7
   Article

   JetBrains·1y

   9 Tips for Productive Java Development With Databases in IntelliJ IDEA

   IntelliJ IDEA enhances productivity for Java developers working with databases through features like automatic data source creation, query method testing without application runs, database schema diagrams, and in-editor query result reviews. Other helpful tools include data modification directly from result sets, query result visualization as charts, execution plan profiling, automated DB migration script generation, and an AI Assistant for query management.

   41
8. 8
   Article

   Data Engineering·1y

   Pivot Tables.

   27

   3
9. 9
   Article

   Materialize·1y

   How to Simplify Microservices with a Shared Database and Materialized Views

   Microservices architecture simplifies application development by breaking it into smaller, independent services. However, isolated databases in microservices complicate data sharing. Introducing a central database with technologies like materialized views can mitigate these challenges by enabling efficient, consistent data sharing. This approach offers benefits such as simplified system design and reduced implementation effort but also introduces issues like coupling and resource contention. Solutions involve using stable interfaces with database views, optimizing data access with materialized views, and isolating workloads through shared storage.

   27

   1
10. 10
    Article

    Community Picks·1y

    Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL

    The post explains how to leverage JSON within PostgreSQL to combine the benefits of both SQL and NoSQL databases. It details the evolution of JSON support in PostgreSQL, from the introduction of the JSON data type to more advanced features like JSONB and JSONPath. Additionally, it covers how to create and query JSON data, the importance of indexing for performance, and common anti-patterns to avoid. The post concludes by comparing JSON in PostgreSQL with traditional NoSQL databases and outlines when it's best to use each approach.

    15

    2
11. 11
    Article

    Towards Data Science·1y

    Scaling Statistics: Incremental Standard Deviation in SQL with dbt

    Incremental aggregation in SQL helps maintain efficiency when recalculating metrics for large datasets. This is particularly useful for complex calculations like standard deviation, which involves updating both the mean and the sum of squared differences. By using algebraic manipulation, a formula for incremental computation can be derived, avoiding the need to recalculate from scratch with each new data point. The example provided demonstrates how to implement this using dbt, enabling efficient and scalable real-time data aggregation.

    12
12. 12
    Article

    Neon·1y

    Building a SQL Query Assistant with Neon, .NET, Azure Functions, OpenAI service

    Learn to build an AI-driven SQL Query Assistant using .NET Core, Azure Functions, Neon, and Azure OpenAI service. This assistant translates natural language queries into SQL, ensuring secure and efficient data handling. The guide covers setting up Azure resources, creating necessary APIs, and deploying models like GPT-4 and text-embedding-ada-002 for generating SQL queries and embeddings.

    11
13. 13
    Article

    Community Picks·1y

    AI Text2SQL Tool for Easy Database Management

    Chat2DB is an open-source AI tool that facilitates easy database management by generating SQL queries and coding efficiently with an AI SQL editor. It supports various databases, ensuring data security with features like local query processing, AES and RSA encryption, and SSH tunneling. Chat2DB also aids in data analysis, offering fast insights and dashboard creation. It ensures high stability for big data queries and supports easy collaboration through shared links and API integration.

    10

    3