Just so that it will be perfectly clear: the logs I have in mind are the ones for DBAs to read – with slow queries, errors, and other interesting information.
So, how does one find them?
Author: depesz
Fixed display of Backward scans on explain.depesz.com
Yaroslav Schekin (ysch) reported on irc that Index Scans Backward do not display properly.
After checking I found out that if explain is in JSON/YAML/XML – node type is changed to “Index Scan" (or “Index Only Scan" if it was originally “Index Only Scan Backward").
Continue reading Fixed display of Backward scans on explain.depesz.com
Starting with Pg – where/how can I set configuration parameters?
Previously I wrote about locating config files.
The thing is – postgresql.conf is not the only place you can set your configuration in.
In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.
Continue reading Starting with Pg – where/how can I set configuration parameters?
explain.depesz.com – now with JITs
Ever since PostgreSQL 11 we have JIT (Just-In-Time compilation).
Information about JIT is displayed in explain analyze plans, but up to this moment, explain.depesz.com didn't display it properly. But not anymore. Thanks to release 1.05 of Pg::Explain and subsequent change in templates for the site, you can now see nice JIT info.
Hope you'll find it useful 🙂
Starting with Pg – where is the config?
Over the years I saw some people find themselves in position where they have to start dealing with PostgreSQL with minimal, or none, prior exposure. This leads to problems with seemingly easy tasks – how to change config? How to find stuff in logs?
So I decided to write some blogposts to be able to point such people to pre-made tutorials.
And I start today, with information on how to find PostgreSQL config files.
Why is there database named “postgres”?
Every so often, on irc, someone asks if they can remove postgres database, and why is it even there.
Figured I can write one blogpost to answer it once and for all, and use it as ready answer whenever the question resurfaces.
Waiting for PostgreSQL 14 – SEARCH and CYCLE clauses
On 1st of February 2021, Peter Eisentraut committed patch:
SEARCH and CYCLE clauses This adds the SQL standard feature that adds the SEARCH and CYCLE clauses to recursive queries to be able to do produce breadth- or depth-first search orders and detect cycles. These clauses can be rewritten into queries using existing syntax, and that is what this patch does in the rewriter. Reviewed-by: Vik Fearing <vik@postgresfriends.org> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/db80ceee-6f97-9b4a-8ee8-3ba0c58e5be2@2ndquadrant.com
Continue reading Waiting for PostgreSQL 14 – SEARCH and CYCLE clauses
How to run some tasks without user intervention, at specific times? – part 2
Couple of days earlier I wrote about tools to run tasks in db at scheduled times.
In writing this I missed pg_cron. So let's look into it now.
Continue reading How to run some tasks without user intervention, at specific times? – part 2
Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time
On 17th of January 2021, Magnus Hagander committed patch:
Add pg_stat_database counters for sessions and session time This add counters for number of sessions, the different kind of session termination types, and timers for how much time is spent in active vs idle in a database to pg_stat_database. Internally this also renames the parameter "force" to disconnect. This was the only use-case for the parameter before, so repurposing it to this mroe narrow usecase makes things cleaner than inventing something new. Author: Laurenz Albe Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at