Geek Logbook

Tech sea log book

Recent Posts

Categories

#50projectsIn50days Airflow Amazon Web Services Apache Iceberg AWS Azure bash bastion host Batch CAP Coders At Work Data data lakes Data Warehouse Docker Edsger W. Dijkstra Excel Git Hadoop HDFS Hellerstein Hive Javascript Networking NoSql Power Bi Powershell Python Redash S3 Social Skills Software Developer Spark Spark: The Definitive Guide SQL SSH terraform Testing The Elements of Programming Style Traversy Media Ubuntu Understanding Distributed Systems Virtual Box whale-alert Yaml

Archives

Tag: SQL

Querying JSONB in PostgreSQL Efficiently

In modern applications, it is common to store semi-structured data in JSON format inside a relational database like PostgreSQL. However, to analyze this data properly, you need a way to transform it into a tabular structure that can be queried with standard SQL. In this article, we will demonstrate a real-world example of reading a
Read More

Understanding Window Functions in SQL: Beyond Simple Aggregations

When we think about SQL functions, we often start with scalar functions (UPPER(), ROUND(), NOW()) or aggregate functions (SUM(), AVG(), COUNT()). But there is a third type that is essential for advanced analytics: window functions. The “Window”: The Metaphor Behind the Concept A window function is evaluated for every row, but not in isolation —
Read More

he Enduring Relevance of Peter Chen’s Entity-Relationship Model

In the landscape of data modeling, few contributions have had the long-lasting impact of Peter Chen’s Entity-Relationship (E-R) Model, introduced in 1976. More than four decades later, it remains a foundational framework for conceptualizing and designing data systems—bridging the gap between abstract business understanding and concrete database implementation. A Unified View of Data Chen’s model
Read More

How Joins Work in PostgreSQL

Joins are one of the most powerful features in SQL, allowing you to combine data from multiple tables in a single query. PostgreSQL, as a relational database system, provides robust support for different types of joins. Understanding how joins work under the hood helps you write more efficient queries and troubleshoot performance issues. What Is
Read More

How to Improve Query Performance in PostgreSQL

PostgreSQL is a powerful relational database, but even the most robust systems can suffer from slow queries without proper tuning. Optimizing query performance is crucial to ensure scalability, responsiveness, and efficient resource usage. In this post, we’ll explore actionable techniques to speed up your PostgreSQL queries. 1. Use Indexes Effectively Create Indexes on Filter and
Read More

Optimizing Joins in PostgreSQL: Practical Cases

Joins are essential for querying relational databases, but they can significantly impact performance if not optimized correctly. PostgreSQL provides several ways to improve join efficiency, from indexing strategies to query restructuring. In this post, we’ll explore different types of joins, performance considerations, and practical ways to optimize them. Types of Joins in PostgreSQL PostgreSQL supports
Read More

Visualizing EXPLAIN ANALYZE in PostgreSQL

When working with PostgreSQL, understanding how queries execute can greatly improve performance tuning and optimization. PostgreSQL provides the EXPLAIN ANALYZE command to help developers analyze query execution plans. However, reading raw execution plans can be challenging. In this post, we will explore how to interpret EXPLAIN ANALYZE output and visualize it using online tools. Understanding
Read More

Tracking Daily File Size Changes in SQL

When working with databases that store file metadata, it’s often useful to track how file sizes change over time. If you have a table with the following structure: You may want to analyze the day-to-day changes in file size. This can help in monitoring storage usage, detecting anomalies, or understanding file growth trends. SQL Query
Read More

Merging Data in PostgreSQL vs. MySQL: How to Handle Upserts

When working with databases, you often need to update existing records or insert new ones based on whether a match is found. In PostgreSQL, this is efficiently handled using the MERGE statement. However, MySQL does not support MERGE, so alternative approaches must be used. This post explores how to achieve the same functionality in MySQL.
Read More

How to Simulate Column Headers Without Selecting from a Table in SQL

In some cases, you may want to produce a result set with specified column names and values without querying an actual table. This is often used for testing purposes, documentation, or even when preparing expected structures for applications that expect specific column headers. Here’s how to do it effectively. Sample Query: Returning Named Columns Without
Read More