Re: [GENEAL] dynamically changing table

On Mon, Mar 30, 2009 at 05:39:19PM +0200, A B wrote:
> Hi,
> In the next project I'm going to have a number of colums in my tables,
> but I don't know how many, they change. They all use integers as
> datatype though.. One day, I get 2 new columns, a week later I loose
> one column, and so on in a random pattern.

I think you should think of something else.

> I will most likely have a few million rows of data so I just wonder if
> there are any problems with running
> alter table x add column .....
> or
> alter table x drop column .....

Well, not as such. Except that deleting a column doesn't really delete
it, it hides it, so it never really goes away. So the number of
"columns" in your table will only go up and eventually you're going to
reach the point (around 1600 IIRC, probably earlier) where it will
simply stop working.

> Adding a column, will it place data "far away" on the disc so that
> select * from x where id=y will result in not quite optimal
> performance since it has to fetch columns from a lot of different
> places?

Nope, no extra cost there.

> Will deleting a column result in a lot of empty space that will anoy
> me later on?

Yes, the space isn't actually released until the next time you update
that row.

> Are there any other clever solutions of this problem?

If this is just for development where the actual space/columns used is
just temporary, your trick might work. Otherwise I'd suggest
normalising so the columns to become rows in another table. But you're
going to have to be more specific as to what you're trying to do if you
want proper answers.

Or perhaps an array of integers?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.