Foreign data wrappers have been around for quite a while and are one of the most widely used feature in PostgreSQL. People simply like foreign data wrappers and we can expect that the community will add even more features as we speak. As far as the postgres_fdw is concerned there are some hidden tuning options, which are not widely known by users. So let us see how we can speed up the PostgreSQL foreign data wrapper.

Foreign data wrappers: Creating a “database link”

To show how things can be improved we first have to create some sample data in “adb”, which can then be integrated into some other database:

adb=# CREATE TABLE t_local (id int);
CREATE TABLE
adb=# INSERT INTO t_local 
                SELECT * FROM generate_series(1, 100000);
INSERT 0 100000

In this case I have simply loaded 100.000 rows into a very simple table. Let us now create the foreign data wrapper (or “database link” as Oracle people would call it). The first thing to do is to enable the postgres_fdw extension in “bdb”.

bdb=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

In the next step we have to create the “SERVER”, which points to the database containing our sample table. CREATE SERVER works like this:

bdb=# CREATE SERVER some_server 
                FOREIGN DATA WRAPPER postgres_fdw 
                OPTIONS (host 'localhost', dbname 'adb');
CREATE SERVER

Once the foreign server is created the users we need can be mapped:

bdb=# CREATE USER MAPPING FOR current_user 
                SERVER some_server 
                OPTIONS (user 'hs');
CREATE USER MAPPING

In this example the user mapping is really easy. We simply want the current user to connect to the remote database as “hs” (which happens to be my superuser).

Finally we can link the tables. The easiest way to do that is to use “IMPORT FOREIGN SCHEMA”, which simply fetches the remote data structure and turns everything into a foreign table.

bdb=# \h IMPORT
Command:     IMPORT FOREIGN SCHEMA
Description: import table definitions from a foreign server
Syntax:
IMPORT FOREIGN SCHEM

A role in PostgreSQL is common to all databases in the cluster. This seems to be the result of a design decision made when the former user and group handling was unified under role. Follow these links for reference:

• https://www.postgresql.org/message-id/flat/20050628161358.GH24207%40ns.snowman.net#fd1e062c263589cf67b68850a92c5b06
• https://www.postgresql.org/message-id/flat/20050627062949.GU24207%40ns.snowman.net
• https://www.postgresql.org/message-id/flat/CA%2BTgmobcQOVCDuOx3bSkYYZ%2BeTdTXFSnp9-OiEF1OtFpw7AnLg%40mail.gmail.com#e5ee53791a8f3ccce8ac8fbb39bbccd0

Roles, or rather those roles that are not just representing a specific user, ought instead to be an intrinsic part of the database model. Roles are defined by the kind of access they provide (read, write, etc) and by what relations(table, view, function, etc) they provide that access to. Access control is ideally managed within a database using roles rather than separately within each individual application that uses that database. So it makes sense that the access control rules (the roles and their associated permissions) would be defined alongside the definitions of the relations for which they are controlling access, any changes are then self contained. The access control model should be represented as part and parcel of the rest of the database model. Individual users (which are also represented as roles in PostgreSQL) are assigned one or more of the roles defined within each particular database model (based on the local enterprise definition of what needs they have of any particular database).

There is no sense to representing this kind of role at the cluster level as the definition of the role is associated specifically with the database where it actually controls access. In PostgreSQL, to encapsulate the full functionality of the database requires using not only the system catalog tables specific to that database but also the roles relevant to that database held in the cluster wide system catalog tables. With the exception of some special cases, li

There are several approaches to assess when you need to upgrade PostgreSQL. In this blog post, we look at the option for upgrading a postgres database using pg_dumpall. As this tool can also be used to back up PostgreSQL clusters, then it is a valid option for upgrading a cluster too. We consider the advantages and disadvantages of this approach, and show you the steps needed to achieve the upgrade.

This is the first of our Upgrading or Migrating Your Legacy PostgreSQL to Newer PostgreSQL Versions series where we’ll be exploring different paths to accomplish postgres upgrade or migration. The series will culminate with a practical webinar to be aired April 17th (you can register here).

