Search

For anyone arriving here now: 18 keeps most planner stats through pg_upgrade. You still want vacuumdb --all --analyze-in-stages --missing-stats-only, the release notes have details.

idle in transaction, 9 days, a reporting tool someone left open. thank you

That's bloat. Check first whether autovacuum is keeping up: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; If last_autovacuum is old, look for a long-running transaction, idle in trans…

Table has maybe 2 million rows and takes 60GB. A row is around 200 bytes. Updates a lot. Is this normal? autovacuum is on.

Yep, .env still said localhost. Changed to db and it connected. Resolved.

Inside the web container, localhost is the web container. Use the service name: postgres://...@db:5432/. Check DATABASE_URL in your .env, it's probably still localhost from running outside docker.

Two services in docker compose, web and db. Web fails with ECONNREFUSED 127.0.0.1:5432 even though db is up and the logs say it's accepting connections. Works fine when I run the app outside docker. What am I missing?

Just did this at work and this thread saved me a bad hour. thank you whoever you are

Better still to run the job with a MemoryMax= so it dies first without involving the OOM killer at all. Postgres getting a SIGKILL means a crash restart of everything.

OOMScoreAdjust. Typo. Can't edit here.

Small box. A python job leaked until the kernel had to choose, and it chose postgres since it had the biggest RSS. Read dmesg, it's all there, including the score. Set this on the service you want to keep: [Service] OMScoreAdjust=-500 and l…

Off. All good now, marking this one resolved.

You can leave it on and raise jit_above_cost, or turn it off for that role. For a small OLTP box I'd just turn it off.

880ms. Old package was built without LLVM support, turns out. Debian's has it.

There's your extra second. It has decided the query's expensive enough to compile. Was jit off in your old config, or was it a different package build? Try SET jit = off in the session and run it again.

Top of it is: JIT: Functions: 42 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 8.1 ms, Inlining 310.2 ms, Optimization 612.4 ms, Emission 201.9 ms, Total 1132.6 ms Execution Time: 2041.8 ms

EXPLAIN (ANALYZE, BUFFERS) it and paste it. Change the table names if you need to.

Back to 20ms on most of it. Thanks. Still one report query that's slower than before, 2s where it was 900ms.

The in-stages version does a very rough pass first, so the worst of it clears up fast. Then finer passes. Check pg_stat_user_tables, last_analyze should be getting filled in.

Running. There's a line about it in the output I had scrolled past.

Oh.

Did you run the analyze step? pg_upgrade doesn't bring the planner statistics across, so right now it's guessing. It prints this at the end and everybody misses it. vacuumdb --all --analyze-in-stages

Upgraded a small Postgres box over the weekend with pg_upgrade --link. It came up fine, no errors. Queries that took 20ms now take 8 seconds. Same hardware, same config carried across. One example does a seq scan on a 40M row table when the…