Re: Converting MySQL tinyint to PostgreSQL

I have a MySQL database that I'm converting to PostgreSQL which has 10 columns
with TINYINT type, i.e., a one-byte integer. Only one of them qualifies as a
true BOOLEAN. Two are entity identifiers (for limited range "classes" or
"categories") and three others are "type"/code values. The last four are month
numbers or day of month numbers (not implemented as date types because the year
is separate and is part of the primary key).

I'm wondering what would be the best conversion choice for these columns:
smallint, numeric(1), char(1), something else? AFAICT, the application does not
directly do any arithmetic on these columns, but the identifier and code types
are used as indices into PHP arrays. The month/day values are formatted into
dates for display (and the month also servers as an index into a month-name array).

BTW, is TINYINT part of any SQL Standard?

Joe

On Jul 12, 2005, at 1:16 AM, Joe wrote:

> I have a MySQL database that I'm converting to PostgreSQL which has
> 10 columns with TINYINT type, i.e., a one-byte integer. Only one
> of them qualifies as a true BOOLEAN. Two are entity identifiers
> (for limited range "classes" or "categories") and three others are
> "type"/code values. The last four are month numbers or day of
> month numbers (not implemented as date types because the year is
> separate and is part of the primary key).
>
> I'm wondering what would be the best conversion choice for these
> columns: smallint, numeric(1), char(1), something else? AFAICT,
> the application does not directly do any arithmetic on these
> columns, but the identifier and code types are used as indices into
> PHP arrays. The month/day values are formatted into dates for
> display (and the month also servers as an index into a month-name
> array).
>

I would use smallint. While PHP might work fine with something else,
it seems like the closest match.

> BTW, is TINYINT part of any SQL Standard?

From the pg docs:

SQL only specifies the integer types integer (or int) and smallint.
The type bigint, and the type names int2, int4, and int8 are
extensions, which are shared with various other SQL database systems.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Joe <svn(at)freedomcircle(dot)net> writes:
> I have a MySQL database that I'm converting to PostgreSQL which has 10 columns
> with TINYINT type, i.e., a one-byte integer.

> I'm wondering what would be the best conversion choice for these columns:
> smallint, numeric(1), char(1), something else?

smallint, for sure; the others have more overhead.

> BTW, is TINYINT part of any SQL Standard?

No. If it were, we'd feel more urgency about implementing it ;-)

regards, tom lane

On 7/12/05, Joe <svn(at)freedomcircle(dot)net> wrote:
> I have a MySQL database that I'm converting to PostgreSQL which has 10 columns
> with TINYINT type, i.e., a one-byte integer. Only one of them qualifies as a
> true BOOLEAN. Two are entity identifiers (for limited range "classes" or
> "categories") and three others are "type"/code values. The last four are month
> numbers or day of month numbers (not implemented as date types because the year
> is separate and is part of the primary key).
>
> I'm wondering what would be the best conversion choice for these columns:
> smallint, numeric(1), char(1), something else? AFAICT, the application does not

smallint takes two bytes. Numeric(1) will take around 10 bytes and char(1) will
take 5 bytes (4 bytes for length of data). The closest match is smallint which
is reasonably small and will do the trick. Remember that PostgreSQL doesn't
have unsigned types.

This may seem "a terrible waste of diskspace" at the first glance, but keep
in mind that PostgreSQL's power lies in an intelligent planner. You can
safely nomralize data and create views which "emulate" one-table which
both simplifies development and can be more efficient in terms of
storage and raw speed.

> directly do any arithmetic on these columns, but the identifier and code types
> are used as indices into PHP arrays. The month/day values are formatted into
> dates for display (and the month also servers as an index into a month-name array).

You should consider using date / time / timestamp column for dates and use
functions like extract(day from mydate). Remember you can use functional
indexes, so if you need data to by indexed by day, you can:
CREATE INDEX dayindex ON sometable ((extract(day from datecolumn)));

Regards,
Dawid

On Tue, Jul 12, 2005 at 01:16:07AM -0400, Joe wrote:
> I have a MySQL database that I'm converting to PostgreSQL which has 10
> columns with TINYINT type, i.e., a one-byte integer. Only one of them
> qualifies as a true BOOLEAN. Two are entity identifiers (for limited range
> "classes" or "categories") and three others are "type"/code values. The
> last four are month numbers or day of month numbers (not implemented as
> date types because the year is separate and is part of the primary key).

