Quinn Weaver: It's just an expression

planet postgresql - 2018-10-30(火) 15:23:00
PostgreSQL has lots of great features for text search.

In particular, there are a bunch of ways to do case-insensitive searches for text:
  • There's standard SQL ILIKE… but than can be expensive — especially if you put %'s at both start and end — and it's overkill if you want an exact string match.
  • The same goes for case-insensitive regexp matching: overkill for simple case-insensitive matches. It does work with indexes, though!
  • Then there's the citext extension, which is pretty much the perfect answer. It lets you use indexes and still get case-insensitive matching, which is really cool. It Just Works.
OK, but what if you didn't have the foresight to use citext? And what if you don't want to go through the pain of changing the data type of that column? In a case like this, an expression index can be really handy.

Without an index, a case-insensitive match like this can be quite expensive indeed:
sandbox# select addy from email_addresses where lower(addy) = '';

                                               QUERY PLAN
 Seq Scan on email_addresses  (cost=0.00..1.04 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=1)
   Filter: (lower(addy) = ''::text)
   Rows Removed by Filter: 3
 Planning time: 0.087 ms
 Execution time: 0.051 ms
(5 rows)

(That's a sequential scan of the entire table, lowercasing the addy column of each row before comparing it to the desired address.)

And a regular index on email_addresses(addy) won't help, because the lower() operation forces a sequential scan.
But an expression index will do the trick:

sandbox# create index email_addresses__lower__addy on email_addresses (lower(addy));
sandbox# explain analyze select addy from email_addresses where lower(addy) = '';
                                                                  QUERY PLAN
カテゴリー: postgresql

damien clochard: Introducing PostgreSQL Anonymizer

planet postgresql - 2018-10-29(月) 19:17:36

I’ve just released today an extension called PostgreSQL Anonymizer that will mask or replace personally identifiable information (PII) or commercially sensitive data from a PostgreSQL database.

The project is open source and available here :

I strongly believe in a declarative approach of anonymization : the location of sensitive information inside database and the rules to hide this information should be declared directly using the Data Definition Language (DDL). In the age of GDPR, developpers should specify an anonymization strategy inside the table definitions, just like they specify data types, foreign keys and constraints.

This project is a prototype designed to show the power of implementing data masking directly inside PostgreSQL. Currently it is based on the COMMENT statement (probably the most unused PostgreSQL syntax) and an event trigger. In the near future, I’d like to propose a new syntax for dynamic data masking (MS SQL Server already has it)

The extension can be used to put dynamic masks on certain users or permanently modify sensitive data. Various masking techniques are available : randomization, partial scrambling, custom rules, etc.

Here’s a basic example :

Imagine a people table

=# SELECT * FROM people; id | name | phone ------+----------------+------------ T800 | Schwarzenegger | 0609110911
  1. Activate the masking engine
  1. Declare a masked user
  1. Declare the masking rules
=# COMMENT ON COLUMN IS 'MASKED WITH FUNCTION anon.random_last_name()'; =# COMMENT ON COLUMN IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$******$$,2)';
  1. Connect with the masked user
=# \! psql test -U skynet -c 'SELECT * FROM people;' id | name | phone ------+----------+------------ T800 | Nunziata | 06******11

Of course this project is a work in progress. I need yo

カテゴリー: postgresql

brian davis: A Poor Man's Column Oriented Database in PostgreSQL

planet postgresql - 2018-10-29(月) 10:16:00

Let's get this out of the way. If you need a real column oriented database, use one. Don't even think about using the insanity below in production.

Having said that, I've been wondering for a while if it would be possible to demonstrate the properties of a column oriented db by simulating one using only native functionality found in good old, row oriented, PostgreSQL.

Two properties of a column oriented database that make it beneficial in OLAP environments are

  1. For queries that involve only a subset of a table's columns, it only needs to read the data for those columns off disk, and no others, saving on IO
  2. Storing each column separately means it can compress that data better since it's all of the same type, further reducing IO

The reduction in IO saves time for queries aggregating data over a large fraction of the table, improving performance.

PostgreSQL, of course, is row oriented. It stores and reads data a whole row at a time, and this is great when you want to access a majority of a table's columns for a small percentage of the table. i.e. OLTP type queries.

The challenge will be, can we structure things so PostgreSQL behaves more like a column oriented db?


The strategy will be to use multiple tables behind the scenes to store the data for each column individually, and throw a view on top to tie them all together and make it look like a single table. If PostgreSQL's query planner is smart enough, it should be able to see that queries involving only a subset of columns only need to access those particular underlying tables, save on IO, and beat out a traditional PostgreSQL table of the same shape. We can even allow for modifying this pseudo-table by creating INSTEAD OF triggers on the view to take INSERT, UPDATE, DELETE statements, chop them up, and perform the necessary operations on the underlying tables.

Here's how the underlying tables will look. We'll have a single 'primary table' that contains only the primary key...

Table "public.primary_table" Column | Type |[...]
カテゴリー: postgresql

第 154 回理事会議事録 (2018-10) news - 2018-10-25(木) 19:14:43
第 154 回理事会議事録 (2018-10) anzai 2018/10/25 (木) - 19:14
カテゴリー: postgresql

Tim Colles: string_to_array behaviour with an empty string

planet postgresql - 2018-10-25(木) 17:49:23

So this caught me out the other day. We had a query which broke out multiple delimited strings each with one or more elements (but always the same number of elements in each) into multiple records using string_to_array and unnest. In each string any element could potentially have an empty value, but at least one string would always have values for all the elements. This meant there should always be at least one output row. So below worked as expected:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a|b'),('1|2')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | 1 b | 2 (2 rows)

Below worked entirely as expected as well:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a|b'),('|')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | b | (2 rows)

So did this when we had some data with just the minimum one element in the input:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a'),('1')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ a | 1 (1 row)

But then we discovered that there was some data that was being completely lost in the query output. Turns out that this occurred when, like above there was just one element in the input, but one value was empty:

SELECT UNNEST(STRING_TO_ARRAY(test.s1, '|')) AS col1, UNNEST(STRING_TO_ARRAY(test.s2, '|')) AS col2 FROM ( VALUES (('a'),('')) ) AS test(s1, s2);

with the following result:

col1 | col2 ------+------ (0 rows)

So after some investigation it turns out that this happens because string_to_array on an empty string returns an array with no elements rather than an array with one element which is an empty string. This does actually make sense because without any application specific context it is indeterminate whether an empty string should be treated a

カテゴリー: postgresql

Ozgun Erdogan: Why Citus Data is donating 1% equity to PostgreSQL organizations

planet postgresql - 2018-10-24(水) 21:15:00

Today, we’re excited to announce that we have donated 1% of Citus Data’s stock to the non-profit PostgreSQL organizations in the US and Europe. The United States PostgreSQL Association (PgUS) has received this stock grant. PgUS will work with PostgreSQL Europe to support the growth, education, and future innovation of Postgres both in the US and Europe.

To our knowledge, this is the first time a company has donated 1% of its equity to support the mission of an open source foundation.

To coincide with this donation, we’re also joining the Pledge 1% movement, alongside well-known technology organizations such as Atlassian, Twilio, Box, and more.

Why make this donation? Because it’s the right thing to do

At Citus Data, we have a special relationship with PostgreSQL. Our flagship product Citus builds on and transforms Postgres into a distributed database. Our team attends and presents at Postgres conferences. We sponsor PostgreSQL events and meetups. We learn from and contribute back to the community. Our customers use many drivers, tools, and connectors in the Postgres ecosystem.

In short, our team and our company have hugely benefited from Postgres over the years. We owe a lot of our success to the PostgreSQL project and the many talented people involved.

Today, we’re giving back to the community that who made this project so successful.

Why is this important? Because PostgreSQL is the world’s only independent database

PostgreSQL is unique.

Postgres is not only the world’s most advanced open source relational database. It is also the world’s only independent database. No single company backs or represents the Postgres project.

In one sense, the PostgreSQL community came to be, rather than being put together by one company. And the Postgres open source community has been steadily building new features for more than 20 years.

Signage for the first PostgreSQL Conference (PGCon) celebrating 10 years of development, in 2006. Photograph by Oleg Bartunov.

Today, Postgres offers a capable, robust, and popular alternativ

カテゴリー: postgresql

Nickolay Ihalainen: PostgreSQL locking, part 2: heavyweight locks

planet postgresql - 2018-10-24(水) 20:39:16

PostgreSQL locking visibility for application developers and DBAs is in most cases related to heavyweight locks. Complex database locking operations require full instrumentation using views from the system catalog. It should be clear which object is locked by a specific database “backend” process. An alternative name for any lock is “bottleneck”. In order to make database operations parallel we should split a single bottleneck into multiple operation specific tasks.

This is the second part of three blog posts related to table level locks. The previous post was about row-level locks, and a subsequent post reviews the latches protecting internal database structures.

Example environment

A simple table with several rows:

CREATE TABLE locktest (c INT); INSERT INTO locktest VALUES (1), (2); Helper view

In order to check different types of these locks, let’s create a helper view as suggested by Bruce Momjian in his presentation:

CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted, CASE WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL THEN virtualxid || ' ' || transactionid WHEN virtualxid::text IS NOT NULL THEN virtualxid ELSE transactionid::text END AS xid_lock, relname, page, tuple, classid, objid, objsubid FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE -- do not show our view’s locks pid != pg_backend_pid() AND -- no need to show self-vxid locks virtualtransaction IS DISTINCT FROM virtualxid -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; RowShareLock (ROW SHARE)

Many applications use the read-modify-write paradigm. For instance, the appl

カテゴリー: postgresql

David Rowley: PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11

planet postgresql - 2018-10-24(水) 18:17:41

During the PostgreSQL 11 development cycle an impressive amount of work was done to improve table partitioning.  Table partitioning is a feature that has existed in PostgreSQL for quite a long time, but it really wasn’t until version 10 that it started to become a highly useful feature. We’d previously claimed that table inheritance was our implementation of partitioning, which was true.  It just left you to do much of the work yourself manually. For example, during INSERTs, if you wanted the tuples to make it to your partitions then you had to set up triggers to do that for you. Inheritance partitioning was also slow and hard to develop additional features on top of.

In PostgreSQL 10 we saw the birth of “Declarative Partitioning”, a feature which is designed to solve many of the problems which were unsolvable with the old inheritance method of partitioning. This has resulted in a much more powerful tool to allow you to horizontally divide up your data!

Feature Comparison

PostgreSQL 11 comes complete with a very impressive set of new features to both help improve performance and also to help make partitioned tables more transparent to applications.

Feature PG9.6 PG10 PG11 Declarative Partitioning Auto Tuple Routing – INSERT Auto Tuple Routing – UPDATE Optimizer Partition Elimination  1 1 Executor Partition Elimination 2 Foreign keys 3 Unique indexes 4 Default Partitions Hash Partitions FOR EACH ROW triggers Partition-level joins  5 Partition-level aggregation Foreign partitions Parallel Partition Scans
  1. Using constraint exclusion
  2. Append nodes only
  3. On partitioned table referencing non-partitioned table only
  4. Indexes must contain all partition key columns
  5. Partition constraint on both sides must match exactly



We’ve also got good news here!  A new method of performing partition elimination has been added. This new algorithm is able to determine matching partitions by looking at the query’s WHERE clause. The previous algo

カテゴリー: postgresql

Quinn Weaver: How to set up scram-sha-256 authentication in PostgreSQL

planet postgresql - 2018-10-24(水) 15:09:00
md5: everyone uses it. But it's insecure — unsuitable for authentication or password storage. PostgreSQL 10 adds support for SCRAM SHA-256 authentication, which is far better1… but how do you use it? Documentation is scarce.

Below I give the complete steps, for two different cases:
  • The case where you migrate every user.
  • The case where you migrate only a subset of users. Step 1 explains why you might need to do this. In this case, you can save time and complexity by creating a containing role, adding all the users you want to migrate to that role, and then configuring that role to work with scram-sha-256. That way you don't have to do it once for each user.
Enough preamble; let's begin!
  1. First, decide which users to migrate to scram-sha-256. The ideal is to migrate everyone, all at once. In practice you may be unable to do that, for a few reasons:
    • Older client tools don't support scram-sha-256. You need at least psql 10, or pgAdmin 4.
    • Your app's client libraries may not support scram-sha-256. At a minimum, you will probably have to upgrade your client libraries2.
    • And it's not enough just to upgrade your libraries; you also have to change the password for each user who's migrating, because PostgreSQL needs to hash this password in scram-sha-256 format. There's no automatic rehash (there can't be, since PostgreSQL doesn't store the cleartext of the old password).
    • Finally, besides your own code, you may have third-party apps connecting to your database, whose library versioning policy and password reset policy you don't control.
  2. Having chosen the users to migrate, upgrade your relevant client libraries, psql/postgresql-client packages, and/or pgAdmin versions.
  3. If you're migrating all users, skip to the next step. If, instead, you're migrating a subset of users, then create a new role, and put all those users in that role. This step saves a bit of repetitive config work. $ sudo su - postgres
    postgres=# create role scram_sha_256_users nologin ROLE alice, bob, eve; … where alice, bob, and eve are the users yo
カテゴリー: postgresql

Joshua Drake: PostgresConf Silicon Valley recap

planet postgresql - 2018-10-24(水) 03:31:00

PostgresConf ran its first Silicon Valley conference on October 15th and 16th in conjunction with Silicon Valley Postgres. The two day conference was considered a “local” or development conference where our focus was introducing the PostgresConf experience to new attendees and partners with world class content. We believe we succeeded in that.

We brought new friends to the community with the addition of Yugabyte, Scalegrid, and AgensGraph. We also had old friends return with Pivotal, AWS, Microsoft, 2ndQuadrant, Timescale, Compose, and Nutanix.

This is the first conference we have organized where the attendance went up during the conference. All community conferences know the stress of waiting for people to register. We all register at the last possible moment but for attendance to increase as the conference is running was new for us. It added a new level of legitimacy to our purpose of:

The conference had 139 attendees and as the goal was 100-150, we are quite pleased with the turn out. We will be returning in 2019 and we look forward to continuing to build the Silicon Valley Community.
Thank you to the attendees, supporters, and organizers for helping pull off yet another amazing event!
カテゴリー: postgresql

Jobin Augustine: PostgreSQL Q&A: Building an Enterprise-Grade PostgreSQL Setup Using Open Source Tools

planet postgresql - 2018-10-20(土) 01:50:33

Hello everyone, and thank you to those that attended our webinar on Building an Enterprise-grade PostgreSQL setup using open source tools last Wednesday. You’ll find the recordings of such as well as the slides we have used during our presentation here.

We had over forty questions during the webinar but were only able to tackle a handful during the time available, so most remained unanswered. We address the remaining ones below, and they have been grouped in categories for better organization. Thank you for sending them over! We have merged related questions and kept some of our answers concise, but please leave us a comment if you would like to see a particular point addressed further.

Backups Q: In our experience, pg_basebackup with compression is slow due to single-thread gzip compression. How to speed up online compressed full backup?

Single-thread operation is indeed a limitation of pg_basebackup, and this is not limited to compression only. pgBackRest is an interesting alternative tool in this regard as it does have support for parallel processing.

Q: Usually one setup database backup on primary DB in a HA setup. Is it possible to automatically activate backup on new primary DB after Patroni failover? (or other HA solutions)

Yes. This can be done transparently by pointing your backup system to the “master-role” port in the HAProxy instead – or to the “replica-role” port; in fact, it’s more common to use standby replicas as the backup source.

Q: Do backups and WAL backups work with third party backup managers like NetBackup for example?

Yes, as usual it depends on how good the vendor support is. NetBackup supports PostgreSQL, and so does Zmanda to mention another one.

Security and auditing Q: Do you know a TDE solution for PostgreSQL? Can you talk a little bit about the encryption at rest solution for Postgres PCI/PII applications from Percona standpoint.

At this point PostgreSQL does not provide a native Transparent Data Encryption (TDE) functionality, relying instead in the underlying file system for data

カテゴリー: postgresql

Sebastian Insausti: How to Minimize RPO for Your PostgreSQL Databases Using Point in Time Recovery

planet postgresql - 2018-10-19(金) 23:48:02

In a disaster recovery plan, your Recovery Point Objective (RPO) is a key recovery parameter that dictates how much data you can afford to lose. RPO is listed in time, from seconds to days. Effectively, RPO is directly dependent on your backup system. It marks the age of your backup data that you must recover in order to resume normal operations.

If you do a nightly backup at 10 p.m. and your database system crashes beyond repair at 3 p.m. the following day, you lose everything that was changed since your last backup. Your RPO in this particular context is the previous day's backup, which means you can afford to lose one day’s worth of changes.

The below diagram from our Whitepaper on Disaster Recovery illustrates the concept.

For tighter RPO, a backup might not be enough though. When backing up your database, you are actually taking a snapshot of the data at a given moment. So when you are restoring a backup, you will miss the changes that happened between the last backup and the failure.

This is where the concept of Point In Time Recovery (PITR) comes in.

What is PITR? Related resources  How to do Point-in-Time Recovery of MySQL & MariaDB Data using ClusterControl  Become a PostgreSQL DBA: Point-in-Time Database Restoration  PostgreSQL Streaming Replication - a Deep Dive  Become a ClusterControl DBA: Safeguarding your Data  How to Overcome Accidental Data Deletion in MySQL & MariaDB

Point In Time Recovery (PITR), as the name states, involves restoring the database at any given moment in the past. For being able to do this, we will need to restore a backup, and then apply all the changes that happened after the backup until rightbefore the failure.

For PostgreSQL, the changes are stored in the WAL logs (for more details about WALs and the data they store, you can check out this blog).

So there are two things we need to ensure for being able to perform a PITR: The backups and the WALs (we need to setup continuous archiving for them).

For performing the PITR, we will need to recover the backup and th

カテゴリー: postgresql

Bruce Momjian: Long-Haul Behavior

planet postgresql - 2018-10-19(金) 22:30:01

People new to the Postgres community are often confused by the deliberateness of the community on some issues, and the willingness to break backward compatibility in other areas. The source code was created 32 years ago, and many of us have been involved with Postgres for 10-20 years. With that length of involvement, we are acutely aware of the long-term consequences of our decisions. Many proprietary and open source developers don't think years in advance, but with Postgres, it is our normal behavior.

This leads to some unusual Postgres behaviors:

  • Weekly source code comment improvements
  • Yearly source code reformatting
  • Exhaustive discussions about even minor changes, particularly user-visible ones
  • API redesigns

This focus on detail often strikes new users as unusual, but it makes sense when a multi-year view is considered.

カテゴリー: postgresql

Regina Obe: http extension for windows 64 and 32-bit

planet postgresql - 2018-10-19(金) 16:41:00
Updated October 19th, 2018 64-bit package for PostgreSQL 11 http extension v1.2.4 release

For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension v1.2.2 release for PostgreSQL 10, 9.6, 9.5, and 9.4 Windows 64-bit.

These should work fine on both PostgreSQL EDB windows distributions as well as the BigSQL windows 64 distributions. Instructions in the ReadMe.txt for where to copy the files to for each distribution. Caution should be taken especially with BigSQL to not overwrite existing BigSQL files. Many of the files in this already exist in the BigSQL distribution since we both compile using the Mingw64 chain.

If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.

Continue reading "http extension for windows 64 and 32-bit"
カテゴリー: postgresql

Tatsuo Ishii: Pgpool-II 4.0 released! (SCRAM authentication)

planet postgresql - 2018-10-19(金) 16:09:00
After 1 year of hard work by Pgpool-II developers,  Pgpool-II 4.0 is finally out.
This version celebrates the 15th anniversary of Pgpool-II, and introduces rich new features. I will explain them one by one in a series of blogs.
Support for SCRAM authentication SCRAM authentication is a modern, secure authentication method supported by PostgreSQL 10 or later. Pgpool-II 4.0 supports the method by providing multiple new functionalities.

  • pool_hba.conf now has "scram-sha-256" authentication method.
  • If this method is specified, users connecting to Pgpool-II are required  to response in proper way of SCRAM authentication.
  • Passwords stored in pool_passwd can be used to verify user's password before a session starts.
  • Passwords can be store in pool_passwd in multiple ways: clear text or AES256 encrypted (md5 hashed passwords can also be stored in pool_passwd but in this case SCRAM cannot use the password).
  • AES256 encryption is preferred and the key to decrypt it is stored in .pgpoolkey under the user's home directory who started Pgpool-II, or a file specified by PGPOOLKEYFILE environment variable.
  • For AES256 encryption, new tool pg_enc can be used.
Of course, Pgpool-II can authenticate itself in SCRAM while connecting to PostgreSQL, if it requires SCRAM authentication to Pgpool-II.

Good thing with Pgpool-II 4.0 is, it allows users to choose different authentication method in users vs. Pgpool-II  and Pgpool-II vs. PostgreSQL. For example, using SCRAM between users and Pgpool-II, while using md5 authentication method between Pgpool-II and PostgreSQL.

SCRAM authentication can also be used for health check and replication delay check.  Before 4.0, user name and password for them are stored in clear text format in pgpool.conf. This is not the most secure way. Pgpool-II 4.0 allows to store AES 256 encrypted passwords in pgpool.conf. .pgpookey is also used for decrypting these passwords.

Next time I will explain about other features of authentication method in Pgpool-II 4.0.
カテゴリー: postgresql

PostgreSQL 11 がリリースされました! (2018-10-18) news - 2018-10-19(金) 15:07:59
PostgreSQL 11 がリリースされました! (2018-10-18) kuwamura 2018/10/19 (金) - 15:07
カテゴリー: postgresql

Jignesh Shah: Hello PostgreSQL 11 - Get ready to say goodbye to PostgreSQL 9.3

planet postgresql - 2018-10-19(金) 14:26:00
Earlier today (Oct 18, 2018), the PostgreSQL community announced the release of PostgreSQL 11.  Having done multiple software releases earlier, I appreciate the hard work by all contributors to get yet another major release on schedule. It is hard to do a major release every year and the community has been doing it since PostgreSQL 8.4 making this the 10th  release in the last decade. 
Everybody has their favorite feature in PostgreSQL 11 and I have one that is top on my list which is the transactional support in stored procedures2nd Quadrant had first announced that feature end of last year and at that time, it instantly became my favorite as I see it as a giant leap in PostgreSQL as it allows people to essentially write long data routines like ETL broken down in multiple transactions. Of course many users will certainly enjoy the improvements in  table partitioning system, query parallelism, and just-in-time (JIT) compilation for accelerating the execution of expressions in queries. However, the developers will certainly get more freedom with the stored procedure improvements.
With the release of PostgreSQL 11, now there are 6 major releases supported: PostgreSQL 9.3, 9.4, 9.5, 9.6, 10 and, 11. It is definitely a good time to start thinking to upgrade your PostgreSQL 9.3 databases. As per the versioning policy, the final minor release for PostgreSQL 9.3 will be on November 8th, 2018.  PostgreSQL 9.3 will be the last major version which does not support logical replication which was first introduced in PostgreSQL 9.4.  Hence, I expect this is will be the last painful upgrade because PostgreSQL 9.4 onwards you can always leverage logical replication to minimize the downtime while switching to a new version. All is not lost for PostgreSQL 9.3, while the experience is not exactly the same there are still tools available using the older trigger based replication to help or just bite the bullet and upgrade once with a small maintenance window as later versions will give you more options for your next major vers[...]
カテゴリー: postgresql

Jobin Augustine: PostgreSQL 11! Our First Take On The New Release

planet postgresql - 2018-10-18(木) 23:19:38

You may be aware that the new major version of PostgreSQL has been released today. PostgreSQL 11 is going to be one of the most vibrant releases in recent times. It incorporates many features found in proprietary, industry-leading database systems, further qualifying PostgreSQL as a strong open source alternative.

Without further ado, let’s have a look at some killer features in this new release.

Just In Time (JIT) Compilation of SQL Statements

This is a cutting edge feature in PostgreSQL: SQL statements can get compiled into native code for execution. It’s well know how much Google V8 JIT revolutionized the JavaScript language. JIT in PostgreSQL 11 supports accelerating two important factors—expression evaluation and tuple deforming during query execution—and helps CPU bound queries perform faster. Hopefully this is a new era in the SQL world.

Parallel B-tree Index build

This could be the most sought after feature by DBAs, especially those migrating large databases from other database systems to PostgreSQL. Gone are the days when a lot of time was spent on building indexes during data migration. Index maintenance (rebuild) for very large tables can now make an effective use of multiple cores in the server by parallelizing the operation, taking considerably less time to complete.

Lightweight and super fast ALTER TABLE for NOT NULL column with DEFAULT values

In the process of continuous enhancement and adding new features, we see several application developments that involve schema changes to the database. Most such changes include adding new columns to a table. This can be a nightmare if a new column needs to be added to a large table with a default value and a NOT NULL constraint. This is because an ALTER statement can hold a write lock on the table for a long period. It can also involve excessive IO due to table rewrite. PostgreSQL 11 addresses this issue by ensuring that the column addition with a default value and a NOT NULL constraint avoids a table rewrite.  

Stored procedures with transaction control


カテゴリー: postgresql

Andrew Dunstan: Adding new table columns with default values in PostgreSQL 11

planet postgresql - 2018-10-18(木) 22:51:01

In PostgreSQL version 10 or less, if you add a new column to a table without specifying a default value then no change is made to the actual values stored. Any existing row will just fill in a NULL for that column. But if you specify a default value, the entire table gets rewritten with the default value filled in on every row. That rewriting behavior changes in PostgreSQL 11.

In a new feature I worked on and committed, the default value is just stored in the catalog and used where needed in rows existing at the time the change was made. New rows, and new versions of existing rows, are written with the default value in place, as happens now. Any row that doesn’t have that column must have existed before the table change was made, and uses this value stored in the catalog when the row is fetched. The great advantage of this is that adding a column with a default value is now quite a fast and cheap operation, whereas before for very large tables it has been horribly, often intolerably slow. Rewriting a whole multi-terabyte table is really something you want to avoid.

The default value doesn’t have to be a static expression . It can be any non-volatile expression, e.g. CURRENT_TIMESTAMP. Volatile expressions such as random() will still result in table rewrites. For a non-volatile expression, it is evaluated at the time the statement runs and the result is what is stored in the catalog for use with existing rows. Of course, for new rows the expression will be re-evaluated at the time the row is created, as happens now.

Any time that the table does get rewritten, say by VACUUM FULL, all this is cleaned up, as there will be no rows left needing the value from the catalog. Likewise, any time that a row is updated the column’s value is filled in on the new row version, so over time the proportion of rows using the value fetched from the catalog will decrease.

カテゴリー: postgresql

Andrea Cucciniello: PGDay Down Under 2018

planet postgresql - 2018-10-18(木) 21:34:36

After the success of last year’s event, the second PGDay held in Australia, we’re back this year with PGDay Down Under. The name “Down Under” refers to Australia and New Zealand, due to the fact these countries are located in the lower latitudes of the southern hemisphere.

The conference is a one-day community event organized by the newborn PostgreSQL Down Under Incorporated (also known as PGDU), a not-for-profit association established to support the growth and learning of PostgreSQL, the world’s most advanced open source database, in Australia and New Zealand.

PGDay Down Under aims to satisfy a large audience of PostgreSQL users and enthusiasts by selecting a wide range of talks and presentations that are of interest to:

  • Database administrators that are already using PostgreSQL, or use a different database and are interested in learning about PostgreSQL
  • Team leaders, architects and decision makers considering PostgreSQL as an alternative database to more traditional proprietary databases
  • Developers that have data persistence requirements or are just wanting to expand their knowledge
  • IT enthusiasts who enjoy getting involved in the open source community

Don’t wait until last minute, early bird tickets are now available, you can register for PGDay Down Under here. To complete the registration log in with your community account or preferred third party platform.

The Call for Papers is open until October 21, at midnight (AEDT). We have already received many submissions and we hope to get some more. To submit your proposal for a presentation please visit and click on Call For Papers on the top right corner.

This event would not be possible without the generous sponsorship and support from many different organisations, such as 2ndQuadrant. Any proceeds from the conference will be used to organize more community events in other locations, creating a virtuous cycle to better support the growth of the PostgreSQL community in the region.

If you are interested in sponsoring and/or volunteering at the event

カテゴリー: postgresql