15 Jan

PostgreSQL – How to get the total index size used by each table in a database

PostgreSQL - How to get the total index size used by each table in a database

As per the documentation, To get the total size of all indexes attached to a table, you use the function. The pg_indexes_size() function accepts the OID or table name as the argument and returns the total disk space used by all indexes attached to that table.

We will use this funcion to get the index sizes of each table in the database.

16 Dec

Sri Lanka Holidays Calendar 2021 for office365, Google etc. in .ics format

Sri Lanka Holidays Calendar 2016 for Outlook & Google

Honouring everyone’s request I added the Sri Lankan Holiday calendar for 2021. This will allow you to add the Sri Lanka Holidays Calendar 2021 to your digital calendar (Most of the online digital calendars that we use these days don’t have inbuilt option to add the holiday calendar for Sri Lanka).

Adding to office365

  1. Add calendar
  2. From file (don’t go to the holiday calendar, Sri Lanka is not available there)
  3. Select file to upload and a calendar that holidays will be added (Create a separate new calendar is recommended. Use RED colour)
  4. Save
Read More
11 Nov

UVA 893 – Y3K Problem: Handling Python years above 9999

I was working on a problem in UVA online judge where I needed to do a simple data addition. However, the catch was year can go beyond 9999 (which is the limit in python). Below code is the python solution for this problem. I simply divided the date-delta with (1200 years, i.e. 438291 days) and added it separately after the computation. If you have unclear areas, let me know in the comments.

Read More
26 Oct

DBA bandits: Self-driving index tuning under ad-hoc, analytical workloads with safety guarantees

Abstract:

“Automating physical database design has remained a long-term interest in database research due to substantial performance gains afforded by optimised structures. Despite significant progress, a majority of today’s commercial solutions are highly manual, requiring offline invocation by database administrators (DBAs) who are expected to identify and supply representative training workloads. Unfortunately, the latest advancements like query stores provide only limited support for dynamic environments. This status quo is untenable: identifying representative static workloads is no longer realistic; and physical design tools remain susceptible to the query optimiser’s cost misestimates (stemming from unrealistic assumptions such as attribute value independence and uniformity of data distribution). We propose a self-driving approach to online index selection that eschews the DBA and query optimiser, and instead learns the benefits of viable structures through strategic exploration and direct performance observation. We view the problem as one of sequential decision making under uncertainty, specifically within the bandit learning setting. Multi-armed bandits balance exploration and exploitation to provably guarantee average performance that converges to a fixed policy that is optimal with perfect hindsight. Our comprehensive empirical results demonstrate up to 75% speed-up on shifting and ad-hoc workloads and 28% speed-up on static workloads compared against a state-of-the-art commercial tuning tool.” [1]

[1] Full Paper: https://arxiv.org/abs/2010.09208

23 Aug

CH-BenCHmark for MS SQL Server – HTAP benchmarking

There aren’t many benchmarks which allow you to test your systems against a hybrid OLTP and OLAP workloads. CH-BenCHmark fills that gap by combining TPC-C and TPC-H. You can download the source from the linked site or you can use something like OLTPBench (a collection of benchmarks). However, the TPC-H modified queries are not written for SQL server. In this article, I will add the modified CH-BenCHmark OLAP queries for SQL Server.

Read More
19 Jul

NetworkX visualization with Graphviz (Example)

NetworkX visualization with Graphviz (Example)

If you are trying to visualize a nice graph with NetworkX, you should be exhausted by now. After all, NetworkX only provides basic functionality for graph visualization. The main goal of NetworkX is to enable graph analysis. For everything other than basic visualization, it’s advisable to use a separate specialized library. In my case, I choose Graphviz. It’s simplistic to get an attractive visualization of a NetworkX graph with Graphviz. I’m taking a gradual start, but you may skip to “NetworkX with Graphviz” directly.

Read More
18 Jul

Index Physical Structure Example; Multi-column Non-Clustered Index with Includes

Structure of a non-clustered multi-column index with include columns.

This article demonstrates the physical design of a multi-column non-clustered index with include-columns. Many examples on the internet only demonstrate the most simple version of an index with a single column. This article gives a proper view of an index with multiple columns through a simple example. Furthermore, you can see how the include-columns are stored, only at the leaf level of the tree.

Here we use a simple table ‘People’ with 6 columns (ID, First Name, Last Name, Age, Sex, Address). We assume we already have a clustered index created on the ID column (it will be almost no difference if there is no clustered index as well, explained at the end). Now we are going to create the non-clustered index as defined below.

Below diagram shows the structure of this non-clustered index.

Structure of a non-clustered multi-column index with include columns.
Read More
27 May

PRIVATE: A Privacy-preserving Data Analysis Language.

This is a new project, I’m working on from early last year. The motivation behind this project is to build a programing language that allows users to analyze private data without exposing sensitive information. Many data analysis languages (R, Python, MATLAB etc.) in the current market assume direct access to data. PRIVATE, on the other hand, performs a privacy calculation that will make sure only non-sensitive information is released to the user.

More Information:

This is the tutorial series by Simon Dennis, Founder of PRIVATE

Contribute to PRIVATE: Git-hub

27 May

Water Bill Calculator – Sri Lanka

Most people got huge water bills after some time due to COVID-19. I wanted to double-check the calculation because the amount was somewhat big. Unlucky I didn’t find any online calculator that get the job done (there was a one in waterboard, but it has a maximum limit of 60 days). So I went with the default option, Excel. I thought of sharing the excel workbook I used as It might be helpful to others. I really don’t know how the VAT calculation is done, so I used 8%.

Read More