We begin this journey by providing you the most straightforward way to carry on with a PostgreSQL upgrade or migration: by rebuilding the entire database from a logical backup.

Defining the scope

Let’s define what we mean by upgrading or migrating PostgreSQL using pg_dumpall.

If you need to perform a PostgreSQL upgrade within the same database server, we’d call that an in-place upgrade or just an upgrade. Whereas a procedure that involves migrating your PostgreSQL server from one server to another server, combined with an upgrade from an older version (let’s say 9.3) to a newer version PostgreSQL (say PG 11.2), can be considered a migration.

There are two ways to achieve this requirement using logical backups :

1. Using pg_dumpall
2. Using pg_dumpall + pg_dump + pg_restore

We’ll be discussing the first option (pg_dumpall) here, and will leave the discussion of the second option for our next post.

pg_dumpall

pg_dumpall can be used to obtain a text-format dump of the whole database cluster, and which includes all databases in the cluster. This is the only method that can be used to backup globals such as users and roles in PostgreSQL.

There are, of course, advantages and disadvantages in employing this approach to upgrading PostgreSQL by rebuilding the database cluster using pg_dumpall.

Advantages of using pg_dumpall for upgrading a Pos

Some people go to great lengths to avoid GROUP BY and HAVING clauses in their queries. The error messages are fussy but they are usually right. GROUP BY and HAVING key words are essential for good SQL reporting.

The primary reason for GROUP BY is to reduce the number of rows, usually by aggregation. It produces only one row for each matching grouping from the input. This allows you to make sophisticated calculations via ordinary SQL.

Fruit Example:

We have some fruit:

 item    | source  | amt | fresh_until
---------+---------+-----+------------
 bananas | Chile   | 50  | 2019-05-01
 bananas | Bolivia | 25  | 2019-04-15
 bananas | Chile   | 150 | 2019-07-10
 apples  | USA-WA  | 75  | 2019-07-01
 apples  | USA-CA  | 75  | 2019-08-15
 apples  | Canada  | 80  | 2019-08-01
 grapes | USA-CA   | 120 | 2019-07-15
(7 rows)

This next case allows us to look forward.  Mid-year, what fruits will be available? We do this with the same query as above, however, after the query runs we check the values of min(fresh_until) by using a having clause. HAVING is how you qualify an aggregate.

select item, count(source) as srcs, sum(amt) as tot_amt,
 min(fresh_until) as min_fresh_until
 from fruit
 group by item
 having min(fresh_until) > '30-jun-2019';

   item  | srcs | tot_amt | min_fresh_until
 --------+------+---------+----------------
  grapes | 1    | 120     | 2019-07-15
  apples | 3    | 230     | 2019-07-01
 (2 rows)

All of the apples and grapes will be available mid-year.

A target list may contain non-aggregates and aggregates. Those non-aggregate columns in the target list
should be in the group by clause. The error message says so. The order of the columns in the group by clause matters. It determines how the aggregates are grouped. The order is often hierarchical. What that means to your columns is your focus. It could be fruit, or sources and/or fresh_until date.

Playing Cards Examples

Let’s look at another set of examples that illustrate extracting information on playing cards. You can learn about cards on Wikipedia St

I’ve written a new 65-page ebook, titled DevOps for the Database. Drawn from what I’ve observed over the last 15 or so years in my own experience as well as that of many others, it tries to answer why and how some organizations deliver value to customers fast, run data-intensive applications with high quality, and have great engineering cultures—and why some don’t.

SQL by itself is great and powerful, and Postgres supports a broad array of more modern SQL including things like window functions and common table expressions. But rarely do I write a query where I don’t want to tweak or format the data I’m getting back out of the database. Thankfully Postgres has a rich array of functions to help with converting or formatting data. These built-in functions save me from having to do the logic elsewhere or write my own functions, in other words I have to do less work because Postgres has already done it for me which I’m always happy about.

