PostgreSQL Cluster: “Too Many Projects” problem

“Too Many Projects” problem

The “too many projects” problem of PostgreSQL Cluster was raised and discussed in the PostgreSQL Clustering Developer Meeting at the JPUG Conference.

Nowadays, the PostgreSQL developer community has many clustering/replication projects, but many of them are not production ready. Though, new project is starting, and old project is still unmature.

I think this is one of the biggest challenges of the OpenSource development community.

Why happens this? How can we solve this, and get solutions mature?

Back to the OpenSource world

Finally, I’m back to the OpenSource world.

In 2004, I started working as a PostgreSQL developer and database engineer. While I was working as a PostgreSQL engineer, I was joining several PostgreSQL cluster projects, including Slony-II. Unfortunately, some were not happen, but it gave me great experiences.

At the same time, I was working as a director of Japan PostgreSQL Users Group (JPUG), and planned and run several community events such as JPUG conferences, camps and study groups. I was enjoying meeting with technical and non-technical people around PostgreSQL in Japan.

After a few years working as a PostgreSQL engineer, I moved to the datacenter business division in Oct.2007, and started working as a business developer of the datacenter services. It was very exciting to look for the market in the datacenter and cloud boom.

New Explain Format

Bruce has just added a new todo item.

- Allow EXPLAIN output to be more easily processed by scripts

I think the well-formed xml is a better format to express the tree structure of the explain output. I don't know xml can be processed easily by *scripts*, but it is easy for the utility programs using xml processing libraries. And xml elements can have many attributes which can be used to hold rows, cost and time parameters.

To switch between the human-readable format and xml format, we need another guc option; 'set explain_format = text' (of course, this is a default) and 'set explain_format = xml'.

I guess this is a good solution to provide explain information for the query utilities around PostgreSQL.

PL/Java 1.2.0 compilation error on PG8.0.0 or 8.0.1

According to the PL/Java release page, PL/Java 1.2.0 still has full support for 8.0.x.

However, PL/Java 1.2.0 can't be compiled with 8.0.0 or 8.0.1, because a number of arguments of DefineCustomIntVariable() has been changed between 8.0.1 and 8.0.2. So I got a following error on 8.0.1. PL/Java 1.2.0 can be compiled only on 8.0.2 or later.

Should it be fixed in future release?

gcc -c -Wno-long-long -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -fno-strict-aliasing -g -fpic -I"/usr/java/j2sdk1.4.2_04/include" -I"/usr/java/j2sdk1.4.2_04/include/linux" -DPKGLIBDIR=\"/home/snaga/pgsql801/lib\" -I/home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/include -I/home/snaga/PostgresForest/installer/build/pljava-1.2.0/build/jni -DPGSQL_MAJOR_VER=8 -DPGSQL_MINOR_VER=0 -I/home/snaga/pgsql801/include/server -I/home/snaga/pgsql801/include/internal -D_GNU_SOURCE /home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/pljava/Backend.c -o Backend.o
/home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/pljava/Backend.c: In function `initializeJavaVM':
/home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/pljava/Backend.c:567: warning: passing arg 6 of `DefineCustomIntVariable' makes pointer from integer without a cast
/home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/pljava/Backend.c:567: warning: passing arg 7 of `DefineCustomIntVariable' makes pointer from integer without a cast
/home/snaga/PostgresForest/installer/build/pljava-1.2.0/src/C/pljava/Backend.c:567: too many arguments to function `DefineCustomIntVariable'
make[1]: *** [Backend.o] Error 1
make[1]: Leaving directory `/home/snaga/PostgresForest/installer/build/pljava-1.2.0/build/objs'
make: *** [c_all] Error 2
{947}snaga@guatemala:~/PostgresForest/installer%

PCTFREE on Pg

I've done a quick hack for a space reservation in a page to use same page on tuple updating. It's similar to Oracle's PCTFREE stuff. heap_update() can use a (reserved) free space on the same page, but heap_insert() can't.
I allocated 1024 bytes for reserve space in each page, then it shows an improvement on the pgbench score 10% or more on my box.

-------------- normal -------------------
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 60.434280 (including connections establishing)
tps = 60.461362 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 57.350695 (including connections establishing)
tps = 57.371082 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 55.928602 (including connections establishing)
tps = 55.951602 (excluding connections establishing)

-------------- pctfree -------------------
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 67.552621 (including connections establishing)
tps = 67.586533 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 69.260082 (including connections establishing)
tps = 69.290771 (excluding connections establishing)
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 16
number of transactions per client: 1000
number of transactions actually processed: 16000/16000
tps = 69.466603 (including connections establishing)
tps = 69.497294 (excluding connections establishing)