PostgreSQL
The world's most advanced open source database
Top posters
Number of posts in the past month
Top teams
Number of posts in the past month
Feeds
Planet
  • Policy for being listed on Planet PostgreSQL.
  • Add your blog to Planet PostgreSQL.
  • List of all subscribed blogs.
  • Manage your registration.
Contact
  • Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.
Posted by Pavel Stehule on 2017-12-15 at 16:07
I like pgFouine much. Unfortunately pgFouine was not updated years and with new PHP there was more and more warnings (last months the documentation is not available).

Miloslav Hula refreshed the code and fixed warnings. Fresh version of pgFouine is available from repository https://github.com/milo/pgFouine.

Recently Stephen Frost called in pgsql-hackers@ mailing list for PostgreSQL-related ideas for Google Summer of Code 2018. I proposed a few ideas and offered to be a possible mentor for corresponding projects. In this post I wold like to share a brief description of these projects (copied from PostgreSQL Wiki …

Yesterday I had a few spare hours (I was waiting for 'Star Wars VIII' movie session, which BTW turned out to be great) so I decided to implement one idea I had in mind for quite some time. The idea is to add support of Protobuf datatype to PostgreSQL.

The …

Ever since Hannes Eder published the idea of the SKYLINE OF operator on the PostgreSQL mailing list years ago, I was somewhat hooked with the idea of being able to make more intelligent queries in PostgreSQL. Now: What is the idea of a “Skyline query”? Here is the basic concept: Suppose you want to go on holiday and you are looking for a nice hotel on the beach. The trouble is: The hotels with a nice view of the beach are ways too expensive – the hotels further back are cheap but far away from the sea. The question is therefore: What is the best compromise?

This is exactly what this stuff is all about.

Here is an example:

test=# CREATE TABLE t_hotel
(
id serial,
name text,
price numeric,
distance_beach numeric
);
CREATE TABLE

The table simply stores the name of a hotel, the price as well as the distance to the beach. Let us add a couple of rows manually:

test=# INSERT INTO t_hotel (name, price, distance_beach)
VALUES ('ABC Motel', 120, 2.4);
INSERT 0 1
test=# INSERT INTO t_hotel (name, price, distance_beach)
VALUES ('Crapstone Hotel', 90, 2.2);
INSERT 0 1
test=# INSERT INTO t_hotel (name, price, distance_beach)
VALUES ('Luxury Watch Spa Hotel', 495, 0.2);
INSERT 0 1
test=# INSERT INTO t_hotel (name, price, distance_beach)
VALUES ('Nowhere Middle Hotel', 45, 9.6);
INSERT 0 1

If we select our hotels sorted by price we will see clearly that we will most likely end up far away from the beach in a cheap low quality hotel. Clearly, this is not desirable:

test=# SELECT * FROM t_hotel ORDER BY price;
id | name | price | distance_beach
----+------------------------+-------+----------------
4 | Nowhere Middle Hotel | 45 | 9.6
2 | Crapstone Hotel | 90 | 2.2
1 | ABC Motel | 120 | 2.4
3 | Luxury Watch Spa Hotel | 495 | 0.2
(4 rows)

However, if we sort by distance, we will end up close to the beach but we simply cannot afford it. The trouble is that none of those queries will actually offer us a good compromise:

test=# SELECT * FROM t_hotel ORDER BY distance_beach;
id | name | price | distance_beach
----+---------------
[...]
Posted by Pavel Stehule on 2017-12-15 at 05:25
now options --ignore-case and --IGNORE-CASE are supported

Source code https://github.com/okbob/pspg
Posted by Paul Ramsey in PostGIS on 2017-12-14 at 16:00

Earlier this month I got to speak at the Spatial Data Science Conference hosted by my employers Carto at our funky warehouse offices in Bushwick, Brooklyn. The topic: PostGIS scaling.

PostGIS Scaling

Now.

“Make it go faster” is a hard request to respond to in the generic: what is “it”, what are you doing with “it”, are you sure that your performance isn’t already excellent but you’re just too damned demanding?

So, the talk covers a number of routes to better performance: changing up query patterns, adding special PostgreSQL extensions, leaning on new features of PostgreSQL, and just plain old waiting for PostgreSQL to get better. Which it does, every release.

The Three-Valued Logic of SQL

SQL uses a three-valued logic: besides true and false, the result of logical expressions can also be unknown. SQL’s three valued logic is a consequence of supporting null to mark absent data. If a null value affects the result of a logical expression, the result is neither true nor false but unknown.

The three-valued logic is an integral part of Core SQL and it is followed by pretty much every SQL database.

Comparisons to null

The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. That’s where the third logical value, unknown, comes in. Unknown means “true or false, depending on the null values”.

The result of each of the following comparisons is therefore unknown:0

NULL = 1
NULL <> 1
NULL > 1
NULL = NULL

Nothing equals null. Not even null equals null because each null could be different.


Mnemonic

Every null is a different null.


That’s why SQL has the is null predicate to test whether a value is null or not and the is not distinct from predicate to compare two values while treating two null values as the same.

Logical Operations Involving Unknown

In logical connections (and, or), unknown behaves like the null value in comparisons: The result is unknown if it depends on an operand that is unknown. In contrast to comparisons, this principle leads to cases in which the result of a logical connection is not unknown even though one operand is unknown. The reason is that the result of a logical connection is only unknown if it actually depends on an operand that is unknown.

Consider the following example:

(NULL = 1) OR (1 = 1)

Although the comparison to null makes the first operand of the or operation unknown, the total result is still true because or operations are true as soon as any operand is true.

Another way to look at it is to mentally replace each null with a call to a random() function. If the overall result of the expression is inevitably the same, no matter which value random() returns,

[...]

Florent Fourcot has read Mastering PostgreSQL in Application Development and has seen tremendous inprovements in his production setup from reading the first chapters and applying the book advices to his use case.

Here’s an interview run with Florent where he explains the context in which such improvements has been made!

One of the headline features of the brand new PostgreSQL release out 2 months ago is Logical Replication. Logical replication allows more flexibility than physical replication, including replication between different major versions of PostgreSQL and selective-table replication. You can get more details on the feature here.

So, now that we have this, I’ve been asked on occasion if we are still going to continue develop pglogical and if it’s even needed. I was also asked a couple of times why we put the native logical replication into PostgreSQL when we already have pglogical. I’d like to answer those questions in this blog post.

Why Logical Replication in PostgreSQL 10?

Let’s start with the simpler, or rather shorter, topic of why we added logical replication into PostgreSQL 10.

The main reason is that more people can use it that way. Many people run in some kind of restricted environment, be it because they use PostgreSQL as a service from somebody else, or because their company has strict policies about what can be installed in production, or any other reason. In those cases one often can’t install extensions, so only built-in functionality is available. The more subtle side of this is that while pglogical is now a quite well-know project, there are still many who have no idea that the possibilities it provides even exist. With Logical Replication being a headline feature of PostgreSQL 10, a lot more people will now recognize the possibilities at their disposal when using PostgreSQL.

There are other reasons for this too. The more features we have around logical decoding and logical replication in PostgreSQL, the more they can be used for other purposes than just simple replication. For example, I can imagine a future where we use logical decoding to make table rewrites virtually lock free. It also makes it easier to develop additional infrastructure that’s needed for new logical replication features, since it’s much easier to prove that something is useful when it’s used directly by PostgreSQL.

What about pglogi

[...]
Posted by Chris Travers on 2017-12-13 at 11:00

Recently, I wrote a PL/PGSQL extension which provides some basic functions for creating and altering roles, and managing permissions. The extension was built to improve our tooling for PostgreSQL user creation internally. Since this has large number of external applications, it has been released to the public under the PostgreSQL license.

This blog post is the first in a series on this extension. In it I cover the difficulties which come with creating tooling around utility statements in PostgreSQL as a whole, why centralising this in user defined functions is a good idea, and what kinds of problems we are trying to solve.

In the next article in this series, we will cover the major implementation details. In that post, we will discuss how we prevent SQL injection from occurring within user-defined functions, both in terms of language injection and object injection. We will also areas of development in this area which have, for now, not been included in the extension and the security problems they pose. In the final article in the series, we will discuss the unique testing needs of such a security critical piece of infrastructure, the tooling available, and the difficulties we ran into in trying to ensure that the tests run consistently on various versions of PostgreSQL.

Word of Warning

This blog post includes sample ways of doing things which are wrong but are included in order to communicate problems that happen. Please resist the urge to copy and paste, and instead make sure you understand what you are doing. Things like placeholders may be handled in different ways depending on different database drivers, for example.

Role Management and DDL in PostgreSQL

PostgreSQL has supported the standard database role-based permissions model since PostgreSQL 8.1. In this model we think about granting access to roles, and also granting one role to another. Depending on how roles are defined, they may pass on permissions to child roles automatically or not. Managing the permissions given to various roles is an important par

[...]

The Enterprise Edition of Mastering PostgreSQL in Application Development ships with a docker image that hosts both a PostgreSQL server instance with a pre-loaded database, the one that’s used throughout the book examples, and also with a Jupyter Network notebook that hosts SQL queries thanks to the sql_magic plugin.



How do you use Postgres?


I work Fred Hutchinson Cancer Research Center and within Fred Hutch, I work for the largest group called SCHARP, Statistical Center for HIV/AIDS Research and Prevention. We use Postgres to monitor the AIDS drug trials real time to see if the trials are working or not. This means we collect the data from doctors and labs around the world, and analyzing the data. We also have servers where we receive data from other research institutes and share our randomized data, de-personalized, with other research institutes.


What community contributions have you provided?


In 2010 I started SeaPUG, Seattle Postgres Users Group, at the request of Josh Drake. I have at least have the presentations there every year. I have also discovered several PostgreSQL: bugs which have been fixed. Some of them affected every version of PostgreSQL. Bug numbers: 7553, 8173, 8257, 8291. I also found 8545, which has not been fixed but Core has acknowledge needs to be fixed but they are not sure where it should be fixed, pg_dump or pg_dumpall. I started the PostgreSQL track at LinuxFest Northwest in 2014 after my GIS presentation in 2013 was standing room only. This year I got a booth at the SeaGL, Seattle GNU Linux, conference with the idea of having a booth there next year along with also doing a PostgreSQL presentation next year at the conference.


You recently took a renewed interest in speaking at Postgres Conferences, why?


I have been giving presentations locally now for the last 7 years and so I am now ready to move on to the next step, doing presentations at the Local and National conferences around the United States.


What is the #1 barrier you see to Postgres adoption?


People not knowing about PostgreSQL, most people know about MYSQL, MSSQL and Oracle, but do not know about PostgreSQL. This is changing, some, with the Cloud providers now offering PostgreSQL, but I go to these conferences, LinuxFest Northwest and SeaGL, and people all the time are asking me, "What is Postgres and why should I use it o
[...]
Posted by Bruce Momjian in EnterpriseDB on 2017-12-12 at 14:00

It has been a year since my last blog post about sharding. There are many sharding improvements in Postgres 10, and this PGConf.Asia talk from NTT staff covers the advances. The slides go into great detail about what has been accomplished, and what remains. This whiteboard image from the PGConf.Asia Unconference gives a single view of the necessary pieces.

What I wrote about a year ago felt like a far-distant goal — now it feels like we are almost there. Yes, it will take years to fully complete this feature, like all our complex features, but it feels like we are very near to proof-of-concept and production deployments, at least for certain workloads. Postgres pulls off these amazing feats regularly, and you would think I would get use to it and not be surprised, but I still am.

We’re big fans of Postgres and enjoy getting around to the various community conferences to give talks on relevant topics as well as learn from others. A few months ago we had a good number of Citus team members over at the largest Postgres conference in Europe. Additionally, three of our Citus team members gave talks at the conference. We thought for those of you that couldn’t make the conference you might still enjoy getting a glimpse of some of the content. You can browse the full set of talks that were given and slides for them on the PGConf EU website or flip through the presentations from members of the Citus team below.

Distributed count(distinct) with HyperLogLog on PostgreSQL

Hacking PostgreSQL with Eclipse

Distributed Computing on PostgreSQL

Posted by Dimitri Fontaine on 2017-12-11 at 09:19

The PostgreSQL community made the explicit choice some times ago that they would not use the infamous master and slave terminology. Instead, the documentation introduces the concepts of High Availability, Load Balancing, and Replication with the terms Primary and Standby, and the even more generic term Replica is used in contexts when only the data flow is considered, rather than the particular role of a node.

PgBouncer is a popular proxy and pooling layer for Postgres. It’s extremely common to reconfigure PgBouncer with repmgr so it always directs connections to the current primary node. It just so happens our emerging Docker stack could use such a component.

In our last article, we combined Postgres with repmgr to build a Docker container that could initialize and maintain a Postgres cluster with automated failover capabilities. Yet there was the lingering issue of connecting to the cluster. It’s great that Postgres is always online, but how do we connect to whichever node is the primary?

While we could write a layer into our application stack to call repmgr cluster show to find the primary before connecting, that’s extremely cumbersome. Besides that, there’s a better way. Let’s alter our stack to incorporate PgBouncer and open a whole new world of RAD opportunities.

Tough Decisions

One major shortcoming regarding Docker containers is that they’re not exactly designed to communicate with each other. Yet simultaneously, Docker encourages single-purpose containers. We already had to circumvent this design to run both repmgr and Postgres in the same container, since repmgr needs access to pg_ctl or other similar utilities.

In that same vein, integrating PgBouncer could mean directly injecting it into the container, or running a separate dedicated PgBouncer container. If we install PgBouncer in every node, each node can only reconfigure its local PgBouncer instance. As a separate container, no nodes can alter the PgBouncer configuration file.

So we need to cheat a bit. We already know from the first part in this series that Docker allows mounting user-defined volumes within a container. If we mount that same volume onto every container, reconfiguring PgBouncer suddenly becomes trivial. With that in mind, let’s do things The Right Way ™, and give PgBouncer its own container.

Hiring a Bouncer

Fortunately a minimal PgBouncer setup only needs to know the hostname of the primary node. We could get far more involved with users

[...]

We rolled out a new database migration feature for the Citus fully-managed database as a service—the Warp migration feature—as part of our Citus Cloud 2 announcement. Today I wanted to walk through Citus Cloud’s warp migration feature in more detail. Before we drill in, we should probably take a step back and look at what typically (and sometimes painfully) goes on for a large database migration.

Pain and heartache of database migrations in the past

For many companies, migrating from one database to another is a decision that gets pushed out as long as possible. After all, migrating your underlying infrastructure isn’t a shiny new feature you can wave around to customers. And we all know that premature optimization can cause a business to die before it even has a chance to become a business. But, pushing out the database migration to the last possible moment has in the past caused feature development to come to a standstill when a large database migration does come.

When you’re smaller, migrating from one database to another is fast and simple. If you have 100 GB of data, it would take roughly ~45 minutes to dump that database and restore to a new system. And if the 100 GB is your total datasize including indexes, your raw data might be even smaller than 100 GB, hence your dump/restore would go even faster. So while 45 minutes of downtime is not ideal under any circumstance, the downtime is likely manageable within some maintenance window. However, with a database that is 1 TB in size, it will probably take you ~7.5 hrs to dump and restore, which is much harder to justify. 7.5 hours of downtime causes a dump/restore method of database migration to become a blocker.

With a dump/restore method of migration a non-starter, you probably turn to other alternatives.

The simplest migration alternative is a brute force method of adding in your application logic to write to two databases in parallel, so that you can preserve uptime while doing the database migration. At the start, adding application logic to write to two

[...]
Posted by Vasilis Ventirozos in OmniTI on 2017-12-08 at 15:39
Im not a big fun of postgres clients like pgadmin, not sure why, but i always favoured psql over everything.
A friend of mine wrote an atom package that brings psql into atom. Which is awesome. It's tested in linux and (briefly) in osx. In osx i only had to change the path for psql because for some reason it didn't get it from the environment. Feel free to check it out and spam him with any issues you might have.

Link for the package can be found here.

Thanks for reading
-- Vasilis Ventirozos

Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL!

A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle.

The commit from last week adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well as ALTER/DROP ROUTINE that can refer to either a function or a procedure (or an aggregate function, as an extension to SQL). It also includes support for procedures in various utility commands such as COMMENT and GRANT, as well as support in pg_dump and psql. Support for defining procedures is available in all the languages supplied by the core distribution.

While this commit simply adds structure that is built upon existing functionality, it lays down the foundation on which we will be building the real meat to be made available in PostgreSQL 11. Next steps include the implementation of:

  • Transaction control – allowing us to COMMIT and ROLLBACK inside procedures
  • Returning multiple result sets

Stay tuned for more information!

The French PostgreSQL Cross-Enterprise Work Group, a non-profit organization advocating for the adoption and use of PostgreSQL, just published an open letter to all software vendors that don’t already do so to take steps to support PostgreSQL as a database management system (DBMS). The ambition of this initiative is to incite them to adapt quickly to the irresistible IT transformation in progress in public and private companies.

Created in 2016, the PostgreSQL Cross-Enterprise Work Group ( called “Groupe de Travail Inter-Entreprise” or “GT Entreprise” in French) is a dedidacted task force inside the PostgreSQLFr non-profit association. Over the last months, it has grown remarkably.

PostgreSQL Cross-Enterprise Work Group

In a new press release (link below) published yesterday, the workgroup sent an open letter to software vendors, asking them officially to support PostgreSQL.

  • Original version :

https://www.postgresql.fr/entreprises/20171206_lettre_ouverte_aux_editeurs_de_logiciels

  • English translation :

https://www.postgresql.fr/entreprises/english

This is a major step for PostgreSQL in France and in the french-speaking countries. For the first time, more than 20 national and international companies take an explicit stance in favor of PostgreSQL by recognizing the technical value of the software but also highlighting the benefits of the open source model itself.

Among these companies and public agencies, you can find : Air France, Carrefour (retail), CASDEN (bank), CNES (national space study center), EDF, MSA / GIE AGORA (mutual insurance), E.Leclerc (retail), MAIF (mutual insurance), Météo France (national weather forecast agency), The National Education Ministry, PeopleDoc, SNCF (national railway company), PeopleDoc, SNCF, Société Générale (bank) and Tokheim Services Group.

These institutions gathered in the PostgreSQLFr association to share their experience, promote PostgreSQL and contribute to its development.  Beyond these 3 big missions, the remarkable point is that the group organized itself by adopting the great principles of the PostgreSQ

[...]
Posted by Paul Ramsey in PostGIS on 2017-12-06 at 16:00

On the twitter this morning, there was a good question:

TL;DR: If you find a feature in “Fund Me” and want to fund it, join the postgis-devel mailing list and make yourself known.

If you go to the PostGIS ticket report and scroll through the pages you’ll first see some milestones tied to released versions. These are usually bug reports, both big and small, valid and invalid, and will eventually be closed.

We unfortunately carry a lot of tickets in the current development milestone (2.5 right now) which are, at best, speculative. They should probably be closed (we really will never do them and don’t much care) or moved to the “Fund Me” category (they are valid, but we have no personal/professional impetus to address them).

The “Fund Me” category used to be called “Future”. This was a bad name, as it implied that sometime in the “Future” the ticket might actually be addressed, and all you needed was sufficient patience to wait. The reality is that they way a ticket got into the “Future” category was that it was ignored for long enough that we couldn’t stand to see it in the current milestone anymore.

The PostGIS development community includes all kinds of developers, who make livings in all kinds of ways, and there are folks who will work on tasks for money. The “Fund Me” milestone is a way of pointing up that there are tasks that can be done, if only someone is willing to pay a developer to do them.

That’s the good news!

The bad news is that the tickets all look the same, but they are wildly variable in terms of level of effort and even feasibility.

  • #220 “Implement ST_Numcurves and ST_CurveN” would probably take a couple hours at the outside, and almost any C developer could do it, even oen with zero experience in the PostGIS/PostgreSQL/GEOS ecosystem.
  • #2597 “[raster] St_Grayscale” would require some knowledge of the PostGIS raster im
[...]

Oleg Bartunov and I have been invited to the PGConf.ASIA 2017 conference that took place 4-6 December in Tokyo, Japan. Both of us attended the developer unconference. Also Oleg had an honor to be a keynote speaker and I gave a talk "PostgreSQL Sharding and HA: Theory and Practice …

NULL-Aware Comparison: is [not] distinct from

In SQL null is not equal (=) to anything—not even to another null. According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null.

With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same.

<expression> IS NOT DISTINCT FROM <expression>

Note that you have to use the negated form with not to arrive at similar logic to the equals (=) operator.

The following truth table highlights the differences between the equals sign (=) and is not distinct from.

A B A = B A IS NOT DISTINCT FROM B
0 0 true true
0 1 false false
0 null unknown false
null null unknown true

The result with equals (=) is unknown if one operator is null. The is not distinct from comparison is true if both values are null or false if only one is null.

Conforming Alternatives


Note

Although there are standard alternatives to is not distinct from, using a proprietary alternative is often the better choice.


Due to SQL’s three-valued logic, a fully equivalent substitute for A is not distinct from B that works in all SQL databases is surprisingly complex:

CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
     THEN 0
     ELSE 1
 END = 0

The result of the expression in the when clause is true if both arguments are equal or both are null. If only one argument is null the result is unknown, not false. This is often not a problem because SQL generally treats unknown like false when making binary decisions such as accepting or rejecting a row for a where clause.

To get the fully equivalent functionality of is not distinct from—i.e. either true or false but never unknown—the case expression reduces the three-valued result into a two a two-valued one. In some databases is not false can be used instead of the case expression.

Another option is to use set operators, which use distinct comparisons internally. The following snippet uses intersect to dete

[...]
Posted by Dimitri Fontaine on 2017-12-05 at 17:10

Today I am very pleased to announce the release of the book Scaling Python from my good friend Julien Danjou!

As Julien says, Python applications can handle millions of requests. Well, we know here that it’s easier on them when they are using PostgreSQL of course!

This is going to be a very short post about a simple solution to the problem of inserting data fast when you really have a lot of it.

The problem

For the sake of having some example to think about, imagine building an app for managing nests of ants.

You can have thousands of nests with hundreds of thousands of ants in each one of them.

To make the fun example applicable for this blog post, imagine that you’re reading data files coming from a miraculous device that “scans” nests for ants and gives you info about every ant with lots of details. This means that creation of the nest is about providing a name, coordinates, and the data file. The result should be a new nest and hundreds of thousands of ant records in the database.

How do we insert this much data without hitting the browser’s timeout?

Approaching the problem

Regular INSERT statements provide a lot of flexibility that is normally much needed, but is relatively slow. For this reason doing many of them isn’t preferred among database experts for pre-populating databases.

The solution that is typically used instead (apart from the case in which a database needs to be restored, with pg_restore having no contenders in terms of speed) is the data-loading method called COPY.

It allows you to provide data in a CSV format either from a file or “streaming” this data into the client itself. Now because it’s almost never a good idea to use the database-superuser account for connecting with the database from Rails, the first option isn’t available (access to the file system is only allowed for admins). Fortunately, there’s the second option which we are going to make use of.

The solution

Here’s a short code excerpt showing how the above mentioned approach could be used in Rails for the fun little app described in the beginning:

# first, grab the underlying connection object coming
# from the lower level postgres library:
connection = Ant.connection.raw_connection

# generate the ants array based on the data file:
ants = AntImporter.run!(data_file)

# now use the conn
[...]

It has been a long time since the last post. Today here is a post about the following feature that will land in Postgres 11:

commit: 4b0d28de06b28e57c540fca458e4853854fbeaf8
author: Simon Riggs <simon@2ndQuadrant.com>
date: Tue, 7 Nov 2017 12:56:30 -0500
Remove secondary checkpoint

Previously server reserved WAL for last two checkpoints,
which used too much disk space for small servers.

Bumps PG_CONTROL_VERSION

Author: Simon Riggs
Reviewed-by: Michael Paquier

Up to Postgres 10, PostgreSQL has been designed to maintain WAL segments (Write-Ahead Log, an internal journal in the shape of binary data which is used for recovering the instance up to a consistent point) worth two checkpoints. This has as direct effect that past WAL segments are not needed once two checkpoints have been completed, those getting either removed or recycled (renamed). The interest behind keeping two checkpoints worth of data is to get a fallback, so as if the last checkpoint record cannot be found then the recovery falls back to the checkpoint record prior that.

Note that on standbys, two checkpoints are not maintained, as only one checkpoint worth of WAL segments is kept in the shape of restart points created. The code path created both checkpoints and restart points is very similar (look at xlog.c and checkpoint.c).

Falling back to the prior checkpoint can be actually a dangerous thing, see for example this thread about the matter. And I have personally never faced a case where the last checkpoint record was not readable and that it was necessary to fallback to the prior checkpoint because the last checkpoint was not readable after an instance crash (PostgreSQL being legendary stable as well, it is not like one face crashes in production much anyway…).

So the commit above removes this prior checkpoint, which has a couple of consequences:

  • Setting value of max_wal_size will reduce by roughly 33% the frequency of checkpoints happening, assuming that checkpoint_target_completion gets close to 1. The maximum amount of time to finish re
[...]

So about two weeks ago we had a stealth release of Citus 7.1. And while we have already blogged a bit about the recent (and exciting) update to our fully-managed database as a service–Citus Cloud—and about our newly-added support for distributed transactions, it’s time to share all the things about our latest Citus 7.1 release.

If you’re into bulleted lists, here’s the quick overview of what’s in Citus 7.1:

  • Distributed transaction support
  • Zero-downtime shard rebalancer
  • Window function enhancements
  • Distinct ON/count(distinct) enhancements
  • Additional SQL enhancements
  • Checking for new software updates

For all these new features in Citus 7.1, you can try them today on Citus Cloud, get started locally with our Citus open source downloads, or keep right on reading to learn more about all the new Citus things.

Distributed transactions in the Citus database

The new Citus 7.1 distributed transactions feature is so important to our customers that we dedicated an entire blog post to it. As well as a blog on distributed deadlocks, an important pre-requisite feature we had to deliver in Citus 7.0, before tackling the distributed transaction challenge in Citus 7.1.

Zero-downtime Shard Rebalancer

We already covered the new zero-downtime shard rebalancer in Craig’s previous Citus Cloud 2 post, so we won’t dig in too much here. Just in case you missed the Citus Cloud 2 launch, bottom line, the Citus Cloud shard rebalancer now offers a fully online operation, with zero-downtime. To implement the zero-downtime shard rebalancer, we extended Postgres 10 logical replication support. Oh, and to make it easy to observe progress during the rebalancer operation, we created a new UI, which we hope you find useful:

Cross-tenant support for Windows Functions in Citus

In SQL, a window function performs a computation across a se

[...]
Posted by Pavel Stehule on 2017-12-01 at 19:25
* fix some crashes
* less pager like status bar

download from github.
Posted by Pavel Stehule on 2017-12-01 at 19:06
Precompiled libraries of Orafce are available on url: https://postgres.cz/files/orafce-3.6.2.zip.

I leave this Sunday for beautiful and ecclectic Austin, Tx. I will be providing the training Postgres Performance and Maintenance as well as speaking on The Power of Postgres Replication. The training is one that I give several times a year but the Replication talk is new. Although I have spoke on replication before, this new presentation is all about the power of Logical Replication. If you would like to learn more, pick up a ticket and let's have some fun. If you can't make it to Austin, perhaps you can make it to the PGConf Mini: NYC on December 14th. I will be presenting the same Replication presentation at that event. Let's bring about the new year with a strong showing of @amplifypostgres!