We’ve covered a set of functions earlier, today we’re going to look at some different categories of functions to dive deeper.

Manipulating strings

When building a nice cleamn report I’m often doing some cleaning up of the data. This could be as simple as combining my first_name and last_name column. In that case a simple concatenation is quite useful:

SELECT first_name || last_name as name
FROM users;

But as you can expect I can do much more. If I’m perfoming some basic search for a keyword or delimiter I can search for the position of that string. Then I can feed that into a case statement:

SELECT CASE
  WHEN position(',' in my_string) > 0
    then True
  ELSE
    False
  END
FROM my_table;

Of course if you’re working with some field that is delimited you may want to take the above to first find the correct field. From there you likely would want to split that delimited field up. For that we can use split_part and grab the appropriate section of our delimited field:

SELECT CASE
  WHEN position(',' in my_string) > 0
    then split_part(my_string, ',', 1)
  ELSE
    null
  END
FROM my_table;

Finally, if you’re dealing with messy input/machine data there are a number of functions that can be handing for cleaning up that data:

• substr - allows you to extract a substring
• rtrim - removes the longest part of a matching string from the right side
• reverse - for the next time you’re asked to reverse a string in an interview :)
• regexp_replace - for

Database people dealing with natural languages are all painfully aware of the fact that encodings, special characters, accents and alike are usually hard to deal with. This is especially true if you want to implement search in a user friendly way.

Consider the following example in PostgreSQL: My name contains a couple of of super inconvenient special characters, which will cause issues for people around the globe. The correct spelling of my surname is “Schönig”, which is pretty hard to type on most keyboards I have seen around the world. And: Who cares about my special characters anyway? People might just want to type in “Schonig” into some search form and find information about me (ideally related to PostgreSQL and database work).

Here is the problem:

test=# SELECT 'Schönig' = 'Schonig';
 ?column? 
----------
 f
(1 row)

test=# SELECT 'Schönig' = 'Schoenig';
 ?column? 
----------
 f
(1 row)

The “=” operator compares those two strings and comes to the conclusion that those two strings are not identical. Therefore the correct answer is “false”. While that is true from a technical point of view it might be a real issue and end users might be unsatisfied with the result. Problems like that can make daily life pretty hard. A solution to the problem is therefore needed.

unaccent: PostgreSQL provides a useful extension

If you want to improve your user experience you can turn to the “unaccent” extension, which is shipped as part of the PostgreSQL contrib package. Installing “unaccent” is really easy:

test=# CREATE EXTENSION unaccent;
CREATE EXTENSION

In the next step you can call the “unaccent” function to clean a string and turn it into something more useful. This is what happens when we use the “unaccent” function on my name and the name of my PostgreSQL support company:

test=# SELECT unaccent('Hans-Jürgen Schönig, Gröhrmühlgasse 26, Wiener Neustadt');
                        unaccent                         
---------------------------------------------------------
 Hans-Jurgen Schonig, Grohrmuhlgasse 26, Wiener

Are you deploying PostgreSQL in the cloud and want to understand your options for achieving high availability? In our previous blog post, Managing High Availability in PostgreSQL – Part I, we discussed the capabilities and functioning of PostgreSQL Automatic Failover (PAF) by ClusterLabs. In Part II, we’re introducing you to an alternative open source tool, Replication Manager from 2ndQuadrant, to be closely followed by Part III where we dive into our third alternative, Patroni by Zalando.

Replication Manager (repmgr)

repmgr is an open-source tool suite developed by 2ndQuadrant for managing replication and failover of your PostgreSQL clusters. It provides the tools to setup, configure, manage, and monitor replication of PostgreSQL, and also enables you to perform manual switchover and failover tasks using repmgr utility. This free tool supports and enhances PostgreSQL’s built-in streaming replication.

Replication Manager provides two main tools to manage replication and failover of PostgreSQL.

repmgr

