Skinny Tables- When/Why to use?

Skinny tables are helpful when you have Large Data Volumes (more than millions of records in object). These tables improves the ready only operations like reports, SOQL query performances. Below are pointers which explain everything about Skinny Tables:

1. Skinny tables are custom tables (read only) created by Salesforce if enabled by customer support. These tables contains important fields information instead of storing all fields information which is present in actual object.
2. Skinny tables can be created for Account, Contact, Opportunity, Lead, Case, and custom objects.
3. The Force.com platform automatically synchronizes the rows between skinny table and the base object,so the data is always kept current. 
4. Soft delete records ((i.e records in the Recycle Bin with isDeleted = true) are not present in Skinny tables which improves query output.
5. Custom indexes are also copied if present in base table.
6. Skinny tables support only few data types(Checkbox,Date,Date and time,Email,Number,Percent,Phone,Picklist (multi-select),Text,Text area,Text area (long),URL).
7. The Force.com platform determines at query runtime when it would make sense to use skinny tables, so you don’t have to modify your reports or develop any Apex code or API calls
8. You need to contact salesforce customer support in order to enable this.

Things which needs to be considered before implementing Skinny tables:

1. Skinny tables can contain maximum of 100 fields. There are also limitation on data type as mentioned above.
2. After creating Skinny table, if you decide to add one more field in Skinny table, then you have to contact customer support. They will delete existing skinny table and will recreate new one.
3. If you change the data type of any field which is used in Skinny table, then Skinny table becomes invalid (unusable) and you need to contact customer support to again recreate it.
4. Skinny tables can not refer fields from another object.
5. Skinny tables get copied to full copy sandbox after refresh but not on other sandbox. in order copy it for other sandbox, contact customer support.
6. Skinny tables can increase the query performance of SOQL queries not SOSL queries as SOSL search based on flat file search whereas SOQL is based on database search.

Important facts which needs to be considered while designing any solution which includes Large data volumes in Salesforce

• Sharing calculation is even performed for records which are in recycle bin which may impact your performance. So if you don't need records then perform hard delete on records after archiving it.

• Only Bulk API supports hard delete functionality.

• Adding custom indexes on records may improve query performance but can degrade the performance of database insert and update. That is reason that Salesforce doesn't allow developers to add custom indexes by themselves. You have to raise support ticket and Salesforce will evaluate your use case before adding custom indexes. So always evaluate the pros and cons before adding any new custom indexes.

• As a best practice, don't use formula fields in where clause in SOQL. It degrade the SOQL performance if it doesn't return deterministic values (formula returns pre-defined set of values which are repeatable).

• Now Salesforce allow you to add custom index to formula fields only if it gives deterministic results

• Use readonly attribute on VF page if you want to query more than 50 millions records and display the result on UI by processing those records.