Some (not all) entity names are simply blank, which should never be possible from this name formula:
I already tried refreshing, waiting, and changing the field values that are used by the formula, but the Names stay BLANK.
It looks the like the better title for this issue might be “My Fibery Workspace is SO SLOW that it is completely unusable” – I have just seen evidence that the name formula is still “catching up” to entity updates from scripts that ran hours ago, but the entire Workspace is still unresponsive.
@mdubakov Fibery Team really needs to come clean about the technical limitations of DB size in Fibery. It is completely unusable at this scale 
. How many months of Workspace development do we have to invest before discovering these limitations the hard way?
Hello.
We are indeed not so clear about DB size limitations. At the moment we are more in reactive phase – when someone complains about performance because of DB size, we try to tweak things here and there and usually it helps.
I can say that 100k-300k DB size should work reasonably fine, although there may be problems on UI in some places. We have active customers with such DB sizes and no huge complains from them. Although currently limit should be somewhere near, as at this size things are not fast in Fibery (views, formulas, etc.).
Could you please tell about DB sizes you plan to work with or already working with?
According to you complains with Automations and Formulas.
Things can get tricky here if you are heavy on scripting (and as I understand you are).
Let me share some internal dev. details here, to provide some insights on how to speed things up.
TLDR: use batching in your scripts.
1.) At the moment Automation Rules and Formulas are calculated sequentially per workspace.
2.) Combination of Formula + Automation Rules can generate a huge chain of calculations in response to a User change. Let’s say User change some field value. Then Formula1 is calculated, then AutomationRule1 reacts to Formula1 change and change Field2, then Formula2 is calculated as a reaction of Field2, etc. Chain can be long, and it may generate pressure on your workspace.
3.) We use batching in Automation Rules (except scripting, as it’s user written). That means if formula field value is changed for let’s say 1k entities, and Automation Rule is run because of this change, rule will perform 1 batch call to fibery for all 1k entities. This is fast.
4.) When calling Rule or a Formula due to change in Fibery, we do a batch calls for data in Event.
Event is smth you can observe in Fibery webhooks. It’s a bit techy, but at the moment Event is generated per fibery command call (e.g. fibery.entity/update).
If you have a Script written in the following manner:
for (const entity of args.currentEntities) {
await fibery.updateEntity(someUpdateHere);
}
it will lead to losing batching in further chain of calculations. And things will get slower. For example there will be 1k events, and formula service will process them sequentially one by one. And this will lead to 1k separate update calls in Fibery. Let’s say each event processing by formula takes 30ms. Then we get 30 seconds as a total. On the other hand batch call will be executed in about 500-1000ms. (I’m giving arbitrary numbers here just to show the scale)
On the other hand such script:
const updates = [];
for (const entity of args.currentEntities) {
updates.push(getSomeUpdate(entity);
}
await fibery.batchUpdateEntity(updates);
will preserve further batching and will be fast.
Unfortunately batchUpdateEntity api is not present in scripts now. There were no necessity and requests before.
Please respond if all above seems like your case. I will add batch api to Scripts them, it should be very fast change. I expect about 100 times faster processing when moving to batches in scripts.
There are ideas to make improvements on our end without making all scripts to be written in a batch manner. But it is out of priority for us now.
3 Likes
@Sergey_Truhtanov Thanks for looking into this for me, and being forthright about your process. 
Yes, I have scripts that would benefit greatly from this. I already tried Promise.all() but it did not seem to help with speed.
I was very surprised that the UI becomes unresponsive while automations/formulas are calculating. This seems unintuitive if it runs on the server, and represents a big limitation 
The biggest DB is Calls, and it is difficult to know the size, but ~1.5M records:
I had to add additional complexity to the Workspace because of Reports inability to handle >200k records, and Smart Folders not working to pre-filter the number of records for Reports by subject and date range. But these would have been required anyway since Reports do not support a time series of aggregated data (i.e. graph of #Calls aggregated by week for a specific user).
So I created a Reporting Periods DB and Stats summary DBs to aggregate all these records across all the different reporting periods and reporting subjects (MUsers and Clients).
The aggregation DBs add a huge number of records:
(#Clients + #MUsers) * (#Reporting Periods) * (#of aggregated DB’s)
#Clients: ~150
#MUsers: ~350
#Reporting periods: ~69, but soon ~300
#of aggregated DBs: 2, maybe 4 soon
Also each aggregation record is linked to every data record that it aggregates: it uses a formula field using filter() to collect all records within its associated reporting period for its associated subject (e.g. Calls), and another formula field to calculate totals from all these records.
Approximate record counts of additional DBs:
Calls: 1.6M (?)
Deals: 660k
Tasks: 224k
Contacts: 200k
Accounts: 17k
Leads: 2k
ZUsers: 1.2k
Also some DBs have many links - especially ZUsers and MUsers are connected to everything else.
All of these numbers will grow daily as more data arrives through the custom integration from Zoho. I’m looking into the growth rate, but maybe ~25% per year.
Oh-oh.
You are not kidding with Fibery as I see 
1.6M is indeed beyond the limit we expected, and I see you are getting timeouts on Views 
.
I don’t think we will address this issues in nearest future to be fair 
. But anything can change…
I was very surprised that the UI becomes unresponsive while automations/formulas are calculating. This seems unintuitive if it runs on the server, and represents a big limitation
This issue we will address at some point though, although we don’t get such complains from other companies tbf. I’ve made few attempts already to mitigate those UI slowness issues, but it seems it’s not enough for your case. Our problem here is that we send all changes to UI for live-updates to work, but many of them are not necessary.
Anyway I hope that batching will reduce number of events (as in the end UI live-updates will benefit from batches as well). I will immediately write here after implementing and testing batch api in Scripts. Hopefully tomorrow.
Let’s see how that will influence your workspace performance.
2 Likes
Could you consider this – instead of completely blocking the UI until server script/formulas are completed, just disallow users from updating entities and display a spinner in the corner of the screen; Hover over the spinner to see an explanation, e.g. “Automations are running, please be patient…”
Perhaps this could be an optional behavior selected Workspace settings.
Probably not possible but having paging controls would also make it easier for the UI to accommodate larger DB’s.
I am surprised that Fibery still works with 1M+ records. As Sergey said, our internal limit is 300K, however we mention it only when people ask, so I fixed this and added a section to our user guide.
https://the.fibery.io/@public/User_Guide/Guide/Databases-7/anchor=Database-size-limit--071b4837-3a8a-4819-a2e8-3913f0ccbbbc
4 Likes
Yeap, working on this.
Unfortunately it seems it will take a bit more than I expected, plus Tuesday was a holiday in my country. So shifting estimate a bit. Probably Thursday, as change seems quite big, and influence other apis, so need to test carefully.
I’ve finally release batch api in scripts (createEntityBatch, updateEntityBatch, addCollectionItemBatch, etc.).
While this api won’t necessary work much faster than single commands (there is known room for improvement here from our side), it will preserve batching in your chain of calculations. So I expect no large queues for Formulas or Automations as you have now.
Please try it in your scripts and tell if it helps.
Also any feedback on new batch api design itself is very welcome!
3 Likes
Do you have any documentation on these? Or the function declarations with params?
I hoped that docs in editor(after you press . after fiberyService object) would be enough.
But here it is (same you will see in editor intellisense):
/**
* Adds items to entities collection.
* @function
* @param {string} type type name, e.g. "User Story", "Bug"
* @param {string} field collection field name. You can use namespace qualified field names, or just field title.
* @param {Array[]} args array of entity id and item id pairs.
* @param {string} args[].id id of fibery entity. Item will be added to its "field" collection
* @param {string} args[].itemId id of fibery entity, that is added to collection.
*/
addCollectionItemBatch(type: string, field: string, args: {id: string, itemId: string}[]): Promise<void>
/**
* Removes items from entities collection.
* @function
* @param {string} type type name, e.g. "User Story", "Bug"
* @param {string} field collection field name. You can use namespace qualified field names, or just field title.
* @param {Array[]} args array of entity id and item id pairs.
* @param {string} args[].id id of fibery entity. Item will be removed from its "field" collection
* @param {string} args[].itemId id of fibery entity, that is removed from collection.
*/
removeCollectionItemBatch(type: string, field: string, args: {id: string, itemId: string}[]): Promise<void>;
/**
* Updates entities.
* @function
* @param {string} type type name, e.g. "User Story", "Bug"
* @param {Array[]} entities array of entities to update.
* @returns {Promise.<object[]>} updated entities
*/
updateEntityBatch(type: string, entities: object[]): Promise<object[]>;
/**
* Creates entities.
* @function
* @param {string} type type name, e.g. "User Story", "Bug"
* @param {object[]} entities entities to create. Each entity is key value pairs of values to set. For reference fields value is entity id. For enums value can be either enum value id or just enum value title.
* @returns {Promise.<object[]>} created entities
*/
createEntityBatch(type: string, entities: object[]): Promise<object[]>;
/**
* Deletes entities.
* @function
* @param {string} type type name, e.g. "User Story", "Bug"
* @param {string[]} ids array of ids of deleted entities.
*/
deleteEntityBatch(type: string, ids: string[]): Promise<void>;
1 Like
What happens when a script exceeds its CPU or elapsed time limits, but it has already made calls to create/update some entities? Is everything rolled back/undone, or are the changes kept?
Changes kept. We just stop the script and that’s it. Any http or fibery api calls that were already made – will finish.
1 Like
@Sergey_Truhtanov Will that also be true for all entities involved in your new batch API calls? And is there a limit to the number of entities we can create/update with such a call?
It’s true for batch commands as well.
In order to correspond to Fibery limitations on a batch operations, we chunk user-provided batch by 1000 entites and execute batches sequentially one by one in our batch API implementation. So it can be that a script has timed out in the middle, and only part of batch commands have been sent.
You can chunk yourself by 1000, so you can control and log yourself. If it’s usefuk for your case.
1 Like
Thanks.
Do the Promises returned by the fibery.*Batch() calls resolve only after all their entities have completed being processed?
That’s right. And btw only one promise is returned from fibery.*Batch() call.
1 Like
I have a question related to this.
We have created several automations, formulas and webhooks related to our planning module daily notification email system.
Think of:
- A formula that checks if an item has deadline today and not state done.
- An automation for creating a new calendar year. If so, then check through all entities that already exist if they have a deadline that matches the newly created year. If so, link them.
- An automation that triggers a webhook on schedule and checks 8 databases provides all data for the daily notification email.
Etc.
The number of entities that meet the criteria is often low. But Fibery obviously has to search all the databases based on those criteria.
@Sergey_Truhtanov can we expect problems if we do this for multiple databases that together have a lot more than 300,000 entities?
I’ve already created automations for the larger databases to aggregate data after 1 year so that the entities itself can be removed. But that’s not possible/ideal for every database.
@YvetteLans
An approach I’ve had to take for script Rules that cannot complete in the allotted 60 seconds is below. It demonstrates deadline-checking and using the new fibery.createEntityBatch() call.
The script searches for its subject entities sorted in order of least-recently-modified, so that it does not try to process entities that it has recently modified. (In this case I know that these entities are not modified anywhere else, but if that were not the case then I would add a Date field to track when they were last modified by this script, and retrieve them sorted by that field instead of the standard Modified-Time).
The main loop has three different Types to process, so upon every script invocation (i.e. every hour) it starts with a different type to ensure that no type gets consistently short-changed due to processing time limits.
The other relevant feature is the use of deadline checking to make the script exits on its own before being killed.
The basic function of the script is to ensure that every “subject” record (of 3 different subject types) has its own linked “Stats” record for every Reporting Period entity:
- Get all Reporting Period entities
- For each different Subject Type (of three):
a. Get all subject records of that Type (sorted by least-recently-modified)
b. For each Subject record:
… Check each Reporting Period, and if if the subject doesn’t already has a corresponding linked Stats record, create one.
NOTE: This is a somewhat hacked-up/incomplete version of the actual script for purposes of illustration.
const DEBUG = false
const log = console.log, warn = console.log
const dbg = (...args) => { if (DEBUG) console.log(...args) }
const assert = (condition, msg) => { if ( !condition ) throw Error(msg) }
const numberOfTypes = 3 // How many different types of Stats records to process?
const MS_PER_HOUR = 1000*60*60, MS_PER_DAY = 24*MS_PER_HOUR
const subject_mod_cutoff_hours = 1 // Only retrieve subject recs NOT modified in the past X hours
const fibery = context.getService('fibery')
const started = new Date()
const deadlineSeconds = 58 // max execution time
const elapsedSeconds = () => Math.round((new Date() - started)/100)/10
const deadlineExceeded = () => elapsedSeconds() >= deadlineSeconds
const checkDeadline = () => { if (deadlineExceeded()) throw `😡 max script runtime (${deadlineSeconds}s) reached` }
const cutoff = new Date (started - subject_mod_cutoff_hours*MS_PER_HOUR ) // Subject search modification minimum-age cutoff
// Queue the creation of a Fibery entity
const batchCreateEntityQueues = {}
const maxBatchLength = 1000
async function createEntity_queued( type=null, entity=null ) {
const totalQueuedCount = () => Object.values(batchCreateEntityQueues).reduce( (total, q) => total + q.length, 0 )
if ( type ) {
// Queue an entity for creation. Each Type gets its own queue.
if (!batchCreateEntityQueues[type]) batchCreateEntityQueues[type] = []
batchCreateEntityQueues[type].push(entity)
}
if ( type==null || totalQueuedCount()>=maxBatchLength ) {
const promises = []
for ( const [type, entities] of Object.entries(batchCreateEntityQueues) ) {
if ( entities.length > 0 ) {
dbg( `🎇Creating ${entities.length} "${type}" records...`)
promises.push( fibery.createEntityBatch(type, entities) )
batchCreateEntityQueues[type] = []
}
}
return Promise.all(promises)
}
}
try {
// Scan subject records for missing Stats records
async function scanSubjectsAndCreateMissingStats( subjects, subjectType, stat_SubjectFieldName ) {
const Type = subjectType.replace(/.*?\//, '') // Subject type without the Space-name prefix
try {
// Create a missing Stats entry for a RP
const createStat = async (subject, rp) => {
++statsCreated[subjectType], ++statsCreated.total
const typeSelector_value = Type.replace(/s$/, '') // Enum name for Subject Type single-select field
return createEntity_queued(Stats_type, {
'Type' : typeSelector_value, // "Subject Type" single-select value
'Reporting Period' : rp['fibery/id'], // Link to Reporting Period
[stat_SubjectFieldName] : subject['fibery/id'] // Link to Zoho subject
})
}
for( const subject of subjects ) {
const subjectName = subject[ Subject_Name ]
const stats = subject[ Stats ]
const statsCount = stats ? subject[Stats].length : 0
const missingRecsCount = reportingPeriods.length - statsCount*numberOfTypes
if ( missingRecsCount <= 0 ) {
dbg( `👍 "${subjectName}" has ${statsCount}/${reportingPeriods.length} [${StatsDB}] recs - skipping` )
continue
}
log( `🟣"${subjectName}" is missing ${missingRecsCount} [${StatsDB}] records (of ${reportingPeriods.length})...` )
// Check if a particular RP is already linked in Stats (key = Type and RP name)
const linkedReportingPeriods = new Set( stats.map(
(stat) => [ stat[Stats_Name].replace( /:.*/, '' ), true ] // First part of Stats name is its linked RP name
))
// For each RP, check if subject already has a Stats record for it
for( const rp of reportingPeriods ) {
if ( !linkedReportingPeriods.has( `${Type}|${rp[Reporting_Period_Name]}` ) ) {
dbg(` ➕Creating missing ${StatsDB} [${rp[Reporting_Period_Name]}] for "${subjectName}"`)
await createStat(subject, rp)
}
checkDeadline()
}
}
}
catch(err) {
if ( err instanceof Error ) throw err
else log(err)
}
finally {
log(`👍 Created ${statsCreated[subjectType]} missing [${subjectType}] stats`)
}
}
// Get subject records to check
const getSubjects = (subjectType) => fibery.executeSingleCommand({
'command' : 'fibery.entity/query', 'args': {
'query' : { 'q/from': subjectType, 'q/select': [
'fibery/id',
Subject_Name,
{[Stats] : {
'q/select' : [ Stats_Name ],
'q/limit' : 'q/no-limit'
}} ],
'q/where' : ['<', [ 'fibery/modification-date' ], '$cutoff'],
'q/order-by' : [[[ 'fibery/modification-date' ], 'q/asc']], // Least-recently-modified records first
'q/limit' : 'q/no-limit',
},
'params': {'$cutoff': cutoff}
}
})
// Get all Reporting Periods - https://community.fibery.io/t/how-to-access-data-stored-in-enum-name-via-query/3325
const reportingPeriods = await fibery.executeSingleCommand({
'command': 'fibery.entity/query', 'args': {
'query': {
'q/from' : Reporting_Period_type,
'q/select' : ['fibery/id', Stats_Name],
'q/limit' : 3000, // There will be ~69 RP's per year per type
},
}
})
// Get and process all subject records
const processSubjects = async(subjectType, subjectFieldName) => {
statsCreated[subjectType] = 0
const subjects = await getSubjects(subjectType)
dbg(`\n${subjects.length} filtered [${subjectType}] records to process...`)
await scanSubjectsAndCreateMissingStats(subjects, subjectType, subjectFieldName)
}
// MAIN
// Alternate the processing order of types, because the script is triggered once per hour
// and may not have enough time to complete processing everything.
let next = started.getHours()
for ( let cnt=0; cnt < numberOfTypes; cnt++ ) {
next = (next+1) % numberOfTypes
if ( next===0 )
await processSubjects(MetaUsers_type, Stat_MetaUsers_field) // Process MetaUsers
else if ( next===1 )
await processSubjects(Clients_type, Stat_Clients_field) // Process Clients
else if ( next===2 )
await processSubjects(Partners_type, Stat_ZohoInstances_field) // Process Partners
else
assert(false, 'Did you forget something...?')
checkDeadline()
}
await createEntity_queued()
}
catch(err) {
log(err)
if ( err instanceof Error ) throw err
}
finally {
await createEntity_queued()
log(`✅ DONE - created ${statsCreated.total} missing ${StatsDB} records in ${elapsedSeconds()} seconds`)
}