• A command-line interface utility which enables you to perform various administrative tasks.
• repmgr enables you to setup standby servers, promote standbys, do a switchover, and monitor the status of your PostgreSQL cluster.
• It also provides dry run option for almost all of the administrative commands.

repmgrd

This is the daemon which:

• Actively monitors the PostgreSQL clusters and performs necessary actions based on the state of the cluster.
• Performs automatic failover in case the primary node goes down by promoting the most eligible standby as the new primary.
• Provides an option to monitor and store the data related to replication performance.
• Provides notification by invoking the user scripts for registered events.

How it Works

repmrg not only manages the replication of PostgreSQL clusters, but also has capabilities for setting up the standby servers for replication. Following the initial installation, we need to make changes to the repmgr configuration file (repmgr.conf) with the required details on each server.

The PostGIS development team is pleased to provide bug fix 2.5.2, 2.4.7, and 2.3.9 for the 2.5, 2.4, and 2.3 stable branches.

These are the first versions to be able to compile against Proj 6.0.0, You must upgrade to these if you are using Proj 6.

2.5.2 This release supports PostgreSQL 9.3-11 (will compile against PostgreSQL 12, but not pass tests. Use only for pg_upgrade. You are encouraged to use the PostGIS 3.0 unreleased branch with PostgreSQL 12 , which has features specifically designed to take advantage of features new in PostgreSQL 12).

• source download
• NEWS
• PDF docs en de
• html doc download
• html online en de ja
• epub doc download
• Changelog

2.4.7 This release supports PostgreSQL 9.3-10.

• source download
• NEWS
• PDF docs en
• html doc download
• html online en
• epub doc download
• Changelog

2.3.9

This release supports PostgreSQL 9.2-10.

• source download
• NEWS
• PDF docs en
• html doc download
• html online en
• epub doc download
• Changelog

View all closed tickets for 2.5.2, 2.4.7, 2.3.9.

After installing the binaries or after running pg_upgrade, make sure to do:

ALTER EXTENSION postgis UPDATE;

— if you use the other extensions packaged with postgis — make sure to upgrade those as well

ALTER EXTENSION postgis_sfcgal UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;

If you use legacy.sql or legacy_minimal.sql, make sure to rerun the version packaged with these releases.

There was a time when every piece of software had to be purchased: operating systems, compilers, middleware, text editors. Those days are mostly gone, though there are a few holdouts (e.g., MS Windows, vertical applications). What happened is that open source software has come to dominate most uses, and software selection is rarely based on cost requirements.

One of the final holdouts for purchased software is databases. You might think that is because database software is complex, but so is the other software mentioned. The big difference is that while non-database software processes or stores user data in a simple or standard way, databases lock user data inside the database. This data locking is a requirement for fast, reliable, and concurrent data access, but it does place the database on a different monetary plane.

In any organization, it is really their data that is valuable, and because the database is so tightly coupled to that valuable data, database software becomes something that is worth significant investment. This explains why databases have resisted the open source commoditization that has happened to so much other purchased software. (Custom database applications tied to complex business logic has also slowed the migration.)

Continue Reading »

The maximum number of connections a PostgreSQL can accept is configured using max_connections. When attempting to connect to a server already at full capacity, logically the server complains:

$ psql
psql: FATAL:  sorry, too many clients already

It is possible to define connection policies, for example at database level with CREATE DATABASE or ALTER DATABASE, and even have superuser-only connection slots using superuser_reserved_connections, so as a superuser has a reserved space to be able to perform some activities even with a server full.

When creating a connection for replication purposes, the connection is spawned under a special status with the context of a WAL sender which is in charge of the communication, and speaks the replication protocol, so as it is possible to do replication, to take base backups, etc. A lot of those tasks are important for availability. One problem however is that this connection uses a shared memory slot which is part of max_connections. Hence, it is possible to get into a case where an application bloats the connections, and it becomes impossible to connect with a replication connection. This can be rather bad for availability, because this could the creation of a new standby after a failover for example.

