Subject: Re: Postgres do not support tinyint?

From: Dominique Devienne <[email-masked]>
Date: Fri, 10 Jan 2025 10:41:12 +0100

On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
<[email-masked]> wrote:
> If you really need 1-byte integer, you can use "char" type. Cast it
> to/from int. See comment at the end of the page
> https://www.postgresql.org/docs/17/datatype-character.html

Hi. What would be the best online doc to learn about the physical
storage / format for rows/pages/btree, etc... To understand alignment,
packing, varint or not, all those things. I'm quite familiar with the
[SQLite format][1], having read that doc dozens of times, and I'd like
a better low-level understanding for PostgreSQL as well. TIA, --DD

[1]: https://www.sqlite.org/fileformat.html

---

From: [email-masked]
Date: Fri, 10 Jan 2025 11:44:29 +0100

> a better low-level understanding for PostgreSQL as well. TIA, --DD

The alignment requirement of each type is available in pg_type

https://www.postgresql.org/docs/current/catalog-pg-type.html

The physical layout on disk is described here:

https://www.postgresql.org/docs/current/storage.html

And the "Postgres intenrals" site might be helpful as well:

https://www.interdb.jp/pg/pgsql01.html

There are various (blog) posts on how to optimize space considering alignment

* https://stackoverflow.com/a/7431468
* https://www.enterprisedb.com/blog/rocks-and-sand

The "postgres_dba" toolset has a little SQL script to evaluate the space savings:

https://github.com/NikolayS/postgres_dba/blob/master/sql/p1_alignment_padding.sql

---

From: Vladlen Popolitov <[email-masked]>
Date: Fri, 10 Jan 2025 22:04:51 +0700

> [1]: https://www.sqlite.org/fileformat.html

I would recommend "PostgreSQL Internals" of Egor Rogov (Chapter 3 about 
tuples,
other chapters also great)
Free download from https://postgrespro.com/community/books/internals

Also PostgreSQL source code is officially the part of the documentation.
It is not joke. PostgreSQL source has clear comments in functions code
and in structure declarations. You can compare with other sources, it is 
hard to find better
comments made with love.

Heap tuple information is in source file 
src/include/access/htup_details.h (HeapTupleHeaderData
structure), but I recommend to read above book first, it simplier 
explains many
internal concepts.
-- 
Best regards,

Vladlen Popolitov.