| CARVIEW |
Navigation Menu
-
Notifications
You must be signed in to change notification settings - Fork 2.5k
Database with large JSON column takes long time to load even in view #6739
Replies: 1 comment · 5 replies
-
|
How many Could you provide a code sample how you are sending the request? Could you provide a sample payload of these big JSON objects or at least their size? I'll try to create a test setup a little bit later today and will investigate it in more details. |
Beta Was this translation helpful? Give feedback.
All reactions
-
|
My test database was seeded with 100k Test data seed script// node index.js
import Pocketbase from "pocketbase"
const pb = new Pocketbase("https://127.0.0.1:8090")
await pb.collection("_superusers").authWithPassword("test@example.com", "1234567890")
pb.autoCancellation(false)
const usersPromises = [];
for (let i = 0; i < 10; i++) {
usersPromises.push(pb.collection("users").create({
"email": i + "_test@example.com",
"name": i + "_test",
"password": "1234567890",
"passwordConfirm": "1234567890",
}))
}
const users = await Promise.all(usersPromises);
const largeTxt = "a".repeat(1000000);
for (let user of users) {
for (let i = 0; i < 100; i++) {
const replsPromises = []
for (let j = 0; j < 100; j++) {
const prefix = `${i}_${j}_`;
replsPromises.push(pb.collection("repls").create({
user: user.id,
name: prefix + `test`,
files: {
"abc": prefix + largeTxt,
},
}))
}
await Promise.all(replsPromises)
}
}Test requests:
1. No
2. With
As seen above we can directly make 2 observations:
So to summarise:
If this doesn't help, please elaborate more on your use case and the requests you are sending. |
Beta Was this translation helpful? Give feedback.
All reactions
-
|
Regarding the earlier mentioned issue with the way how we construct the CREATE INDEX `idx_9w4jJvgBb8` ON `repls` (`user`, `id`)(note that the combined index columns order is important) Results locally in ~1.3s response time for both with and without |
Beta Was this translation helpful? Give feedback.
All reactions
-
|
Mmm i tried to create such index but from the admin ui i can't select |
Beta Was this translation helpful? Give feedback.
All reactions
-
|
Uh scratch that, just realized i can edit the sql field while creating the idx |
Beta Was this translation helpful? Give feedback.
All reactions
-
|
Just for info in case someone stumble here: with v0.27.2 |
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 1
This discussion was converted from issue #6739 on April 18, 2025 13:53.
Uh oh!
There was an error while loading. Please reload this page.
-
Hi, I have a decently simple database of which the exported tables looks like this
[ { "id": "_pb_users_auth_", "listRule": "", "viewRule": "", "createRule": "", "updateRule": "id = @request.auth.id", "deleteRule": "id = @request.auth.id", "name": "users", "type": "auth", "fields": [ { "autogeneratePattern": "[a-z0-9]{15}", "hidden": false, "id": "text3208210256", "max": 15, "min": 15, "name": "id", "pattern": "^[a-z0-9]+$", "presentable": false, "primaryKey": true, "required": true, "system": true, "type": "text" }, { "cost": 10, "hidden": true, "id": "password901924565", "max": 0, "min": 8, "name": "password", "pattern": "", "presentable": false, "required": true, "system": true, "type": "password" }, { "autogeneratePattern": "[a-zA-Z0-9_]{50}", "hidden": true, "id": "text2504183744", "max": 60, "min": 30, "name": "tokenKey", "pattern": "", "presentable": false, "primaryKey": false, "required": true, "system": true, "type": "text" }, { "exceptDomains": null, "hidden": false, "id": "email3885137012", "name": "email", "onlyDomains": null, "presentable": false, "required": false, "system": true, "type": "email" }, { "hidden": false, "id": "bool1547992806", "name": "emailVisibility", "presentable": false, "required": false, "system": true, "type": "bool" }, { "hidden": false, "id": "bool256245529", "name": "verified", "presentable": false, "required": false, "system": true, "type": "bool" }, { "autogeneratePattern": "users[0-9]{6}", "hidden": false, "id": "text4166911607", "max": 150, "min": 3, "name": "username", "pattern": "^[\\w][\\w\\.\\-]*$", "presentable": false, "primaryKey": false, "required": true, "system": false, "type": "text" }, { "autogeneratePattern": "", "hidden": false, "id": "users_name", "max": 0, "min": 0, "name": "name", "pattern": "", "presentable": false, "primaryKey": false, "required": false, "system": false, "type": "text" }, { "hidden": false, "id": "users_avatar", "maxSelect": 1, "maxSize": 5242880, "mimeTypes": [ "image/jpeg", "image/png", "image/svg+xml", "image/gif", "image/webp" ], "name": "avatar", "presentable": false, "protected": false, "required": false, "system": false, "thumbs": null, "type": "file" }, { "exceptDomains": null, "hidden": false, "id": "et0pq3ch", "name": "avatarUrl", "onlyDomains": null, "presentable": false, "required": false, "system": false, "type": "url" }, { "hidden": false, "id": "autodate2990389176", "name": "created", "onCreate": true, "onUpdate": false, "presentable": false, "system": false, "type": "autodate" }, { "hidden": false, "id": "autodate3332085495", "name": "updated", "onCreate": true, "onUpdate": true, "presentable": false, "system": false, "type": "autodate" } ], "indexes": [ "CREATE UNIQUE INDEX `__pb_users_auth__username_idx` ON `users` (username COLLATE NOCASE)", "CREATE UNIQUE INDEX `__pb_users_auth__email_idx` ON `users` (`email`) WHERE `email` != ''", "CREATE UNIQUE INDEX `__pb_users_auth__tokenKey_idx` ON `users` (`tokenKey`)" ], "system": false, "authRule": "", "manageRule": null, "authAlert": { "enabled": true, "emailTemplate": { "subject": "Login from a new location", "body": "<p>Hello,</p>\n<p>We noticed a login to your {APP_NAME} account from a new location.</p>\n<p>If this was you, you may disregard this email.</p>\n<p><strong>If this wasn't you, you should immediately change your {APP_NAME} account password to revoke access from all other locations.</strong></p>\n<p>\n Thanks,<br/>\n {APP_NAME} team\n</p>" } }, "oauth2": { "mappedFields": { "id": "", "name": "", "username": "username", "avatarURL": "" }, "enabled": true }, "passwordAuth": { "enabled": true, "identityFields": [ "email", "username" ] }, "mfa": { "enabled": false, "duration": 1800, "rule": "" }, "otp": { "enabled": false, "duration": 180, "length": 8, "emailTemplate": { "subject": "OTP for {APP_NAME}", "body": "<p>Hello,</p>\n<p>Your one-time password is: <strong>{OTP}</strong></p>\n<p><i>If you didn't ask for the one-time password, you can ignore this email.</i></p>\n<p>\n Thanks,<br/>\n {APP_NAME} team\n</p>" } }, "authToken": { "duration": 1209600 }, "passwordResetToken": { "duration": 1800 }, "emailChangeToken": { "duration": 1800 }, "verificationToken": { "duration": 604800 }, "fileToken": { "duration": 180 }, "verificationTemplate": { "subject": "Verify your {APP_NAME} email", "body": "<p>Hello,</p>\n<p>Thank you for joining us at {APP_NAME}.</p>\n<p>Click on the button below to verify your email address.</p>\n<p>\n <a class=\"btn\" href=\"{APP_URL}/_/#/auth/confirm-verification/{TOKEN}\" target=\"_blank\" rel=\"noopener\">Verify</a>\n</p>\n<p>\n Thanks,<br/>\n {APP_NAME} team\n</p>" }, "resetPasswordTemplate": { "subject": "Reset your {APP_NAME} password", "body": "<p>Hello,</p>\n<p>Click on the button below to reset your password.</p>\n<p>\n <a class=\"btn\" href=\"{APP_URL}/_/#/auth/confirm-password-reset/{TOKEN}\" target=\"_blank\" rel=\"noopener\">Reset password</a>\n</p>\n<p><i>If you didn't ask to reset your password, you can ignore this email.</i></p>\n<p>\n Thanks,<br/>\n {APP_NAME} team\n</p>" }, "confirmEmailChangeTemplate": { "subject": "Confirm your {APP_NAME} new email address", "body": "<p>Hello,</p>\n<p>Click on the button below to confirm your new email address.</p>\n<p>\n <a class=\"btn\" href=\"{APP_URL}/_/#/auth/confirm-email-change/{TOKEN}\" target=\"_blank\" rel=\"noopener\">Confirm new email</a>\n</p>\n<p><i>If you didn't ask to change your email address, you can ignore this email.</i></p>\n<p>\n Thanks,<br/>\n {APP_NAME} team\n</p>" } }, { "id": "4231um01lpl5v14", "listRule": "", "viewRule": "", "createRule": "@request.auth.id != \"\"", "updateRule": "@request.auth.id = user.id", "deleteRule": "@request.auth.id = user.id", "name": "repls", "type": "base", "fields": [ { "autogeneratePattern": "[a-z0-9]{15}", "hidden": false, "id": "text3208210256", "max": 15, "min": 15, "name": "id", "pattern": "^[a-z0-9]+$", "presentable": false, "primaryKey": true, "required": true, "system": true, "type": "text" }, { "hidden": false, "id": "gqsfegcs", "maxSize": 0, "name": "files", "presentable": false, "required": true, "system": false, "type": "json" }, { "cascadeDelete": true, "collectionId": "_pb_users_auth_", "hidden": false, "id": "8udknpm3", "maxSelect": 1, "minSelect": 0, "name": "user", "presentable": false, "required": false, "system": false, "type": "relation" }, { "autogeneratePattern": "", "hidden": false, "id": "8hnolajl", "max": 0, "min": 2, "name": "name", "pattern": "", "presentable": false, "primaryKey": false, "required": true, "system": false, "type": "text" }, { "hidden": false, "id": "autodate2990389176", "name": "created", "onCreate": true, "onUpdate": false, "presentable": false, "system": false, "type": "autodate" }, { "hidden": false, "id": "autodate3332085495", "name": "updated", "onCreate": true, "onUpdate": true, "presentable": false, "system": false, "type": "autodate" } ], "indexes": [], "system": false }, { "id": "802ydin94rbg4go", "listRule": "", "viewRule": "", "createRule": null, "updateRule": null, "deleteRule": null, "name": "profile_repls", "type": "view", "fields": [ { "autogeneratePattern": "", "hidden": false, "id": "text3208210256", "max": 0, "min": 0, "name": "id", "pattern": "^[a-z0-9]+$", "presentable": false, "primaryKey": true, "required": true, "system": true, "type": "text" }, { "cascadeDelete": true, "collectionId": "_pb_users_auth_", "hidden": false, "id": "_clone_Z5L4", "maxSelect": 1, "minSelect": 0, "name": "user", "presentable": false, "required": false, "system": false, "type": "relation" }, { "autogeneratePattern": "", "hidden": false, "id": "_clone_NPiP", "max": 0, "min": 2, "name": "name", "pattern": "", "presentable": false, "primaryKey": false, "required": true, "system": false, "type": "text" }, { "hidden": false, "id": "_clone_ZTqc", "name": "created", "onCreate": true, "onUpdate": false, "presentable": false, "system": false, "type": "autodate" }, { "hidden": false, "id": "_clone_p3mz", "name": "updated", "onCreate": true, "onUpdate": true, "presentable": false, "system": false, "type": "autodate" } ], "indexes": [], "system": false, "viewQuery": "SELECT repls.id as id, repls.user, repls.name, repls.created, repls.updated FROM repls" } ]the
repls.filescolumn contains very big JSON objects like suggested in #3859 (reply in thread) .The problem arise when i try to filter this
replstable byuser_idbecause the query takes way to long (more than 60 seconds)...as you can see i tried to create a VIEW that excludes the files from thereplstable and in the admin UI it does load very quickly...however as soon as i try to filer byuser_idhere too it takes way too long. I also tried to add anindexonuser_idbut unexpectedly this make things worse and even loading the table without filtering takes a lot of time.Any suggestion on how to fix this?
Beta Was this translation helpful? Give feedback.
All reactions