One way to counter that is to connect to the server for base backups and standbys with a superuser role. Still this is not completely right either as by design there can be replication roles, which allow a role to connect to a server in replication mode, without being a superuser. In this context, this is where the following commit of Postgres 12 becomes handy:

commit: ea92368cd1da1e290f9ab8efb7f60cb7598fc310
author: Michael Paquier <michael@paquier.xyz>
date: Tue, 12 Feb 2019 10:07:56 +0900
Move max_wal_senders out of max_connections for connection slot handling

Since its introduction, max_wal_senders is counted as part of
max_connections when it comes to define how many connection slots can be
used for replication connections with a WAL sender context.  This can
lead t

Having explained that lock-in is not a binary option, what are the Postgres tool support options available, at a high level?

• Develop in-house database tools and support them yourself
• Use open source tools and support them yourself
• Use open source tools with vendor support (hopefully the vendor supports your chosen tools)
• Use closed-source tools with vendor support

Of course, you can mix and match these options, i.e., use a support vendor for the open source tools they support, use other open source tools they don't support, and use some tools you develop in-house, e.g.:

• open source Postgres database (vendor support)
• pgBackRest for backup (vendor support)
• patroni for failover (community support channels)
• In-house developed tools (self support)

I went over these options in more detail in this presentation. This diversity of options is rarely available for closed-source, single-vendor database solutions.

Many people know that explicit table locks with LOCK TABLE are bad style and usually a consequence of bad design. The main reason is that they hamper concurrency and hence performance.

Through a recent support case I learned that there are even worse effects of explicit table locks.

Table locks

Before an SQL statement uses a table, it takes the appropriate table lock. This prevents concurrent use that would conflict with its operation. For example, reading from a table will take a ACCESS SHARE lock which will conflict with the ACCESS EXCLUSIVE lock that TRUNCATE needs.

You can find a description of the individual lock levels in the documentation. There is also the matrix that shows which lock levels conflict with each other.

You don’t have to perform these table locks explicitly, PostgreSQL does it for you automatically.

Explicit table locks with the LOCK TABLE statement

You can also explicitly request locks on a table with the LOCK statement:

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

There are some cases where it is useful and indicated to use such an explicit table lock. One example is a bulk update of a table, where you want to avoid deadlocks with other transactions that modify the table at the same time. In that case you would use a SHARE lock on the table that prevents concurrent data modifications:

LOCK atable IN SHARE MODE;

Typical mistakes with LOCK TABLE

Unfortunately most people don’t think hard enough and just use “LOCK atable” without thinking that the default lock mode is ACCESS EXCLUSIVE, which blocks all concurrent access to the table, even read access. This harms performance more than necessary.

But most of the time, tables are locked because developers don’t know that there are less restrictive ways to achieve what they want:

• You don’t want concurrent transactions to modify a row between the time you read it and the time you update it? Use SELECT ... FOR UPDATE!
  If concurrent modifications are unlikely and you are not sure that you ar

2019 March 21 Meeting 6pm-8pm

Location:

PSU Business Accelerator
2828 SW Corbett Ave · Portland, OR
Parking is open after 5pm.

Speaker: Hannah Stepanek

Postgres has had streaming (binary) replication for a long time, and logical (row shipping) replication since Postgres 10. Implementing these was a lot of work, and they work well.

However, the simplest way to do replication is often considered to be replaying SQL queries on standbys. The primary was modified by SQL queries, so isn't the simplest way to replicate replaying SQL? A novice would think so, and many database server developers initially try replication by replaying SQL. It seems simple because SQL queries are more concise than per-row changes. Imagine a DELETE that affects one million rows being shipped to a standby as a single SQL query. The conciseness and simplicity of SQL replication looks promising.

However, if you try implementing replication via SQL, you will realize that SQL runs in a multi-user environment. SQL commands do not contain enough information to replay queries the exact same way on standbys as the primary. Concurrent DML, volatile functions, sequence assignment, locks, and cursor contents can all cause inconsistencies. Developers have tried patching over these issues, but eventually the fundamental limitations of this approach become clear. I doubt Postgres will ever implement SQL-level replication for this reason.

