DEV Community: Aabishkar Wagle

What is database indexing and how it works

Aabishkar Wagle — Mon, 28 Sep 2020 17:06:49 +0000

Let's say you have a dictionary book containing hundreds of thousands of words. Now, you may need to find the meaning of a word, say 'tenebrous'. What will you do? Will you search from the first page until you find tenebrous, or will you lookup for 't' index in your dictionary book and skip all other words to find the meaning of 'tenebrous'.

Indexing in the database uses the same concept as indexing in a dictionary book.

Suppose you have a table called Persons in your database, which has millions of rows containing First and Last Names of millions of Persons. For instance, here's an illustration of a similar table:

id	first_name	last_name
1	Ahmed Ahmed	Khan
2	Sam	Curran
3	Sean	Jordan
4	Jeny	Stokes
5	Robert	Clarke
6	Patrick	Cummins
.	...	...
1000000	Luke	Wright

Now, without indexing, when you perform a query to search for the person with the first name 'Sean' then your SQL query will look up all the 1 million rows to perform this simple operation. Now, think about how expensive such a simple query can be when data scales to ten million rows. This will be even more expensive when you join another table with another million records.

Hence, to tackle this issue, the database index comes to help. What indexing will do is sets up the column in sorted order to help in optimizing query performance and searching in a large record.

For example, if you create an index for the first_name from the above Persons table, which can be done by following SQL query:

create index person_index on persons("first_name");

Then, it will create a separate index named person_index for you in your database and store first_name column of the Persons table in sorted order. Similarly, along with the first_name, the index will also store the memory address of its corresponding column, so that to lookup for the data from other columns of the same row when the search operation is performed.

Now when you search for a person name 'Sean' in a Persons table, then it will only lookup for the words matching 'S' and will skip other words, just like a human being will search in a dictionary. With this, unnecessary searches can be skipped and the database performance can be boosted.

I hope this helps somebody.
Thank you.

Configuring multiple kubeconfig on your machine

Aabishkar Wagle — Sun, 27 Sep 2020 17:14:21 +0000

While working with Kubernetes, probably the best way to access the Kubernetes cluster is by the using kubeconfig file provided by the cloud provider.

The kubeconfig file is a declarative YAML file which stores the configuration of a Kubernetes cluster. Basically, When you set up a Kubernetes cluster on a cloud, the cloud provider will provide you a kubeconfig file which helps you to get access to the cluster.

The kubeconfig files should be stored as KUBECONFIG in your bash_profile or else it needs to be under ~./kube/config folder if you are using Linux. This way, when you run kubectl get nodes or any other command then you will get output straight from the configured Kubernetes cluster to your terminal.

Another approach is to provide a kubeconfig path in every kubectl script you run. For example:

kubectl get nodes --kubeconfig={kubeconfig_path}

However when you have multiple Kubernetes cluster running on the cloud, then it can be a bit difficult to manage those kubeconfig files, since providing a kubecofig flag to every kubectl script you run can be clumsy, confusing, and difficult to maintain.

Hence, to solve this issue, we can use the Kubernetes context.

Suppose if you have multiple Kubernetes clusters running on the cloud and have different kubeconfig files then you can export all those kubeconfig files to the same KUBECONFIG variable, like below:

export KUBECONFIG=/home/aabishkar/Documents/kubernetes/kubeconfig/civo-google-microservice-kubeconfig:/home/aabishkar/Documents/kubernetes/kubeconfig/civo-kubernetes-practise-kubeconfig

Here, I have exported the kubeconfig file of two cluster name google-microservice and kubernetes-practice to the same KUBECONFIG variable.

Now, when I run the following command:

kubectl config get-contexts

This will give me contexts of both dev and prod environments.
Now to switch between different Kubernetes cluster, I just have to use a single command. For example to switch to a dev cluster:

kubectl config use-context google-microservice

This command will switch my cluster and I can now access the google-microservice cluster without providing the kubeconfig option to every kubectl script.

I hope this helps somebody.
Thank you for reading.