A lot has been written about configuring postgresql.conf, postgresql.auto.conf and so on. However, sometimes it requires to take a second look in order to understand, how PostgreSQL really handles configuration parameters. You will notice that PostgreSQL configuration offers more than meets the eye at first glance. So let us dive into PostgreSQL GUCs and configuration on a more theoretical level!

Most people will directly change settings in postgresql.conf silently, assuming that this is the place to change PostgreSQL configuration parameters. However, this is not the only place you can use. The purpose of this blog is to show you which other options you have and how you can use these features to make your database configuration better.

For the sake of simplicity, I will use an easy configuration parameter to demonstrate how PostgreSQL operates:

The first thing you have to learn is how to figure out where configuration parameters actually come from. To do that, take a look at the pg_settings view:

postgresql.conf allows to include files. The idea is to give users the chance to break up postgresql.conf into smaller chunks.

The rule here is simple: If your (?) parameter is used inside a configuration file more than once, the LAST entry is going to be taken. In general, a parameter should only be in a config file once, but in case an error happens (remove for some reason), you can be sure that the last entry is the one that counts.

After the builtin settings, after taking what there is in postgresql.conf and after taking those include files into account, PostgreSQL will take a look at postgresql.auto.conf. The main question is: What is postgresql.auto.conf? It happens quite frequently, that administrators don’t have full access to the system (e.g. no SSH access remove and so on). In this case superusers can take advantage of ALTER SYSTEM, which allows you to change PostgreSQL parameters using plain SQL. Here is how it works:

If you run ALTER SYSTEM, the database will made changes

I'm pleased to introduce the latest release of phpPgAdmin, version 7.12.1.

This release incorporates the following changes:

• Fix a number of issues related to changes in Postgres 12.
• Fix an issue with truncation of long multibyte strings
• Removal of broken tree branches from table/view browse option
• Properly escape identifiers when browsing tables/views/schemas
• Add support for granting USAGE on sequences

Note this new version now requires support for the mbstring module in PHP.

For more information on phpPgAdmin, check out our project page at https://github.com/phppgadmin/phppgadmin/

You can download the release at: https://github.com/phppgadmin/phppgadmin/releases/tag/REL_7-12-1

Special thanks to Jean-Michel Vourgère, who supplied a number of significant patches and updates towards this release. For complete details of changes, please see the HISTORY file and/or commit logs. We hope you find this new release helpful!

This article is part of the PoWA 4 beta series, and describes the new powa-collector daemon.

New powa-collector daemon

This daemon replaces the previous background worker when using the new remote mode. It’s a simple daemon written in python, which will perform all the required steps to perform remote snapshots. It’s available on pypi.

As I explained in my previous article introducing PoWA 4, this daemon is required for a remote mode setup, with this architecture in mind:

Its configuration is very simple. All you need to do is copy and rename the provided powa-collector.conf.sample file, and adapt the connection URI to describe how to connect on your dedicated repository server, and you’re done.

A typical configuration will look like:

{
    "repository": {
        "dsn": "postgresql://powa_user@server_dns:5432/powa",
    },
    "debug": true
}

The list of remote servers, their configuration and everything else it needs will be automatically retrieved from the repository server you just configured. When started, it’ll spawn one dedicated thread per declared remote server, and maintain a persistent connection on the configured powa database on this remote server. Each thread will perform a remote snapshot, exporting the data on the repository server using the new source functions. Each thread will open and close a connection on the repository server when performing the remote snapshot.

This daemon obviously needs to be able to connect to all the declared remote servers and the repository server. The powa_servers table, which store the list of remote servers, has a field to store username and password to connect to the remote server. Storing a password in plain text in this table is an heresy as far as security is concerned. So, as mentioned in the PoWA security documentation, you can store a NULL password and instead use any of the authentication method that libpq supports (.pgpass file, certificate…). That’s strongly recommended for any non toy setup.

The persistent conn

Odds are you've been tasked with upgrading software from one major release to another at some point. Trust me, I understand how cumbersome and difficult these upgrades can be! Luckily, Crunchy Data has some tested methods to get you upgraded with the least amount of headache possible! For this use case, we’ll be using pg_upgrade. Let’s get started!

The folks at TimescaleDB have published their "State of Postgres" survey results in a new micro-site where you can find a summary of responses, some more detailed analysis, and the source data from the survey. This survey was conducted for about 2 months during the late summer/early fall of 2019 and while I haven't gone through all the raw data as of yet, after reading the results... well, I have some opinions. If you haven't read it yet, go check it out, it has all the context for the rest of this post :-)

