Skinny Tables - Salesforce

Question

I read through some documents, and learned that Salesforce internally maintains metadata tables, pivot tables and large data tables, and salesforce kernel then performs queries on these tables to present the application to the tenant. While I was reading the concept of Skinny tables, I stumbled on line saying that SF stores Standard field and custom field values in different tables , which forces a join to happen in case a query refers to both kind of field. Skinny tables helps stores all frequents used fields data in a separate table to avoid join and thus giving faster results.

I am pretty confused when it is said that data for these two kind of fields are stores separately, while my understanding was that all data are stored in large heap tables.

Could you please help me grasp the concept of skinny table and help me to clarify the confusion?

Answer 1

It makes sense to store this info in separate tables. There's only handful of standard fields and almost all of them are indexed. It's a good idea to have them in a table that doesn't change very often (running ALTER TABLE ADD COLUMN in normal database when you have millions of rows isn't fun).

Similarly I wouldn't be surprised if they would keep "normal" custom fields (numbers, dates, booleans, short text) in one table and long text area/ rich text area & attachments in another. "BLOB" and "CLOB" if you're familiar with these data types; if somebody suddenly puts a very long string in the field and as a result the field has to expand and there's no room (data would overwrite next record on disk) - database has to move whole row into new location. That takes time, wastes space, creates holes which you need to keep track on and maybe try to fill in later... All kinds of problems. But that's really a concern for database administrators :)

If you need to know more about the underlying architecture check out these links:

• Force.com architecture overview
• Review(?) of architecture (but the whitepaper from 2008 the article talks about is a dead link. Probably this is the right one)
• yet another article

There's quite a lot going on under the hood. Hell, they even can't use the normal indexes and db statistics, they need something custom per customer! See the links at the end of the cheat sheet for more goodies.