Re: Converting MySQL tinyint to PostgreSQL - Mailing list pgsql-general

From Martijn van Oosterhout
Subject Re: Converting MySQL tinyint to PostgreSQL
Date
Msg-id 20050714212922.GA6184@svana.org
Whole thread Raw
In response to Re: Converting MySQL tinyint to PostgreSQL  ("Jim C. Nasby" <decibel@decibel.org>)
Responses Re: Converting MySQL tinyint to PostgreSQL
List pgsql-general
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@svana.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.

Attachment

pgsql-general by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: ERROR: could not open relation
Next
From: Karsten Hilbert
Date:
Subject: Re: chosing a database name