Just to broaden your choices, there is also a "char" datatype (quotes
included! If you omit them, it's a different thing, more overhead),
which you can use to store a single byte. Could be used as a poor man's
ENUM. Add appropiate CHECK constraints and it could be good enough for
some purposes. Not useful for months, but maybe for your categories or
classes.

There are some usages of this in the system catalogs (e.g.
pg_class.relkind)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
www.google.com: interfaz de línea de comando para la web.

Dawid Kuroczko wrote:
> smallint takes two bytes. Numeric(1) will take around 10 bytes and char(1) will
> take 5 bytes (4 bytes for length of data).

I never would've imagined *that* amount of overhead for CHAR(1)! I would've
imagined that it would take up one byte (or two with a NULL indicator). After
all, we're not talking about VARCHAR(1) [which is sort of useless]. Don't the
catalogs know the declared length and if so, why the length overhead? I'm also
surprised --albeit less-- about the NUMERIC(1) overhead. Is any of this
discussed in the Internals chapters? I didn't see this discussed elsewhere.

As a perhaps-related aside, I've noticed several examples which declare string
types as 'text', rather than VARCHAR or CHAR, the former being non-SQL standard.
Is there some performance benefit to using 'text' (other than it being shorter
to type :-) or is it just the "usual Postgres way"?

Joe

Joe <svn(at)freedomcircle(dot)net> writes:
> I never would've imagined *that* amount of overhead for CHAR(1)! I
> would've imagined that it would take up one byte (or two with a NULL
> indicator). After all, we're not talking about VARCHAR(1) [which is
> sort of useless]. Don't the catalogs know the declared length and if
> so, why the length overhead?

Because the length specification is in *characters*, which is not by any
means the same as *bytes*.

We could possibly put enough intelligence into the low-level tuple
manipulation routines to count characters in whatever encoding we happen
to be using, but it's a lot faster and more robust to insist on a count
word for every variable-width field.

regards, tom lane

Tom Lane wrote:
> Because the length specification is in *characters*, which is not by any
> means the same as *bytes*.
>
> We could possibly put enough intelligence into the low-level tuple
> manipulation routines to count characters in whatever encoding we happen
> to be using, but it's a lot faster and more robust to insist on a count
> word for every variable-width field.

I guess what you're saying is that PostgreSQL stores characters in
varying-length encodings. If it stored character data in Unicode (UCS-16) it
would always take up two-bytes per character. Have you considered supporting
NCHAR/NVARCHAR, aka NATIONAL character data? Wouldn't UCS-16 be needed to
support multi-locale clusters (as someone as inquiring about recently)?

Joe

On Tue, Jul 12, 2005 at 05:37:32PM -0400, Joe wrote:
> Tom Lane wrote:
> >Because the length specification is in *characters*, which is not by any
> >means the same as *bytes*.
> >
> >We could possibly put enough intelligence into the low-level tuple
> >manipulation routines to count characters in whatever encoding we happen
> >to be using, but it's a lot faster and more robust to insist on a count
> >word for every variable-width field.
>
> I guess what you're saying is that PostgreSQL stores characters in
> varying-length encodings.

It _may_ store characters in variable length encodings. It can use
fixed-length encodings too, such as latin1 or plain ASCII (actually,
unchecked 8 bits, which means about anything) -- you define that at
initdb time or database creation time, I forget. It would be painful
for the code to distinguish fixed-length from variable-length at
runtime, an optimization that would allow getting rid of the otherwise
required length word. So far, nobody has cared enough about it to do
the job.

> If it stored character data in Unicode (UCS-16) it would always take
> up two-bytes per character.

Really? We don't support UCS-16, for good reasons (we'd have to rewrite
several parts of the code in order to support '0' bytes embedded in
strings ... we use regular C strings extensively).

However we do support Unicode as UTF-8, but it's been said a couple of
times that characters can be wider than 2 or 3 bytes in some cases. So,
I don't see how UCS-16 could always use only 2 bytes.

> Have you considered supporting NCHAR/NVARCHAR, aka NATIONAL character
> data?

