Ruminating about Relational Databases and Algorithms
When designing database schemas, you may find yourself in need of creating a property bag table that is in a 1-n relationship with an entity table. It isn’t always viable to just add more columns to a table because you may not know all the properties you want in advance. The problem with these property … Read more
When writing code for analytical databases, there comes a point where you learn that window aggregates often allow you to express complex joins in new ways. Sometimes, they even allow you to get rid of a join entirely. In this blog, we look at a clever way to look for next and previous values in … Read more
These days, columnar storage formats are getting a lot more attention in relational databases. Parquet, with its superior compression, is quickly taking over from CSV formats. SAP Hana, Vertica, Yellowbrick, Databricks, SQL Azure and many others all promote columnar representations as the best option for analytical workloads. It is not hard to see why. But … Read more
If you ever looked at query plan from a SQL databases – you are likely to have come into something called “cost”. You may even have heard that most advanced database use “cost based optimisers” But what exactly is cost and how does the database use it? SQL Query optimisation – Introduction The goal of … Read more
Most relational databases are both by human SQL query writers and tools that automatically generate queries. Query tools frequently add joins into the queries, even when those joins are not needed. This results in unnecessary query slowdowns and increase CPU usage. For example, consider the classic Star Schema model with a fact table in the … Read more