Alexey Lesovsky: Deep dive into postgres stats: pg_stat_activity and pg_locks.

planet postgresql - 3時間 5分
DBA's best friends: pg_stat_activity and pg_locks.

Today, I would like to discuss another handy stats views - pg_stat_activity. This is really useful and powerful view the value of which is hard to measure. In short, the main goal of pg_stat_activity is to show the current activity in Postgres. Hence, if you would like to know what is going on in the database at any given moment, pg_stat_activity is the first place to check. Having worked with Postgres for several years now I’ve accumulated many useful queries. The majority of which I’ve already mentioned in the first article in these series. Though that post is two years old now, I’d recommend reading it to those who might have missed it. There, I talk about basic examples related to pg_stat_activity usage, such as checking current client activity, viewing clients' states, checking transactions and queries ages, etc. Of course, since then PostgreSQL moved on and pg_stat_activity was slightly changed in the newer versions. In today's post I am going to cover new aspects and some advanced techniques related to pg_stat_activity and another useful view - pg_locks.

Wait events is the new feature that has been introduced in the newer versions of PostgreSQL. In older versions you were able to check states of connected clients and get a little idea about what they actually do. The most curious state and, at the same time, the most unwanted one is waiting state. Pg_stat_activity has boolean column named “waiting” where “true” value means that the client is currently in a waiting state and is not performing. When waiting clients are detected, the next step is to check another view - pg_locks that helps to locate locked and locking clients. That’s pretty much it, PostgreSQL doesn’t provide any other ways to investigate the reason behind the waiting client, especially in cases when clients are waiting on events not related to inter-transactional communications. Wait events, that would allow us to get more information, were introduced in 9.6 where waiting column was replac[...]
カテゴリー: postgresql

Peter Gagarinov: Performance comparison of Postgres connectors in Matlab, Part 3: retrieving arrays

planet postgresql - 20時間 36分

In this paper we continue the investigation of PostgreSQL connectors in Matlab started in Part I and Part II. In the latter paper we compared the performance of data retrieval from PostgreSQL for the case all the fields to be retrieved having scalar types. Compared were two connectors. The first one was Matlab Database Toolbox (working with PostgreSQL via a direct JDBC connection). The second connector is PgMex library (providing a connection to PostgreSQL via libpq library). Here we consider retrieving of data containing values of array types.

[Read More]
カテゴリー: postgresql

Jim Mlodgenski: Expanding the NYC Conference

planet postgresql - 2017-10-18(水) 22:49:00
We just announced the dates and the CFP for PGConf 2018, our 7th Postgres Conference in the New York City area. We are back at the Westin in Jersey City April 16th-20th again this year just across the river from Manhattan and an easy PATH ride to all things New York. As we learn from year to year, we evolve based on our attendees and sponsors feedback. This biggest change we are doing this year is expanding the program to be a full 5 days. Our attendees have asked for even more training so we are now starting on Monday with several full-day training classes. We will then roll into half-day tutorials on Tuesday and the full conference schedule starting on Wednesday.

Other changes you will see this year is the 4th Annual Regulated Industry Summit will now be part of the main program. A number of people who wanted to attend the RIS also wanted to attend the tutorials so we removed that conflict while opening up the RIS to a wider audience. Along with RIS, we are hosting two additional summits. Thanks to one of our Diamond Sponsors, Pivotal, we will have the inaugural Greenplum Summit to bring together the best in Big Data, Massively Parallel Processing (MPP), Machine Learning, Graph, AI and Spatial Analytics centered around the Open Source, Postgres derived Greenplum database. Finally, we are having the Open Source Summit for our fellow local Open Source communities with data related technologies such as Python, R and Ruby.

All of this wouldn't be possible without our sponsors:
Diamond:        AWS, Pivotal
Platinum:        Compose, OpenSCG, 2ndQuadrant, Microsoft
Gold:              JetBrains, Cybertec, Citus, EnterpriseDB
Silver:            Command Prompt

Save the date and submit a talk at:

カテゴリー: postgresql

Jobin Augustine: PostgreSQL 10.0 – Features New and Updated

planet postgresql - 2017-10-18(水) 20:03:12

Overview: PostgreSQL 10 contains many new features that will allow users to both scale out and scale up their PostgreSQL infrastructure. To upgrade to PostgreSQL 10 from any lower releases, a pg_dumpall dump and restore or pg_upgrade is necessary. In this article, we will go over some new features that have been added and also significant modifications that have been done with a focus on information relevant for operational DBA’s in normal work.


  • Logical Replication has been included as a built-in option for replicating specific tables or using replication to upgrade
  • Native Table Partitioning with range and list partitioning
  • Additional Query Parallelism is included for index scans, bitmap scans, and merge joins

