Dave Conlin: Multi-column indexes

planet postgresql - 2019-05-20(月) 21:08:11

This post covers standard Postgres B-tree indexes. To make it easier to read, I’ve just referred to “indexes” throughout, but it’s worth bearing in mind that the other Postgres index types behave very differently.

At pgMustard, we’ve been looking into multi-column indexes recently: particularly what problems they can cause and what advice we can offer to fix those problems. So I thought that now would be a good time to talk about their disadvantages, how to tell if they’re letting you down, and the alternatives.

In my introduction to indexing, I talked about multi-column indexes and how useful they can be if you have an expensive sort that you want to prepare in advance. They can also be used to speed up retrieving rows that match a filter or constraint, but unfortunately it’s not as simple as “I need to query on columns a, b and c, so I’ll make a multi-column index on a, b and c”.

Photo by Erol Ahmed When multi-column indexes fall flat

Suppose I have a table full of information on fiction books and I want to query it by author. Then you can imagine my index is a filing cabinet full of index cards, organised in alphabetical order by author, with dividers that make it easy to jump to any point in the catalogue. So if I want to find all the John le Carré books, I look him up under “C” and find a list of excellent spy novels. Great.

Now suppose I want to add another column to my index — publication date. If I add it as a second column after the author, then this is similar to sorting all the cards in my filing cabinet, first by author, and then by publication date within each author.

This works fine if I want to know, for example, all the Ursula K. le Guin books published after 1970. Turn to “Guin, Ursula K. le”, and then within that section, take all the cards that are after the first of January 1970. Sweet.

But if I want to know which books in my library were published before 1700? I have to go through every single author and look up which of their books are pre-1700. The index won’t save us much time, in fact

カテゴリー: postgresql

How to build a Docker development setup for PHP Projects [Tutorial Part 3]

planet PHP - 2019-05-20(月) 17:07:00

In the third part of this tutorial series on developing PHP on Docker we'll lay the fundamentals to build a complete development infrastructure and explain how to "structure" the Docker setup as part of a PHP project. Structure as in

  • folder structure ("what to put where")
  • Dockerfile templates
  • solving common problems (file permissions, runtime configuration, ...)

We will also create a minimal container setup consisting of php-fpm, nginx and a workspace container that we refactor from the previous parts of this tutorial.

Published parts of the Docker PHP Tutorial

All code samples are publicly available in my Docker PHP Tutorial repository on github. The branch for this tutorial is part_3_structuring-the-docker-setup-for-php-projects.

If you want to follow along, please subscribe to the RSS feed or via email to get automatic notifications when the next part comes out :)


Shout out to Nils Meyer for giving the final "[...] nichts offensichtlich falsch" ("nothing obviously wrong") :)

Table of contents

Truncated by Planet PHP, read more at the original (another 58661 bytes)

カテゴリー: php

pgCMH - Columbus, OH: Users, Roles, and Permissions

planet postgresql - 2019-05-20(月) 13:00:00

The May meeting will be held at 18:00 EST on Tues, the 28th. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.


Seal Software’s very own Arlette will be presenting this month. She’s going to tell us all about PostgreSQL users, role and groups. The talk will discuss best practices around their user and GRANTing permissions as well as some lessons learned around their use. Arlette will even show us a trick using roles to cut down on having to manage your pg_hba.conf file.


CoverMyMeds has graciously agreed to validate your parking if you use their garage so please park there:

You can safely ignore any sign saying to not park in the garage as long as it’s after 17:30 when you arrive.

Park in any space that is not marked ‘24 hour reserved’.

Once parked, take the elevator/stairs to the 3rd floor to reach the Miranova lobby. Once in the lobby, the elevator bank is in the back (West side) of the building. Take a left and walk down the hall until you see the elevator bank on your right. Grab an elevator up to the 11th floor. (If the elevator won’t let you pick the 11th floor, contact Doug or CJ (info below)). Once you exit the elevator, look to your left and right; one side will have visible cubicles, the other won’t. Head to the side without cubicles. You’re now in the community space:

The kitchen is to your right (grab yourself a drink) and the meeting will be held to your left. Walk down the room towards the stage.

