Storing Allowed Websites Per User in Postgres

views:

answers:

+1 Q:

Storing Allowed Websites Per User in Postgres

Hi all,

I have a User table in my Postgres database. In my application, the User can have various allowed websites. My question is: which is more disk space efficent, having a many-to-many relationship between a user and a url or storing the array in JSON in a column in the User table. Essintially, how much space does postgres use to store table headers.

Thanks.

+1 A:

which is more disk space efficent, having a many-to-many relationship between a user and a url or storing the array in JSON in a column in the User table.

Updating a many-to-many relationship means an UPDATE (and/or DELETE?) statement.

Updating a JSON array stored in a database tables means:

SELECTing the data to get it out of the database, to the application
Manipulating the data in the application
UPDATE statement to write the updated JSON array back to the table

Which is simpler/more efficient to you?

OMG Ponies 2010-07-22 17:27:33

@OMG Ponies: Postgres has a native array type - it doesn't involve that much application manipulation. (Not that I'm saying the array method is better, I agree with a many-to-many.)

rfusca 2010-07-22 18:03:08

@rfusca: I was trying to find how many bytes Postgre allocates for VARCHAR - do you have a link?

OMG Ponies 2010-07-22 18:15:33

@OMG Ponies: There a section that talks a bit about it. Its a little hard to calculate because postgres will automatically compress long strings. http://www.postgresql.org/docs/current/static/datatype-character.html

rfusca 2010-07-22 18:50:26

ansaurus

tags:

views:

answers:

Storing Allowed Websites Per User in Postgres

related questions