There have been noises, but so far nobody has stepped up the plate to do
the work.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)

Alvaro Herrera wrote:
> On Tue, Jul 12, 2005 at 05:37:32PM -0400, Joe wrote:
>
>>If it stored character data in Unicode (UCS-16) it would always take
>>up two-bytes per character.
>
> Really? We don't support UCS-16, for good reasons (we'd have to rewrite
> several parts of the code in order to support '0' bytes embedded in
> strings ... we use regular C strings extensively).

I'm sorry. I meant to say UCS-2, which by definition uses 16 bits = 2 octets =
2 bytes. To support an even larger code space, you could use UCS-4 which always
uses 4 bytes.

Joe

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> I don't see how UCS-16 could always use only 2 bytes.

Simple: it fails to handle Unicode code points above 0x10000. (We only
recently fixed a similar limitation in our UTF8 support, by the by, but
it *is* fixed and I doubt we want to backpedal.)

The problem with embedded null bytes is quite serious though, and I
doubt that we'll ever see the backend natively handling encodings that
require that. It's just not worth the effort. Certainly the idea of
not having to store a length word for CHAR(1) fields is not going to
inspire anyone to invest the effort involved ;-)

Keep in mind also that any such change would involve putting slower and
more complicated logic into some routines that are hotspots already;
so even if you did all the work involved, you might find the patch
rejected on the grounds that it's a net performance loss. Most of the
developers have plenty of tasks to do with a larger and more certain
reward than this.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Certainly the idea of not having to store a length word for CHAR(1) fields
> is not going to inspire anyone to invest the effort involved ;-)

That's a pretty big motivation though. Storage space efficiency is a huge
factor in raw sequential scan speed.

Personally I would settle for a fuller set of small fixed size datatypes. The
"char" datatype is pretty much exactly what's needed except that it provides
such a quirky interface.

I'm not sure exactly how to clean it up but if it were more user-friendly (and
had less of an "undocumented internal feature" character to it) I have a
feeling a lot of people would be using for things like flags, state codes,
etc. And it would reduce storage space significantly over having lots of text
or even integer fields.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Personally I would settle for a fuller set of small fixed size datatypes. The
> "char" datatype is pretty much exactly what's needed except that it provides
> such a quirky interface.

I'm not actually against inventing an int1/tinyint type. I used to be
worried that it would screw up the numeric datatype promotion hierarchy
even more than it already was screwed up :-( ... but I think we have
dealt with most of those issues now. It'd be worth trying anyway ---
much more so than trying to optimize char(1), IMHO.

regards, tom lane

On 7/13/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Personally I would settle for a fuller set of small fixed size datatypes. The
> > "char" datatype is pretty much exactly what's needed except that it provides
> > such a quirky interface.
>
> I'm not actually against inventing an int1/tinyint type. I used to be
> worried that it would screw up the numeric datatype promotion hierarchy
> even more than it already was screwed up :-( ... but I think we have
> dealt with most of those issues now. It'd be worth trying anyway ---
> much more so than trying to optimize char(1), IMHO.

The problem with int1 type is that the smaller the value, the more
push for unsigned types... I think it may be worth doing, but is not
exactly the problem -- smallint is fine for most of situations. The
only place where I was unhappy with signed integers was... int4
(I wanted to put full 32bit unsigned values, so I had to use bigint,
with couple of millions of rows its a bit of a waste ;)).

As for the char/varchar type -- I was wondering. Worst case
scenario for UTF-8 (correct me on this) is when 1 character
takes 4 bytes. And biggest problem with char/varchar is that
length indicator takes 4 bytes... How much overhead would
it be to make a length variable, for example:

(var)char(1)-char(63) -- 1 byte length + string
char(64)-char(16383) -- 2 byte length + string
char(16384)-text -- 4 byte length + string, like now

This would reduce length of char(5) string from 9 bytes to
6 bytes, char(2) from 6 bytes to 3 bytes (for multibyte chars
it would be a win also).

I don't know the internals too well (read: at all), but I guess there
would be a problem of choosing which length of length to use --
would it be possible to make some sort of on-the-fly mapping
when creating tables -- varchar(224) is text_2bytelength,
text is text_4bytelength, char(1) is text_1bytelength...

Regards,
Dawid

On 7/12/05, Joe <svn(at)freedomcircle(dot)net> wrote:
> Dawid Kuroczko wrote:
> > smallint takes two bytes. Numeric(1) will take around 10 bytes and char(1) will
> > take 5 bytes (4 bytes for length of data).
> I never would've imagined *that* amount of overhead for CHAR(1)! I would've
> imagined that it would take up one byte (or two with a NULL indicator). After
> all, we're not talking about VARCHAR(1) [which is sort of useless]. Don't the
> catalogs know the declared length and if so, why the length overhead? I'm also
> surprised --albeit less-- about the NUMERIC(1) overhead. Is any of this
> discussed in the Internals chapters? I didn't see this discussed elsewhere.

It is all described in
http://www.postgresql.org/docs/8.0/interactive/datatype.html
with given above space requirements also. Noone hides it, it's all black on
white. :-)