If you have any issues or questions with parking or the elevators, feel free to text/call Doug at +1.614.316.5079 or CJ at +1.740.407.7043

カテゴリー: postgresql

Pavel Stehule: how to don't emulate schema (global) variables

planet postgresql - 2019-05-19(日) 14:00:00
Postgres has not global variables for PLpgSQL. This week I worked for one customer who had emulation of global variables based on temporary tables.

Depends on usage, this implementation can be very slow - more due often using temporary tables, a pg_class, pg_attribute tables can bloat, and these functions are slower and slower. So don't use it. There are alternative, much better solution, based on custom configuration variables:

Original code:

CREATE OR REPLACE FUNCTION public.setvar(_varname text, _value text)
LANGUAGE plpgsql
1 AS $function$
2 begin
3 create temporary table if not exists variables(varname text unique, value text);
4 insert into variables (varname, value) values(_varname, _value) on conflict (varname) do update set value = _value;
5 end;
6 $function$

CREATE OR REPLACE FUNCTION public.getvar(_varname text)
LANGUAGE plpgsql
1 AS $function$
2 begin
3 return (select value from variables where varname = _varname);
4 exception when others then
5 return null;
6 end;
7 $function$

There are more slow things: a) creating of table (first time in session can be slow (or very slow if system catalog is bloated), b) handling a exception (inside a safe point is created and dropped every time).

Better code:

CREATE OR REPLACE FUNCTION public.setvar2(_varname text, _value text)
LANGUAGE plpgsql
1 AS $function$
2 begin
3 perform set_config('variables.' || _varname, _value, false);
4 end
5 $function$

CREATE OR REPLACE FUNCTION public.getvar2(_varname text)
LANGUAGE plpgsql
1 AS $function$
2 begin
3 return current_setting('variables.' || _varname, true);
4 end;
5 $function$

The differences can be measured by few synthetic benchmarks (attention - because it is tested on fresh postgresql instance, the result is best case for temporary tables solution, realit[...]
カテゴリー: postgresql

Paul Ramsey: Keynote @ FOSS4G NA 2019

planet postgresql - 2019-05-18(土) 01:00:00

Last month I was invited to give a keynote talk at FOSS4G North America in San Diego. I have been speaking about open source economics at FOSS4G conferences more-or-less every two years, since 2009, and I look forward to revisting the topic regularly: the topic is every-changing, just like the technology.

In 2009, the central pivot of thought about open source in the economy was professional open source firms in the Red Hat model. Since they we’ve taken a ride through a VC-backed “open core” bubble and are now grappling with an environment where the major cloud platforms are absorbing most of the value of open source while contributing back proportionally quite little.

What will the next two years hold? I dunno! But I have increasingly little faith that a good answer will emerge organically via market forces.

If you liked the video and want to use the materials, the slides are available here under CC BY.

カテゴリー: postgresql

Newcrafts 2019 Day 1

planet PHP - 2019-05-17(金) 23:15:00

This week I attended and spoke at the Newcrafts conference in Paris. The following is a mix of notes and personal observations I wanted to share, centered around some of the talks I saw there.

Romeu Romera: Bourdieu's Social theory and our work in tech

I had never attended a talk by Romeu before. I really enjoyed this one. Somehow I already knew that he uses a mindmap to support his talk. I thought he would use an existing mind map to navigate through the talk, but it turned out he was creating one during the talk. For me personally, a slide deck helps to keep track of the story, and it helps me remember all the different topics I need to talk about. Not so much for Romeu, who knew exactly what he was going to talk about, and didn't seem to forget to mention any important part, or make important connections.

The topic is one that seems close to his heart. Still, he called himself "not an expert", saying that this talk was an experiment. It turned out that he was hinting at the fact that the subject matter is vast, and he could only cover some parts of it during the talk. Still, the things he covered, maybe simplified a lot, were very impactful, and very interesting. I'd definitely recommend watching this talk once it becomes available online.

More than with any other talk, I think you can't help it but apply the ideas mentioned to your own situation when you listen to Romeu. He covered three parts of Bourdieu's social theory. The first part is about icons of power. The way you look and behave shows how much power you have. This modified appearance is called Symbolic Violence; an act of violance people in positions of power put onto themselves. I think in the context of conferences, being a public speaker is a great example of violence the speaker puts onto themselves. Personally, I often find it a painful experience (although I'll keep doing it as long as there's a way to help people do a better job in any way).

The second part of the theory has to do with Cultural Capital. Everyone has their own amount of cultural capital. Take for example the people in your team. Some will have more experience than others, a deeper understanding of design, architecture, etc. People with less cultural capital will be seen as lesser people. Having more cultural capital can also be an issue with speakers at a conference, where they will be automatically taken to be experts, to be better humans (or at least, better designers, programmers, etc.). They will be perceived to be more powerful, and more right. This isn't fair to either party; speakers, and attendees alike, but it's how the game gets played.

Differences in the amounts of cultural capital between people will result in Dissociation. The first thing that might happen is that you see a person with less cultural capital as someone you can ignore, not take seriously, etc. The other thing that could happen is that you'll feel that a person with more cultural capital than you is unreachable, and that they wouldn't be interested in even talking to you. Personally I can relate to this problem a lot. When I'm at a conference, it totally depends how I feel: if I feel like I have a sufficient amount of cultural capital, I'll be perfectly fine, and can speak freely with anyone in the room. If I feel that I lack cultural capital, I'm very shy, and generally tend to avoid other speakers, as I will quickly feel like an imposter, noticing a mismatch between the expected and the actual amount of cultural capital.

The third part of the theory is about Hexis, which means something like to what level you feel like you belong somewhere. Hexis could be considered "high" if you never doubt that you should be where you are now. It's low if you have doubts about your presence. Being self-condident is much appreciated, showing doubt is a signal of fragility, and it will look punishable. The immediate association I had, was how code reviews show a difference in seniority (which comes with self-confidence, never a doubt that you're in the right place). The senior developer is likely to provide a lot of nitpicking comments to the one who is more junior. The junior developer will likely have a hard time providing feedback to the senior. The situation gets worse if the senior is considered to be the boss/manager/team lead as well.

