Dimitri Fontaine: Find the number of the longest continuously rising days for a stock

planet postgresql - 2018-02-07(水) 07:24:17

Today I want to react to an article that claims that Relational Algebra Is the Root of SQL Problems in which the author hand-waves the following position:

SQL becomes more a hindrance to data manipulation than an efficient tool. SQL’s greatest problem isn’t in the implementation level, but at its theory foundation. The problem can’t be solved by application optimization. Relational algebra isn’t sophisticated enough for handling the complicated data manipulation scenarios.

Then they go on to several arguments from authority to “prove” their point. My reading of the article is that SQL is very hard when you didn’t care to learn it, as most technologies are.

In this article, we’re going to look at the simple examples provided where apparently SQL makes it so much harder to find a solution compared to writing some Java or C++ code. Contrary to the original article, we go as far as to actually writing both the SQL solution and a complete Python solution, so that we can compare.

カテゴリー: postgresql

WavePHP 2018 - Call for Speakers

php.net - 2018-02-07(水) 02:20:49
カテゴリー: php

Jonathan Katz: Easy PostgreSQL 10 and pgAdmin 4 Setup with Docker

planet postgresql - 2018-02-06(火) 02:05:34

The open source software ecosystem around PostgreSQL is as robust as the database itself, but sometimes it can be hard for people new to PostgreSQL, and even some seasoned veterans, to get all of the software dependencies for their development environment setup on their computers.

At Crunchy Data, we obsess  on making things as easy as possible for people to get software up and running, regardless of the technology. While I was trying to find things to experiment with using Docker, one of our engineers gave me a recipe to setup PostgreSQL 10 and pgAdmin 4 easily and quickly.  It was so simple, I thought it would be fun to share it!

While going through this process, I was also learning how to better use Docker, so I wrote a detailed guide explaining each part of the process to help shed some light on how to work with Docker and PostgreSQL. However, if you just want to get up and running, the tl;dr guide is just below.

tl;dr: Quick setup for PostgreSQL 10 & pgAdmin 4 1. Run the following commands in your terminal:

Alternatively, you can download this script.


2.  Do the following in your web browser:
  1. Go to http://localhost:5050/
  2. Log into pgAdmin 4 with
    • Email: youremail@yourdomain.com
    • Password: yoursecurepassword
  3. Add a server using:
    • Hostname: postgres
    • Username: yourusername
    • Password: yourpassword


The Detailed guide


カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Fix for parallel scans on explain.depesz.com

planet postgresql - 2018-02-05(月) 21:30:23
On Friday, Zr40 reported on irc that explain.depesz.com doesn't show table/index names for Parallel Seq Scan nodes. Checked it and found couple of other omissions of the same kind with other Parallel* scans. Fixed (I hope) all of them in: Pg::Explain plan parsing library website templates The change is not really big, but just figured […]
カテゴリー: postgresql

Hans-Juergen Schoenig: PostgreSQL indexing: Index scan vs. Bitmap scan vs. Sequential scan (basics)

planet postgresql - 2018-02-05(月) 18:00:01

Many people keep asking about index scans in PostgreSQL. This blog is meant to be a basic introduction to the topic because many people do not seem to be aware of what the optimizer does, when a single query is processed. I decided to give a brief introduction showing, how a table can be accessed in some of the most basic cases. Let us get started with PostgreSQL indexing.

Indexes are somehow the backbone of good performance. Without proper indexing your PostgreSQL database might be in dire straits and end users might complain about slow queries and bad response times. It therefore makes sense to see, which choices PostgreSQL makes, when a single column is queried.

Preparing some demo data in PostgreSQL

To show you how things work we can use a simple table:

test=# CREATE TABLE sampletable (x numeric); CREATE TABLE

If your table is almost empty, you will never see an index scan because it might be too much overhead to consult an index – it is cheaper to just scan the table directly and throw away some rows, which don’t match your query.

So to demonstrate how an index actually works, we can add 10 million random rows to the table we just created before:

test=# INSERT INTO sampletable SELECT random() * 10000 FROM generate_series(1, 10000000); INSERT 0 10000000

Then an index is created:

test=# CREATE INDEX idx_x ON sampletable(x); CREATE INDEX

