The world's most advanced open source database
Top posters
Number of posts in the past month
Top teams
Number of posts in the past month
  • Policy for being listed on Planet PostgreSQL.
  • Add your blog to Planet PostgreSQL.
  • List of all subscribed blogs.
  • Manage your registration.
  • Get in touch with the Planet PostgreSQL administrators at planet at
Posted by Pavel Stehule on 2019-12-15 at 05:58
This year I finish more projects - pspg - there are watch mode, integrated readers from csv and tsv, and vertical cursor and sort on client side.

plpgsql_check can detect some basic sql injection problems and has integrated profiler now. Lot of bug was fixed. These tools works well and I am almost satisfied with their functionality.

Today some lighter topic. I did screenshot of any theme supported by pspg. What theme do you use, or what theme do you prefer?

Posted by Craig Healey on 2019-12-11 at 14:34
In a previous post I deployed a PostgreSQL database in a container from a standard image. Now I’m going to look at deploying a PostgreSQL cluster. Well, kind of. PostgreSQL uses the term cluster to refer to “a collection of databases managed by a single PostgreSQL server instance”. I’m using it in the more general sense, to refer to High Availability.
Being a PostgreSQL database administrator and managing backups on a daily basis, one of the new features of Barman 2.10 that I liked the most is the transparent management of .partial WAL files during recovery. And, most likely, you feel the same if you have been using Barman with WAL streaming and have been asked […]

A lot has been written about configuring postgresql.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 The main question is: What is 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

Posted by Robert Treat in credativ on 2019-12-10 at 20:31

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

You can download the release at:

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!

Posted by Julien Rouhaud on 2019-12-10 at 18:54

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

Recently, on irc, there have been some talks with people using various pg_dump/pg_dumpall calls to get dumps of database. I voiced my ideas, but figured it could be good subject for a blog post. First things first – we have two tools available: pg_dumpall pg_dump pg_dumpall dumps all databases in given PostgreSQL installation (cluster), and … Continue reading "How to effectively dump PostgreSQL databases"

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.”

screenshot RQV analytics dashboard
Figure 1: The internal RQV analytics dashboard at Microsoft helps the Windows team to assess the quality of upcoming Windows releases. The RQV dashboard tracks 20,000 diagnostic and quality metrics, and currently supports over 6 million queries per day, with hundreds of concurrent users. The RQV analytics dashboard relies on Postgres—along with the Citus extension to Postgres to scale out horizontally—and is deployed on Microsoft Azure.

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

In PostgreSQL, there is this amazing feature called logical replication, which enables you to replicate a set of table(s) from one server to other. Being a new feature it is less mature and needs many improvements. Nevertheless, it should not limit us enjoying its advantage and so here is a post to highlight some interesting aspects of the feature.

No matter how similar it might sound, it is quite a different thing. It solves a different problem altogether and in some sense it even overcomes the issues of streaming replication. Some of the important aspects of logical replication are as follows,

  • 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
It's not a secret that databases are damn complicated systems. And they tend to run on top of even more complicated stacks of software. Nowadays you will not surprise anyone (or at least not that much) by running your database on a Kubernetes cluster or inside a virtual machine. It's probably still questionable whether it's good and appropriate, but this approach is something we have to face — sometimes it's at least convenient, sometimes it allows to be more resource efficient and sometimes it's the only available infrastructure in a company. And one of the problems in this situation is that reasoning about the performance is not that easy any more. Well, it's not like it was much easier before, but still. Let's see what can we do about it and how strace, perf and BPF can change the game.
Posted by Luca Ferrari on 2019-12-05 at 00:00

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


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.


sql beautifier


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 |

  "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.

Just pushed new version of site. Changes: most importantly : changed favicon to be more related to site extended, quite a lot, help page added a way to embed explains on your page The embedding looks like: <iframe width=800 height=400 src=""></iframe> effect: Basically, in plan url, you have to change /s/ to /i/.
postgresql improvement

PostgreSQLThere 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


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

for the source code. Each client connection request is expected to send a startup message to the PostgreSQL server, and this information in the startup packet is used for setting up the backend process. But there are many more things happening when we deploy PostgreSQL in a datacenter. There could be different monitoring solutions, security scanners, port scanners, HA Solutions, etc hitting on PostgreSQL Port 5432. PostgreSQL starts processing these incoming connections for establishing a client-server communication channel. But many of these tools may have a different intention and won’t be participating in a good client-server protocol.  Historically, PostgreSQL generates a log entry for each of these suspected/bad hits. This can result in log files growing to a huge size and can cause unwanted log-related IO.

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

Posted by Bruce Momjian in EnterpriseDB on 2019-12-02 at 01:00

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 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 –

“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.:

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,

