Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 12 Minuten

Pavel Stehule: precompiled libraries for orafce 3.8 for PostgreSQL 10, 11 and 12

20. Oktober 2019 - 11:55
I upload precompiled libraries to These libraries are 64bit only (there is not 32bit build for Postgres 11 and 12). For 32bit or older PostgreSQL releases, please, use older orafce builds. Windows build is partially reduced - there are not support for PLVlex for PostgreSQL 10 and 11 (due compilation problems), and there are not support for utl_file (due crash in these functions - I am not able to fix it on MSWIN platform).

Regina Obe: PostGIS 3.0.0

20. Oktober 2019 - 2:00

The PostGIS development team is pleased to release PostGIS 3.0.0.

This release works with PostgreSQL 9.5-12 and GEOS >= 3.6.

If you are using postgis_sfcgal extension, you need to compile against SFCGAL 1.3.1 or higher.

Best served with PostgreSQL 12 , GEOS 3.8.0 and pgRouting 3.0.0-beta.

Magnus Hagander: Nordic PGDay 2020 - Call for Papers open

18. Oktober 2019 - 17:15

The call for papers for Nordic PGDay 2020 in Helsinki, Finland, is now open. Submit your proposals for interesting talks about all things PostgreSQL, and join us in March.

Just like two years ago, the conference is held in cooperation with which is held two days later. So if you are interested in both, you can submit the same proposal to both conferences at once!

Alex Korban: Generating land-constrained geographical point grids with PostGIS

17. Oktober 2019 - 2:00
When I was in the market for an EV, one of the things I wondered about was how far I would be able to go outside the city before I had to charge it. Having a number for the range isn't enough to know offhand whether I'd be able to reach a particular destination. So I wanted to make the range more obvious in my EV guide by visualising vehicle range on a map. The trivial solution is to use range as a radius and show a circle: Distance circle But of course, that's going to be massively inaccur...

Regina Obe: PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you

16. Oktober 2019 - 1:15

PostGIS 3.0.0 is planned for release early next week. In the meantime you will find PostGIS 3.0.0rc1 or rc2 available via,, and EDB Windows 64-bit stackbuilder for PostgreSQL 12.

Continue reading "PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you"

Vasilis Ventirozos: Tuning checkpoints

15. Oktober 2019 - 23:52
We recently had the chance to help a customer with some IO related issues that ended up being unconfigured checkpoints. Something that may not always be obvious but can actually be somewhat common.

Let's start with how things roughly work.
Postgres smallest IO unit is a disk block that is 8kb (by default). Each time postgres needs a new block it will fetch it from the disks and load it to an area in RAM called shared_buffers.
When postgres needs to write, it does it in the same manner:

Rajeev Rastogi: An Overview of Various Auxiliary Plan Nodes in PostgreSQL

15. Oktober 2019 - 19:36

All modern database system supports a Query Optimizer module to automatically identify the most efficient strategy for executing the SQL queries. The efficient strategy is called “Plan” and it is measured in terms of cost which is directly proportional to “Query Execution/Response Time”.  The plan is represented in the form of a tree output from the Query Optimizer. The plan tree nodes can be majorly divided into the following 3 categories:

Hans-Juergen Schoenig: Prewarming PostgreSQL I/O caches

15. Oktober 2019 - 10:00

PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce
disk I/O and to speed up the database in the most efficient way
possible. During normal operations your database cache will be pretty useful and
ensure good response times. However, what happens if your database instance is
restarted – for whatever reason? Your PostgreSQL database performance will suffer
until your I/O caches have filled up again. This takes some time and it can
be pretty damaging to your query response times.

Álvaro Herrera: Managing another PostgreSQL Commitfest

15. Oktober 2019 - 7:28
I have written about managing a PostgreSQL commitfest before. During the PostgreSQL 13 development cycle, I did it again. This time I used a different strategy, mostly because I felt that there was excessive accumulation of very old patches that had received insufficient attention. So apart from bugfixes (which are always special cases), I focused […]

Federico Campoli: Regenerated

15. Oktober 2019 - 2:00

With PostgreSQL 12 the generated columns are now supported natively. Until the version Postgresql 11 it were possible to have generated columns using a trigger.

In this post we’ll see how to configure a generated column via trigger and natively then we’ll compare the performances of both strategies.

Euler Taveira de Oliveira: Postgres Object ownership

15. Oktober 2019 - 0:26
Sometimes I have to fix some object ownership such as tables and views. Let's figure out if there is such object in your database:

-- list tables, views, foreign tables and sequences not owned by role postgres
c.relname AS relation,
pg_get_userbyid(c.relowner) AS ROLE,
'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'

Robert Haas: Braces Are Too Expensive

14. Oktober 2019 - 20:39
PostgreSQL has what's sometimes called a Volcano-style executor, after a system called Volcano, about which Goetz Greafe published several very interesting papers in the early to mid 1990s. PostgreSQL was in its infancy in those days, but many of the concepts in the Volcano papers have made their way into PostgreSQL over the years. It may also be that Volcano took inspiration from PostgreSQL or its predecessors; I'm not entirely sure of the history or who took inspiration from whom.

Álvaro Herrera: PostgreSQL 12: Foreign Keys and Partitioned Tables

14. Oktober 2019 - 9:55
Now that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they […]

Dimitri Fontaine: Compute database size

14. Oktober 2019 - 0:05
Photo by unsplash-logoCharles 🇵🇭 It is well known that database design should be as simple as possible, and follow the normalization process. Except in some cases, sometimes, for scalability purposes. Partitioning might be used to help deal with large amount of data for instance. But what is a large amount of data? Do you need to pay attention to those scalability trade-offs now, or can you wait until later?

Dimitri Fontaine: Table of Content

14. Oktober 2019 - 0:05
Photo by unsplash-logoNicole Honeywill / Sincerely Media Each part of The Art of PostgreSQL can be read on its own, or you can read this book from the first to the last page in the order of the parts and chapters therein. A great deal of thinking have been put in the ordering of the parts, so that reading “The Art of PostgreSQL” in a linear fashion should provide the best experience.

Daniel Vérité: Nondeterministic collations

14. Oktober 2019 - 0:00

Since version 12, PostgreSQL collations are created with a parameter named deterministic, that can be true or false, so that collations are now either deterministic (which they are by default), or nondeterministic.

What does that mean? This term refers to what Unicode calls deterministic comparisons between strings:

Regina Obe: PostGIS 3.0.0rc2

13. Oktober 2019 - 2:00

The PostGIS development team is pleased to release PostGIS 3.0.0rc2. This will be the final RC before release.

This release works with PostgreSQL 9.5-12 and GEOS >= 3.6

Best served with PostgreSQL 12 , GEOS 3.8.0 and pgRouting 3.0.0-alpha.

Continue Reading by clicking title hyperlink ..

Avinash Kumar: How to Set Up Streaming Replication in PostgreSQL 12

11. Oktober 2019 - 16:21

PostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no