Rapid schema development with PostgreSQL

Re:Rapid schema development with PostgreSQL by tedunangst on Friday October 18, 2013 @04:53PM (#6573944)

I think the question is how does that compare to the usual trick of adding a key/value/attribute table?

  create table misc_attrs (maintable_id foreign key, name text, value text)

[ Parent | on HN ]

Re:Rapid schema development with PostgreSQL by simonw on Friday October 18, 2013 @06:41PM (#6574465)

When reading data back, it's more efficient to query a single table than to have to join against an extra attributes table

See https://news.ycombinator.com/item?id=6574269 for anecdotal evidence.

[ Parent | on HN ]

Re:Rapid schema development with PostgreSQL by moron4hire on Saturday October 19, 2013 @12:33AM (#6575345)

there are so many variables in that anecdote that there is nothing that can be gleaned from it.

[ Parent | on HN ]

Re:Rapid schema development with PostgreSQL by Herbert2 on Saturday October 19, 2013 @05:07AM (#6575850)

Alright, these were approximately the fields that were Postgre-fied.

CREATE TABLE event ( "references" text[], (was 2 tables, references and reference_on_event) "entities" text[], (ditto) "actors" text[], (ditto) "targets" text[], (ditto) "payload" json, (EAV) )

Gin index on all the text arrays. There are about 150k events saved per day and PostgreSQL is running on the same kinds of hardware as SQL Server was. The application is quite read heavy.

[ Parent | on HN ]

Re:Rapid schema development with PostgreSQL by einhverfr on Friday October 18, 2013 @09:52PM (#6575018)

There are times actually I find it helpful to break 1NF in PostgreSQL. This is certainly not free but it really helps some sorts of queries, and it avoids having to do a sort of pseudo-EAV to get certain things working.

[ Parent | on HN ]