planet postgresql

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

Ernst-Georg Schmid: Not all CASTs are created equal?

2019-05-03(金) 01:32:00
Can somebody explain this?

PostgreSQL 11.2.

The documentation says:

"A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:

CAST ( expression AS type )


The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage."

But when I test the lower limits of PostgreSQL's integer types, strange things happen.

select cast(-9223372036854775808 as bigint);
select cast(-2147483648 as integer);
select cast(-32768 as smallint);

All OK.

select -9223372036854775808::bigint;
select -2147483648::integer;
select -32768::smallint;

All fail with SQL Error [22003]: ERROR: out of range


select -9223372036854775807::bigint;
select -2147483647::integer;
select -32767::smallint;

All OK.

カテゴリー: postgresql

Pavel Golub: 1-to-1 relationship in PostgreSQL for real

2019-05-02(木) 17:00:32

Years ago

Years ago I wrote the post describing how to implement 1-to-1 relationship in PostgreSQL. The trick was simple and obvious:


You put a unique constraint on a referenced column and you’re fine. But then one of the readers noticed, that this is the 1-to-(0..1) relationship, not a true 1-to-1. And he was absolutely correct.

Keep it simple stupid!

A lot of time is gone and now we can do this trick much simpler using modern features or PostgreSQL. Let’s check

BEGIN; CREATE TABLE uProfiles ( uid int8 PRIMARY KEY, payload jsonb NOT NULL ); CREATE TABLE Users ( uid int8 PRIMARY KEY, uname text NOT NULL, FOREIGN KEY (uid) REFERENCES uProfiles (uid) ); ALTER TABLE uProfiles ADD FOREIGN KEY (uid) REFERENCES Users (uid); INSERT INTO Users VALUES (1, 'Pavlo Golub'); INSERT INTO uProfiles VALUES (1, '{}'); COMMIT;

Things are obvious. We create two tables and reference each other using the same columns in both ways.
Moreover, in such model both our foreign keys are automatically indexed!
Seems legit, but executing this script will produce the error:

SQL Error [23503]: ERROR: insert or update on table "users" violates foreign key constraint "users_uid_fkey" Detail: Key (uid)=(1) is not present in table "uprofiles".

Oops. And that was the pitfall preventing the easy solutions years ago during my first post.

What about now?

But now we have DEFERRABLE constraints:

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXC

カテゴリー: postgresql

Pavel Stehule: pspg on Solaris

2019-05-02(木) 16:54:00
I fixed some issues and pspg can be used on Solaris too. I found some issues on Solaris side on utf8 support - but it is related just for subset of chars. Due this issues, don't use psql unicode borders.
カテゴリー: postgresql

Ibrar Ahmed: Benchmark ClickHouse Database and clickhousedb_fdw

2019-05-02(木) 01:37:29

In this research,  I wanted to see what kind of performance improvements could be gained by using a ClickHouse data source rather than PostgreSQL. Assuming that I would see performance advantages with using ClickHouse, would those advantages be retained if I access ClickHouse from within postgres using a foreign data wrapper (FDW)? The FDW in question is clickhousedb_fdw – an open source project from Percona!

The database environments under scrutiny are PostgreSQL v11, clickhousedb_fdw and a ClickHouse database. Ultimately, from within PostgreSQL v11, we are going to issue various SQL queries routed through our clickhousedb_fdw to the ClickHouse database. Then we’ll see how the FDW performance compares with those same queries executed in native PostgreSQL and native ClickHouse.

Clickhouse Database

ClickHouse is an open source column based database management system which can achieve performance of between 100 and 1000 times faster than traditional database approaches, capable of processing more than a billion rows in less than a second.


clickhousedb_fdw, a ClickHouse database foreign data wrapper, or FDW, is an open source project from Percona. Here’s a link for the GitHub project repository:

I wrote a blog in March which tells you more about our FDW:

As you’ll see, this provides for an FDW for ClickHouse that allows you to SELECT from, and INSERT INTO, a ClickHouse database from within a PostgreSQL v11 server.

The FDW supports advanced features such as aggregate pushdown and joins pushdown. These significantly improve performance by utilizing the remote server’s resources for these resource intensive operations.

Benchmark environment
  • Supermicro server:
    • Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
    • 2 sockets / 28 cores / 56 threads
    • Memory: 256GB of RAM
    • Storage: Samsung  SM863 1.9TB Enterprise SSD
    • Filesystem: ext4/xfs
  • OS: Linux
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled.

2019-05-01(水) 10:02:06
On 4th of April 2019, Robert Haas committed patch: Allow VACUUM to be run with index cleanup disabled.     This commit adds a new reloption, vacuum_index_cleanup, which controls whether index cleanup is performed for a particular relation by default. It also adds a new option to the VACUUM command, INDEX_CLEANUP, which can be used … Continue reading "Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled."
カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

2019-05-01(水) 03:22:52
On 3rd of April 2019, Tomas Vondra committed patch: Add SETTINGS option to EXPLAIN, to print modified settings.     Query planning is affected by a number of configuration options, and it may be crucial to know which of those options were set to non-default values. With this patch you can say EXPLAIN (SETTINGS ON) … Continue reading "Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings."
カテゴリー: postgresql

Eric Hanson: "Aquameta Revisited" on FLOSS Weekly

2019-04-30(火) 20:50:08

Eric talks about Aquameta 0.2 and the advantages of migrating the web stack into PostgreSQL on This Week in Tech's FLOSS Weekly.

カテゴリー: postgresql

Markus Winand: A Close Look at the Index Include Clause

2019-04-30(火) 09:00:00

Some database—namely Microsoft SQL Server, IBM Db2, and also PostgreSQL since release 11—offer an include clause in the create index statement. The introduction of this feature to PostgreSQL is the trigger for this long overdue explanation of the include clause.

Before going into the details, let’s start with a short recap on how (non-clustered) B-tree indexes work and what the all-mighty index-only scan is.

Recap: B-tree Indexes

To understand the include clause, you must first understand that using an index affects up to three layers of data structures:

  • The B-tree

  • The doubly linked list at the leaf node level of the B-tree

  • The table

The first two structures together form an index so they could be combined into a single item, i.e. the “B-tree index”. I prefer to keep them separate as they serve different needs and have a different impact on performance. Moreover, explaining the include clause requires making this distinction.

In the general case, the database software starts traversing the B-tree to find the first matching entry at the leaf node level (1). It then follows the doubly linked list until it has found all matching entries (2) and finally it fetches each of those matching entries from the table (3). Actually, the last two steps can be interleaved, but that is not relevant for understanding the general concept.

The following formulas give you a rough idea of how many read operations each of these steps needs. The sum of these three components is the total effort of an index access.0

  • The B-tree: log100(<rows in table>), often less than 5

  • The doubly linked list: <rows read from index> / 100

  • The table: <rows read from table>1

When loading a few rows, the B-tree makes the greatest contribution to the overall effort. As soon as you need to fetch just a handful of rows from the table, this step takes the lead. In either case—few or many rows—the doubly linked list is usually a minor factor because it stores rows with similar values next to each other so that a single read operation can fetch

カテゴリー: postgresql