Neues vom PostgreSQL Planet
Everybody who has ever written any kind of database application had to use time and date. However, in PostgreSQL there are some subtle issues most people might not be aware of. To make it easier for beginners, as well as advanced people, to understand this vital topic I have decided to compile some examples which are important for your everyday work.
When writing software, it is necessary to decide whether to use external facilities available in command-line tools, libraries, frameworks, and the operating system, or write the facilities yourself. Why would you write them yourself? You might be worried about adding reliance on an external facility or a facility might not have sufficient flexibility or performance.
One of the least-appreciated PostgreSQL extensions is the powerful PgRouting extension, which allows routing on dynamically generated graphs.
Before Postgres 12, queries specified as common table expressions (WITH clauses) always behaved as optimization barriers, meaning that common table expression queries were executed independently, and were not moved to later parts of the query.
For a standby node to be used in a repmgr configuration, it has to be registered. Normally you do this by allowing repmgr to clone the standby first. If a node was not cloned by repmgr, you can nonetheless register it using the -F/–force option. This is useful for cases where you want to use another tool (like pgbackrest) to backup and restore your clusters.
The apt.postgresql.org has been extended to cover the arm64 architecture.
We had occasionally received user request to add "arm" in the past, but it was never really clear which kind of "arm" made sense to target for PostgreSQL. In terms of Debian architectures, there's (at least) armel, armhf, and arm64. Furthermore, Raspberry Pis are very popular (and indeed what most users seems to were asking about), but the raspbian "armhf" port is incompatible with the Debian "armhf" port.
Postgres must guarantee durability and good performance. To meet these objectives, Postgres does writes to the file system and storage in the background as much as possible. In fact, there are only two major cases where writes happen in the foreground:
This is the second blog in a series of blogs attempting to walk you through the query optimization process. We started from the very basics of understanding the “EXPLAIN” command. Reading part 1 is not a prerequisite, however, if you wish to understand how you can reconstruct a query from a given plan, Optimizing SQL – Step 1: EXPLAIN in PostgreSQL – Part 1 may help.