And this is where Romeu brings the discussion back to software development. The problem with some agile practices is that they assume equality in the workplace. Pair programming is easy if none of the programmers are the (perceived) boss. Retrospectives are easy if the (perceived) boss isn't there.

If you have enough cultural capital, and symbolic violence, you can ignore the problem. But if you have not, you can't. The problem is real. And of course, it's better if nobody would ignore the pro

Truncated by Planet PHP, read more at the original (another 4122 bytes)

カテゴリー: php

Command Pattern for Legacy Code Refactoring

planet PHP - 2019-05-17(金) 22:49:00

Just recently I run into an issue on one of my projects which I gracefully solved using the Command pattern. The project has a lot of legacy code, and to avoid the pain of major refactoring we do it gradually, piece by piece.

Continue reading
カテゴリー: php

Julien Rouhaud: PoWA 4 brings a remote mode, available in beta!

planet postgresql - 2019-05-17(金) 20:04:17

PoWA 4 is available in beta.

New remote mode!

The new remote mode is the biggest feature introduced in PoWA 4, though there have been other improvements.

I’ll describe here what this new mode implies and what changed in the UI.

If you’re interested in more details about the rest of the changes in PoWA 4, I’ll soon publish other articles for that.

For the most hurried people, feel free to directly go on the v4 demo of PoWA, kindly hosted by Adrien Nayrat. No credential needed, just click on “Login”.

Why is a remote mode important

This feature has probably been the most frequently asked since PoWA was first released, back in 2014. And that was asked for good reasons, as a local mode have some drawbacks.

First, let’s see how was the architecture up to PoWA 3. Assuming an instance with 2 databases (db1 and db2), plus one database dedicated for PoWA. This dedicated database contains both the stat extension required to get the live performance data and to store them.

A custom background worker is started by PoWA, which is responsible for taking snapshots and storing them in the dediacted powa database regularly. Then, using powa-web, you can see the activity of any of the local databases querying the stored data on the dedicated database, and possibly connect to one of the other local database when complete data are needed, for instance when using the index suggestion tool.

With version 4, the architecture with a remote setup change quite a lot:

