39

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.

33

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:

  • Salesforce governor limits are generally toxic to big data. Take a look at the ironically named 38-page "Limits Quick Reference Guide" for what I'm talking about. The big ones you'll run into are query limits, aggregate statement row limits, efficient query restrictions, batch job size limits, and batch job daily execution limits. In short, it will be difficult to create any sort of functionality around the data that requires custom code. If you just need to store the data and run limited reports on it, you would probably be fine, but if you need actual functionality that deals with the entire data set you will get frustrated quickly. That said, Salesforce has been murmuring about a true "big data" NoSQL-type solution in the near future, but it hasn't been announced yet.
  • Data storage will be expensive. You'll need to get a quote, but under Enterprise the data limit is 1GB + 20MB/user, and every row costs you a minimum of 2k of data quota. 50m rows will consume at least 100GB. Like I said, get a quote, but data is not cheap in the Salesforce world.

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.

  • 4
    The limit on the number of rows is based on the available free space for data storage on the application cluster where the organization is hosted. Any time a cluster reaches a certain threshold, they simply drop in more storage. When a cluster reaches a critical size, they add a new cluster and split the existing cluster as a migration. A single cluster is easily capable of holding more records than any one organization would ever store, I'd hazard somewhere in the billions or trillions. Oracle documentation states there is no logical maximum. – sfdcfox Feb 12 '14 at 5:53
  • Limits link is broken – Brad Thomas Nov 18 '15 at 22:31
  • 4
    Update! The limits guide is now 49 pages :) – Sam Kamensky Feb 2 '17 at 15:47
  • 1
    @SamKamensky 49 pages, and yet they still don't mention the XML Tree Depth limit! – JDB Mar 23 '17 at 21:02
31

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.

  • what do you mean by "skinny tables", would it be another object but not a report type right? – Bartley Jan 29 '15 at 9:50
  • @Bartley Skinny tables are database level tables that contain a subset of fields from another table. Basically, when you have a skinny table, queries that only retrieve and filter fields in that table, soql will change its query execution plan to use this abridged table, retrieving records faster than using the main table that backs the skinny table. See Help and Training for more details. – sfdcfox Jan 29 '15 at 15:42
8

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

1

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.

  • 3
    I'm having a hard time understanding your answer... Can you polish it up a bit? – dphil Oct 13 '14 at 18:56
  • This is really good info, but you might need to introduce it more and elaborate on the implications for it to be useful to most people. – ca_peterson Feb 19 '15 at 3:33
  • Please add more detail and put into context how this answers the question – Sam Kamensky Feb 2 '17 at 15:27
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.