On Wed, 9 Mar 2011 10:56:19 -0800
Mark Holmquist <[email protected]> wrote:
> > I used arrays way back when I first started using Postgres but gave
> > them up pretty quick. I found that whenever I used an array, a small
> > table was a lot simpler and more flexible.
>
> That would be a *lot* of tables for me, and I doubt that would be
> acceptable. I wanted this app to scale well, and having thousands of
> tables lying around would make a nightmare on the sysadmin side.
I'm not trying to argue you into fitting your app into PyGreSQL if it
doesn't fit but I wonder about your data model. Are you saying that
you currently have thousands of fields defined as arrays?
> On top of that, I allow variable-length arrays--a table would have to
> be static, or ALTERed each time I needed more space--and that would
I must be misunderstanding something here. This is what I mean. If I
have a table that looks like this;
CREATE TABLE order_item (
order_item_id serial PRIMARY KEY,
model_id REFERENCES model,
quantity int,
serial text[]);
I would change it to this;
CREATE TABLE order_item (
order_item_id serial PRIMARY KEY,
model_id REFERENCES model,
quantity int);
CREATE TABLE serial (
order_item_id int REFERENCES order_item
serial_num text);
I would probably put an index on serial_num. This table is, of course,
variable length. I can attach as many serial numbers to any order that
I wish. So, one array field translates into exactly one table.
In practice I have very few situations like this anyway. Invariably I
decide, possibly after the database has been in use, that I need more
information about the item so I need to add more fields and an array
column would have to be changed anyway, along with all the code using
it. I'm not saying that I get a lot of these types of requirement
changes. Certainly no more than once a day.
> increase the size of the table not by one cell, but by however many
> rows I had at that point, even if that entry was the only one to use
> the last column(s).
Are you concerned about the overall physical size of the database or
speed of access? Cheap drives deal with the first and PostgreSQL deals
with the second.
> I may have to switch to psycopg, but thanks for the help and ideas,
No problem. As I said, I am not trying to be argumentitive here. I'm
just curious about your model.
> guys. If you do fix the arrays, let me know and maybe I can switch
> back, but at the moment I don't have the patience to fix the library
> myself. Cheers!
Unfortunately in an open source environment like this it is the people
with the itch that drive feature changes. As you can see, no one seems
to have the itch for this particular feature. On the other hand I'm
not sure how hard it would be to add. One day when I get bored I might
look at it.
Take care.
--
D'Arcy J.M. Cain
PyGreSQL Development Group
http://www.PyGreSQL.org
_______________________________________________
PyGreSQL mailing list
[email protected]
http://mailman.vex.net/mailman/listinfo/pygresql