> As a perhaps-related aside, I've noticed several examples which declare string
> types as 'text', rather than VARCHAR or CHAR, the former being non-SQL standard.
> Is there some performance benefit to using 'text' (other than it being shorter
> to type :-) or is it just the "usual Postgres way"?

Some time ago people used text as a way for making "easily changeable varchar",
say:
col text CHECK (length(VAL) < 100)
...when person sees that 100 is too small it was just a matter of changing the
CHECK constraint.

...but it was long time ago, and since that time PostgreSQL is able to change
the type of column with no problems, and efficiently.

Regards,
Dawid

On Wed, Jul 13, 2005 at 10:48:56AM +0200, Dawid Kuroczko wrote:
> As for the char/varchar type -- I was wondering. Worst case
> scenario for UTF-8 (correct me on this) is when 1 character
> takes 4 bytes. And biggest problem with char/varchar is that
> length indicator takes 4 bytes... How much overhead would
> it be to make a length variable, for example:
>
> (var)char(1)-char(63) -- 1 byte length + string
> char(64)-char(16383) -- 2 byte length + string
> char(16384)-text -- 4 byte length + string, like now

Well, you get another issue, alignment. If you squeeze your string
down, the next field, if it is an int or string, will get padded to a
multiple of 4 negating most of the gains. Like in C structures, there
is padding to optimise access.

> This would reduce length of char(5) string from 9 bytes to
> 6 bytes, char(2) from 6 bytes to 3 bytes (for multibyte chars
> it would be a win also).

The only types that won't require padding if they are next field are
bool, "char" and cstring. So char(1-4) will actually go from 8 to 4
bytes in most cases. char(5-8) will go from 12 to 8 bytes.

> I don't know the internals too well (read: at all), but I guess there
> would be a problem of choosing which length of length to use --
> would it be possible to make some sort of on-the-fly mapping
> when creating tables -- varchar(224) is text_2bytelength,
> text is text_4bytelength, char(1) is text_1bytelength...

At the moment there are two basic types: variable length and fixed
length as defined by the type id. Fixed length are stored as is.
Variable length is a 4 byte length plus the string. The two highest
bits are flags. AFAIK it's the typlen value that decides the decoding,
the type itself is irrelevent.

I suppose somebody could create a new encoding but I don't know how
hard that would be...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout wrote:
>
> Well, you get another issue, alignment. If you squeeze your string
> down, the next field, if it is an int or string, will get padded to a
> multiple of 4 negating most of the gains. Like in C structures, there
> is padding to optimise access.

Anecdotally I hear at least as many people say that their database
is more I/O bound than CPU bound; and it seems that adding bytes
for alignment is a way of reducing CPU for more disk I/O.

I guess unaligned access so expensive that it makes up for the extra i/o?

Ron Mayer wrote:
> Martijn van Oosterhout wrote:
> >
> > Well, you get another issue, alignment. If you squeeze your string
> > down, the next field, if it is an int or string, will get padded to a
> > multiple of 4 negating most of the gains. Like in C structures, there
> > is padding to optimise access.
>
> Anecdotally I hear at least as many people say that their database
> is more I/O bound than CPU bound; and it seems that adding bytes
> for alignment is a way of reducing CPU for more disk I/O.
>
> I guess unaligned access so expensive that it makes up for the extra i/o?

