Is discourse's database structure , optimized?


(SMHassanAlavi) #1

When we create a user_custom field, a row at user_fields will be created. and each time we add info to this user custom field, a row at user_custom_fields will be created.
Now, When you want to select or modify these custom fields, you should select the id of the user_custom_field at user_fieldsand then, from that id, you should select the property from user_custom_fields table. and this isn’t good.
I think it could be better like this:
each time we create a user_custom_fields, a column at userstable should be created with name of that user_custom_field we want. in this way, calling this property will be so much faster.
what is the reason of the present database structure?


(Matt Palmer) #2

Because dynamic DDL statements are an amusing way to cause all manner of perplexing problems in your database. Whilst PostgreSQL is a lot better at executing DDLs live than Certain Other Database-Like Systems, there’s still plenty of ways you can seize up your database. As an added bonus, unless most users have values for most custom fields (rarely the case), each of those columns is consuming an extra four bytes of storage, which doesn’t seem like much, until you start thinking about larger sites with lots of custom fields, where you start thinking, “now what was the point of all this again?”

Which is where this all gets to. Have you benchmarked the performance of your suggestion against the current implementation? I’d be quite surprised if you could pick the difference out of the noise, because the custom fields tables are all heavily indexed.