Top Postgresql queries to checkup on database performance

These are the list of PostgreSQL queries which help me a lot over the time to troubleshoot problems with the database.

1. Find out which queries running currently

SELECT 
  pid, client_addr, query, state 
FROM pg_stat_activity;

2. Table access stat
This query is really helpful when you want to find out which table is most frequent accessed and by which way. Ex: sequence scan or index scan.

SELECT 
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct
FROM pg_stat_user_tables WHERE (idx_scan + seq_scan) > 0
ORDER BY idx_scan_pct;

3. Find out missing indexes
Well, index is one of the most crucial thing which make the database fast or slow. So check for missing indexes are super important. And we can doing that with simple query like below.

SELECT 
  relname, 
  seq_scan-idx_scan AS too_much_seq, 
  case when seq_scan-idx_scan> 0 THEN 'Missing Index?' ELSE 'OK' END,    
  pg_relation_size(relname::regclass) AS rel_size, 
  seq_scan, 
  idx_scan
FROM pg_stat_all_tables
WHERE schemaname='public' AND pg_relation_size(relname::regclass) > 80000 
ORDER BY too_much_seq DESC;

4. Find out unused indexes
Well, indexes are great however over indexing will slow the database down. So how we know that we’re over indexing. Ok, we can use the following query.

SELECT
  relid::regclass AS table,
  indexrelid::regclass AS index,
  pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
  idx_tup_read,
  idx_tup_fetch,
  idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0;

5. Find out duplicate indexes
Over indexing is one of the problem, and duplicate index also another problem. We can find that out with the following query.

SELECT 
  pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
  (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
  (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (SELECT 
        indexrelid::regclass AS idx, 
        (indrelid::text ||E'\n' || indclass::text ||E'\n'|| indkey::text ||E'\n'||
         coalesce(indexprs::text,'') || E'\n' || coalesce(indpred::text,'')) AS KEY
      FROM pg_index) sub
GROUP BY KEY HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;

6. Find out tables size
Checkup on table size is also important so we can know which table is largest and we can take some action such as: archiving or truncating the table.

SELECT 
  relname as "Table",
  pg_size_pretty(pg_relation_size(relid)) As " Table Size",
  pg_size_pretty(pg_total_relation_size(relid) -
  pg_relation_size(relid)) as "Index Size"
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Actually there’re more ways help to monitor the performance of the database. However those queries I find most useful for my case. So hope it’s also helpful to you.

Top Postgresql queries to checkup on database performance

How did I upgrade Rails 4.1.7 to Rails 4.2.4

I recently upgraded our Rails 4.1.7 codebase to the latest stable version of Rails 4.2.4. Since our codebase is quite large. We use lot of different gems. So the upgrade would definitely break lot of things. So I decide to do it step by step.

1. Upgrade to Rails 4.1.9

This step was quite easy for me. By changing the Rails version only in the Gemfile. And fix any dependencies conflict if any. And all specs was green for me. So all good, now ready for Rails 4.2.0

2. Upgrade to Rails 4.2.0

This is the important part. And I followed all the notes from here http://edgeguides.rubyonrails.org/upgrading_ruby_on_rails.html#upgrading-from-rails-4-1-to-rails-4-2. And yeah, it failed nearly of the unit tests (controllers/presenters/models/libs/..). In this step I only fix dependencies conflict. And got the bundle install done. I just ignored all those failed tests. I got a feeling that it might break again when I upgrade to 4.2.4. So I hold on the that until 4.2.4

3. Upgrade to Rails 4.2.4

Ok. So this is the final step. I planed to remove all the deprecated warnings. Of course after resolving all the dependencies conflicts. Updating all the gems which require >= Rails 4.2.x. Rails 4.2.4 actually changes many thing that not mentioned in the official documentation. Here’re couple of them:

  • exists?(object) -> deprecated. Solution exists?(id)
  • Arel::SqlLiteral.new -> undefined object. Solution Arel::Nodes::SqlLiteral.new
  • belongs_to :objects, class: AnotherObject -> doesn’t exist class option anymore. Solution belongs_to :objects, class_name: ‘AnotherObject’
  • where_values -> doesn’t work as expected like in the old version. This method not recommend to use. Solution: in my case I choose to use Arel instead to achieve the same result. 
  • deliver -> This method will be deprecated in Rails 5. However I resolve it anyway by using deliver_now or deliver_later
  • And there’re many more small things come up. All I needed to do is solve them one by one. Until I got all the unit tests pass. In case you don’t have the unit tests. Then you pretty mess up.

Final words: Upgrading Rails 4.2.4 was not so difficult (in my case). Just require little of patient and debugging skill. The errors or breaks might different from each codebase. It depends on the gems you uses and the code also. So keep calm and upgrading Rails.

4. Couple of links that I found useful

How did I upgrade Rails 4.1.7 to Rails 4.2.4

Why I think Elixir is one of the future programming language?

I recently has very much interested in a programming language called Elixir. I heard about Elixir more than a year ago. But I never took a chance to look detail into it until recently. My impression to it is small, simple, and powerful language.

So I decided to give it a chance by solving Elixir programming challenging on Exercism. By solving those problems I gradually learn Elixir syntax, building block, and feel the language. I actually like it alot. Even though it’s functional programming language but the syntax really friendly, not like Haskell or Clojure. Elixir actually like Ruby a lot. Since I got strong Ruby background so this one big thumbup for me.

Anyway, here is what I think Elixir can be one of the future programming language.

1. It’s powerful language

It’s a concurrent language built on top of Erlang Virtual Machine. Which mean it will carries all the power from Erlang. Even though I haven’t use any of those thing in real project yet. But this is a huge promise.

2. The syntax is feasible 

Unlike many other functional programming languages the syntax really hard to wrap in mind. Elixir provides nice, clean, and really compact syntax. Which super great for me. The simpler the better.

3. Great toolbox

Great language is not enough for any real projects. It must come with extra tools for developing and debugging. And yes, Elixir got a lot of those kind of tools. Many of them come with standard libraries. For example: `iex` the interactive console, very much similar to `irb` in Ruby. And `Elixir Pry` for debugging also similar to `Pry` in Ruby/Rails, and so much more.

4. Great community

This is one of the most important thing for any open source project – Community. Without the support of the community no software can exist for long time. Elixir community is getting more momentum everyday. By looking at the open source projects written in Elixir on Github. And the Elixir meetup around the world.

Final thought, I got very positive feeling about the future of Elixir. And decided it become one of my main programming languages arsenal.

# My solution for Elixir problems on Exercism.io https://github.com/kimquy/elixir-exercies

Why I think Elixir is one of the future programming language?