Horton
Menu

Thread

  1. Postgres do not support tinyint?

    Igor Korot <ikorot01@gmail.com> — 2025-01-08T05:06:10Z

    Hi, ALL,
According to https://www.postgresql.org/docs/9.1/datatype-numeric.html, the
smallest numeric type supports numbers from -32768 to 32767/

My data will be in a range of [0..4], and so I guess my DB table will waste
space, right?

Thank you.

  2. Re: Postgres do not support tinyint?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-01-08T05:20:56Z

    On 1/7/25 21:06, Igor Korot wrote:
> Hi, ALL,
> According to https://www.postgresql.org/docs/9.1/datatype-numeric.html, the
> smallest numeric type supports numbers from -32768 to 32767/

In this case it does not matter, but you should not consult 
documentation that is for a version(9.1) that is ~8 years past EOL.

Go here:

https://www.postgresql.org/docs/

and click on Current or the version you are actually using.

> 
> My data will be in a range of [0..4], and so I guess my DB table will waste
> space, right?

Yes, though is that actually going to be an issue?

> 
> Thank you.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  3. Re: Postgres do not support tinyint?

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-01-08T05:24:21Z

    On Wed, Jan 8, 2025 at 12:06 AM Igor Korot <ikorot01@gmail.com> wrote:

> Hi, ALL,
> According to https://www.postgresql.org/docs/9.1/datatype-numeric.html,
> the
> smallest numeric type supports numbers from -32768 to 32767/
>
> My data will be in a range of [0..4], and so I guess my DB table will waste
> space, right?
>

1. It's not 1994 anymore, when 8M rows was enormous.
2. Record structures are padded by word size, so tinyint wouldn't matter
unless you specifically ordered the fixed width columns from largest to
smallest size when creating the table.
3. The "bit" type might serve your needs.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

  4. Re: Postgres do not support tinyint?

    Igor Korot <ikorot01@gmail.com> — 2025-01-08T06:26:32Z

    Hi, Ron,

On Tue, Jan 7, 2025 at 11:24 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> On Wed, Jan 8, 2025 at 12:06 AM Igor Korot <ikorot01@gmail.com> wrote:
>>
>> Hi, ALL,
>> According to https://www.postgresql.org/docs/9.1/datatype-numeric.html, the
>> smallest numeric type supports numbers from -32768 to 32767/
>>
>> My data will be in a range of [0..4], and so I guess my DB table will waste
>> space, right?
>
>
> 1. It's not 1994 anymore, when 8M rows was enormous.
> 2. Record structures are padded by word size, so tinyint wouldn't matter unless you specifically ordered the fixed width columns from largest to smallest size when creating the table.
> 3. The "bit" type might serve your needs.

 I don't see the "bit" field here:
https://www.postgresql.org/docs/current/datatype-numeric.html...

Thank you..

>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!

  5. Re: Postgres do not support tinyint?

    Christophe Pettus <xof@thebuild.com> — 2025-01-08T06:28:14Z

    
> On Jan 7, 2025, at 22:26, Igor Korot <ikorot01@gmail.com> wrote:
> I don't see the "bit" field here:
> https://www.postgresql.org/docs/current/datatype-numeric.html...

https://www.postgresql.org/docs/current/datatype-bit.html

  6. Re: Postgres do not support tinyint?

    David G. Johnston <david.g.johnston@gmail.com> — 2025-01-08T06:44:21Z

    On Tuesday, January 7, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

>
> 3. The "bit" type might serve your needs.
>
>
You suggest a type with a minimum size of 6 bytes when the complaint is
that the otherwise acceptable 2 byte data type is too large?

David J.

  7. Re: Postgres do not support tinyint?

    Christophe Pettus <xof@thebuild.com> — 2025-01-08T06:49:08Z

    
> On Jan 7, 2025, at 22:44, David G. Johnston <david.g.johnston@gmail.com> wrote:
> 
> You suggest a type with a minimum size of 6 bytes when the complaint is that the otherwise acceptable 2 byte data type is too large?

Although it's not clear from the OP's question, if there are going to be a significant number of these 3-bit fields, packing them into a bitstring might be a way forward.  It's a good solution for tables with a ton of booleans.

  8. Re: Postgres do not support tinyint?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-01-08T06:53:34Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, January 7, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>> 3. The "bit" type might serve your needs.

> You suggest a type with a minimum size of 6 bytes when the complaint is
> that the otherwise acceptable 2 byte data type is too large?

I think the point here is that there's zero value in trying to pack a
small integer value into 1 byte (let alone 4 bits) if it's all by
its lonesome in the row.  Alignment padding will eat whatever gain
you thought you had.  If you want a win, you need to store a lot of
such values in one field.  Ron's suggesting that you pack them into
bit arrays and manually insert/extract individual values.  That could
be worth doing if you were sufficiently desperate, but you'd have to
value compact storage over access simplicity quite a lot.

