Re: How to do faster DML

On 2024-02-06 11:25:05 +0530, veem v wrote:
> With respect to the storage part:- In Oracle database we were supposed to keep
> the frequently accessed column first and less frequently accessed columns
> towards last in the table while creating table DDL. This used to help the query
> performance while accessing those columns as it's a row store database. Are
> there any such performance implications exists in postgres? And there the data
> types like varchar used to only occupy the space which the real data/value
> contains. 
> 
> But here in postgres, it seems a bit different as it has significant
> overhead in storage, as these seem like being fixed length data types and will
> occupy the space no matter what the read data holds.

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 curious to know, if in this case will there be any performance
> impact accessing those columns, if such a column which is frequently
> accessed but has been put towards the end of the table because of
> storage space saving?

Yes. PostgreSQL has to skip over each column until it gets to the column
it wants to read. So reading a column at the end of the row will be
slower than reading one at the start. A very simplistic benchmark (100
columns of type text each containing a single character) suggests that
accessing column 100 takes about 4 or 5 times as long as column 1, and
the access times for the coiumns between are pretty linear.

So there's a bit of a tradeoff between minimizing alignment overhead and
arranging columns for fastest access.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"