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 .
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
In this Blog we will talk about the answers of all these .
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 .
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…
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
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:
on start of any function call
on every function call
before every return statement
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:
In Debugging any issue or any dealing any problem or circumstance two things are important
In this Blog , I will explain the following :
From here we…
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 .
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.
I had to resolve this issue and find the root cause of it.
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…