You can see the a dedicated powa database is still required, but only for the stat extensions. Data are now stored on a different instance. Then, the background worker is replaced by a new collector daemon, which reads the performance data from the remote servers, and store them on the dedicated repository server. Powa-web will then be able to display the activity connecting on the repository server, and also on the remote server when complete data are needed.

In short, with the new remote mode introduced in this version 4:

  • a PostgreSQL restart is not required anymore to
カテゴリー: postgresql

Interview with Phil Jackson

planet PHP - 2019-05-17(金) 19:06:00
カテゴリー: php

Laurenz Albe: Abusing SECURITY DEFINER functions

planet postgresql - 2019-05-17(金) 17:00:44
© xkcd.com under the Creative Commons License 2.5


Functions defined as SECURITY DEFINER are a powerful, but dangerous tool in PostgreSQL.

The documentation warns of the dangers:

Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects (e.g., tables, functions, and operators) that mask objects intended to be used by the function.

This article describes such an attack, in the hope to alert people that this is no idle warning.

What is SECURITY DEFINER good for?

By default, PostgreSQL functions are defined as SECURITY INVOKER. That means that they are executed with the User ID and security context of the user that calls them. SQL statements executed by such a function run with the same permissions as if the user had executed them directly.

A SECURITY DEFINER function will run with the User ID and security context of the function owner.

This can be used to allow a low privileged user to execute an operation that requires high privileges in a controlled fashion: you define a SECURITY DEFINER function owned by a privileged user that executes the operation. The function restricts the operation in the desired way.

For example, you can allow a user to use COPY TO, but only to a certain directory. The function has to be owned by a superuser (or, from v11 on, by a user with the pg_write_server_files role).

What is the danger?

Of course such functions have to be written very carefully to avoid software errors that could be abused.

But even if the code is well-written, there is a danger: unqualified access to database objects from the function (that is, accessing objects without explicitly specifying the schema) can affect other objects than the author of the function intended. This is because the configuration parameter search_path can be modified in a database session. This para

カテゴリー: postgresql

PGConf.ASIA 2019

www.postgresql.jp news - 2019-05-16(木) 16:35:49
PGConf.ASIA 2019 harukat 2019/05/16 (木) - 16:35
カテゴリー: postgresql

Remove unused "use" imports in PHP

planet PHP - 2019-05-16(木) 04:09:00

To clean up unused namespace imports (use statements) in many PHP files at once, php-cs-fixer (version 2) is of great help:

$ php php-cs-fixer.phar fix --rules=no_unused_imports src/
カテゴリー: php

Viorel Tabara: Benchmarking Managed PostgreSQL Cloud Solutions - Part Four: Microsoft Azure

planet postgresql - 2019-05-15(水) 23:12:26

This is the 4th and last part in the series Benchmarking Managed PostgreSQLCloud Solutions. At the time of this writing, Microsoft Azure PostgreSQL was at version 10.7, newer than the two contenders: Amazon Aurora PostgreSQL at version 10.6and Google Cloud SQL for PostgreSQL at version 9.6.

Microsoft decided to run Azure PostgreSQLon Windows:

postgres=> select version(); version ------------------------------------------------------------ PostgreSQL 10.7, compiled by Visual C++ build 1800, 64-bit (1 row)

For this particular test that didn’t work out too well, and I will hazard to guess that Microsoft is well aware of the limitations, the reason why under the PostgreSQL umbrella they also offer a preview version of Citus Data version of PostgreSQL. The approach looks similar to AWS PostgreSQL flavors, RDS and respectively Aurora.

As a side note, while setting up my Azure account, I was taken aback by the lack of 2FA/MFA (Two-Factor/Multi-Factor) authentication that I took as granted with Amazon’s AWS Virtual MFA and Google’s 2-step Verification. Microsoft offers MFA only to enterprise customers subscribed to Active Directory or Office 365. Since Citus Cloud enforces 2FA for production database, perhaps Microsoft isn’t that far from implementing it in Azure.


