sambecker/exif-photo-blog

vercel Pg CPU compute high usage

Closed this issue · 2 comments

ghenry commented

Hi,

I'm running this at https://gavinhenry.com - I think I've said before, and have upgraded to the Pro plan due to Pg CPU compute time. Yesterday I just got a warning of 75% usage, e.g. 75hrs of my Pro Plan 100 hours.

What is causing this?

Pro support have said:

Hi Gavin,

Thanks for reaching out.

Compute hours are calculated based on the compute size (in Compute Units) and active hours. An active hour is not just the time spent executing queries but any period the compute resource is considered active, which includes keeping the connection alive or idle time before autosuspend kicks in.

Even if your queries are brief, compute could still accumulate active hours if the autosuspend feature does not immediately suspend the compute resources after query execution. By default, a period of inactivity (e.g., 5 minutes) is required before the compute is put into an Idle state so if your application frequently wakes the compute (even without executing significant queries), this could explain the high compute time you're seeing.

Have you looked into whether or not your code/queries might be considering active time, as well as if you have any operations or maintenance tasks running in the background? For example:
Are there any CRON jobs set up to connect with the database?
Are there any local apps, CLI tools, or uptime monitors that interact with your database?
Are there any APIs involved on your project which regularly interact with the DB?
Please also share a brief summary on what type of Postgres database operations you're performing, how frequent, and estimated size of the data being transferred that you expect.

You may also run the following query that will provide you an insight on which pid were running queries to keep the db state active for the past 24 hrs:

SELECT 
  pid, 
  usename, 
  query, 
  state, 
  query_start 
FROM 
  pg_stat_activity
WHERE 
  query_start >= now() - interval '24 hours'
ORDER BY 
  query_start DESC;

If the above query has results like cloud_admin with the state idle - you can safely ignore those.

Lastly, you can see how usage is calculated for Vercel Postgres here: https://vercel.com/docs/storage/vercel-postgres/usage-and-pricing#usage

Please let me know if you have any further questions at all. I'll be happy to help.

Cheers,
Justin
▲ Senior Customer Success Engineer at Vercel

Thanks.

heykapil commented

What is causing this?

The photographs (link, blurdata, caption, title, aspect ratio, etc.) are retrieved straight from the Postgres database each time a page of your website loads. You are obviously performing a lot more database requests, which is why the compute consumption alert is displayed.

Few fixes/work arounds:

  1. Disable the blur to limit some pg compute hrs.NEXT_PUBLIC_BLUR_DISABLED = 1
  2. Look for the logs & metrics, and optimise the sql queries manually.
  3. Migrate to another postgres database. I have tried all these: Neon, Supabase, CocroachDB(check certificate in pg connection), Aiven(It's great! Currently using 1CPU/1GB RAM/5GB Disc for 0$).
sambecker commented

These are excellent recommendations @heykapil

@ghenry long-term, I'm hoping to improve caching/performance by upgrading to Next.js 15 + proper PPR support—stay tuned for that eventual update

  • ❤️1