This is a good point. We have always stored data on disk that exactly
matches its layout in memory. We could change that, but no one has
shown it would be a win.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
> Ron Mayer wrote:
> > Martijn van Oosterhout wrote:
> > >
> > > Well, you get another issue, alignment. If you squeeze your string
> > > down, the next field, if it is an int or string, will get padded to a
> > > multiple of 4 negating most of the gains. Like in C structures, there
> > > is padding to optimise access.
> >
> > Anecdotally I hear at least as many people say that their database
> > is more I/O bound than CPU bound; and it seems that adding bytes
> > for alignment is a way of reducing CPU for more disk I/O.
> >
> > I guess unaligned access so expensive that it makes up for the extra i/o?
>
> This is a good point. We have always stored data on disk that exactly
> matches its layout in memory. We could change that, but no one has
> shown it would be a win.

Out of curiosity, what would be involved in hacking the backend enough
to be able to test this theory out? I'm guessing you'd want to convert
between on-disk and in-memory formats as you read pages in, so either
on-disk pages would become variable size (and smaller than memory pages)
or in-memory pages would become variable size (and larger than on-disk
pages).

Or maybe as an alternative, would it be possible to determine how much
space in a given relation was being wasted due to padding? That could be
used to figure out how much IO could be saved on different transactions.
While there would be a slight CPU penalty every time you read or write a
page, I find it hard to believe it could come close to equaling IO cost.

On a side note, I think it might be useful to have a seperate TODO
catagory for ideas that need to be tested to see if they're worth
implementing for real. This is a case where it's probably substantially
easier to estimate (or maybe even measure) how much there is to gain
from this than to do the actual work and then see if it helps. It's also
likely that a less experienced hacker could test the theory out. Some
likely items for this list:

Reduce WAL traffic so only modified values are written rather than
entire rows?
Find a way to reduce rotational delay when repeatedly writing last WAL
page
Precompile SQL functions to avoid overheadDo async I/O for faster random
read-ahead of data

Not on todo:
Estimate gains from not using the in-memory format of data for on-disk
storage
Estimate gains from reducing the amount of space used by visibility
information in each tuple

BTW, what ever happened to the idea of having a list of projects for
beginners? (Or maybe it'd be better to assign a numeric difficulty to
each TODO item?)
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
> On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
> > This is a good point. We have always stored data on disk that exactly
> > matches its layout in memory. We could change that, but no one has
> > shown it would be a win.
>
> Out of curiosity, what would be involved in hacking the backend enough
> to be able to test this theory out? I'm guessing you'd want to convert
> between on-disk and in-memory formats as you read pages in, so either
> on-disk pages would become variable size (and smaller than memory pages)
> or in-memory pages would become variable size (and larger than on-disk
> pages).

It's a pain because on some architectures you can't do unaligned
accesses. I imagine you'd have to have the on-disk pages in memory and
copy them to a temporary space when you actually want to use the data,
converting on the fly.

IMHO a much much better approach would be the two phase:
- Decouple order of columns on disk from logical column order
Then people can rearrange columns, people do ask that occasionally.
- Change CREATE TABLE to rearrange columns on disk (not the logical
order) to minimize padding.

This gives you real benefits without having to overhaul the code...

> On a side note, I think it might be useful to have a seperate TODO
> catagory for ideas that need to be tested to see if they're worth
> implementing for real. This is a case where it's probably substantially
> easier to estimate (or maybe even measure) how much there is to gain
> from this than to do the actual work and then see if it helps. It's also
> likely that a less experienced hacker could test the theory out. Some
> likely items for this list:

What usually happens is someone tries it and it either works or it
doesn't...

Can't comment on the other ideas.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote:
> On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
> > On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
> > > This is a good point. We have always stored data on disk that exactly
> > > matches its layout in memory. We could change that, but no one has
> > > shown it would be a win.
> >
> > Out of curiosity, what would be involved in hacking the backend enough
> > to be able to test this theory out? I'm guessing you'd want to convert
> > between on-disk and in-memory formats as you read pages in, so either
> > on-disk pages would become variable size (and smaller than memory pages)
> > or in-memory pages would become variable size (and larger than on-disk
> > pages).
>
> It's a pain because on some architectures you can't do unaligned
> accesses. I imagine you'd have to have the on-disk pages in memory and
> copy them to a temporary space when you actually want to use the data,
> converting on the fly.

