planet postgresql

Subscribe to planet postgresql のフィード
Planet PostgreSQL
更新: 2時間 10分 前

Dimitri Fontaine: How to Learn SQL?

4時間 56分

Here we are, another SQL query to write. You wish you knew how to write that mechanically, like you would a loop in your favorite programming language. Or at least have a pretty clear idea of a skeleton to tweak until it gives the result set you expect. So instead of working on your SQL query, you google How to write a SQL query? or maybe even How to learn SQL? Right. I feel you, I’ve been there too, even if quite some time ago…

So here my article where I teach you how to learn SQL.

I want to share with you how I did it, and how I continue to do it. There’s no magic secret sauce to it though, it’s all basic work. Again, we have to learn the main concepts and how they play together, then practice simple steps, and then build from there.

カテゴリー: postgresql

Luca Ferrari: New Release of PL/Proxy

2019-09-16(月) 09:00:00

There is a new release of PL/Proxy out there!

New Release of PL/Proxy

There is a new exciting release of PL/Proxy: version 2.9 has been released a few hours ago!

This is an important release because it adds support for upcoming PostgreSQL 12. The main problem with PostgreSQL 12 has been that Oid is now a regular column, meaning that HeapTupleGetOid`` is no longer a valid macro. I first proposed a patch that was based on the C preprocessor to get rid of older PostgreSQL version.

The solution implemented by Marko Kreen is of course much more elegant and is based on defining helper functions that are pre-processed depending on the PostgreSQL version.

Enjoy proxying!

カテゴリー: postgresql

Fabien Coelho: Data Loading Performance of Postgres and TimescaleDB

2019-09-13(金) 19:46:08

Postgres is the leading feature-full independent open-source relational database, steadily increasing its popularity for the past 5 years. TimescaleDB is a clever extension to Postgres which implements time-series related features, including under the hood automatic partioning, and more.

Because he knows how I like investigate Postgres (among other things) performance, Simon Riggs (2ndQuadrant) prompted me to look at the performance of loading a lot of data into Postgres and TimescaleDB, so as to understand somehow the degraded performance reported in their TimescaleDB vs Postgres comparison. Simon provided support, including provisioning 2 AWS VMs for a few days each.


The short summary for the result-oriented enthousiast is that for the virtual hardware (AWS r5.2xl and c5.xl) and software (Pg 11.[23] and 12dev, TsDB 1.2.2 and 1.3.0) investigated, the performance of loading up to 4 billion rows in standard and partioned tables is great, with Postgres leading as it does not have the overhead of managing dynamic partitions and has a smaller storage footprint to manage. A typical loading speed figure on the c5.xl VM with 5 data per row is over 320 Krows/s for Postgres and 225 Krows/s for TimescaleDB. We are talking about bites of 100 GB ingested per hour.

The longer summary for the performance testing enthousiast is that such investigation is always much more tricky than it looks. Although you are always measuring something, what it is really is never that obvious because it depends on what actually limits the performance: the CPU spent on Postgres processes, the disk IO bandwidth or latency… or even the process of generating fake data. Moreover, performance on a VM with the underlying hardware systems shared between users tend to vary, so that it is hard to get definite and stable measures, with significant variation (about 16%) from one run to the next the norm.

Test Scenario

I basically reused the TimescaleDB scenario where many devices frequently send timespamped data points wh

カテゴリー: postgresql

Pavel Stehule: pspg 2.0.2 is out

2019-09-12(木) 13:58:00
there is just one new feature - sort is supported on all columns, not only on numeric columns.
カテゴリー: postgresql

Laurenz Albe: Tracking view dependencies in PostgreSQL

2019-09-11(水) 16:00:13


We all know that in PostgreSQL we cannot drop an object if there are view dependencies on it:

CREATE TABLE t (id integer PRIMARY KEY); CREATE VIEW v AS SELECT * FROM t; DROP TABLE t; ERROR: cannot drop table t because other objects depend on it DETAIL: view v depends on table t HINT: Use DROP ... CASCADE to drop the dependent objects too. ALTER TABLE t DROP id; ERROR: cannot drop column id of table t because other objects depend on it DETAIL: view v depends on column id of table t HINT: Use DROP ... CASCADE to drop the dependent objects too.

Some people like it because it keeps the database consistent; some people hate it because it makes schema modifications more difficult. But that’s the way it is.

In this article I want to explore the mechanics behind view dependencies and show you how to track what views depend on a certain PostgreSQL object.

Why would I need that?

