Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this community
Can salesforce handle 50 or 70 million records?
in the past I worked on the org which had 20 million tasks and it was real problem, so wanted to get experts thoughts on what is the impact if we import 50 or 70 million records in the system, any guidance ? best practices?
basically what is the max volume salesforce can handle for single object records.
Can a Salesforce instance contain 50+ million records? Almost certainly. I've seen instances with millions of records, although none as large as 50 million. A Salesforce sales person would be able to confirm the max size of a table, if there is one. (50 million records will get their attention and you can expect a quick answer.)
Would I recommend Salesforce as the primary data solution for a 50+ million record need? Usually not, for the following reasons:
I'd be looking at ways to keep the high-volume data in an external big-data-friendly data store (Heroku, a Salesforce-owned company, would be an option -- so would Amazon Web Services, hosted traditional SQL databases or NoSQL databases, and even locally-managed servers). And then synthesize the report-level data into summaries that are loaded into Salesforce to be used there.
Being able to store 50M rows in a single organization would require 834 Unlimited Edition licenses, 5000 Enterprise Edition licenses, or 40 500MB "blocks" of additional data storage (or some combination thereof), making storing this amount of data very expensive. Assuming that the organization's soft storage limit was of no concern, hypothetically speaking, you would then be able to explore other considerations.
50M rows in a single table is easy if the rows are linked together logically; ID values are indexed, and are always able to be looked up in a fraction of a second. Searches (e.g. SOSL) performed against 50M rows would be incredibly fast, because all non-stop words are indexed in efficient index tables that can be scanned in a matter of seconds. Since there's an upper limit to the number of returned rows per search, each search can be optimized to return just relevant data.
Now, we get to the tricky part of business: reports and queries. For queries, there is a hard limit of 50,000,000 rows returned for users with View All Data. This doesn't mean that the database can't store more than that, it simply means that this is the most you can return at once. Exporting all of this data would be still fairly trivial, since you could simply break a query into two parts (e.g. CreatedDate < 2000-01-01 and CreatedDate >= 2000-01-01). Normal users are much more limited, at most 1,000,000 standard object rows or 333,333 custom object rows (because of the selectivity rules). Queries above this threshold would be cut off, and users would have to refine their queries.
Reports, similarly, can take a long time to run if users don't leverage indexes properly. I specifically recall a situation that occurred some time back where managers would try to run a sales report in one organization and the report would take nearly 10 minutes to run just to return a few hundred or few thousand opportunities. Eventually that issue was resolved, although I don't recall the specifics of how it was resolved. Even despite that long run time, the results were always accurate, and there was no concern over "missing" data.
However, like any other database system, there are definitely problems that can appear and affect performance. To combat performance issues, there are a number of tools available to organizations that are utilizing this much data storage. This includes sharing settings, standard and custom indexes, skinny tables, divisions, archiving, and selective filters.
Sharing settings can limit visibility, and these settings are indexed, meaning that a user that can only view 1M records out of all 50M records will have better performance than the system administrator trying to run a report to find 5M records out of all 50M records. Private settings are almost always better than public read-only settings for this reason.
Indexes are pointers to records that have a specific value, and they can be used to great effect. If a frequently used field is causing a report to run slowly, adding the External ID modifier or Unique value modifier can boost performance by over 1000%, assuming mostly unique values. Standard indexes can be set by an administrator, while custom indexes are built through technical support.
Skinny tables are used for small tables that meet a specific set of criteria. They can only contain so many fields and the field types are restricted. However, these tables perform better than the standard tables. Skinny tables can contain a subset of fields from a large object; all fields queried in a report or query must reside in the skinny table for the benefit of skinny tables to be utilized. Usually this means selecting the most frequently queried fields and placing them into a single skinny table for massive performance boosts.
Divisions are a type of setting similar to sharing controls, but do not affect record visibility directly. Instead, organizations with divisions can assign users and records to divisions, and by default, users will view only records in their division (but they can choose to view any division, so divisions are not a security control). Reports and searches can be limited by division, greatly boosting performance by selecting only a subset of the records. This feature has to be enabled by technical support, and only after analysis to ensure that the feature would benefit the organization.
Archiving is a mechanism that applies to tasks and events (usually the bulk of most organizations) where some records no longer appear in a normal query or report, but will still appear in "queryAll" queries, related lists, and detail pages. The cutoff time is configurable, and archiving isn't instantaneous, but it does generally get around the problems of having many tasks in the system, assuming users are closing tasks. If users don't close their tasks, but simply "dismiss" the reminders, then this pile up of open records can cause problems with performance. Generally, simply closing the tasks will resolve the problem, since most users do not have more than a few (dozen?) tasks open at once.
Finally, simply avoiding full table scans can yield incredible speed boosts. For example, using "equals," "starts with," "greater than," "less than," "greater or equal," and "lesser or equal" filters on an indexed field will yield faster returns than using "not equal to", "contains", or "ends with." Similarly, not using a wildcard at the beginning of a search term will greatly increase performance. More information on that is included in the "query optimization" documents that salesforce provides, including the "Working with Very SOQL Queries" document, "Inside the Force.com Query Optimizer" webinar, and many other documents hosted around the Salesforce.com community.
Personally, I know of implementations with 100M Tasks--Watch http://wiki.developerforce.com/page/Webinar:_Extreme_Salesforce_Data_Volumes_(2013-Feb) Next, how about loading 20M records in 1 hour? http://events.developerforce.com/en/events/webinars/bulk-api
The asnwer is YES.
Salesforce has come up with a new concept - BIG OBJECT
As the name suggests it can hold billions of records. Although, it has some limitations. You can not work with it like a Custom object.
Please check Salesforce documentation for more info: https://developer.salesforce.com/docs/atlas.en-us.bigobjects.meta/bigobjects/
You can also check trailhead on big object: https://trailhead.salesforce.com/modules/big_objects
http://sfdc-know-how.blogspot.com/2014/01/big-objects-in-salesforce-platform.html for more scalability Salesforce is working on multi-tenant big data store using HBase, which is an open-source database. Salesforce is also working on the Phoenix project, which allows one to run low-latency queries on top of HBase.
Initially, Big Objects will come with less functionality and more scalability. So you would want to use these Big Objects only if record count > 100 million and you can live with some platform limitations.