After loading so much data it can be a good idea to create optimizer statistics in case autovacuum has not caught up yet. The PostgreSQL optimizer needs this statistics to decide on whether to use an index or not:


In PostgreSQL a btree uses Lehman-Yao High-Concurrency btrees (which will be covered in more detail in a later blog).


Selecting a small subset of data in PostgreSQL

When only a small set of rows is selected, PostgreSQL can directly ask the index. In this case it can even use an “Index Only Scan” because all columns needed are actually already in the index:

test=# explain SELECT * FROM sampletable WHERE x = 42353; [...]
カテゴリー: postgresql

Markus Winand: Standard SQL Features Where PostgreSQL Beats its Competitors

planet postgresql - 2018-02-05(月) 09:00:00
Standard SQL Features Where PostgreSQL Beats its Competitors

Last Friday I've been at the PgDay before FOSDEM in Brussels and presented a selection of “Standard SQL Features Where PostgreSQL Beats its Competitors”. Slides below.

Next time I give this talk (in German and updated) is at the „Deutschsprachige PostgreSQL Konferenz“ in Berlin (registration is open). Find a complete list of my upcoming conference talks at winand.at. There you can also find slides for other talks.

Standard SQL Features Where PostgreSQL Beats its Competitors” by Markus Winand was originally published at modern SQL.

カテゴリー: postgresql

Andreas Scherbaum: How we manage the PostgreSQL FOSDEM devroom

planet postgresql - 2018-02-04(日) 23:02:00

Andreas 'ads' Scherbaum

The PostgreSQL Project was assigned a devroom at FOSDEM again this year. We got a room with approx. 200 seats, and as usual every last seat was taken for many of the talks. This requires that we manage the room very effectively and carefully, both to avoid that people walk in and out at all times and disturb the speaker, but also in order to keep the exits free at all times.


For a speaker it is quite disturbing if the door in the back opens, someone walks in, down all the way to the front, tries to find a seat, walk around to the other side, maybe leaves again, or just stays somewhere and blocks the exit. The worst case is when there is a free seat somewhere in the middle of a row, and everybody has to get up to let the new attendee in.


The FOSDEM team requires that we keep the exits free, in case the room needs to be evacuated. If people are staying on the steps along the wall, it is not possible to clear the room effectively and quickly.



Continue reading "How we manage the PostgreSQL FOSDEM devroom"
カテゴリー: postgresql

Simon Riggs: PostgreSQL Developer Meeting Brussels

planet postgresql - 2018-02-02(金) 19:38:02

The PostgreSQL Developer Meeting went well with attendees from UK, Czechia, US, Russia and Germany. The meeting is in Brussels, near the FOSDEM conference.

Andreas Seltenreich was the new face at the meeting. Andreas’ sqlsmith tool has found many PostgreSQL bugs, so we discussed extending that for the latest features in PostgreSQL 11.

We discussed all of the patches in the queue and moved a few forwards quickly. With more than 200 patches in the queue there is much work still to do and the last Commitfest hasn’t even started yet.

Bruce was able to commit a patch to improve the usability of psql with commands exit and quit. Finally the usability annoyance of people not remembering “\q” command ends in 2018!

I’ve taken on shepherding the new WAIT FOR command which should allow us to get causal consistency into PG11. That’s a subject for another blog, so more on that later.

We also looked at 64-bit TransactionIds, CommitSequenceNumber (CSNs) and Storage APIs. The meeting agreed unanimously to a big push to get Storage APIs into PG12, requiring people to publish their new storage plugins. Alexander’s patch for 64-bit TransactionIds will be split into two: part 1) TransactionId will become 64-bit in code, yet continue to be stored on disk as 32-bit value, an earlier suggestion by Heikki. part 2) develop a storage plugin that allows full 64-bit TransactionId to be stored on disk, which will then give us a migration path between current and future heap formats. No agreement to commit, but a clear path forwards for development, so we can look to commit early in the PG12 release cycle.

We discussed my MERGE patch and required next steps.

Various other discussions, ending with the usual group photo

カテゴリー: postgresql

Kaarel Moppel: pgwatch2 Feature Pack 3

planet postgresql - 2018-02-02(金) 18:00:31

