Обсуждение: Postgres do not support tinyint?
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.
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
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!
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 widthcolumns 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!
> 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
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.
> 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 typeis 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, packingthem into a bitstring might be a way forward. It's a good solution for tables with a ton of booleans.
"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
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 typeis 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, packingthem 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. >
> 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.
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.
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)
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.
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
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
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.