With highlights of PostgreSQL 10.0 as listed above, let us see the features under the below headers.


  1. Addition of new system catalog tables like pg_hba_file_rules, pg_partitioned_table, pg_stat_subscription, etc is certainly a highlight of PostgreSQL 10.
  2. New columns have been added to few of the earlier catalog tables like pg_class, pg_replication_slots, pg_stat_activity, pg_stat_replication, etc., for efficient management.
  3. PostgreSQL Client library libpq has been enhanced with the feature to add multi-instance specifications. Multiple host names and port numbers can be described in PGHOST And PGPORT. In addition, “passfile” has been added as a new connection attribute. It was earlier specified with the environment variable PGPASSFILE etc.
  4. Log directory pg_log has been changed to log.
  5. Also, pg_xlog has been renamed to pg_wal so as to distinguish more clearly between the directories write-ahead log and server activity logs.
  6. All SQL functions, tools, and options that reference “xlog” have been updated to “wal”. Example: pg_switch_xlog() is now pg_switch_wal(), pg_receivexlog is pg_receivewal, etc.
  7. Transaction status directory pg_clog has been renamed to pg_xact directory. The name “location” indicating the location of WAL has been changed to “lsn”. Example: sent_location to sent_ls
カテゴリー: postgresql

Bruce Momjian: Speaking in China

planet postgresql - 2017-10-18(水) 11:00:01

I had the pleasure of speaking at Alibaba's Computing Conference last week in Hangzhou, China. I gave two presentations. The first covered Postgres 10 features (images, video). The second was a generic talk about the future of open source databases (images, video starts at 153 minutes).

This week I am attending a community Postgres conference in Shenzhen, China. Postgres users here have a strong desire to increase Postgres adoption in China in the coming years.

カテゴリー: postgresql

Craig Kerstiens: A tour of Postgres Index Types

planet postgresql - 2017-10-18(水) 04:35:00

At Citus we spend a lot of time working with customers on data modeling, optimizing queries, and adding indexes to make things snappy. My goal is to be as available for our customers as we need to be, in order to make you successful. Part of that is keeping your Citus cluster well tuned and performant which we take care of for you. Another part is helping you with everything you need to know about Postgres and Citus. After all a healthy and performant database means a fast performing app and who wouldn’t want that. Today we’re going to condense some of the information we’ve shared directly with customers about Postgres indexes.

Postgres has a number of index types, and with each new release seems to come with another new index type. Each of these indexes can be useful, but which one to use depends on 1. the data type and then sometimes 2. the underlying data within the table, and 3. the types of lookups performed. In what follows we’ll look at a quick survey of the index types available to you in Postgres and when you should leverage each. Before we dig in, here’s a quick glimpse of the indexes we’ll walk you through:

  • B-Tree
  • Generalized Inverted Index (GIN)
  • Generalized Inverted Seach Tree (GiST)
  • Space partitioned GiST (SP-GiST)
  • Block Range Indexes (BRIN)
  • Hash

Now onto the indexing

In Postgres, a B-Tree index is what you most commonly want

If you have a degree in Computer Science, then a B-tree index was likely the first one you learned about. A B-tree index creates a tree that will keep itself balanced and even. When it goes to look something up based on that index it will traverse down the tree to find the key the tree is split on and then return you the data you’re looking for. Using an index is much faster than a sequential scan because it may only have to read a few pages as opposed to sequentially scanning thousands of them (when you’re returning only a few records).

If you run a standard CREATE INDEX it creates a B-tree for you. B-tree indexes are valuable on the most common data types such as text, num

カテゴリー: postgresql

William Ivanski: Postgres-XL with OmniDB

planet postgresql - 2017-10-17(火) 19:44:56
1. Introduction

Postgres-XL (or just XL, for short) is an open source project from 2ndQuadrant. It is a massively parallel database built on top of PostgreSQL, and it is designed to be horizontally scalable and flexible enough to handle various workloads.

Here we will show how to build a test environment to play with XL and how to configure it using the OmniDB 2.2 web interface.

2. Building test environment

In this experiment, we will build a cluster with 1 GTM, 1 coordinator and 2 data nodes. It would be simpler to put them in the same virtual machine, however split them across multiple virtual machines is a more realistic scenario. So we will build 3 virtual machines:

Machine IP Role xl_gtmcoord GTM and coordinator xl_datanode1 data node xl_datanode2 data node