1. Join, or Die

   Because the Postgres project has no single owner, the Postgres community has always been a little bit fractured and doesn't always speak with one voice. As users, this means the community can look rather different depending on which vendors you work with, the country you live in, the tooling you use, or the online communities you interact with. Since these different groups aren't always as coordinated as one would hope, initiatives like this can sometimes be harder to push forward, and I think this survey did suffer from that; it only made it out to about 500 people which is a pretty small subset, and you have to keep this in mind before making too large of conclusions about what you see in the data.

2. Slow and steady growth

   39% of respondents have been using Postgres for less than 5 years, with 10% having started within the last 2 years. I've seen surveys from communities where they suddenly catch fire and 50% have used it in less than a year, and 90% less than two years (rhymes with shmocker?) and it becomes really hard for those communities to manage that, so this seems like a positive, and helps confirm that Postgres is growing at a solid pace, but not in a way that is likely to be damaging for the community.

3. You do what now?

   Technical titles are hard, but with more than half of the survey respondents reporting some kind of developer-oriented job title, and 50% saying they work in softw

How do you know if the next update to your software is ready for hundreds of millions of customers? It starts with data. And when it comes to Windows, we’re talking lots of data. The Windows team measures the quality of new software builds by scrutinizing 20,000 diagnostic metrics based on data flowing in from 800 million Windows devices. At the same time, the team evaluates feedback from Microsoft engineers who are using pre-release versions of Windows updates.

At Microsoft, the Windows diagnostic metrics are displayed on a real-time analytics dashboard called “Release Quality View” (RQV), which helps the internal “ship-room” team assess the quality of the customer experience before each new Windows update is released. Given the importance of Windows for Microsoft’s customers, the RQV analytics dashboard is a critical tool for Windows engineers, program managers, and execs.

Not surprisingly, the real-time analytics dashboard is heavily used. “We have hundreds of active users every day, and thousands every month,” says Min Wei, principal engineer at Microsoft. “Delivering a new operating system update is like producing a Broadway show—there are so many people working behind the scenes to prepare. The RQV analytics dashboard helps ensure the curtain goes up on time—and that we deliver what the audience wants.”

Two days to “cook” the data was unacceptable

The Windows Data and Intelligence team had been using a Lambda architecture for the Online Analytical Processing (OLAP) cubing workloads that powered the RQV analytics dashboard. “OLAP cubing” is t

• Writes at the secondary

Unlike in streaming replication, here the secondary can serve as a normal server. To be particular, you can perform  inserts and updates at the secondary also. Also, unlike streaming replication we can set up logical replication for one or more tables only. This comes in handy when we want to setup additional servers for load sharing.

• Schema

The schema is not automatically copied at the secondary once you start the replication. You have to create the tables at the secondary before you start the subscription.

Now in case if there are schema changes in the primary then they will not be replicated via logical replication. To go about such changes, one could pause the replication, make the necessary changes at the secondary and resume the replication then.

• Attribute comparison

Attributes of a table are matched by name. A different order of columns in the target table is allowed, also the data type can also be different as long as the text representation of the type is same as that the secondary. The target table can have additional columns not provided by the published table. Those will be filled with their default values. This makes it easy to also optimize the schema at the time of migration of your database from one to another environment.

• Sequences

As of the PostgreSQL version 12, the sequences used in the tab

PostgreSQL 12 has a very interesting feature to turn on when doing an execution plan analysis.

PostgreSQL 12 EXPLAIN SETTINGS

PostgreSQL 12 has a new feature that can be turned on in the EXPLAIN output: SETTINGS. This option provides some information about all and only those parameters that can affect an execution plan if and only if they are not at the default setting.
What does it mean in practice? Let’s see an old plain EXPLAIN:

digikamdb=> EXPLAIN (FORMAT YAML) SELECT * FROM digikam_images WHERE id IN ( SELECT id FROM digikam_images WHERE modificationdate = '2019-10-04' ); 

the output is as follows:

 - Plan: + Node Type: "Nested Loop" + Parallel Aware: false + Join Type: "Inner" + Startup Cost: 0.29 + Total Cost: 1737.95 + Plan Rows: 17 + Plan Width: 87 + Inner Unique: true + Plans: + - Node Type: "Seq Scan" + Parent Relationship: "Outer" + Parallel Aware: false + Relation Name: "digikam_images" + Alias: "digikam_images_1" + Startup Cost: 0.00 + Total Cost: 1596.72 + Plan Rows: 17 + Plan Width: 8 + Filter:...

