Asheet Kumar

Postgres Indexes and Their Types

| Comments

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. Without index system would scan entire table row by row to find all the matching records. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

Following command can be used to create index on one or more coulmns of a table.

1
CREATE INDEX index-name ON table-name (column-names);

While creation of index only by default postgres allows only reads to occur on the table. writes(INSERT, UPDATE, DELETE) are blocked. If Index creation takes longer time then it is often unacceptable. Indexes can be created concurently without blocking writes.

Batch Upserts in Child Tables of a Partition Set Created Using Pg Partman

| Comments

This post is in continuation to prev post. This post deals with problem of batch upserts in different child tables of same partition set created using pg_partman.

This uses ON CONFLICT clause. This clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. The create_parent() function in partman schema allows us to specify the alternative action that ON CONFLICT clause will use in case when insertion returns a violation error.

Specify an action for ON CONFLICT while creating partition set.

following function creates partition set as in prev post, but here an action is passed for ON CONFLICT to handle the case of unique constraint violation.

1
2
3
4
5
6
7
select partman.create_parent(p_parent_table := 'public.users', 
  p_control := 'created_at', 
  p_type := 'partman', 
  p_interval := 'daily', 
  p_automatic_maintenance := 'on',
  p_upsert := 'ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, last_name = EXCLUDED.last_name, updated_at = EXCLUDED.updated_at',
  p_debug := TRUE);

Time Based Table Partitioning Using Pg Partman

| Comments

Table partitioning is splitting of a large table in smaller tables based on values of some attribute in the table. This post is about partitioning tables using pg_partman. It provides following two types of table partitioning,

  • Time based table partitioning
  • Serial id based partioning. Follow below sections to do time-based table partitioning. I will be using postgres database to describe the example.

Installing and setting up pg_partman

  • run following commands to install pg_partman.
1
2
3
4
git clone https://github.com/keithf4/pg_partman
cd pg_partman
make install
make NO_BGW=1 install 

Setting Up Vim for Golang

| Comments

I have been using vim for more than three months. Now i do most of my editing on vim. In this post i shall be describing about setting up vim for golang from scratch. Vim config that i shall be posting here is available here. I play with my vim configuartion frequently to increase productivity and improve my experience with vim. Follow below sections to setup your vim.

Set up a plugin manager

There are many available plugin managers like pathogen, vim-plug, vim packages etc. I use pathogen.

  • Run following command to install pathogen.vim.
1
2
mkdir -p ~/.vim/autoload ~/.vim/bundle && \
curl -LSso ~/.vim/autoload/pathogen.vim https://tpo.pe/pathogen.vim
  • Create a .vimrc file in home directory.
1
2
cd ~
touch .vimrc
  • Add following lines in .vimrc file.
1
2
3
execute pathogen#infect()
syntax on
filetype plugin indent on