The concept of Linux HugePages has existed for quite a while: for more than 10 years, introduced to Debian in 2007 with kernel version 2.6.23. Whilst a smaller page size is useful for general use, some memory intensive applications may gain performance by using bigger memory pages. By having bigger memory chunks available to them, they can reduce lookup time as well as improve the performance of read/write operations. To be able to make use of HugePages, applications need to carry the specific code directive, and changing applications across the board is not necessarily a simple task. So enter Transparent HugePages (THP).

By reputation, THPs are said to have a negative impact on performance. For this post, I set out to either prove or debunk the case for the use of THPs for database applications.

The Linux context

On Linux – and for that matter all operating systems that I know of – memory is divided into small chunks called pages. A typical memory page size is set to 4k. You can obtain the value of page size on Linux using getconf.

# getconf PAGE_SIZE
4096

Generally, the latest processors support multiple page sizes. However, Linux defaults to a minimal 4k page size. For a system with 64GB physical memory, this memory will be divided into more than 16 million pages. Linking between these pages and physical memory (which is called page table walking) is undertaken by the CPU’s memory management unit (MMU). To optimize page lookup, CPU maintains a cache of recently used pages called the Table Lookaside Buffer (TLB). The higher the number of pages, the lower the percentage of pages that are maintained in TLB. This translates to a higher cache miss ratio. With every cache miss, a more expensive search must be done via page table walking. In effect, that leads to a degradation in performance.

So what if we could increase the page size? We could then reduce the number of pages accessed, and reduce the cost of page walking. Cache hit ratio might then improve because more relevant data now fits in one page rather than

One of the major attractions of Postgres is the ability to stop using database software controlled by a single vendor. Single-vendor control means a single entity controls the software, tooling, training, and support. There are sometimes options from other vendors, but they are usually hampered because the source code is closed.

Postgres is a strong move away from that, but is it always a complete disconnection from lock-in? Well, it can be — you could:

• Download the Postgres source code and compile it yourself
• Evaluate, integrate, and test the tooling you need to use Postgres in your organization
• Create a training program for your employees
• Develop a Postgres internals team to support Postgres and your tooling

This is not possible for proprietary software, but because Postgres is open source, it is certainly possible.

Continue Reading »

Introduction

When I first got involved in managing a Postgres database, I was quickly introduced to the need for replication. My first project was to get our databases up on Slony, which was the hot new replication technology, replacing our clunky DRBD setup and allowing near-realtime read-only copies of the database. Of course, with time and scale, Slony had a hard time keeping up with the write traffic, especially since it ultimately suffered from write amplification (each write ultimately becomes two or more writes to the database, because of all the under-the-hood work involved). When Postgres Streaming Replication came out in v. 9.0, everyone felt like they struck gold. Streaming Replication was fast, and it took advantage of an already-existing feature in Postgres: the WAL Stream.

Many years have passed since v. 9.0 (we’re coming up on v. 12 very soon). More features have been added, like Hot Standby, Logical Replication, and some two-way Master-Master replication extensions have been created. This has been quite a path of growth, especially since I remember someone saying that Postgres’ roadmap would not include replication at a BOF at PGCon, circa 2010.

With all the improvements to Streaming Replication over the years, I think one of the most misunderstood features is hot_standby_feedback, and I hope to clarify that here.

With Streaming Replication, users are able to stand up any number of standby servers with clones of the primary, and they are free to throw all sorts of load at them. Some will send read-only traffic for their OLTP apps, huge cronjobs, and long-running reporting queries, all without affecting write traffic on the primary. However, some will occasionally see that their queries get aborted for some reason, and in their logs they might see something like:

ERROR:  canceling statement due to conflict with recovery

That’s an unfortunate reality that nobody likes. Nobody wants their queries canceled on them, just like nobody likes to order a pastrami sandwich, only to be told 10 minutes later