Imagine you want to modify a table, e.g. change a column’s data type from integer to bigint because you realize you will need to store bigger numbers.
However, you cannot do that if there are views that use the column. You first have to drop those views, then change the column and then run all the CREATE VIEW statements to create the views again.

As the example shows, editing tables can be quite a challenge if there is a deep hierarchy of views, because you have to create the views in the correct order. You cannot create a view unless all the objects it requires are present.

Best practices with views

Before I show you how to untangle the mess, I’d like to tell you what mistakes to avoid when you are using views in your database design (excuse my switching to teacher mode; I guess holding courses has that effect on you).

Views are good for two things:

  • They allow you to have a recurring SQL query or expression in one place for easy reuse.
  • They can be used as an interface to abstract from the actual table definitions, so that you can reorganize the tables without having to modify the
カテゴリー: postgresql

Paul Ramsey: Waiting for PostGIS 3: ST_Transform() and Proj6

2019-09-11(水) 01:03:37

Where are you? Go ahead and figure out your answer, I'll wait.

No matter what your answer, whether you said "sitting in my office chair" or "500 meters south-west of city hall" or "48.43° north by 123.36° west", you expressed your location relative to something else, whether that thing was your office layout, your city, or Greenwich.

A geospatial database like PostGIS has to have able to convert between these different reference frames, known as "coordinate reference systems". The math for these conversions and the definition of the standards needed to align them all is called "geodesy", a field with sufficient depth and detail that you can make a career out of it.

Fortunately for users of PostGIS, most of the complexity of geodesy is hidden under the covers, and all you need to know is that different common coordinate reference systems are described in the spatial_ref_sys table, so making conversions between reference systems involves knowing just two numbers: the srid (spatial reference id) of your source reference system, and the srid of your target system.

カテゴリー: postgresql

Devrim GÜNDÜZ: Installing PostgreSQL 12 beta/RC on RHEL/CentOS/Fedora

2019-09-09(月) 20:36:00
PostgreSQL 12 is in beta right now, and we need everyone to test it!

We have been building repos even from daily builds, Below are the steps to install PostgreSQL 12 on RHEL 8: Continue reading "Installing PostgreSQL 12 beta/RC on RHEL/CentOS/Fedora"
カテゴリー: postgresql

Pavel Stehule: pspg 2.0.1 released

2019-09-09(月) 15:11:00
I released new version of pspg 2.0.1. I started work on pspg three years ago, and now there is almost all features what is possible with this design - the data are stored in original form (created by some sql client). The alternative of pspg can be sophisticated CSV viewers, because psql can produce a content in CSV format. Maybe (in far future) pspg 3.0 will be based on internal CSV storage with formatting on pspg side. But it is not a plan for few next years (or somebody can do it). So last month I wrote vertical cursor support (necessary for next step), and last week I finished "sort by column" feature. The sort command has sense only on numeric columns (works with numbers only - I really would not supply ORDER BY clause in SQL). With this limit is has maybe interesting feature - some size units (kB, MB, GB, TB) are supported.

Please, use it freely. And if you like this software, send me a postcard from your country - pspg is postcardware.
カテゴリー: postgresql

Jignesh Shah: Tuning DB Parameters for PostgreSQL 12 in Amazon RDS

2019-09-09(月) 13:00:00
In my last entry, we saw how to setup PostgreSQL 12 beta 3 in Amazon RDS. In that entry I purposely left out how to change database parameters as I realized that it deserves an entry (or more) by itself.

Using the AWS CLI you can create a new database parameter as follows:

$ aws rds create-db-parameter-group --db-parameter-group-name jkpg12pg \
--db-parameter-group-family postgres12 --description "My PostgreSQL 12 Parameter Group" \
--region us-east-2 --endpoint

We have just created a group and not applied the parameters to any database. Before we apply, we do to see what are the default values created you can run a command as follows to see values being set by default

$ aws rds describe-db-parameters --db-parameter-group-name jkpg12pg \
--region us-east-2 --endpoint \
--query 'Parameters[].[ParameterName,ParameterValue]' --output text

You see an output containing a list of parameters with values. Lets look at some of the values to see how to interpret them.

application_name None
autovacuum_max_workers GREATEST({DBInstanceClassMemory/64371566592},3)
autovacuum_vacuum_cost_limit GREATEST({log(DBInstanceClassMemory/21474836480)*600},200)
effective_cache_size {DBInstanceClassMemory/16384}
jit None
maintenance_work_mem GREATEST({DBInstanceClassMemory*1024/63963136},65536)
max_connections LEAST({DBInstanceClassMemory/9531392},5000)
shared_buffers {DBInstanceClassMemory/32768}
shared_preload_libraries pg_stat_statements
work_mem None
xmlbinary None
xmloption None

