With orderBy=createdAt (the Recently added page), getTimeBucket filtered on
date_trunc('MONTH', createdAt AT TIME ZONE 'UTC'), which has no matching
index (only the localDateTime month expression is indexed), so every bucket
request read all of the user's assets. Use the equivalent half-open UTC
range instead so asset_createdAt_idx can be combined with the owner index.
Results are identical.
Median per bucket, PG 14:
| user | before | after |
|-------------|--------|---------|
| 10k assets | 29 ms | 4.5 ms |
| 200k assets | 152 ms | 28 ms |
| 600k assets | 149 ms | 98 ms |
SharedLinkRepository.get (shared link page load, /shared-links/me, and the
OpenGraph tags rendered for /share/:key) json_agg'd every asset of the
album, with its full exif row, ordered by fileCreatedAt. The only things
used from it are the asset count and the first/last localDateTime.
Load the album without assets and fill assetCount/startDate/endDate from
getMetadataForIds, which is what GET /albums/:id already uses. The counts
now also respect the default visibility filter, and the dates are
formatted the same as on /albums/:id.
150k-asset album link, PG 14:
| | before | after |
|------------------------|-----------------|--------|
| query | 4.2-6.5 s | ~0.26 s (metadata aggregate) |
| JSON built by postgres | 261 MB | - |
| GET /shared-links/me | - | 0.28 s, 961 B |
* Update sorting
Updates the sorting to sort
1. First by timeCreatedAt, but then
2. By filename.
* Push tsts
* Sync SQL
* Formatting fixes
---------
Co-authored-by: Daniel Dietzler <36593685+danieldietzler@users.noreply.github.com>
`upsertExif` writes derived audio, video and keyframe metadata with an
`ON CONFLICT DO UPDATE`, but every column in those three update lists
assigns the stored row back to itself:
bitrate: ref('asset_audio.bitrate')
`SET bitrate = asset_audio.bitrate` is a self-assignment, so once a row
exists nothing can change it. Re-running metadata extraction re-reads the
file, builds a fresh snapshot, and then quietly discards it. The rest of the
file already uses `excluded` for this, as does `plugin.repository.ts`.
`asset_video.frameCount` had a second problem: it is supplied on insert but
was missing from the update list, so it would have stayed stale even after
the reference was corrected.
Extraction is meant to be repeatable. Probing improves between releases, a
file can be repaired or replaced, and a fix to how a stream is chosen is
worthless if it cannot reach the assets that were already imported. An
`upsert` that silently degrades to insert-only defeats all of that.
Metadata extraction is the only caller that passes these three objects, and
it passes a complete snapshot or nothing: each object is built from a single
probe behind a guard, and `upsertExif` skips the branch entirely when the
object is absent. So taking the incoming row cannot write partial values
over good ones. The other three callers pass `exif` only, and every other
reference to these tables is a read.
* implement updated rename tag feature
* Fix tag update handler
* Ensure toast displays updated tag name
* Ensure toast displays updated tag name
* Handle updates to tag closure table, prevent slash in API filename, test case updates
* delete child tags on parent delete
* Update frontend for better handling of nested tags
* pr checklist fixes
* fix unit test for tag controller
* Hide path for top-level tags
* regenerate SQL files
* name updates optional for tag update operations
* e2e tests for new tag api changes
* changes from code review, additional test coverage
* test fixes, web component refactor
* test assets
* test asset updates
* open-api rebuild
* update unit tests
* fix tag dto tests
* Update web/src/lib/modals/TagEditModal.svelte
Fix missing color bug
Co-authored-by: Ray <3608878+Pecacheu@users.noreply.github.com>
* fix tag e2e test
* remove closure table bug fixes
* remove nullish from tag name update schema
* regenerate openapi and sql
* fix test and e2e test
* simplify tag repository update process
* minor fix and sql regenerate
* fix tests, regen open-api, update dto
* Remove unused function
* Remove accidental test asset ref
* re-add test assets
* pin submodule to main branch commit
* Update test cases based on feedback
* Server fixes based on feedback
* regenerate sql
---------
Co-authored-by: Ray <3608878+Pecacheu@users.noreply.github.com>
bo0tzz