Larger large objects

Large objects (BLOBs) have been there since PostgreSQL was born. The size limit of a large objects has been 2GB(assuming default block size) . Now I decide to expand the limit for PostgreSQL 9.3: 4TB is the target. Actually PostgreSQL backend could hold up to 4TB large objects. It has been just limitation of API: for example, lo_lseek() and lo_tell cannot return over 2GB offset. Those function's return type is "int". So you might wonder why not they cannot return over 2GB value? Well, the secret is frontend/backend protocol for large object.

The underlying protocol is called "fast path interface". It's similar to RPC(Remote Procedure Call). Client sends "Function call" packet along with target function OID(Object Id). The called function within backend is executed and the result is returned back through "Function call response".

The functions called in large object interface are:

• lo_open
• lo_close
• lo_creat
• lo_create
• lo_unlink
• lo_lseek
• lo_tell
• lo_truncate
• loread
• lowrite

Those functions OIDs are retrieved from backend at the first time when accessing large objects and they are cached in the connection handle(I'm talking about libpq and other interfaces such as JDBC's implementation might be different).

Problem is, lo_lseek and lo_tell as I said earlier. First, their offset parameter is defined 4 bytes long. Second their result length is defined as 4 byte long. So we can handle only up 2^31-1 = 2GB. What shall we do? Well, we will add new function in backend namely, lo_lseek64 and lo_tell64. Libpq will check if those 64-bit functions exist. If yes, then use them. Otherwise (that means backend is likely pre-9.3 version) we use plain old 32-bit limited lo_lseek and lo_tell. This way, we do not break backward compatibility. Of course you need to use 9.3 libpq to enjoy "larger large objects".

I hope I'm going to post the first cut of patch by in September.

Pgpool-II talk at PostgreSQL Conference Europe 2012

I'm going to give a pgpool-II talk at upcoming PostgreSQL Conference Europe 2012. The talk is titled "Boosting performance and reliability by using pgpool-II" and I will explain how to enhance DB performance by using pgpool-II 3.2's "on memory query cache". Also I will explain how to set up "watchdog", which is also a new feature of pgpool-II 3.2. By using this, you can avoid SPOF(single point of failure) problem of pgpool-II itself without using extra HA software.

The conference will be held in Prague, the Czech Republic, October 23-26. I've never been to Prague, and am excited at this opportunity to visit the old beautiful city!

pgpool-II 3.2.0 is out!

After 11 month of development, finally pgpool-II 3.2.0 is out! Also a GUI management tool for pgpool-II 3.2.0(and before), pgpool-Admin 3.2.0 is released.

See  http://www.pgpool.net/pgpool-web/NEWS.txt for more details. I would like to thank everyone who have worked for making the release reality.

Also next week(Aug 6, 2012) we are going to release following stable releases:

• 3.1.4
• 3.0.8
• 2.3.4
• 2.2.8