Beginning of the year happens to be luckily somewhat slow at customers, so last couple of weeks I’ve had the chance to work on our Open Source PostgreSQL monitoring tool called pgwatch2 and implement some stuff that was in the queue. Changes include only a couple of fixes and a lot of new features… so hell, I’m calling it “Feature Pack 3″… as we’ve had some bigger additions in the past also. Read on for a more detailed overview on the most important new stuff.

Becoming “Enterprizy”

Focus word this time could be “Enterprise”. Meaning – firstly trying to make pgwatch2 easier to deploy for larger companies who maybe need to think about security or container orchestration and secondly also adding robustness. Security even got some special attention – now there’s a pgwatch2 version suitable for running on OpenShift, i.e. Docker process runs under an unprivileged user. But there are also quite some new dashboards (see screenshots at the end), like the long-awaited “Top N queries”, that should delight all “standard” users and also some other smaller UI improvements.

And please do let us know on Github if you’re still missing something in the tool or are having difficulties with something – I think myself I’ve lost the ability to look at the tool with beginner eyes. Thus – any feedback would be highly appreciated!

Project Github link – here.

Version 1.3 full changelog – here.

Most important changes for v1.3.0
  • “Non-root” Docker support

Suitable for example for OpenShift deployments.

  • Explicit Docker volumes added to Postgres, Grafana, InfluxDB and pgwatch2 config

Using volumes was of course also possible before, but people didn’t seem to think about it, so now it’s more explicit. Volumes helps a lot for long term Docker deployments as it makes updating to new image versions quite easy compared to dump/restore.

  • Dockerfiles for deploying “non-root” components separately

These can be used to do some advanced orchestrated setups. See the “docker” folder for details. There are also latest images available on Docker Hub,

カテゴリー: postgresql

Craig Kerstiens: How Citus rebalances your data

planet postgresql - 2018-02-02(金) 05:48:00