There are no results for Azure. On the 8-core database instance, identical in the number of cores to those used on AWS and G Cloud, the tests failed to complete due to database errors. On a 16-core instance, pgbench did complete, and sysbench got as far as creating the first 3 tables at which point I interrupted the process. While I was willing to spend a reasonable amount of effort, time, and money on performing the tests, and documenting the errors and their causes, the goal of this exercise was running the benchmark, therefore I never considered pursuing any advanced troubleshooting, or contacting Azure support, nor did I finish the sysbench test on the 16-core database.

Cloud Instances Client

The Azure client instance the clo

カテゴリー: postgresql

My Bref Makefile

planet PHP - 2019-05-15(水) 19:02:00

In order to use Bref efficiently, I've developed a Makefile so that I don't have to remember all the various commands required. In particular, looking up the correct parameters to sam package & sam deploy is a pain and it's much easier to type make deploy and it all works as I expect.

It looks like this:


# vim: noexpandtab tabstop=4 filetype=make .PHONY: list invoke invoke-local deploy outputs lastlog clean clean-all setup REGION := eu-west-2 PROJECT_NAME := hello-world UNIQUE_KEY := 1557903576 BUCKET_NAME := $(PROJECT_NAME)-$(UNIQUE_KEY)-brefapp STACK_NAME := $(PROJECT_NAME)-$(UNIQUE_KEY)-brefapp # default function to invoke. To override: make invoke FUNCTION=foo FUNCTION ?= my-function list: @$(MAKE) -pRrq -f $(lastword $(MAKEFILE_LIST)) : 2>/dev/null | awk -v RS= -F: '/^# File/,/^# Finished Make data base/ {if ($$1 !~ "^[#.]") {print $$1}}' | sort | egrep -v -e '^[^[:alnum:]]' -e '^$@$$' invoke: vendor/bin/bref --region=$(REGION) invoke $(FUNCTION) invoke-local: sam local invoke $(FUNCTION) --no-event deploy: sam package \ --region $(REGION) \ --template-file template.yaml \ --output-template-file .stack-template.yaml \ --s3-bucket $(BUCKET_NAME) -sam deploy \ --region $(REGION) \ --template-file .stack-template.yaml \ --stack-name $(STACK_NAME) \ --capabilities CAPABILITY_IAM vendor/bin/bref deployment --region $(REGION) $(STACK_NAME) outputs: aws --region $(REGION) cloudformation describe-stacks --stack-name $(STACK_NAME) | jq '.Stacks[0]["Outputs"]' lastlog: sam logs --region $(REGION) --name $(FUNCTION) geterror: vendor/bin/bref deployment --region $(REGION) $(STACK_NAME) clean: aws --region $(REGION) cloudformation delete-stack --stack-name $(STACK_NAME) clean-all: clean aws --region $(REGION) s3 rb s3://$(BUCKET_NAME) --force setup: aws --region $(REGION) s3 mb s3://$(BUCKET_NAME)

There's three variables that I need to set at the top:

  • REGION – The AWS region. This has to match the Bref layer used in template.yaml.
  • PROJECT_NAME – The name of the project. This is used as part of the S3 bucket and CloudFormation stack names
  • UNIQUE_KEY – A random string to ensure uniqueness for bucket and stack names. I tend to use the current time to the ms, but any string.

I've included a full-cycle set of targets so make setup will create the initial S3 bucket that's required for the project and then make deploy is used to deploy my project.

If I want to start again, make clean will remove the CloudFormation stack and make clean-all will remove the stack and the bucket.

I've also included a few utility targets:

  • make invoke FUNCTION=foo invokes the function foo on AWS.
  • make invoke-local FUNCTION=foo invokes the function foo on sam-local.
  • make outputs displays the outputs of the CloudFormation stack. This is useful for picking up the API Gateway URL for instance, if you set it up in your template.yaml.
  • make lastlog FUNCTION=foo displays the logs for the last invocation of the function foo.
Parameters for template.yaml

I pass the PROJECT_NAME and UNIQUE_KEY through to the template as the parameters ProjectName and UniqueKey respectively. These are then set in the Parameters section of the template:


Parameters: ProjectName: Type: String UniqueKey: Type: String

I then use them in the template when I need uniqueness, such as when creating an S3 bucket:


Resources: ImagesBucket: Type: AWS::S3::Bucket Properties: BucketName: !Join [ '-', [!Ref "ProjectName", !Ref "UniqueKey", "files" ] ]

