Best of SQL — September 2024

  1. 1
    Article
    Avatar of gcgitconnected·2y

    In Search of Improving Database Performance: A Comprehensive Guide with 8 Key Strategies

    The post explores eight key strategies to boost database performance. It covers query optimization techniques, avoiding correlated subqueries, batching operations, leveraging materialized views, utilizing indexes, and analyzing query execution plans. Additional strategies include configuring database settings, employing caching strategies, employing database partitioning, sharding, and replication methods to enhance read/write speeds and manage large datasets efficiently.

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

    Database Transactions in Go with Layered Architecture

    This post explores handling database transactions in Go while adhering to layered architecture principles. It emphasizes the importance of keeping critical logic separate from SQL queries using patterns such as the Repository pattern, the UpdateFn pattern, and the Transaction Provider pattern. Various approaches to transaction management are discussed, highlighting their advantages and drawbacks to help maintain code clarity and integrity.

  3. 3
    Article
    Avatar of communityCommunity Picks·2y

    How Indexing Enhances Query Performance

    Indexes play a crucial role in enhancing database query performance by allowing quick data retrieval without scanning the entire dataset. The post explores various types of indexes like primary, secondary, clustered, and non-clustered, and provides examples of their implementation in Spring Boot applications using JPA annotations. It also discusses scenarios where indexing is beneficial, such as high-volume read operations, frequent filtering, sorting, and join operations. Moreover, it highlights when not to use indexes, such as in low-volume tables or columns with low cardinality, and delves into how indexes operate behind the scenes, including index creation and maintenance.

  4. 4
    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.

  5. 5
    Article
    Avatar of javarevisitedJavarevisited·2y

    The 2024 Database Admin RoadMap

    This guide outlines the roadmap to becoming a Database Administrator in 2024, covering essential skills like SQL, database systems, advanced SQL topics, database administration, and cloud databases. Recommended learning resources include courses for SQL, Linux, Oracle, and PostgreSQL. The role is highly in-demand and offers robust career prospects.

  6. 6
    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.

  7. 7
    Article
    Avatar of freecodecampfreeCodeCamp·2y

    How to Work with SQL Databases in Go – Different Approaches and Examples

    The post explores various Go packages for working with SQL databases, including the built-in database/sql, sqlx, GORM, and sqlc. It covers their strengths and weaknesses, providing hands-on examples for each. Additionally, it touches on database migrations in Go and offers a brief comparison of migration tools like golang-migrate and goose.

  8. 8
    Video
    Avatar of laraveldailyLaravel Daily·2y

    12 Laravel/PHP Tips in 8 Minutes: August 2024

    Discover 12 handy Laravel and PHP tips gathered from Twitter for August 2024. These include SQL best practices, filtering emails in Laravel validations, specific uses of the explode function in PHP, and optimizing Laravel performance. Also included are general advice on handling tutorials, particularly those generated by AI, and notable announcements from the Laravel community. Perfect for both developers looking to refine their techniques and those eager to stay updated with the latest in the Laravel ecosystem.

  9. 9
    Article
    Avatar of hnHacker News·2y

    Data Engineering Vault

    The Data Engineering Vault is a comprehensive resource designed to help you explore and discover interconnected terms in data engineering. It covers the definition and evolution of data engineering, highlighting the importance of tools like Python, Apache Airflow, and SQL. Additionally, it offers resources for getting started with data engineering, including must-read articles, influential books, and valuable community insights.

  10. 10
    Article
    Avatar of softwaretestingmagazineSoftware Testing Magazine·2y

    Open Source Database Testing Tools

    Database testing has fewer open-source tools compared to programming languages. This post lists various tools for unit, load, and security testing on relational and NoSQL databases. With the growing prominence of NoSQL and open-source relational databases, more tools are emerging. Key tools mentioned include Database Benchmark, Database Rider, DBChaos, DBKover, dbstress, DbUnit, DB Test Driven, GODBT, HammerDB, JdbcSlim, JdbcRunner, and many others.

  11. 11
    Article
    Avatar of hnHacker News·2y

    ben-n93/SQL-tips-and-tricks: SQL tips and tricks

    Discover a list of practical SQL tips and tricks. Highlights include using leading commas for fields in SELECT clauses, understanding the behavior of NOT IN with NULL values, effectively using anti-joins, and utilizing the QUALIFY clause for filtering window functions. The post also emphasizes the importance of commenting code and referring to documentation to avoid common mistakes.

  12. 12
    Article
    Avatar of astro_sourceAstro·2y

    Goodbye Studio, Hello DB

    Astro has decided to shut down its hosted database platform Astro Studio and focus on Astro DB, which now supports connecting to any libSQL database. Despite success in providing fast SQL data storage, Astro Studio didn't achieve product-market fit. Existing users will need to migrate by March 1, 2025. Astro DB continues to evolve, with enhanced open-source capabilities and potential future support for MySQL and Postgres databases.

  13. 13
    Article
    Avatar of baeldungBaeldung·2y

    Guide to @DynamicInsert in Spring Data JPA

    The @DynamicInsert annotation in Spring Data JPA optimizes insert operations by generating SQL statements that include only non-null fields, enhancing efficiency for entities with many nullable fields. However, it incurs runtime overhead and should be used selectively, especially when dealing with entities with default values or critical insert performance requirements. It may introduce complexity and inefficiencies in cases with mostly non-null fields, bulk insertions, or intricate database constraints.

  14. 14
    Article
    Avatar of vladmihalceaVlad Mihalcea·2y

    Eleven years of blogging

    The blog celebrates eleven years of existence, with 610 articles generating over 23.2 million views from nearly 12.5 million visitors. Highlights include the growth of social media followers, particularly on LinkedIn and X (Twitter), and the release of a High-Performance SQL video course and new modules for high-performance Spring Persistence training. Additionally, the Hypersistence Optimizer tool has seen multiple updates to support newer versions of Hibernate.

  15. 15
    Article
    Avatar of langchainLangChain·2y

    Building a Data Visualization Agent with LangGraph Cloud

    Explore how to build a data visualization agent using LangGraph Cloud. This guide explains the step-by-step workflow, including schema extraction, SQL query generation, and choosing appropriate visualizations. It demonstrates handling smaller datasets without complex techniques like RAG or LSH and provides code snippets for implementing various parts of the workflow.

  16. 16
    Article
    Avatar of baeldungBaeldung·2y

    How to Make a Field Optional in JPA Entity?

    When working with Spring Data and databases, it's sometimes necessary to make certain fields optional to improve performance and reduce overhead. This guide explores various techniques for achieving this, including projections, DTOs, @SqlResultSetMapping, and using Object or Tuple for native queries. Examples and tests demonstrate how to selectively fetch only the necessary fields from a table.

  17. 17
    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.

See all SQL archives