Perhaps a "char"[] array (note the quotes) would provide an
intermediate level of compactness versus pain.

			regards, tom lane

  9. Re: Postgres do not support tinyint?

    Igor Korot <ikorot01@gmail.com> — 2025-01-08T19:30:04Z

    Hi, Christophe,

On Wed, Jan 8, 2025 at 12:49 AM Christophe Pettus <xof@thebuild.com> wrote:
>
>
>
> > On Jan 7, 2025, at 22:44, David G. Johnston <david.g.johnston@gmail.com> wrote:
> >
> > You suggest a type with a minimum size of 6 bytes when the complaint is that the otherwise acceptable 2 byte data type is too large?
>
> Although it's not clear from the OP's question, if there are going to be a significant number of these 3-bit fields, packing them into a bitstring might be a way forward.  It's a good solution for tables with a ton of booleans.

There is no boolean - it is 0-4 inclusive.

Also - there are not too many records in that table...

Thank you.

>

  10. Re: Postgres do not support tinyint?

    Christophe Pettus <xof@thebuild.com> — 2025-01-08T19:34:15Z

    
> On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
> There is no boolean - it is 0-4 inclusive.

Unless you have somehow gotten PostgreSQL running on an IBM 7070, the range 0-4 can be represented by three binary digits, aka booleans. :-)

To be serious, though, the situation is:

1. If there are just one or two tinyints, having a tinyint type wouldn't save any space in the row.
2. If there are a lot of them, it's worth encoding them into a bitstring.

  11. Re: Postgres do not support tinyint?

    Igor Korot <ikorot01@gmail.com> — 2025-01-08T19:40:38Z

    Hi, Christopphe,

On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <xof@thebuild.com> wrote:
>
>
>
> > On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
> > There is no boolean - it is 0-4 inclusive.
>
> Unless you have somehow gotten PostgreSQL running on an IBM 7070, the range 0-4 can be represented by three binary digits, aka booleans. :-)

The only booleans I know of are 0 and 1. ;-)

>
> To be serious, though, the situation is:
>
> 1. If there are just one or two tinyints, having a tinyint type wouldn't save any space in the row.

No it is not a lot of them.
So then "smallint" is the best bet, right?

Thank you

> 2. If there are a lot of them, it's worth encoding them into a bitstring.

  12. Re: Postgres do not support tinyint?

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-01-08T19:47:59Z

    On 2025-Jan-08, Igor Korot wrote:

> Also - there are not too many records in that table...

In that case, you've probably wasted more time on this discussion than
the computer will ever save by storing a smaller column.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
 of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)

  13. Re: Postgres do not support tinyint?

    Vladlen Popolitov <v.popolitov@postgrespro.ru> — 2025-01-10T09:13:05Z

    Igor Korot писал(а) 2025-01-09 02:40:
> Hi, Christopphe,
> 
> On Wed, Jan 8, 2025 at 1:34 PM Christophe Pettus <xof@thebuild.com> 
> wrote:
>> 
>> 
>> 
>> > On Jan 8, 2025, at 11:30, Igor Korot <ikorot01@gmail.com> wrote:
>> > There is no boolean - it is 0-4 inclusive.
>> 
>> Unless you have somehow gotten PostgreSQL running on an IBM 7070, the 
>> range 0-4 can be represented by three binary digits, aka booleans. :-)
> 
> The only booleans I know of are 0 and 1. ;-)
> 
>> 
>> To be serious, though, the situation is:
>> 
>> 1. If there are just one or two tinyints, having a tinyint type 
>> wouldn't save any space in the row.
> 
> No it is not a lot of them.
> So then "smallint" is the best bet, right?
> 
> Thank you
> 
>> 2. If there are a lot of them, it's worth encoding them into a 
>> bitstring.

Hi!
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

-- 
Best regards,

Vladlen Popolitov.

  14. Re: Postgres do not support tinyint?

    Dominique Devienne <ddevienne@gmail.com> — 2025-01-10T09:41:12Z

    On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
<v.popolitov@postgrespro.ru> 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

  15. Re: Postgres do not support tinyint?

    shammat@gmx.net — 2025-01-10T10:44:29Z

    Dominique Devienne schrieb am 10.01.2025 um 10:41:
> On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
> <v.popolitov@postgrespro.ru> 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

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

  16. Re: Postgres do not support tinyint?

    Vladlen Popolitov <v.popolitov@postgrespro.ru> — 2025-01-10T15:04:51Z

    Dominique Devienne писал(а) 2025-01-10 16:41:
> On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
> <v.popolitov@postgrespro.ru> 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

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.