PostgreSQL - MC

PostgreSQL #7- Tuning Part 1 It`s been a while since my last post (to be accurate half a year) but I'm back and I got a lot of things to talk about. In the next few posts I decided to focus on Tuning. Performance issues are something that everyone has encountered in some way. It can be a very slow application, a query that is running for a long time and even a very slow server. I think that in the next chapters I will focus on some advanced topics.  If you are asking yourself some of the next questions then I think that this post and the next ones might be useful for you :  -Where should we start from ? -Is it a database issue or an application issue ?  -On what part of the database should we focus ? -What is an execution plan and how can I understand it ? -What are indexes and when to use them ? What type of indexes there are ? -Why the database doesn't use an index that I created ? -Does the order of the columns in the index has any meanin...

PostgreSQL #6- Backup and Restore What do you do if your data is lost ? Are you ready for this kind of scenario ? Every database that exists and even PostgreSQL in sepecific might crush(power outage,physical damage to the server and so on.). As a result of that you might lose crucial/non crucial data . In both cases, if you dont have any backup of your database you have a BIG problem. If your PostgreSQL cluster is after a crush and you start the instance it will try to recover the data with the transaction log or with the wal files. The wal files are the files that contains all the changes since the last checkpoint and they are saved in the pg_xlog directory(pg_wals in PostgreSQL 10). The instance will try to roll forward the wals in order to save/restore the data that was lost. When the database start rolling forward the wals it start point is the last checkpoint. I'm pretty sure that I talked about it in the last posts but think about it as the last saving point tha...

PostgreSQL #5 - Roles&Schemas In the last post I described the physical layer(files on disk) of PostgreSQL db. From now on everything that we will talk about can be explained because you already now the physical and logical structure of PostgreSQL. In this post I'm gonna focus on roles and schemas. The topics that I am going to focus on this post are:  -What are roles and schemas? -How can I create each one of them ? -How can I manage them ? -What is search path and how we configure it ? - Priviledges -Important schemas Roles The official and most clear defenition of a role is that a role is an entity that can own database objects and has database priviledges (- PostgreSQL 9.6 documentation ). A role can be a specific user or a group or even sometimes it can both, depends on how you use it. Roles are defined on the cluster level and therefore they are available in all the databases in our cluster.  You can get a list of all the roles in ...