Thread: single bit integer (TINYINT) revisited for 8.5
I'd like to see this topic revisited since as far as I can see it hasn't been seriously discussed in years. I believe the main arguments against are why do we need more more numeric datatypes and increased maintenance. It would seem to me that a tinyint datatype maintenance wise would get all the same updates as the other int types, making it only a slight increase in maintenance. I think there was 1 more reason but I can't find the original thread now. most (if not all?) of posgresql's major competitor's (mysql, sql server, db2, etc) support a single bit integer datatype. it would bring increased compatibility with existing mysql apps esp, making them easier to port. It (in theory?) should also bring a speed enhancement where usable since it would take less disk space. A couple of times I've been told "you don't need tinyint, use boolean" which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. -- Caleb Cushing http://xenoterracide.blogspot.com
Caleb Cushing <xenoterracide@gmail.com> wrote: > most (if not all?) of posgresql's major competitor's (mysql, sql > server, db2, etc) support a single bit integer datatype. > A couple of times I've been told "you don't need tinyint, use > boolean" which is not true, several projects I've worked on I've > needed and integer field that supports number within a small range > 0-5 1-10 1-100 or something similar. I think you mean byte where you've said bit. Boolean would be adequate for a single bit, and I haven't (so far) seen any database which supports both a single-bit type and a boolean. Many databases support a TINYINT type as a single-byte value, although I'm not sure there's consistency on whether that's a signed or unsigned value. -Kevin
Caleb. > I'd like to see this topic revisited since as far as I can see it > hasn't been seriously discussed in years. I believe the main arguments > against are why do we need more more numeric datatypes and increased > maintenance. It would seem to me that a tinyint datatype maintenance > wise would get all the same updates as the other int types, making it > only a slight increase in maintenance. I think there was 1 more reason > but I can't find the original thread now. The main reason not to have one is that given byte-alignment, 95% of the time using a tinyint would save no actual disk space or memory over just using INT2 (or indeed INT4). I'll point out that the MySQLers are enamored of the 3-byte integer, which even on MySQL saves zero space or memory over using a 4-byte. Just because people like it doesn't mean it works. So the only real point in having an INT1 would be if you had a table with a lot of them in a row; an unusual but not unheard-of design. Still, that makes this feature less-than-critical for most users. But ... the nice thing about PostgreSQL is that data types can be loaded at runtime. Which means that you don't need INT1 in core for it to be useful to you and others; just write the data type and put it on pgFoundry. Then submit it for /contrib for 8.5, and we'll see how popular the idea is. Overall, I'm not keen on it. For the handful of times when peformance-optimization-by-datatype makes sense, there's a large number where it's develpers who have no idea what they're doing. We should be moving in the direction of having the database engine take care of space optimizations, not having the user do it. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> But ... the nice thing about PostgreSQL is that data types can be loaded
> at runtime. Which means that you don't need INT1 in core for it to be
> useful to you and others; just write the data type and put it on
> pgFoundry.
Yeah. The argument against that used to be that you couldn't integrate
it properly into the numeric type hierarchy, but I think that's no
longer the case now that add-on types can define which type category
they belong to. At the very least it would be useful to try to do it
as an add-on and see if there are still any obstacles to that.
Which of course brings up the other argument against doing it, which
was that it'd possibly mess up the rather delicate arrangement of
implicit promotions among the numeric types. That was based on very
old bad experiences, though, so I'm not certain if there's still a
problem. Again, if there is a reason why an add-on type couldn't solve
the problem while a built-in type could, I'd be more interested in
finding and fixing that problem than in the value of INT1 per se.
regards, tom lane
On Wed, Jul 1, 2009 at 11:41 AM, Kevin Grittner<Kevin.Grittner@wicourts.gov> wrote: > I think you mean byte where you've said bit. you're correct. I'm being a nerf. > Boolean would be > adequate for a single bit, and I haven't (so far) seen any database > which supports both a single-bit type and a boolean. wasn't aware of that. I'm admittedly most familiar with sqlite, postgres, and mysql > Many databases > support a TINYINT type as a single-byte value, although I'm not sure > there's consistency on whether that's a signed or unsigned value. wouldn't any implementation in pg support both? -- Caleb Cushing http://xenoterracide.blogspot.com
Caleb Cushing <xenoterracide@gmail.com> writes:
> On Wed, Jul 1, 2009 at 11:41 AM, Kevin
> Grittner<Kevin.Grittner@wicourts.gov> wrote:
>> Many databases
>> support a TINYINT type as a single-byte value, although I'm not sure
>> there's consistency on whether that's a signed or unsigned value.
> wouldn't any implementation in pg support both?
Introducing unsigned types into PG is a whole different discussion.
The problem there is designing reasonable automatic promotion rules.
Considering that C's rules still confuse people after nigh 40 years,
I'm not enthusiastic about it ...
regards, tom lane
On Wed, Jul 1, 2009 at 12:09 PM, Josh Berkus<josh@agliodbs.com> wrote: > The main reason not to have one is that given byte-alignment, 95% of the > time using a tinyint would save no actual disk space or memory over just > using INT2 (or indeed INT4). I'll point out that the MySQLers are enamored > of the 3-byte integer, which even on MySQL saves zero space or memory over > using a 4-byte. Just because people like it doesn't mean it works. > > So the only real point in having an INT1 would be if you had a table with a > lot of them in a row; an unusual but not unheard-of design. Still, that > makes this feature less-than-critical for most users. The primary type of field that I see it used as involves some sort of statistics collection. like ratings, or maybe "strongly agree - strongly disagree" (this could be in hundreds of colums), even collecting gender's is appropriate if I recall.. as the iso for genders is an integer with like 4 possible value's. At least this is my thought, and a lot of these things end up in the same table. It's not critical, but I'd suggest the benefits outweigh the costs. > But ... the nice thing about PostgreSQL is that data types can be loaded at > runtime. Which means that you don't need INT1 in core for it to be useful > to you and others; just write the data type and put it on pgFoundry. Then > submit it for /contrib for 8.5, and we'll see how popular the idea is. I suppose I can see what I can do to this end (since it seems to be the consensus) although it'll take me a while given I've no idea what I'm doing... I can read C better than I write it, which doesn't say much. On the other hand I'd think this would be on the list of 'easiest things to do' which means I should be able to get it done. > Overall, I'm not keen on it. For the handful of times when > peformance-optimization-by-datatype makes sense, there's a large number > where it's develpers who have no idea what they're doing. We should be > moving in the direction of having the database engine take care of space > optimizations, not having the user do it. eh... not sure I agree on that 100%... but to some degree that's what sql is for... at the same time that kinda sounds like sqlite's more dynamic like typing. but it is another discussion -- Caleb Cushing http://xenoterracide.blogspot.com
Incidentally there *is* a single-byte integer data type in Postgres, it's called "char" (the quote marks are necessary in SQL due to the char(n) data type). It's a bit weird though, mainly because its output format is to output ascii characters -- kind of like how C's single-byte integer data type is mainly used to hold ascii characters...
On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: > I'd like to see this topic revisited since as far as I can see it > hasn't been seriously discussed in years. I believe the main arguments > against are why do we need more more numeric datatypes and increased > maintenance. It would seem to me that a tinyint datatype maintenance > wise would get all the same updates as the other int types, making it > only a slight increase in maintenance. I think there was 1 more reason > but I can't find the original thread now. > > most (if not all?) of posgresql's major competitor's (mysql, sql > server, db2, etc) support a single bit integer datatype. it would > bring increased compatibility with existing mysql apps esp, making > them easier to port. > > It (in theory?) should also bring a speed enhancement where usable > since it would take less disk space. > > A couple of times I've been told "you don't need tinyint, use boolean" > which is not true, several projects I've worked on I've needed and > integer field that supports number within a small range 0-5 1-10 1-100 > or something similar. I end up using smallint but it's range is huge > for the actual requirements. Completely agree. I'm most or the way through working on this as an add-on module, rather than a new datatype in core. I don't see much reason to include it in core: its not an SQL standard datatype, it complicates catalog entries and most people don't need or want it. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
On Thursday 02 July 2009 12:40:49 Simon Riggs wrote: > On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: > > A couple of times I've been told "you don't need tinyint, use boolean" > > which is not true, several projects I've worked on I've needed and > > integer field that supports number within a small range 0-5 1-10 1-100 > > or something similar. I end up using smallint but it's range is huge > > for the actual requirements. > > Completely agree. > Blech. More often than not, I find people using all these granular types to be nothing more than premature optimization. And if you really do need a single byte type, you can use "char" (though again I'm not a big fan of that) > I'm most or the way through working on this as an add-on module, rather > than a new datatype in core. I don't see much reason to include it in > core: its not an SQL standard datatype, it complicates catalog entries > and most people don't need or want it. > That's too bad. I'd much rather see someone implement something closer to Oracle's number type. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com
On Fri, 2009-07-03 at 13:38 -0400, Robert Treat wrote: > On Thursday 02 July 2009 12:40:49 Simon Riggs wrote: > > On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: > > > A couple of times I've been told "you don't need tinyint, use boolean" > > > which is not true, several projects I've worked on I've needed and > > > integer field that supports number within a small range 0-5 1-10 1-100 > > > or something similar. I end up using smallint but it's range is huge > > > for the actual requirements. > > > > Completely agree. > > > > Blech. More often than not, I find people using all these granular types to be > nothing more than premature optimization. And if you really do need a single > byte type, you can use "char" (though again I'm not a big fan of that) I agree that many optimizations are used inappropriately. Another reason for making it an add-on module. I'm aware of "char" and it doesn't do all I would wish. > > I'm most or the way through working on this as an add-on module, rather > > than a new datatype in core. I don't see much reason to include it in > > core: its not an SQL standard datatype, it complicates catalog entries > > and most people don't need or want it. > > > > That's too bad. I'd much rather see someone implement something closer to > Oracle's number type. Please explain what you mean? -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support