When you see None it basically is equivalent to null which means in such cases it is not set in postgresql.conf and the default value of the PostgreSQL version engine is set by PostgreSQL. In the above example, you will notice that jit is set to None which means it will take the default  ON value of PostgreSQL 12 and enable jit in the instance.

If you change a parameter set to a specific value based [...]
カテゴリー: postgresql

Regina Obe: PostgreSQL 11 64-bit Windows FDWs

2019-09-08(日) 12:28:00

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 11 Windows 64-bit.

To use these, copy the files into your PostgreSQL 11 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

Continue reading "PostgreSQL 11 64-bit Windows FDWs"
カテゴリー: postgresql

Dimitri Fontaine: The R in ORM

2019-09-08(日) 05:55:00
Ok, let’s face it, I like SQL. A lot. I think it’s a fine DSL given how powerful it is, and I respect its initial goal to attract non developers and try to build English sentences rather than code. Also, I understand that manually hydrating your collection of objects in your backend developement language is not the best use of your time. And that building SQL as strings makes your code ugly.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL Aggregate?

2019-09-08(日) 05:00:00
In our previous articles we had a look at What is an SQL relation? and What is a SQL JOIN?. Today, I want to show you what is an aggregate in SQL. You might have heard about Map/Reduce when Google made it famous, or maybe some years before, or maybe some years later. The general processing functions map and reduce where invented a very long time ago. The novelty from the advertising giant was in using them in a heavily distributed programming context.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL JOIN?

2019-09-08(日) 04:30:00
It took me quite some time before I could reason efficiently about SQL JOINs. And I must admit, the set theory diagrams never helped me really understand JOINs in SQL. So today, I want to help you understand JOINs in a different way, hoping to make the concept click at once for you too! As we saw in the previous article What is an SQL relation?, in SQL a relation is a collection of objects, all sharing the same definition.
カテゴリー: postgresql

Dimitri Fontaine: What is an SQL relation?

2019-09-08(日) 04:00:00
If you’re like me, understanding SQL is taking you a long time. And some efforts. It took me years to get a good mental model of how SQL queries are implemented, and then up from the lower levels, to build a mental model of how to reason in SQL. Nowadays, in most case, I can think in terms of SQL and write queries that will give me the result set I need.
カテゴリー: postgresql

Kaarel Moppel: A Primer on PostgreSQL Upgrade Methods

2019-09-07(土) 18:00:46

Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v12. So I thought it’s about time to go over some basics on upgrading to newer major versions! Database upgrades (not only Postgres) are quite a rare event for most people (at least for those running only a couple of DB-s). Since upgrades are so rare, it’s quite easy to forget how easy upgrading actually is. Yes, it is easy – so easy that I can barely wrap my head around why a lot of people still run some very old versions. Hopefully, this piece will help to convince you to upgrade faster in the future :). For the TLDR; (in table version) please scroll to the bottom of the article.

Why should I upgrade in the first place?

Some main points that come to my mind:

  • More security
    Needless to say, I think security is massively important nowadays! Due to various government regulations and the threat of being sued over data leaks, mistakes often carry a hefty price tag. Both new and modern authentication methods and SSL/TLS version support are regularly added to new PostgreSQL versions.
  • More performance without changing anything on the application level!
    Based on my gut feeling and on some previous testing, typically in total around ~5-15% of runtime improvements can be observed following an upgrade (if not IO-bound). So not too much…but on some releases (9.6 parallel query, 10.0 JIT for example) a possibly game changing 30-40% could be observed. As a rule, the upgrades only resulted in upsides – over the last 8 years, I’ve only seen a single case where the new optimizer chose a different plan so that things got slower for a particular query, and it needed to be re-written.
  • New features for developers
  • Every year, new SQL standard implementations, non-standard feature additions, functions and constructs for developers are introduced, in order to reduce the amounts of code writt
カテゴリー: postgresql

Shaun M. Thomas: PG Phriday: Postgres 12 Revs up Vacuum

2019-09-07(土) 02:00:44
Any long-time user of Postgres is likely familiar with VACUUM, the process that ensures old data tuples are identified and reused to prevent unchecked database bloat. This critical element of maintenance has slowly, but surely, undergone incremental enhancements with each subsequent release. Postgres 12 is no different in this regard. In fact, there are two […]
カテゴリー: postgresql