In both Citus Cloud 2 and in the enterprise edition of Citus 7.1 there was a pretty big update to one of our flagship features—the shard rebalancer. No, I’m not talking about our shard rebalancer visualization that reminds me of the Windows ‘95 disk defrag. (Side-node: At one point I tried to persuade my engineering team to play tetris music in the background while the shard rebalancer UI in Citus Cloud was running. The good news for all of you is that I was overwhelmingly veto'ed by my team. Whew.) The interesting new capability in the Citus database is the online nature of our shard rebalancer.

Before I dig into shard rebalancer updates, it’s probably helpful to have some baseline understanding of the Citus distributed database. Citus takes a table and distributes it into shards (aka smaller Postgres tables) across multiple physical nodes. Here’s an example:

How sharding in Postgres with Citus works

You have an events table. When you shard this events table with the Citus extension to Postgres, under the covers we’ll create events_001, events_002, events_003, etc.

Each of these shards is a standard Postgres table. Your shard count is actually independent of the number of nodes in your Citus database cluster. We then take the shards and split them up among the nodes in your cluster. Which shard lives on which node is all determined by the Citus software and recorded within metadata tables on the Citus coordinator node.

Now let’s take an example SQL query:

SELECT * FROM events WHERE user_id = 85

Citus would transform this SQL query under the covers. Note your application doesn’t have to do anything extra to distribute the SQL query across nodes onr across shards, rather, our Citus dynamic executors and router executors take care of it for you. The resulting query would look something like:

SELECT * FROM events_003 WHERE user_id = 85

Nodes, shards, tables, the transformation of SQL queries… what does any of this have to do with shard rebalancing and scaling out Postgres?

Well, when you initially create your

カテゴリー: postgresql

Craig Ringer: Using multimaster and BDR appropriately – linux.conf.au

planet postgresql - 2018-02-02(金) 02:30:39

Unless you were in Sydney last week you probably didn’t see my talk Geographically distributed multi-master replication with PostgreSQL and BDR. Luckily for you it’s on archive.org and YouTube.

If you’re interested in multi-master replication of any sort, even non-PostgreSQL-based multi-master, it should be worth taking a look. The talk could’ve been better titled “Understand Multi-Master and if it’s right for you” or “Physics is Mean”.

Comments and questions are welcome – @craig2ndq.

Huge thanks to Next Day Video for the amazing work they did on the recordings, to the conference conveners, the organizing team and volunteers.

I really enjoyed at linux.conf.au. It was a lovely community conference that has kept its relaxed culture and soul despite a growing base of business-focused professional attendees.
I would like to give a shout out to a few outstanding speakers while I’m here:

Find more talks on the @nextdayvideo twitter stream.

カテゴリー: postgresql

Mark Wong: February 2018 Meeting: Automatic For The People

planet postgresql - 2018-02-02(金) 02:30:18

Want automated high availability PostgreSQL *without* relying on someone
else’s cloud service? It’s possible today, and easier than you think;
we just need a little container orchestration pixiedust, and your DBAs
can take the weekend off.

Josh Berkus will explain the current state of HA Postgres services on
Kubernetes/OpenShift, introducing the several projects in this area, and
then going into a deep dive on Patroni/Spilo. He’ll bring you up to
date on the new “Kube-native” Patroni, and work expected in the future.
With live demos!


Josh Berkus helps manage the Kubernetes community for Red Hat. He is a
“hacker emeritus” for PostgreSQL. He lives in NE Portland with a large
black cat, a pottery studio, and a librarian.



Our meeting will still be held at iovation, but now on the 3rd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines.  For access to the 3rd floor of the plaza, please either take the lobby stairs to the third floor or take the plaza elevator (near Subway and Rabbit’s Cafe) to the third floor. There will be signs directing you to the meeting room.

カテゴリー: postgresql

PHP 7.1.14 Released

php.net - 2018-02-01(木) 23:54:13
カテゴリー: php

PHP Experience 2018

php.net - 2018-02-01(木) 21:20:29
カテゴリー: php

Andreas Scherbaum: PostgreSQL @ FOSDEM 2018

planet postgresql - 2018-02-01(木) 18:54:00

Andreas 'ads' Scherbaum

For more than 10 years, the PostgreSQL Project is present at FOSDEM. We also organize a separate PostgreSQL Day on Friday.

This blog post presents useful information about both the PGDay and the booth and devroom at FOSDEM.


Continue reading "PostgreSQL @ FOSDEM 2018"
カテゴリー: postgresql

PHP 7.2.2 Released

php.net - 2018-02-01(木) 18:12:34
カテゴリー: php

CoderCruise 2018 - Call for Speakers

php.net - 2018-02-01(木) 06:00:37
カテゴリー: php

Dimitri Fontaine: Exporting a Hierarchy in JSON: with recursive queries

planet postgresql - 2018-02-01(木) 02:00:01

In another article here, entitled on JSON and SQL, we saw in great details how to import a data set only available as a giant JSON file. Then we normalized the data set, so as to be able to write SQL and process our data. This approach is sometimes very useful and was a good way to learn some of the JSON functions provided by PostgreSQL.

In this article, we’re going to use SQL to export the data from our relational model into a JSON document. The trick that makes it complex in this example is that we have a recursive data model, with a notion of a parent row that exists in the same table as the current one. That’s a nice excuse to learn more about the SQL construct WITH RECURSIVE.

カテゴリー: postgresql

Craig Kerstiens: Postgres hidden gems

planet postgresql - 2018-02-01(木) 01:00:00

Postgres has a rich set of features, even when working everyday with it you may not discover all it has to offer. In hopes of learning some new features that I didn’t know about myself as well as seeing what small gems people found joy in I tweeted out to see what people came back from. The response was impressive, and rather than have it lost into ether of twitter I’m capturing some of the responses here along with some resources many of the features.

Whats your favorite hidden gem of Postgres that you wish more people knew about?

— Craig Kerstiens (@craigkerstiens) January 30, 2018 [...]
カテゴリー: postgresql

Simon Riggs: Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11

planet postgresql - 2018-01-31(水) 00:02:39

My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It’s worth explaining what these are rather than trying to piece together what is happening from reading commit messages.

The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.

To achieve that, we need to understand the structure of features in PostgreSQL.

Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.

Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I’ll write about partitioned indexes separately, because they are very cool. The best bit is that they don’t actually exist, its just metadata! That requires some explanations and a longer post.

So that gives us a set of features and an order in which they should be implemented:

  • Create Index on Partitioned Tables
  • Allow Unique Index on Partitioned Tables
  • Create Triggers on Partitioned Tables
  • Allow FKs on Partitioned Tables

Taken together, these features are now looking pretty good after lots of work and review. So PostgreSQL 11 is looking like we’ll be able to add referential integrity to partitioned tables.

カテゴリー: postgresql