In our last blog we learned about Need and Usage of Hypothetical indexes in Postgresql . Now we can check easily in live environment also if some particular index will be helpful or not , but how we get to know which index to test . It requires in-depth knowledge of indexing and experience in Postgresql , But in PostgreSql we can get automatic recommendation of indexes for specific queries by using three extensions hypog,pg_stat_statements,pg_qualstats .

Now Let’s move directly to the practical part how we can use this feature in postgres :

i am doing experiments on Postgres10 installed…


In this Blog We will first cover what are hypothetical indexes and what is need of these type of indexes and then secondly we shall see the practical usage of these indexes .

Need of Hypothetical Indexes :

As the name suggests these are not real indexes, these are hypothetical indexes i.e.. They are virtual indexes which PostgreSQL query planner does not consider when running queries .

Now the question arises where these Hypothetical Indexes are useful ? ? ?

First Let’s discuss one scenario , we have a large table which is currently in the production environment and we need to make some index…


Regarding Load Average shown in Linux there are many confusion around the world like

  • What is load average show in top command ?
  • What this values represent ?
  • When it will be high when low ?
  • When to consider it as critical ?
  • In which scenarios it can increase ?

In this Blog we will talk about the answers of all these .


Introduction

In my previous Article i explained Tuning PostgreSQL Database Memory Configuration Parameters to Optimize Performance and as i said Database performance does not only depend on Postgresql configurations but also on system parameters .Poorly configured OS kernel parameters can cause degradation in database server performance. Therefore, it is imperative that these parameters are configured according to the database server and its workload. In this article i will be talking about centos/redhat linux system specially .

Story

I will start the article with small story where on one of our client huge amount of writes were there and customer have provided us…


Introduction

Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. Default values are set to ensure that PostgreSQL runs everywhere, with the least resources it can consume and so that it doesn’t cause any vulnerabilities. It has default settings for all of the database parameters. It is primarily the responsibility of the database administrator or developer to tune PostgreSQL according to their system’s workload. In this blog, we will establish basic guidelines for setting PostgreSQL database parameters to improve database performance according to workload.

Bear in mind that while optimizing PostgreSQL server configuration improves performance…


As in my previous Blog where i explained how i came to know if my system is hacked or compromized ( link here). Here in this blog i will explain what basic things we can check on our system when we have doubt if our system is compromized .

This Blogs have 3 parts

  • List of Checks which can determine if system is compromised or hacked — Part 1
  • List of checks which can give a direction how system is compromised or hacked — Part 2
  • What preventive steps (specially infra related) can be taken care to avoid hacking or…

We as a developers understand the importance of coding but most of us do not know the right way to log.

While seeing others code you must have seen various patterns of logging:

  • Log everything

on start of any function call

on every function call

before every return statement

  • Don’t log anything
  • Log only Exceptions

I think everybody must have seen all type of developers, lets understand when to log and also in diff. scenarios , but before that first see the purpose of logging:

Purpose of Logging

  • Provides insights about the flow of your code, what happen , what are the parameters…

Introduction :

In Debugging any issue or any dealing any problem or circumstance two things are important

  • Observation — Observation not only at the time of issue but in general times also .
  • Combining your general observations and Observations at the time of issue to conclude something .

In this Blog , I will explain the following :

  • What was happening on my machine
  • How i came to know my machine is broken into — Power of observation

What was happening on my machine

  • Load on my machine is going very high
  • On top command one process ./kwsapd0 is consuming around 3000% cpu

From here we…


Introduction

There are many types of queries and of course all are important , but when it comes to monitoring and debugging , we are generally in a hurry and do not remember what to query to check the problem/issue though . We at that time thinks what’s can we find and what will be the query for this , we then search on internet all somehow make some queries and then after issue we forgot . Here in this Bog i will brief some queries and its uses specially in case of Monitoring and Debugging Postgresql Related issue .

In…


In this article, I will explain some consequences of session leaks, which I faced when an issue came to me.

Session Leak is very common in developers’ lives. In this article, I will explain some consequences of session leaks, which I faced when an issue came to me and how I came to the root cause of the issue.

Issue and Analysis

Issue: Load is increasing on the server and Postgres queries are consuming CPU and taking time.

I had to resolve this issue and find the root cause of it.

Analysis: After all my debugging, I came to the following analysis:

There is a table that has 200 rows, but the number of live tuples showing there is more than that (around 60K). We are using Postgresql 9.3.

The following are the queries that I ran.

select count(*) from subscriber_offset_manager;count
-------
200 (1 row)
SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store