For this test environment to work, you need to put both machines in the same internal network. If necessary, you can edit IP addresses in files Vagrantfile and bootstrap.shlocated inside the folder of each machine.

2.1. Pull OmniDB repo

The first thing you need to do is to download OmniDB repo from GitHub and make sure you are in the development branch. Run the following:

git clone https://github.com/OmniDB/OmniDB cd OmniDB git checkout dev 2.2. Creating virtual machines

On your host machine, you need to have installed:

  • VirtualBox
  • Vagrant
  • Vagrant plugin vbguest

Please refer to VirtualBox and Vagrant websites for more information.

If necessary, please edit the file Vagrantfile for all machines now, like this:

config.vm.network "private_network", ip: '', :name => 'vboxnet0', :adapter => 2 You also will need to replace the IP addresses on the file bootstrap.sh for all machines.

On your terminal (assuming you are on the root directory of OmniDB repo), to create the first virtual machine with XL, you need to do:

cd OmniDB/OmniDB_app/tests/vagrant/multinode/xl_gtmcoord vagrant up Please note that this may take a while to finish. It will perform the following tasks automat[...]
カテゴリー: postgresql

Douglas Hunley: updated PostgreSQL homebrew script

planet postgresql - 2017-10-16(月) 22:09:23

With the release of PostgreSQL 10, I’ve updated my pg script. You might recall from previous posts that this script is for Homebrew users that have tapped Peter’s brew recipes. It allows for installing and switching between multiple version of PostgreSQL seemlessly. While I was in there adding v10 support, I tweaked and tuned the code a bit and tidyied up the output significantly. I’m pretty pleased with the new version actually.

As always, it’s been added as a gist:


カテゴリー: postgresql

Craig Ringer: Plan for the unexpected: install diagnostic tools on your PostgreSQL servers

planet postgresql - 2017-10-16(月) 17:34:33

There’s a lot of information out there on how to configure PostgreSQL, on the importance of backups and testing them, etc.

But what about the server you run PostgreSQL on? We tend to pay a lot less attention to tools that you won’t need unless something breaks. But it’s worth taking some time to do so now, because that’s time you won’t have when your server is down and you’re in a rush.

Debuginfo and gdb

Seriously consider installing debug-info packages for the major services you run on a server, and for the libraries that service uses. It wastes some disk space, but it saves you a lot of time if you end up needing that information in a rush.

Debug info lets tools like the GNU Debugger gdb show variables, program arguments, etc when it is connected to a running program, or to a crash dump (core file). It lets people doing advanced support and bug fixing help you a lot more effectively. I have been able to fix problems in running PostgreSQL instances in gdb without having to shut the server down due to the availability of gdb and debuginfo.

Ideally, you wouldn’t need to install debuginfo before you intend to use it. You could just fetch it on demand. But debuginfo needs to exactly match the packages installed, and often repositories only carry a limited history of old versions, so it might not be there when you need it. It’s also fairly common for servers to be Internet-isolated, so you can’t just install packages on demand. And it’s possible the person doing the debugging won’t be the sysadmin with root access, so they can’t install them if they want to.

So please install it along with the PostgreSQL packages. For example, on Debian/Ubuntu:

sudo aptitude install postgresql-9.6-dbg

or for RHEL/Fedora:

sudo dnf install postgresql96-debuginfo

Better, use the debuginfo-install program for RHEL/Fedora:

sudo debuginfo-install postgresql

strace is nice to have too.

Performance: sysstat, iotop, perf

You should always install sysstat and make sure that it is sar is collecting information for sar. I recommend also lo

カテゴリー: postgresql

Jobin Augustine: Setting up PL/R with Postgres on Windows

planet postgresql - 2017-10-16(月) 15:34:01
1. Introduction

Setting up extra extensions / features on proprietary platform like Windows is a challenge because we don’t have a default build environment. Setting up a complete build environment for installing a language extension is bigger challenge. Thanks to vibrant community, many of the heavy lifting is already done. I would like to Acknowledge the contribution Dave Cramer who built the Windows extension binaries and Shawn from BigSQL user forum for leading me to this learning. Special thanks to Joe Conway and PL/R community for maintaining the language extension.

2. getting pl/R extension binaries

Windows binaries for PL/R Repository is available here

As the name indicating, PostgreSQL 9.6 extension is built against R version 3.4.1 and extension for Postgres 10 is built against R version 3.4.2. Download the corresponding zip file and unpack into a direcotory. For this demonstration I am using Postgres 9.6 extension. Downloaded plr- file and unpacked into a directory plr-