My thought was to convert as pages were read and written. That should
minimize the code impact.

> IMHO a much much better approach would be the two phase:
> - Decouple order of columns on disk from logical column order
> Then people can rearrange columns, people do ask that occasionally.
> - Change CREATE TABLE to rearrange columns on disk (not the logical
> order) to minimize padding.
>
> This gives you real benefits without having to overhaul the code...

True, that would be of some benefit, but not as much as being able to
compact the disk storage.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Jim C. Nasby wrote:
>On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote:
>>On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
>>>On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
>>>
>>>>This is a good point. We have always stored data on disk that exactly
>>>>matches its layout in memory. We could change that, but no one has
>>>>shown it would be a win.
>>>
>>[...]
>
> My thought was to convert as pages were read and written. That should
> minimize the code impact.

If that were practical, even more radical I/O saving tricks might be
possible beyond removing alignment bytes - like some compression algorithm.

Jim C. Nasby wrote:
> Or maybe as an alternative, would it be possible to determine how much
> space in a given relation was being wasted due to padding? That could be
> used to figure out how much IO could be saved on different transactions.

Well, I do notice that if I gzip my larger tables's data files they
tend to compress between 80-95% so it seems there's a fair amount of
redundancy in at least some tables.

Has anyone tried running postgresql on a filesystem that does compression?

It seems that would be an easy way to guess at the ultimate
potential I/O savings of separating memory layout from disk layout.

Ron

On Sat, Jul 16, 2005 at 03:18:24PM -0700, Ron Mayer wrote:
> Jim C. Nasby wrote:
> >On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote:
> >>On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
> >>>On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
> >>>
> >>>>This is a good point. We have always stored data on disk that exactly
> >>>>matches its layout in memory. We could change that, but no one has
> >>>>shown it would be a win.
> >>>
> >>[...]
> >
> >My thought was to convert as pages were read and written. That should
> >minimize the code impact.
>
> If that were practical, even more radical I/O saving tricks might be
> possible beyond removing alignment bytes - like some compression algorithm.

True, though there's a few issues with zlib compression. First, you have
to be able to pull specific pages out of the files on disk. Right now
that's trivial; you just read bytes xxx - yyy. With compression things
are more difficult, because you no longer have a fixed page size.

Another issue is that with a variable disk page size, you have to deal
with what happens when you try to put a page back on disk but the page
is now larger than it's original size.

These issues are why I suggested a fixed disk page size and a variable
in-memory page size; it simplifies things a bit. It does however create
some problems of it's own. When you go to transform/compress a page to
put it on disk if the in-memory page is now too large you'll need to
move some tuples to another page.

Something else to consider is that a simple compression scheme such as
eliminating alignment padding makes it easy to determine how large a
tuple will be on disk versus in memory. This means you can do things
like determine at the time of tuple creation if that tuple will fit in
an existing page or not. I don't know if the same can be said for other
methods. Another factor is that more complex compression methods will be
much more CPU intensive.

FWIW, the way oracle handles compression is as a one-time operation.
When you tell it to compress a table it will re-write the entire table,
compressing it as it goes. But any pages that get changed after that
will end up uncompressed. Of course in a data warehouse environment
that's perfectly acceptable.

Ultimately I don't see anything being done along these lines unless
someone can come up with some data indicating performance gains, which
will probably mean hacking some amount of this in and benchmarking it.
Although for the case of simple elimination of alignment padding you
could probably come up with some pretty good estimates just by looking
at a table's layout and it's statistics.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

I believe that one should leave such on-the-fly disk compression to the
O/S. Postgresql already does compression for TOAST.

However, maybe padding for alignment is a waste on the disk - disks being
so much slower than CPUs (not sure about that once the data is in memory ).
Maybe there should be an option to reorder columns so that less space is
wasted.

At 05:47 PM 7/17/2005 -0500, Jim C. Nasby wrote:

