[postgres@stagdb pg_dynshmem]$ ls -lrt total 8 -rw——- 1 postgres postgres 6928 Feb 17 16:36 mmap.2106693104 [postgres@stagdb pg_dynshmem]$ pwd /u01/pgsql/10/pg_dynshmem There is a file in pg_dynshmem sub-directory under PostgreSQL data directory. But what is that file? The answer is here, But before we understand that, we will have to understand some basics. If you want to … Read more
Media failure is one of the crucial things that the database administrator should be aware of. Media failure is nothing but a physical problem reading or writing to files on the storage medium. A typical example of media failure is a disk head crash, which causes the loss of all files on a disk drive. … Read more
In this post we are going to understand PostgreSQL Page Layout in detail. Bonus is coming up towards the end!! Do I have ORACLE’s PCTFREE and PCTUSED kind of mechanism in PostgreSQL? PostgreSQL Page Layout Each data file in PostgreSQL is divided into PostgreSQL disk blocks of 8 Kb each.The blocks are numbered sequentially, 0 … Read more
This post answers the following question with comprehensive details. How much memory you need to give to your shared buffers in PostgreSQL and why? Bonus!! Why my RDS postgreSQL shared buffers uses 25% of system RAM where as Aurora’s shared buffers is 75%? The answer is here. Understanding OS Cache vs Shared Buffers in PostgreSQL … Read more
PostgreSQL Autovacuum In the last post, we understood that PostgreSQL Vacuum helps in clearing the dead tuples in the table and releasing the space, but how often the vacuum happens on a table?PostgreSQL Autovacuum helps here!! Vacuum can be initiated manually and it can be automated using the autovacuum daemon. By default, autovacuum is enabled in PostgreSQL. The … Read more
PostgreSQL vacuum: Introduction: PostgreSQL vacuum is an important topic in PostgreSQL database administration. Before we get into PostgreSQL vacuum we first have to understand MVCC architecture in PostgreSQL. In a multi-user database management system, a transaction must support the following two properties Data concurrency: means that many users can access data at the same … Read more
PostgreSQL Checkpoint: Database blocks are temporarily stored in Database shared buffers. As blocks are read, they are stored in DB shared buffers so that if any user accesses them later, they are available in memory and need not be read from the disk. When we update any row, the buffer in DB shared buffers corresponding … Read more
In this post, we are going to learn about the operating system kernel parameters, shared memory, and semaphores. But why? As we all know, PostgreSQL highly interacts with the operating system parameters for the operations that it does on the database. Understanding the operating system resource limits is one of the important things for a … Read more
Happy day everyone!! As we all know, PostgreSQL highly interacts with the operating system for the operations that it does on the database. Now, before we deep dive into the nitty gritty of the case study that I have done with the Operating System’s “system calls” and “kill signals” on PostgreSQL background processes, let’s understand … Read more