Optimize database queries

[yomete]

Is your feature request related to a problem?
PostgreSQL compute costs can be high due to expensive database queries. The current implementation uses SELECT * for all queries, resulting in 60-70% higher CPU usage than necessary. This leads to significant hosting cost increases, especially for photo-heavy applications.

Describe the feature/solution you'd like
Database query optimizations that reduce PostgreSQL CPU usage by implementing:

• Context-specific column selection (grid/admin/detail views only fetch needed
  columns)
• Connection pooling optimization

I've implemented these optimizations and achieved a 67% reduction in compute usage ($20/month savings) with no functionality regressions after 3+ months in production.

Neon database billing before optimizations

Neon database billing after optimizations

Live deployment
https://pics.yomieluwan.de

Would you be interested in a PR with these optimizations?

[sambecker]

This sounds fascinating!

Been waiting for Next.js cache improvements to improve overall query efficiency, but definitely willing to incorporate these improvements, especially if they are targeted.

Any idea on the rough scale of this PR?

[yomete]

It should be quite focused and targeted. Here's the rough outline:

For the files to be modified:

• src/photo/db/query.ts - Main optimization (we’ll need to add context-specific column selection)
• src/platforms/postgres.ts - Connection pool configuration

What Context-Specific Column Selection Does:
Currently every query to the db uses SELECT *, fetching ALL photo data even when most isn't needed. In reality, different parts of the app actually only need specific columns. So for example:

// Grid context - only display essentials
 const COLUMNS_GRID = [
    'id', 'url', 'extension', 'aspect_ratio',
    'blur_data', 'taken_at'
  ].join(', ');

// Admin context - includes management metadata  
  const COLUMNS_ADMIN = [
    'id', 'url', 'extension', 'aspect_ratio', 'blur_data',
    'title', 'caption', 'tags', 'make', 'model',
    'film', 'priority_order', 'hidden', 'taken_at', 'updated_at'
].join(', ');

And this is how we can then use these context aware queries to fetch the photos from the DB efficiently.

const context = options.context || 'full';
const columns = getColumnsForContext(context);
const sql = [`SELECT ${columns} FROM photos`];

getPhotos({ context: 'grid' }) // Only 6 columns
getPhotos({ context: 'admin' }) // 15 columns  
getPhotos() // All columns (backward compatible)

This is just pseudocode but the overall idea is to only fetch what is needed for different context/parts of the app. This should be very useful for folks like me who has a lot of images and also wants to manage costs.

[sambecker]

Agreed that streamlining the column requests is an interesting opportunity. Are you sure that that alone has significant compute savings? I ask, because I think a consequence of this strategy would be changing the concept of a Photo throughout the application, which will then have unknown fields. Did you pair this approach with different flavors of a Photo object? Or is the whole thing a Partial now?

Do you have any sense for how much the savings come from improved connection pooling?

[yomete]

Actually, I kept it even simpler than a Partial approach. The functions still returns the same Photo type, and the database parsing handles missing columns gracefully by setting them to undefined.

No type changes were needed as I just added an optional context parameter to GetPhotosOptions:

  export type GetPhotosOptions = {
    // ... existing options
    context?: 'grid' | 'admin' | 'detail' | 'meta' | 'full'
  }

https://github.com/yomete/pics.yomieluwan.de/blob/main/src/photo/db/index.ts#L32

https://github.com/yomete/pics.yomieluwan.de/blob/main/src/photo/db/query.ts#L481-L543

Honestly, I don't have precise metrics on how much the connection pooling contributed to the savings. Maybe that’s something that can be skipped.

[sambecker]

Yeah, the query logic makes sense, I was just pointing out that those queries do not all return the same Photo object. On the other hand, I just checked the Photo type and it looks like most of the fields are partial anyway. It just means being careful about which queries are used where. For example, in the admin section, sql UPDATE expects full copies of the Photo object (definitely a naive approach) so we'd need to be very mindful of data loss there.

In any event, I'm game to publish these on a test branch and start serving in production on the demo site to see what kind of results we get.

Feel free to post a PR! We can always collaborate on areas where we might want to shore up types/verify data protections.

[sambecker]

On further thought, I wonder if we want to create a family of photo types to enforce these requirements:

• PhotoLight (just image url + blur data)
• Photo (complete meta for display throughout the app)
• PhotoComplete (required for /admin usage)

Not sure if this is right (and may be hard to reason about), but I wonder if something like this is necessary to ensure predictable behavior throughout the app.

[yomete]

That sounds good to me. I'll think more about this (creating more Photo family types) and let you know what my approach will be.

I'll start working on the PR and hope to send something in the coming weeks.