3. Installion of R

As the name indicates, this needs R version 3.4.1. Windows installer for the same is available here One important aspect I would suggest to take care is to avoid directory names with spaces while installing R or Postgres. In the installer screen, I Installed into a directory C:\R\R-3.4.1 instead of “Program Files” to avoid spaces and potential problems that can cause.

We can leave rest of defaults in the Installer.

4. Install Postgres if not already done.

For this demonstration, I am using portable PostgreSQL binaries provided by BigSQL project. its just a matter of opening windows command line in administrator mode and run a single line power shell script.

5. Installing PL/R Extension

We have to do this manually, by copying the files to corresponding directories. In order to detect the location of files, few environment variables also required. I have done the following from the command prompt (from the directory where PL/R extensions are unpacked)


カテゴリー: postgresql

Peter Geoghegan: amcheck for Postgres 9.4+ now available from PGDG apt and yum repositories

planet postgresql - 2017-10-16(月) 10:44:00
amcheck, a tool for index corruption detection, now has packages available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository.

This means that installations built on those community resources can easily install amcheck, even on PostgreSQL versions before PostgreSQL 10, the release that contrib/amcheck actually first appears in.

Full details on installing these packages are available from the README: https://github.com/petergeoghegan/amcheck/

It's also possible to install the packages on PostgreSQL 10, because the extension these packages install is actually named "amcheck_next" (not "amcheck"). Currently, it isn't really useful to install "amcheck_next" on PostgreSQL 10, because its functionality is identical to contrib/amcheck. That's expected to change soon, though. I will add a new enhancement to amcheck_next in the coming weeks, allowing verification functions to perform "heap matches index" verification on top of what is already possible.

Many thanks to Christoph Berg and Devrim Gündüz for their help with the packaging.
カテゴリー: postgresql

Leo Hsu and Regina Obe: PLV8 binaries for PostgreSQL 10 windows both 32-bit and 64-bit

planet postgresql - 2017-10-15(日) 15:48:00

Now that we are in midst of moving our databases and client databases to PostgreSQL 10, we started to build our favorite extensions. One popular one which several have asked when we'll have ready for PostgreSQL 10, is the plv8 extensions.

We now have version 1.4.10 for PostgreSQL 10 windows both the 32-bit and 64-bit. The 64-bit versions will work for PostgreSQL EDB Windows as well as the BigSQL distribution.

Continue reading "PLV8 binaries for PostgreSQL 10 windows both 32-bit and 64-bit"
カテゴリー: postgresql

Leo Hsu and Regina Obe: PLV8 binaries for PostgreSQL 10 windows both 32-bit and 64-bit

planet postgresql - 2017-10-15(日) 15:04:42

Now that we are in midst of moving our databases and client databases to PostgreSQL 10, we started to build our favorite extensions. One popular one which several have asked when we'll have ready for PostgreSQL 10, is the plv8 extensions.

カテゴリー: postgresql

REGINA OBE: Using pg_upgrade to upgrade PostGIS without installing an older version of PostGIS

planet postgresql - 2017-10-15(日) 14:11:00

PostGIS releases a new minor version of PostGIS every one or two years. Each minor version of postgis has a different libname suffix. In PostGIS 2.1 you'll find files in your PostgreSQL lib folder called postgis-2.1.*, rtpostgis-2.1.*, postgis-topology-2.1.*, address-standardizer-2.1.* etc. and in a PostGIS 2.2 you'll find similar files but with 2.2 in the name. I believe PostGIS and pgRouting are the only extensions that stamp the lib with a version number. Most other extensions you will find are just called extension.so e.g. hstore is always called hstore.dll /hstore.so even if the version changed from 9.6 to 10. On the bright side this allows people to have two versions of PostGIS installed in a PostgreSQL cluster, though a database can use at most version. So you can have an experimental database running an very new or unreleased version of PostGIS and a production database running a more battery tested version.

On the sad side this causes a lot of PostGIS users frustration trying to use pg_upgrade from an older version of PostGIS/PostgreSQL to a newer version of PostGIS/PostgreSQL; as their pg_upgrade often bails with a message in the loaded_libraries.txt log file something to the affect:

could not load library "$libdir/postgis-2.2": ERROR: could not access file "$libdir/postgis-2.2": No such file or directory could not load library "$libdir/postgis-2.3": ERROR: could not access file "$libdir/postgis-2.3": No such file or directory

