Geek Logbook

Tech sea log book

Recent Posts

Categories

#50projectsIn50days Airflow Amazon Web Services AWS Azure Batch CAP Coders At Work cron Data Databases Data Warehouse Django Edsger W. Dijkstra Excel Git Google Hadoop Hellerstein Hive Javascript Knowledge Transfer MVC Network Address Translation Networking Notes on Structured programming PHP Power Bi Powershell Public Subnet Python Redash Social Skills Software Developer Spark Spark: The Definitive Guide SQL SSH SurrogateKeys Testing The Elements of Programming Style Traversy Media Understanding Distributed Systems whale-alert Yaml

Archives

Category: Programming

Different posts about programming in general

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

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

Splitting Strings in Excel: A Simple Guide

When working with Excel, you may encounter situations where you need to split a string into separate parts. For example, consider the following string: If you want to separate orderId and the actual ID value, there are several methods available depending on your Excel version. Method 1: Using TEXTSPLIT (Excel 365/2021) If you’re using the
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

Optimizing Queries with Partitioning in Databricks

Partitioning is a crucial optimization technique in big data environments like Databricks. By partitioning datasets, we can significantly improve query performance and reduce computation time. This post will walk through an exercise on partitioning data in Databricks, using a real-world dataset. Exercise: Managing Partitions in Databricks Objective Step 1: Load Data into Databricks For this
Read More

Calculating Levenshtein Distance in Apache Spark Using a UDF

When working with text data in big data environments, measuring the similarity between strings can be essential. One of the most commonly used metrics for this is the Levenshtein distance, which calculates the number of insertions, deletions, and substitutions required to transform one string into another. In this post, we’ll demonstrate how to implement a
Read More