Using PostgreSQL logical replication on Docker

1. Prereuities OS: CentOS7PostgreSQL: PostgreSQL13.0Docker version 20.10 2. What I want to set up Publisher: container postgres13-server1, database testlrep, replication user repuser, table t1Subscriber: container postgres13-server2, database testlrep, table t1 3. Creating Docker containers (1) Install Docker and Docker images of PostgreSQL13 according to e.g. [1] (2) Creating Docker containers by the following commands docker […]

Using PostgreSQL on Docker

1. Prerequities OS: CentOS7PostgreSQL: PostgreSQL13Docker version 20.10 2. Setup Docker (1) Install docker engine performing the following commands sudo yum install -y yum-utils sudo yum-config-manager –add-repo sudo yum makecache fast sudo yum install docker-ce systemctl enable docker (2) Check whether docker engine runs sample container or not docker run hello-world docker stop hello-world 3. […]

Looking up PostgreSQL internal related to performance of inserting big data

1. Introduction Response time of inserting big data(e.g 10GB~) is often longer than one of writing that data into storage. I looked up the cause of this difference by reading PostgreSQL source and performing simple experiment. 2. Prerequities Version of PostgreSQL: PostgreSQL13.0Experimental Setting: CentOS7, PC spec: Ryzen 7 3700X 3.6GHz 8Cores, 128GB memory, SSD M.2 […]

How to build and debug PostgreSQL using MinGW

1. Install PostgreSQL using MSYS2 and MinGW (1) Download MSYS2 installer from the following link and install along with the installer Add path to D:\msys2-64\mingw64\bin. (3) Start MSYS2 by running the following command in command prompt D:\msys2-64\msys2_shell.cmd -mingw64 (4) Install build tools and debug tool by running the following commands in MSYS2 pacman -Syupacman –needed […]

How to find operators accelarated by JIT in PostgreSQL

1. Introduction PostgreSQL can apply Just-in-time(JIT) compilation to some operations to convert general purpose code to native code. So PostgreSQL JIT can acclarate CPU-bound query. 2. What I looked up (1) How to use PostgreSQL JIT (2) Effect of PostgreSQL JIT(3) How to find operators accelarated by PostgreSQL JIT 3. Prerequisites Version of PostgreSQL: PostgreSQL13.0OS: […]

Effect of PostgreSQL buffer cache for read-only query for large data in PostgreSQL

1. Introduction In OLAP(Online Analytical Processing) workload, PostgreSQL server often reads large data from one table. So buffer cache for table containing large data is effective. In this short article, I show what I found on effect of buffer cache for accelarating query which reads large data from one table in PostgreSQL. 2. Prerequisites Version […]

How to do linux debugging PostgreSQL remotely using Visual Studio Code

1. Prerequisites Version of PostgreSQL: PostgreSQL13 Target Machine: CentOS7 Host Machine: Windows10 2. Preparation in target machine Hereinafter we assume your username is postgres. Perform the follwing steps in target machine according to [1] (1) Download sources of PostgreSQL from official website and change directory wget -xzvf ./postgresql-13.0.tar.gzcd ./postgresql-13.0 (2) Install optional softwares as […]

How to find limits of GUC parameter in PostgreSQL

1. Introduction PostgreSQL has many configuration parameters which control server behavior, so called GUC parameter. For example, work_mem control max amount of memory which is used postgres server when hash join, etc. are executed. There are no description upper limit or lower limit of some GUC parameters in PostgreSQL official document. I know there are […]