PostgreSQL provides a bevy of authentication methods to allow you to pick the one that makes the most sense for your environment. One desired implementation that I have found customers wanting is to use  Windows Active Directory with PostgreSQL's GSSAPI authentication interface using Kerberos. I've put together this guide to help you take advantage of this setup in your own environment. 

Postgres has long lived in the shadow of proprietary and other open source databases. We kind of got used to that, though we had early support from Fujitsu and NTT. In recent years, Postgres has become more noticed, and the big companies promoting Postgres have become somewhat of a flood:

• Amazon (RDS)
• Google
• IBM (cloud and non-cloud)
• Microsoft

Even with IBM having DB2 and Microsoft having SQL Server, they still support Postgres.

It is odd having multi-billion-dollar companies asking how they can help the Postgres community, but I guess we will have to get used to it. These companies support the community to varying degrees, but we certainly appreciate all the help we receive. Just having these companies list us as supported is helpful.

Index-only scans can be a really effective way to speed up table reads that hit an index. Of course, they’re not a silver bullet to all your performance problems, but they are a very welcome and useful part of the toolbox.

In order to understand index-only scans, why (and when) they’re valuable, let’s recap how a “normal” index scan works.

Index scans

An index is just references to the rows in a table, stored in a data structure (usually a binary tree) based on their values in the indexed columns.

An index scan reads through the index and uses it to quickly look up the rows that match your filter (something like WHERE x > 10), and return them in the order they’re stored in the index.

Postgres then goes to look up the data in these rows from the table, in the heap, where it would have found them if it had done a sequential scan.

It checks that they are visible to the current transaction — for example they haven’t been deleted or replaced by a newer version — and passes them on to the next operation.

It’s a bit like using an index in a book. Instead of starting at page one and turning over the pages until you find the ones that deal with, say, soil erosion, you skip to “s” in the index, look up “soil erosion” and turn to the listed pages to read about where all the dirt is going.

Enter index-only scans

Index-only scans start off like index scans, but they get all their column information from the index, obviating the need to go back to the table to fetch the row data — the second step in the index scan process.

Returning to our book example, if we want to produce a list of topics in the book, ordered by the number of pages they appear on, then all that information is stored in the book’s index, so we can do so purely from reading the index without ever actually turning to the pages in question.

As you can imagine, under the right circumstances this can be an incredibly fast way for Postgres to access the table data. In pgMustard, we suggest considering an index-only scan to improve slow index

I tend to use FreeBSD as my PostgreSQL base machine, and that’s not always as simple as it sounds to get software running on it. In this post I take some advices on running pgbackrest on FreeBSD 12.

Running pgbackrest on FreeBSD

pgbackrest is an amazing tool for backup and recovery of a PostgreSQL database. However, and this is not a critique at all, it has some Linux-isms that make it difficult to run on FreeBSD. I tried to install and run it on FreeBSD 12, stopping immediatly at the compilation part. So I opened an issue to get some help, and then tried to experiment a little more to see if at least I could compile.

The first trial was to cross-compile: I created the executable (pgbackrest has a single executable) on a Linux machine, then moved it to the FreeBSD machine along with all the ldd libraries (placed into /compat/linux/lib64). But libpthread.so.0 prevented me to start the command:

% ./pgbackrest ./pgbackrest: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory 

So I switched back to native compilation and, as described in the issue I made a little changes to the client.c and the Makefile. Since it compiled (using of course gmake), I also made a little more changes to Makefile to compile and install it the FreeBSD way (i.e., under /usr/local/bin). The full diff is the following (some changes are not shown in the issue):

% git diff diff --git a/src/Makefile b/src/Makefile index 73672bff..0472c7f1 100644 --- a/src/Makefile +++ b/src/Makefile @@ -8,7 +8,7 @@ CC=gcc # Compile using C99 and Posix 2001...

dnf install pspg

export PSQL_PAGER=pspg #for Postgres 11 
export PAGER=pspg
psql dbname