Re: moving from MySQL to pgsql

On 10/11/2012 05:07 PM, Vineet Deodhar wrote:
> On Thu, Oct 11, 2012 at 1:12 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au
> <mailto:ringerc(at)ringerc(dot)id(dot)au>> wrote:
>
> The difference between SMALLINT and BOOLEAN (or TINYINT if Pg
> supported it) is 1 byte per column. If you had 30 smallint columns
> and quite a few million rows it might start making a difference, but
> it's *really* not worth obsessing about. Unless you have
> high-column-count tables that contain nothing but lots of integers
> of range 0-255 there's no point caring.
>
> --
> Craig Ringer
>
>
> To give an example, I have tables for storing master records (year
> master, security master, etc.) for which pkid TINYINT is just sufficient.
> These pkid's are used as fk constraints in tables for storing business
> transactions.
> The no. of rows in business transactions tables is in millions.
> Here, I NEED to worry about the storage space occupied by the pkid fields.

AFAIK in most situations alignment requirements will mean you won't gain
any space in those situations anyway.

I would be truly amazed if you saw more than something like a 1%
difference in size due to this; it'll be *massively* outweighed by all
the other differences. You're optimising prematurely. See if it's a
problem in practice, and if it is look into using a custom data type
(warning: lots of work) or some other approach.

--
Craig Ringer