0

In PostgreSQL there is alignment padding of various datatypes which can increase (or decrease) the size of records (as stored on disk) in varying ways (see these links, 1, 2, 3):

I searched for "Oracle alignement padding" (and also for SQL Server, MySQL and FirebirdSQL) and various combinations of these words and came up with one link of interest for Oracle (C struct related). For the other systems I found nothing of interest (mostly stuff like LPAD()/RPAD() or other presentation related material).

I did find this by Eric Raymond - generic C struct material again.

http://www.catb.org/esr/structure-packing/ which is also about C structs

Note: "should generalize to any compiled language with C-like structures." (end of section 1. - Who should read this.)

For a shorter read on the same topic, see here.

Providing a "behind the scenes" remedy has been discussed (logical vs. physical order) on the PostgreSQL lists but has gone nowhere - see the discussions here and also this answer.

So, as I understand it, this alignment issue is fundamental to C/C++ (and all compiled languages with structs - esr) and to the best of my knowledge, the kernels of all the systems I searched for are written in various combinations of C/C++.

However, only PostgreSQL appears to be discussing the issue but not other systems.

My question is that I'm wondering have all the other mainstream systems have implemented some sort of column rearrangement behind the scenes?

I'd be interested in hearing how other systems have mitigated any problems caused by this if they haven't done some sort of column rearranging?

EDIT:

On searching further, I have found these two posts (1, 2) about marshalling of C structs (which, if I've understood correctly) are the way that records move from disk to memory and back - the similarity between a C struct and a record structure being reasonably obvious.

So, and this is again AIUI, you can either store C structs (or records) in a sort of way that directly maps to the processor memory alignment requirements (and "wastes" space) OR the struct can be marshalled (or serialised - v. similar) as a bit (or byte) stream and stored that way (less "waste" but more processor intensive).

But this poses a further problem!

The developers of PostgreSQL are very adept at C programming and would know about these techniques - so why would they not have done what (apparently AFAICS) other systems have done and removed this overhead?

1 Answer 1

0

Oracle uses of proprietary BCD encoding for numbers, and also uses proprietary own arithmetic implementation. So results are identical on every CPU platform.

Oracle's number can have up to 30 valid decimal digits, each digit occupies one nibble(half byte), plus two bytes for mantissa. Oracle's number occupies variable length, like a string on Pascal, 1st byte length, then 2 bytes mantissa and then array of decimal digits.

See this article

In case of Oracle any conversion usually takes place on client's side. You have to call OCINumbeToInt or similar to convert OCINumber into int32/int64 or string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.