Muhammad Usama: Can you gain performance with Pgpool-II as a load balancer?

2019-09-06(金) 23:26:30

The world’s most valuable resource is no longer the oil or gold, but data. And at the heart of data lies the database which is expected to store, process and retrieve the desired data as quickly as possible. But having a single database server doesn’t mostly serve the purpose. A single server has its drawbacks with a huge risk of data loss and downtime.

So most data platforms use multiple replicated database servers to ensure high availability and fault tolerance of their databases. In such an environment, how can we maximize performance against the resourced being used?

One of the questions I get asked very often from Pgpool-II users is, what is the performance benefit of using load balancing of Pgpool-II? Once in a while, we get complains from users that they get better performance when they connect directly to PostgreSQL than through Pgpool-II, even when the load balancing is working fine. How true is this complain?

In this blog, I’ll benchmark the performance of the Pgpool-II load balancing feature against a direct connection to PostgreSQL.

Before we start measuring the actual performance, let’s start with what is Pgpool-II load balancer and why we should use it.

Replicated PostgreSQL servers.

Almost in every data setup, we need more than one copy of database servers to ensure minimum or zero downtime and to safeguard against data loss. For that, we use the replicated database servers.

There are many ways to create a replicated PostgreSQL cluster, which is the topic for some other blog post, but most of the replication solution exists for PostgreSQL supports one-way replication. That means one master PostgreSQL server feeding data to one or multiple standby servers. and in this setup, only maser server is capable of handling the write queries while standby servers sit idle waiting for the moment when the master goes down and one of them gets promoted to become a new master.

This setup is good enough to handle server failures and to provide the high availability but it is not

カテゴリー: postgresql

Jonathan Katz: Creating a PostgreSQL Cluster with Kubernetes CRDs

2019-09-05(木) 02:08:00

The PostreSQL Operator provides users with a few different methods to perform PostgreSQL cluster operations, via:

  • a REST API
  • pgo, PostgreSQL Operator Command Line Interface (CLI)
  • Directly interfacing with Kubernetes, including various APIs and custom resource definitions (CRDs).

While the REST API and pgo provide many conveniences, there are use cases that require the third method, i.e. interacting directly with Kubernetes to create the desired PostgreSQL cluster setup.

To demonstrate this, let's look at how we can manipulate the CRD responsible for managing PostgreSQL clusters to create a new cluster managed by the PostgreSQL Operator.

カテゴリー: postgresql

Dimitri Fontaine: Postgres Connection Strings and psql

2019-09-04(水) 18:38:02

PostgreSQL connection strings embedded in your application can take two different forms: the key-value notation or the postgresql:// URI scheme. When it comes to using psql though, another form of connection string is introduced, with command line options -h -p -U and environment variable support.

In this short article you will learn that you can use either of the three different forms in psql and thus easily copy & paste you application connection string right at the console to test it!

カテゴリー: postgresql

Granthana Biswas: Remote Backup and Restore with pgBackRest

2019-09-04(水) 15:30:16

In my previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup,  only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended (or rather does not serve the purpose) to perform the backup on the same server where the database is running.

So: let’s get familiar with how remote backup servers are set up with pgBackRest, and how a full and incremental backup is performed from the backup server and restored on the database server.

We need two servers. Let’s call ours:

  1. pgbackup
  2. db1


Installing pgbackrest:

We need to install pgBackRest on the database and the backup server. Make sure you install the same version on both.


For the database server, please follow the installation steps from my previous post. The steps are slightly different for the backup server, since it is a better practice to create a separate user to own the pgBackRest repository.


Create a pgbackrest user on the backup server


sudo adduser --disabled-password --gecos "" pgbackrest


Install required Perl package and pgBackRest from a package or manually on pgbackup as below


sudo apt-get install libdbd-pg-perl sudo scp BUILD_HOST:/root/pgbackrest-release-2.14/src/pgbackrest /usr/bin/ sudo chmod 755 /usr/bin/pgbackrest


Create pgBackRest configuration files, directories and repository on pgbackup


sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown pgbackrest:pgbackrest /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf sudo mkdir -p /var/lib/pgbackrest sudo chmod 750 /var/lib/pgbackrest sudo chown pgbackrest:pgbackrest /var/lib/pgbackrest


Now we are ready to proceed with enabling c

カテゴリー: postgresql