Re: How to do faster DML

On Tue, 13 Feb 2024 at 20:32, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> Please do not conflate "char(n)" with native machine types like int or
> float. These are very different things. A char(n) is string of fixed but
> arbitrary length. This is not something a CPU can process in a single
> instruction. It has to go over it character by character.
>
> There is almost never a reason to use char(n). Just use varchar(n) or in
> the case of PostgreSQL just varchar or text.
>
> > However I do see even in Oracle databases, we have Integer type too,
>
> Not really. INTEGER is just an alias for NUMBER(38) in Oracle (see for
> example
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlqr/Data-Types.html
> ).
> It's not the same as an INTEGER in PostgreSQL.
>
>
Thank you so much for the clarification.

Basically as i understood, we can follow below steps in serial,

Step-1)First wherever possible use Smallint,Integer,bigint,float data types
rather than numeric. This will give better performance.

Step-2)Use the frequently queried columns first and least frequently
queried columns towards last in the row while creating the table. This is
too intended for better performance.

Step-3)Define the columns with typlen desc as per below formula( column
tetris symptom). This is for better storage space utilization.

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'user_order'
AND a.attnum >= 0
ORDER BY t.typlen DESC;

One question here, if we have defined one column as a fixed length data
type "integer" and slowly we noticed the length of data keeps increasing
(in case of a sequence generated PK column which will keep increasing), and
we want to alter the column to "bigint" now. In such scenario, will it
append/pad the additional spaces to all the existing values which were
already stored with integer type initially in the table. And that would be
then an increase to the existing table storage. Please correct me if I'm
wrong.

Regards
Veem