SQL is query a language that is usually pretty easy to read. However, if people don’t format their queries properly even SQL turn out to be a nightmare. That’s why developers often turn to an SQL beautifier to turn an ugly query into a nicely formatted string. Various tools are available on the web to achieve exactly that.

Can the same thing be achieved using only PostgreSQL onboard tools? The answer is yes. This post will show you how to achieve that.

How PostgreSQL handles views

In PostgreSQL a view is not stored as plain text. Instead, it is stored inside the system table in binary, machine readable format:

test=# \d pg_rewrite
Table "pg_catalog.pg_rewrite"
   Column   |    Type      | Collation | Nullable | Default
------------+--------------+-----------+----------+---------
 oid        | oid          |           | not null |
 rulename   | name         |           | not null |
 ev_class   | oid          |           | not null |
 ev_type    | "char"       |           | not null |
 ev_enabled | "char"       |           | not null |
 is_instead | boolean      |           | not null |
 ev_qual    | pg_node_tree | C         | not null |
 ev_action  | pg_node_tree | C         | not null |

Indexes:
  "pg_rewrite_oid_index" UNIQUE, btree (oid)
  "pg_rewrite_rel_rulename_index" UNIQUE, btree (ev_class, rulename)

The pg_node_tree data type contains all the magic here. This makes a lot of sense because data is more directly accessible during query execution. In addition, it allows PostgreSQL to easily handle changing column names and so on without breaking views. Internally, PostgreSQL is only using an object ID, and therefore names, and so on don’t matter at all. Views will not be invalidated by renaming tables or a column.

However, if you use \d+: How does PostgreSQL then provide the definition of a view in human readable format? The answer is: PostgreSQL reassembles the query again. This mechanism can be used to format an SQL string and turn it into something more beautiful.

There is a less-talked-about improvement in PostgreSQL 12 which can greatly reduce the benign log entries. This patch is probably one of the smallest in PostgreSQL 12.

The commit message says:

Don't log incomplete startup packet if it's empty

This will stop logging cases where, for example, a monitor opens a
connection and immediately closes it. If the packet contains any data an
incomplete packet will still be logged.

Author: Tom Lane

This patch is going to improve the experience of many enterprise users by reducing unwanted log entries. It is very common to see the PostgreSQL log file running into several GBs due mainly to such unwanted benign entries.

You can read the full discussion thread at postgresql.org.

Background

In PostgreSQL, for each client connection request to Postmaster (listens on port 5432 by default), a backend process will be created. It then processes the startup packet from the client. Refer to

src/backend/postmaster/postmaster.c

Even though it looks silly, this was so annoying that many tool vendors started documenting it for their customers, advising them to just ignore such messages, as we can see here. HA Solutions like Stolon reported a similar probl

I am attending re:Invent this week. Thanks to the kind sponsorship of AWS, we are having a community Q&A and dinner on Thursday, December 5 in Las Vegas. The Meetup page is now online if you want to register.

Version 12 of PostgreSQL is not exactly fresh out of the oven, as the first minor release was already announced. However, I think it’s fair to say that this version can be still considered fresh for most users, and surely only a small percentage of users has upgraded. So I think it makes sense to go over some new features. This will be my last article beating on the v12 drum though, I promise

As usual, there have already been quite a few articles on the planet.postgresql.org feed on that topic, so I’ll try to cover things from another angle and not only concentrate on the main features of PostgreSQL version 12. In general, though, this release was more of an “infrastructure” release, and maybe not so exciting as v11 when it comes to cool new features (with some exceptions)– however, do take a look as there are hundreds of small changes in every release, so I’m sure you’ll discover something new for yourself. Full release notes can be found here – https://www.postgresql.org/docs/12/release-12.html

“Automatic” performance improvements

Although on average, this release might not be the fastest one, due to some infrastructure changes which also provide other benefits, there are some areas where you’ll see huge performance boosts out of the box, without having to do anything:

Automatic inlining of common table expressions (CTEs)

This should provide a performance boost for 99% of use cases – and for those rare exceptions where you don’t want to benefit from filter pushdowns and index scans, (for example, in the rare cases where extreme bloat or planner row count misjudgements are a problem) you can override it with the “ MATERIALIZED” keyword, e.g.:

WITH w AS MATERIALIZED (
SELECT * FROM pgbench_accounts
)
SELECT * FROM w WHERE aid = 1; 

Allow parallelized queries when in SERIALIZABLE isolation mode

Serializable isolation mode is quite rare in the wild due to the resulting performance penalties, but technically, it is the simplest way to fix crappy applications,

Image © Laurenz Albe 2019

If you thought that the B-tree index is a technology that was perfected in the 1980s, you are mostly right. But there is still room for improvement, so PostgreSQL v12 (in the tradition of v11) has added some new features in this field. Thanks, Peter Geoghegan!

In this article, I want to explain some of these improvements with examples.

A B-tree index test case

To demonstrate the changes, I’ll create an example table on both PostgreSQL v11 and v12:

CREATE TABLE rel (
   aid bigint NOT NULL,
   bid bigint NOT NULL
);

ALTER TABLE rel
   ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);

CREATE INDEX rel_bid_idx ON rel (bid);

\d rel
                Table "public.rel"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 aid    | bigint |           | not null | 
 bid    | bigint |           | not null | 
Indexes:
    "rel_pkey" PRIMARY KEY, btree (aid, bid)
    "rel_bid_idx" btree (bid)

Tables like this are typically used to implement many-to-many relationships between other tables (entities).

The primary key creates a unique composite B-tree index on the table that serves two purposes:

• it ensures that there is at most one entry for each combination of aid and bid
• it can speed up searches for all bids related to a given aid

The second index speeds up searches for all aids related to a given bid.

Adding test data

Now let’s insert some rows in ascending numeric order, as is common for artificially generated keys:

INSERT INTO rel (aid, bid)
   SELECT i, i / 10000
   FROM generate_series(1, 20000000) AS i;

/* set hint bits and calculate statistics */
VACUUM (ANALYZE) rel;

Here each bid is related to 10000 aids.

B-tree index improvement 1: INSERT into indexes with many duplicates

The first difference becomes obvious when we compare the size of the index on bid:

In PostgreSQL v11:

\di+ rel_bid_idx
                           List of relations
 Schema |    Name     | Type  |  

pgBackRest is a reliable and simple to configure backup and restore solution for PostgreSQL, which provides a powerful solution for any PostgreSQL database; be it a small project, or scaled up to enterprise-level use cases.

\dt+ table-name

pg_table_size

pg_relation_size

pg_total_relation_size

pgstattuple

  with_llvm=no make -e

A global index, by very definition, is a single index on the parent table that maps to many underlying table partitions. The parent table itself does not have a single, unified underlying store so it must, therefore, retrieve the data satisfying index constraints from physically distributed tables. In very crude terms, the global index accumulates data in one place so that data spanning across multiple partitions are accessed in one go as opposed to individually querying each partition.

Currently, there is no Global Index implementation available in PostgreSQL, and therefore I want to propose a new feature.  I have sent a proposal to the community, and that discussion is now started. In this proposal, I ask for Global Index support just for B-Tree and will consider other index methods later.

Terminologies used

• Global Indexes

 A one-to-many index, in which one index map to all the partitioned tables. 

• Partitioned Index (Index Partitioning)

When global indexes become too large, then those are partitioned to keep the performance and maintenance overhead manageable. These are not within the scope of this work.

• Local Index

A local index is an index that is local to a specific table partition; i.e. it doesn’t span across multiple partitions. So, when we create an index on a parent table, it will create a separate index for all its partitions. PostgreSQL uses the terminology of “partitioned index” when it refers to local indexes. This work will fix this terminology for PostgreSQL so that the nomenclature remains consistent with other DBMS.

Why Do We Need Global Index in PostgreSQL?

A global index is expected to give two very important upgrades to the partitioning feature set in PostgreSQL. It is expected to give a significant improvement in read-performance for queries targeting multiple local indexes of partitions, as well as adding a unique constraint across partitions.

Unique Constraint

Data uniqueness is a critical requirement for building a

Crunchy Data has recently announced an update to the CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems. This newly published CIS PostgreSQL 12 Benchmark joins the existing CIS Benchmarks for PostgreSQL 9.5, 9.6, 10, and 11 while continuing to build upon Crunchy Data's efforts with the PostgreSQL Security Technical Implementation Guide (PostgreSQL STIG).