Which creates a bucket named "hello-world-1557903576-files" which nicely complements "hello-world-1557903576-brefapp".

カテゴリー: php

Sorting select fields in EasyAdminBundle

planet PHP - 2019-05-15(水) 18:04:00

I'm currently working on an application using Symfony and their EasyAdminBundle. The experience has been great overall, although there are lots of details and specific usecases that are hard to figure out.

For instance when using relations in your entities and creating the related forms. Select fields for related entities are by default sorted by the key (usually the ID of the related entity), however you'd usually want to sort it alphabetically by the name of the entity. My initial thought was to use the @OrderBy annotation, however that only works for the actual OneToMany relations on the other side of the relation, not on the selectbox for the ManyToOne side of the relation. So that was quickly discarded.

Next up I found that you can do it in Symfony by specifying a query_builder parameter to your form configuration. The downside here is that by default, EasyAdminBundle works with a yaml configuration for your form so that makes it a lot harder to do this. I could do this in an extended AdminController, but that would mess with my form field order.

Eventually, however, I found this comment on Github that gave me the solution. Instead of specifying an anonymous function, you can also specify a static method to be called to fetch the values. And so, my solution was now easily implemented.

In my YAML file, I could now specify the query_builder parameter:

- { property: supplier, label: 'Leverancier', type_options: { 'query_builder': 'App\Repository\SupplierRepository::getSuppliersForSelect' } }

In said repository, I added the specified static method:

static public function getSuppliersForSelect(EntityRepository $entityRepository) { return $entityRepository ->createQueryBuilder('s') ->orderBy('s.name', 'ASC'); }

and now my select field has a nicely alphabetically sorted list of suppliers.

カテゴリー: php

Mark Wong: May 16 2019 Meetup

planet postgresql - 2019-05-15(水) 07:13:13

When: 6-8pm Thursday May 16, 2019
Where: PSU Business Accelerator (Parking is open after 5pm.)
Who: Mark Wong
What: Disaster Recovery and High Availability Planning

Learn about the considerations in planning for disaster recovery in order to maintain business continuity. There are solutions available that can help you achieve your backup and recovery objectives by taking advantage of PostgreSQL features, such as point-in-time recovery, and help implement retention policies.

Also learn how to take advantage of PostgreSQL’s replication features to keep your database highly-available, and the basic cluster architectures to consider to keep a PostgreSQL cluster up and running.

Mark works at 2ndQuadrant as a consultant for English Speaking Territories, based out of Oregon. He is a Contributor to PostgreSQL, co-organizer of the Portland PostgreSQL User Group, and serves as a Director and Treasurer for the United States PostgreSQL Association.

カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: How to play with upcoming, unreleased, PostgreSQL?

planet postgresql - 2019-05-15(水) 07:11:45
Lately reader of my blog asked about some detail, and then in next comment said that he doesn't have PostgreSQL 12 (which is currently developed version of Pg) available. Well, I answered the original question, but I figured that if someone is reading my Waiting for … series, then it would make sense that such … Continue reading "How to play with upcoming, unreleased, PostgreSQL?"
カテゴリー: postgresql

Paul Ramsey: Quick and Dirty Address Matching with LibPostal

planet postgresql - 2019-05-15(水) 04:43:37

Most businesses have databases of previous customers, and data analysts will frequently be asked to join arbitrary data to the customer tables in order to provide analysis.

カテゴリー: postgresql

PSR-14: Example - layered caching

planet PHP - 2019-05-15(水) 04:18:00
PSR-14: Example - layered caching

So far we've looked at a number of complete, practical examples of using PSR-14 Events in various ways, both conventional and unconventional. In our final (probably) installment, I want to offer a highly unconventional but still practical use of PSR-14 that really shows off just how flexible Events can be: Layered caching.

"But wait, isn't caching the realm of PSR-6 and PSR-16?" Yes. Yes it is. But neither of those offer a built-in way to compose multiple cache backends together. It's certainly possible, but doing so is left as an exercise for the implementer. Let's use PSR-14 to get some exercise.

Continue reading this post on SteemIt.

Larry 14 May 2019 - 2:18pm
カテゴリー: php