PostgreSQL 12 contains two new server settings: ssl_min_protocol_version ssl_max_protocol_version As the names indicate, these are used to control the oldest (minimum) and newest (maximum) version of the SSL and TLS protocol family that the server will accept. (For historical reasons, in PostgreSQL, all settings related to SSL and TLS are named ssl_something, even though TLS […]
On 19th of November 2019, Thomas Munro committed patch: Allow invisible PROMPT2 in psql.   Keep track of the visible width of PROMPT1, and provide %w as a way for PROMPT2 to generate the same number of spaces.   Author: Thomas Munro, with ideas from others   Discussion: So, I guess you know that … Continue reading "Waiting for PostgreSQL 13 – Allow invisible PROMPT2 in psql."

After all, a B-tree index is just a tree
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:

   aid bigint NOT NULL,
   bid bigint NOT NULL

   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 | 
    "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 */

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.

Posted by Rafia Sabih in EnterpriseDB on 2019-11-23 at 11:20
If you have ever used postgreSQL and wondered about the size of the tables, this post might help. A postgreSQL table is no simple table rather it has many things associated with it like index tables, toast tables, then there are other important stuff like free space map and visibility map. Now postgreSQL provides multiple options for measuring the size of table and there are interesting distinctions among them.

\dt+ table-name

This is one of the most common way to find the size of a table. On your psql client you may simple type it and it will return the information about the mentioned table including its size in kB. Internally, this size is same as
select * from pg_size_pretty(pg_relation_size('table-name')) -- for versions older than 9
select * from pg_size_pretty(pg_table_size('table-name')) -- for later versions
This brings us to our next function.



This is the size of the table which includes the size of its heap, any associated toast table, free space map, and visibility map.  So, this is the actual size of the relation on the disk. Note that this does not include the size of any indexes associated with this table.


This is the measure to provide the disk space used by one particular fork -- main, init, fsm, or vm. Now, when you write pg_relation_size('table-name') it will be same as pg_relation_size('table-name', 'main'). So, if one wants to measure the space by one fork, this is the function to call.

Now, what about if we want to know the table size with all of its indexes, for that we move to this next function.


This is the function which gives you the total size of the relation (as in pg_table_size) plus the size of all the indexes associated with it.

Here is the time to introduce an interesting extension in this regards. 


If you are little more into the table size and how postgreSQL stores it, you might have come across this ext

PostgreSQL does not use IN-PLACE update mechanism, so as per the way DELETE and UPDATE command is designed,

  • Whenever DELETE operations are performed, it marks the existing tuple as DEAD instead of physically removing those tuples.
  • Similarly, whenever UPDATE operation is performed, it marks the corresponding existing tuple as DEAD and inserts a new tuple (i.e. UPDATE operation = DELETE + INSERT).

So each DELETE and UPDATE command will result in one DEAD tuple, which is never going to be used (unless there are parallel transactions). These dead tuples will lead to unnecessary extra space usage even though the same or less number of effective records. This is also called space bloating in PostgreSQL. Since PostgreSQL is widely used as OLTP kind of relational database system, where there are frequent INSERT, UPDATE and DELETE operations carried out, there will be many DEAD tuples and hence corresponding consequences. So PostgreSQL required a strong maintenance mechanism to deal with these DEAD tuples. VACUUM is the maintenance process which takes care of dealing with DEAD tuple along with a few more activities useful for optimizing VACUUM operation. Let’s understand some terminology to be used later in this blog.

Visibility Map

As the name implies, it maintains visibility info about pages containing only tuples that are known to be visible to all active transactions. For each page, one bit is used. If the bit is set to 1 means all tuples of the corresponding page are visible. The bit set to 0 means there is no free space on the given page and tuples can be visible to all transactions.

Visibility map is maintained for each relation (table and index) and gets associated alongside main relations i.e. if the relation file node name is 12345, then the visibility file gets stored in the parallel file 12345_vm.

Free Space Map

It maintains free space info containing details about the available space in the relation. This

if somebody has problem with missing clang like "clang: command not found", then workaround can be running make with variable:

  with_llvm=no make -e

Posted by Beena Emerson in EnterpriseDB on 2019-11-21 at 11:43
To enable PQtrace, we need to add the following code into the client-side source in the function where it establishes the connection with the server.

FILE *trace_file;
trace_file = fopen("/tmp/trace.out","w");
PQtrace(conn, trace_file);

First, declare the file variable and just after the connection is established on the client-side (by PQconnectdb), open the file with write permissions and start the trace. Do not forget to close the file before your return from the function where you have added this code.

From the file specified, we can get all the messages exchanged between the client and the server.

If you need to further debug the source of the messages being passed then run the client from gdb and break at PQconnectdb where it connects to the server. Attach another gdb process to the server process created.

In the client-side put a breakpoint on the following:
b pqPutc b pqPuts b pqPutnchar b pqPutInt b pqPutMsgStart b pqPutMsgEnd  

In the server-side put a breakpoint on the following:
b socket_putmessage

Now run the command necessary from the client gdb process and you

Puppet is open source software for configuration management and deployment. Founded in 2005, it’s multi-platform and even has its own declarative language for configuration.

The tasks related to administration and maintenance of PostgreSQL (or other software really) consists of daily, repetitive processes that require monitoring. This applies even to those tasks operated by scripts or commands through a scheduling tool. The complexity of these tasks increases exponentially when executed on a massive infrastructure, however, using Puppet for these kind of tasks can often solve these types of large scale problems as Puppet centralizes and automates the performance of these operations in a very agile way.

Puppet works within the architecture at the client/server level where the configuration is being performed; these ops are then diffused and executed on all the clients (also known as nodes).

Typically running every 30 minutes, the agents’ node will collect a set of information (type of processor, architecture, IP address, etc..), also called as facts, then sends the information to the master which is waiting for an answer to see if there are any new configurations to apply. 

These facts will allow the master to customize the same configuration for each node.

In a very simplistic way, Puppet is one of the most important DevOps tools available today. In this blog we will take a look at the following...

  • The Use Case for Puppet & PostgreSQL
  • Installing Puppet
  • Configuring & Programming Puppet
  • Configuring Puppet for PostgreSQL 

The installation and setup of Puppet (version 5.3.10) described below were performed in a set of hosts using CentOS 7.0 as operating system.

The Use Case for Puppet & PostgreSQL

Suppose that there is an issue in your firewall on the machines that host all your PostgreSQL servers, it would then be necessary to deny all out

Posted by Ibrar Ahmed in Percona on 2019-11-20 at 15:26
Global Indexes in PostgreSQL

PostgreSQLA 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).