This is also a hassle because we generally don't support a newer version of PostgreSQL on older PostGIS installs because the PostgreSQL major version changes tend to break our code often and backporting those changes is both time-consuming and dangerous. For example the DatumGetJsonb change and this PostgreSQL 11 crasher we haven't isolated the cause of yet. There are several changes like this that have already made the PostGIS 2.4.0 we released recently incompatible with the PostgreSQL 11 head development.

Continue reading "Using pg_upgrade to upgrade PostGIS without installi[...]
カテゴリー: postgresql

Robins Tharakan: First alpha release of PsqlForks - Menon

planet postgresql - 2017-10-14(土) 04:15:00
Primer: PsqlForks aims to support all DB Engines that (even partially) speak Postgres (psqlforks = psql for Postgres forks). Given that PsqlForks has been in development for a few weeks, it's time to stabilize a bit and towards that, we finally have Menon, PsqlForks first Alpha Release. Being an alpha, by definition it isn't ready for production, but it feels stable enough ... feel free to test
カテゴリー: postgresql

Kaarel Moppel: Best of PostgreSQL 10 for the DBA

planet postgresql - 2017-10-13(金) 22:30:15

Last week a new PostgreSQL major version with the number 10 was released! Announcement, release notes and the „What’s new“ overview can be found from here, here and here – it’s highly recommended reading, so check them out. As usual there have been already quite some blog postings covering all the new stuff, but I […]

The post Best of PostgreSQL 10 for the DBA appeared first on Cybertec - The PostgreSQL Database Company.

カテゴリー: postgresql

Dimitri Fontaine: Set Returning Functions and PostgreSQL 10

planet postgresql - 2017-10-13(金) 20:25:21

PostgreSQL 10 is now available for everyone to use, and hinted by David Fetter I had to review my previous article on Json and SQL to adapt to Set Returning Functions changes.

A Set Returning Function is a PostgreSQL Stored Procedure that can be used as a relation: from a single call it returns an entire result set, much like a subquery or a table.

It used to be possible to use SRF in the SELECT clause, with dubious (but useful at times) semantics, and also in scalar contexts. The semantics have been fixed and are now much clearer, and the uses in scalar contexts are forbidden — they were a hack and never made sense anyway.

カテゴリー: postgresql

Dimitri Fontaine: Set Returning Fonctions and PostgreSQL 10

planet postgresql - 2017-10-13(金) 20:25:21

PostgreSQL 10 is now available for everyone to use, and hinted by David Fetter I had to review my previous article on Json and SQL to adapt to Set Returning Functions changes.

A Set Returning Function is a PostgreSQL Stored Procedure that can be used as a relation: from a single call it returns an entire result set, much like a subquery or a table.

It used to be possible to use SRF in the SELECT clause, with dubious (but useful at times) semantics, and also in scalar contexts. The semantics have been fixed and are now much clearer, and the uses in scalar contexts are forbidden — they were a hack and never made sense anyway.

カテゴリー: postgresql

gabrielle roth: PDXPUG: October meeting in one week

planet postgresql - 2017-10-13(金) 02:00:28

When: 6-8pm Thursday October 19, 2017
Where: iovation
Who: Grant Holly
What: Golang + Pg

This month, Grant will be talking about using Golang with Postgres 9.6 and showing us some of the client libraries.

Grant Holly is a Service Reliability Engineer at iovation, where he works with a lot of different database systems.

If you have a job posting or event you would like me to announce at the meeting, please send it along. The deadline for inclusion is 5pm the day before the meeting.

Our meeting will be held at iovation, on the 32nd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines. Underground bike parking is available in the parking garage; outdoors all around the block in the usual spots. No bikes in the office, sorry!

iovation provides us a light dinner (usually sandwiches or pizza).

Elevators open at 5:45 and building security closes access to the floor at 6:30.

See you there!

カテゴリー: postgresql

Joshua Drake: London PostgreSQL Meetup

planet postgresql - 2017-10-13(金) 01:51:22

One of the fantastic charateristics of Postgres leaders is their willingness to serve the community. Yesterday I found out that one of our former team members, Devrim Gunduz, has created a new London PostgreSQL user group and they are having their innaugural meeting in November. At the time of this writing there were 123 members in the group. That shows a great demand for Postgres content considering they haven't even had their first meetup yet! I spoke with Devrim yesterday and he has a mission to provide the London community with new content each month. It is a bold goal as running meetups that frequently can be daunting, but we believe there is enough support in the area to warrant it. Devrim has even asked me to cross the pond to present, which I plan on doing in 2018. It will be great to see a leader of Postgres provide consistent growth opportunities for the community in London.

Rock on and @amplifypostgres!

カテゴリー: postgresql