On 2024-02-11 13:25:10 +0530, veem v wrote:
> On Sun, 11 Feb 2024 at 05:55, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
> Yes. Numbers in Oracle are variable length, so most Oracle tables
> wouldn't contain many fixed length columns. In PostgreSQL must numeric
> types are fixed length, so you'll have quite a lot of them.
>
>
>
> So it means , say in other databases like (oracle database), we were careless
> choosing the data length , say for example Varchar2(4000), if the real data
> which is inserted into the table holds a varchar string of length 20 bytes then
> Oracle trimmed it to occupy the 20 bytes length only in the storage. but in
> postgre here we need to be cautious and define the length as what the data
> attribute can max contains , because that amount of fixed space is allocated
> to every value which is inserted into the table for that attribute/data
> element.
No. Varchar is (as the name implies) a variable length data type and
both Oracle and PostgreSQL store only the actual value plus some length
indicator in the database. Indeed, in PostgreSQL you don't need to
specify the maximum length at all.
However, if you specify a column as "integer" in PostgreSQL it will
always occupy 4 bytes, whether you store the value 15 in it or
999999999. In Oracle, there is no "integer" type and the roughly
equivalent number(10,0) is actually a variable length floating point
number. So 15 will occupy only 3 bytes and 999999999 will occupy 7
bytes[1].
> Similarly for Number/Numeric data type.
Number in Oracle and numeric in PostgreSQL are variable length types.
But in PostgreSQL you also have a lot of fixed length numeric types
(from boolean to bigint as well as float4 and float8) and you would
normally prefer those over numeric (unless you really need a decimal or
very long type). So padding is something you would encounter in a
typical PostgreSQL database while it just wouldn't happen in a typical
Oracle database.
But as Laurenz wrote that's a micro optimization which you usually
shouldn't spend much time on. OTOH, if you have hundreds of columns in a
table, maybe it is worthwhile to spend some time on analyzing access
patterns and optimizing the data layout.
hp
[1] From memory. I may be misremembering the details.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