>On Sat, Jul 16, 2005 at 03:18:24PM -0700, Ron Mayer wrote:
>
> > If that were practical, even more radical I/O saving tricks might be
> > possible beyond removing alignment bytes - like some compression algorithm.
>
>True, though there's a few issues with zlib compression. First, you have
>to be able to pull specific pages out of the files on disk. Right now
>that's trivial; you just read bytes xxx - yyy. With compression things
>are more difficult, because you no longer have a fixed page size.
>
>methods. Another factor is that more complex compression methods will be
>much more CPU intensive.
>
>FWIW, the way oracle handles compression is as a one-time operation.
>When you tell it to compress a table it will re-write the entire table,
>compressing it as it goes. But any pages that get changed after that
>will end up uncompressed. Of course in a data warehouse environment
>that's perfectly acceptable.

On 7/18/05, Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> wrote:
> However, maybe padding for alignment is a waste on the disk - disks being
> so much slower than CPUs (not sure about that once the data is in memory ).
> Maybe there should be an option to reorder columns so that less space is
> wasted.

Out of curiosity, do I understand right that if I create table

CREATE TABLE sample1 (
a boolean,
b int,
c boolean
);

...it will take more storage than:

CREATE TABLE sample2 (
b int,
a boolean,
c boolean
);

...I don't think such ordering should matter, but I would like to know
how it really is. :)

Regards,
Dawid

On Tue, Jul 19, 2005 at 02:02:28AM +0200, Dawid Kuroczko wrote:
> On 7/18/05, Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> wrote:
> > However, maybe padding for alignment is a waste on the disk - disks being
> > so much slower than CPUs (not sure about that once the data is in memory ).
> > Maybe there should be an option to reorder columns so that less space is
> > wasted.
>
> Out of curiosity, do I understand right that if I create table
>
> CREATE TABLE sample1 (
> a boolean,
> b int,
> c boolean
> );
>
> ...it will take more storage than:
>
> CREATE TABLE sample2 (
> b int,
> a boolean,
> c boolean
> );
>
> ...I don't think such ordering should matter, but I would like to know
> how it really is. :)

Actually, I believe that's the case with just about every database,
though of course each one has different alignment constraints. The point
is that I don't know of any database that will silently re-order fields
under the covers to optimize storage.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Tue, Jul 19, 2005 at 02:02:28AM +0200, Dawid Kuroczko wrote:
> Out of curiosity, do I understand right that if I create table
>
> CREATE TABLE sample1 (
> a boolean,
> b int,
> c boolean
> );
>
> ...it will take more storage than:
>
> CREATE TABLE sample2 (
> b int,
> a boolean,
> c boolean
> );

Yep. If you created a C structure with those types it would add padding
also. It's a fact of life given that some (many/most?) processors can't
directly read a 4 byte value that's not 4-byte aligned. If you think
about it, the processor has to do two 4-byte reads (aligned) and glue
them together to get the value you want. The i386 has microcode to
automate this but RISC processors in particular make the compiler
and/or OS deal with it.

int, bool, bool 6 bytes, no padding
bool, int, bool 9 bytes, including 3 bytes padding
bool, bool, int 8 bytes, including 2 bytes padding

Assuming an int is 4 bytes. If it's 8-bytes it's even more obvious.

I think it would be a really good for postgresql to reorder the fields
in the background (on disk, not in select * statements).

First all fixed width fields, starting from highest alignment and going
down. Then the variable length fields. Note, postgresql actually has
optimisations for lookups on fixed width fields, but only if they are
fixed width from the beginning of the tuple. Ofcourse, NULL fields
screw it up but it would be good to make use of the optimisation
whenever possible.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Tue, Jul 19, 2005 at 11:48:16AM +0200, Martijn van Oosterhout wrote:
> int, bool, bool 6 bytes, no padding
> bool, int, bool 9 bytes, including 3 bytes padding
> bool, bool, int 8 bytes, including 2 bytes padding
We store bool's in a byte and don't compact? That would be another very
handy change... the first 8 bools in a table all use one byte, the next
8 use another, etc.

> Assuming an int is 4 bytes. If it's 8-bytes it's even more obvious.
>
> I think it would be a really good for postgresql to reorder the fields
> in the background (on disk, not in select * statements).

I believe the last time this was discussed the consensus was that we
should first have a way for users to do this manually, since the backend
would need most of that framework anyway. I don't know that much (if
any) work has been done on this.

