Re: How to do faster DML - Mailing list pgsql-general

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.