Re: How to do faster DML

On Thu, Feb 8, 2024 at 12:12 AM Lok P <loknath(dot)73(at)gmail(dot)com> wrote:

> Rearranging the table columns by typlen desc, didn't give much storage
> space reduction.
>

It's not so much "biggest to smallest" as making sure you don't have any
gaps when you move from one data type to another. You may have not had any
"holes" originally, so it's hard to state anything without data. The other
thing you can do is use smaller data types if you can get away with it.
smallint better than int, int better than bigint, etc.

So it seems the compression does not apply for the rows inserted using
> "CTAS" or "INSERT AS SELECT". Does that mean it is only applicable for the
> row by row inserts but not batch inserts(which a bigger system normally
> will have)? I was not expecting this though, so it was disappointing.
>

TOAST compression doesn't care how the data arrived. It does have criteria
though as to how large something is before it gets toasted and/or
compressed. See:

https://www.crunchydata.com/blog/postgres-toast-the-greatest-thing-since-sliced-bread

Cheers,
Greg