One of the advantages of divorcing the in-memory page layout from the
on-disk page layout is that by doing so you get even more benefit from
re-ordering fields, and it's not dependant on figuring out how to
present fields in a different order than how they're stored in the page.
The downside is that it doesn't get you some of the in-memory benefits
that field ordering will get you, but I suspect in most cases the
on-disk gains will swamp those out anyway.

Does anyone have any idea what it would take to hack in divorcing disk
page layout from memory layout just for testing?
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Added to TODO:

* Research storing disk pages with no alignment/padding

---------------------------------------------------------------------------

Lincoln Yeoh wrote:
> I believe that one should leave such on-the-fly disk compression to the
> O/S. Postgresql already does compression for TOAST.
>
> However, maybe padding for alignment is a waste on the disk - disks being
> so much slower than CPUs (not sure about that once the data is in memory ).
> Maybe there should be an option to reorder columns so that less space is
> wasted.
>
> At 05:47 PM 7/17/2005 -0500, Jim C. Nasby wrote:
>
> >On Sat, Jul 16, 2005 at 03:18:24PM -0700, Ron Mayer wrote:
> >
> > > If that were practical, even more radical I/O saving tricks might be
> > > possible beyond removing alignment bytes - like some compression algorithm.
> >
> >True, though there's a few issues with zlib compression. First, you have
> >to be able to pull specific pages out of the files on disk. Right now
> >that's trivial; you just read bytes xxx - yyy. With compression things
> >are more difficult, because you no longer have a fixed page size.
> >
> >methods. Another factor is that more complex compression methods will be
> >much more CPU intensive.
> >
> >FWIW, the way oracle handles compression is as a one-time operation.
> >When you tell it to compress a table it will re-write the entire table,
> >compressing it as it goes. But any pages that get changed after that
> >will end up uncompressed. Of course in a data warehouse environment
> >that's perfectly acceptable.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 7/19/05, Jim C. Nasby <decibel(at)decibel(dot)org> wrote:
> > CREATE TABLE sample1 (
> > a boolean,
> > b int,
> > c boolean
> > );
> >
> > ...it will take more storage than:
> >
> > CREATE TABLE sample2 (
> > b int,
> > a boolean,
> > c boolean
> > );
> >
> Actually, I believe that's the case with just about every database,

I tried making alternating int and boolean fields (8 columns total), and
the loss due to padding was around 30%.

Out of curiosity I repeated the test using MySQL 4.1 MyISAM (alternating
int and tinyint fields versus ints fist, then tinyints) -- the resulting files
had the same size. So, for this case, MySQL MyISAM either reorders
data or stores data without padding.

Regards,
Dawid

> though of course each one has different alignment constraints. The point
> is that I don't know of any database that will silently re-order fields
> under the covers to optimize storage.

On Thu, 2005-07-21 at 09:23, Dawid Kuroczko wrote:
> On 7/19/05, Jim C. Nasby <decibel(at)decibel(dot)org> wrote:
> > > CREATE TABLE sample1 (
> > > a boolean,
> > > b int,
> > > c boolean
> > > );
> > >
> > > ...it will take more storage than:
> > >
> > > CREATE TABLE sample2 (
> > > b int,
> > > a boolean,
> > > c boolean
> > > );
> > >
> > Actually, I believe that's the case with just about every database,
>
> I tried making alternating int and boolean fields (8 columns total), and
> the loss due to padding was around 30%.
>
> Out of curiosity I repeated the test using MySQL 4.1 MyISAM (alternating
> int and tinyint fields versus ints fist, then tinyints) -- the resulting files
> had the same size. So, for this case, MySQL MyISAM either reorders
> data or stores data without padding.
>
> Regards,
> Dawid
>
> > though of course each one has different alignment constraints. The point
> > is that I don't know of any database that will silently re-order fields
> > under the covers to optimize storage.

Yep, I'm pretty sure I read it in their docs somewhere that the disk
ordering is determined by the db engine, not by the logical order in the
create table statement.

This is of course bittersweet, since an alter table add column in mysql
results in the entire table being read and rewritten back out, doubling
storage requirements of the table being altered, and often causing a
very long wait for large tables.