Neues vom PostgreSQL Planet

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

Hugo Dias: PostgreSQL Deployment & Configuration with Puppet

20. November 2019 - 21:17

Puppet is open source software for configuration management and deployment. Founded in 2005, it’s multi-platform and even has its own declarative language for configuration.

Ibrar Ahmed: Proposal for Global Indexes in PostgreSQL

20. November 2019 - 16:26

A global index, by very definition, is a single index on the parent table that maps to many underlying table partitions. The parent table itself does not have a single, unified underlying store so it must, therefore, retrieve the data satisfying index constraints from physically distributed tables. In very crude terms, the global index accumulates data in one place so that data spanning across multiple partitions are accessed in one go as opposed to individually querying each partition.

Doug Hunley: Enhancing PostgreSQL 12 Security with the CIS Benchmark

20. November 2019 - 16:12

Crunchy Data has recently announced an update to the CIS PostgreSQL Benchmark by the Center for Internet Security, a nonprofit organization that provides publications around standards and best practices for securing technologies systems.

Hans-Juergen Schoenig: effective_cache_size: What it means in PostgreSQL

20. November 2019 - 9:00

Many PostgreSQL database users might have stumbled over the effective_cache_size parameter in postgresql.conf. But how can it be used to effectively tune the database and how can we speed up PostgreSQL using effective_cache_size? This blog will hopefully answer some of my readers’ questions and reveal the hidden power of this secretive setting.

Pavel Stehule: using pspg for SQLite

20. November 2019 - 6:54
pspg can be used for SQLite too. Not directly, because sqlite client is not too rich. But there are some possibilities:

a) you can use litecli. litecli is able to use pspg

b) generate CSV from sqlite client - this format is well readable for pspg

sqlite3 -csv -header testdb.db 'select * from foo2' | pspg --csv --csv-header=on --double-header

Thank you for tip to Trent W. Buck.

Leigh Halliday: Similarity in Postgres and Rails using Trigrams

19. November 2019 - 13:00
You typed "postgras", did you mean "postgres"? Use the best tool for the job. It seems like solid advice, but there's something to say about keeping things simple. There is a training and maintenance cost that comes with supporting an ever growing number of tools. It may be better advice to use an existing tool that works well, although not perfect, until it hurts. It all depends on your specific case. Postgres is an amazing relational database, and it supports more features than you might…

Paul Ramsey: OGR FDW Spatial Filtering

18. November 2019 - 9:00

The OGR FDW now pushes spatial filters down to remote data sources!


The Basics

OK, first, “OGR” is a subcomponent of the GDAL toolkit that allows generic access to dozens of different geospatial file formats. The OGR part handles the “vector” data (points, lines and polygons) and the GDAL part handles the “raster” data (imagery, elevation grids).

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Introduce the ‘force’ option for the Drop Database command.

16. November 2019 - 14:02
On 13rd of November 2019, Amit Kapila committed patch: Introduce the 'force' option for the Drop Database command.     This new option terminates the other sessions connected to the target database and then drop it. To terminate other sessions, the current user must have desired permissions (same as pg_terminate_backend()).

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Allow sampling of statements depending on duration

16. November 2019 - 13:56
On 6th of November 2019, Tomas Vondra committed patch: Allow sampling of statements depending on duration   This allows logging a sample of statements, without incurring excessive log traffic (which may impact performance). This can be useful when analyzing workloads with lots of short queries.

Christophe Pettus: Why submit a paper to PgDay 2020?

15. November 2019 - 18:00

If you have something interesting to day about PostgreSQL, we [would love to get a proposal from you]. Even if you have never spoken before, consider responding to the CfP! PgDay 2020 is particularly friendly to first-time and inexperienced speakers. You’re among friends! If you use PostgreSQL, you almost certainly have opinions and experiences that others would love to hear about… go for it!

Beena Emerson: Investigating bulk load operation in partitioned tables

14. November 2019 - 12:22
Partitioning introduced in PostgreSQL 10 increases performance in certain scenarios. In PostgreSQL 13, pgbench was extended to run benchmark tests for range and hash partitions by partitioning the pgbench_accounts table according to the parameters provided. Since pgbench_accounts is the largest table, the bulkload command COPY is used to populate it and pgbench logs the time taken to insert in that table. This blog will explore how this operation is affected by table partitioning. 

Christophe Pettus: PgDaySF 2020!

13. November 2019 - 21:26

The very first PgDay San Francisco is coming to the Swedish-American Hall on January 21, 2020. It’s going to be an amazing event.

If you have something to say about PostgreSQL…

… the Call for Proposals is now open through November 22, 2019. We are looking for 40 minute talks about anything related to PostgreSQL. First-time speakers are particularly encouraged to send in proposals.

Craig Kerstiens: An interview on what makes Postgres unique (extensions)

13. November 2019 - 15:00

I’ve been at dinners before with developers that admitted developers, themselves included, can be a bit opinionated. In one case one said for example, “I love Postgres, but I have no idea why.” They were sitting at the wrong table to use Postgres as an example… But it is quite often that I am asked Why Postgres.

Vik Fearing: pgDay Paris 2020 - Call for Sponsors Open

13. November 2019 - 10:00

The call for sponsors for pgDay Paris 2020 is now open. There are two levels available: Partner and Supporter. The Partner level, which gives you a booth at the event, is limited to just five spots so hurry up and get yours now before it's too late!

Florian Nadler: Open Street Map to PostGIS – The Basics

13. November 2019 - 9:00
OSM to PostGIS – The Basics

Ever wondered how to import OSM (OpenStreetMap) data into PostGIS [1] for the purpose of visualization and further analytics? Here are the basic steps to do so.
There are a bunch of tools on the market— osm2pgsql; imposm; ogr2org; just to mention some of those. In this article I will focus on osm2pgsql [2] .

Paul Ramsey: ST_Subdivide all the Things

13. November 2019 - 9:00

This post originally appeared in the CARTO blog.

One of the things that makes managing geospatial data challenging is the huge variety of scales that geospatial data covers: areas as large as a continent or as small as a man-hole cover.

The data in the database also covers a wide range, from single points, to polygons described with thousands of vertices. And size matters! A large object takes more time to retrieve from storage, and more time to run calculations on.

Hubert 'depesz' Lubaczewski: More functions for json/yaml/xml plans on

13. November 2019 - 6:15
Added two new functions, which basically work only for JSON / YAML / XML plans. First – the view of source explain is now syntax highlighted. Just click source button on one of these to see: JSON YAML XML Next additon is text tab, which shows the same explain but converted to text-format. Hope you'll … Continue reading "More functions for json/yaml/xml plans on"

Hubert 'depesz' Lubaczewski: New Pg::Explain and

12. November 2019 - 23:41
Just now pushed new version of Pg::Explain Perl library that is handling parsing for There have been many changes, but the short summary is: Fix display of heap fetches from json ( Move global data (planning/execution/total time, trigger info) from top_node to explain object itself Add method to easily get real total, wall-clock, runtime … Continue reading "New Pg::Explain and"

Dimitri Fontaine: List PostgreSQL tables using extensions

12. November 2019 - 18:30

Postgres has extensions, and that’s awesome! Of course as the author of CREATE EXTENSION I’m a little biased… just remember that the ability to extend Postgres is way more than just this command. The whole database system has been design from the ground up to allow for extensibility. Parts of the design is to be found in the way you can register new objects at runtime: functions of course, and also data types, operators, index support structures such as operator classes and families, even index access methods!

Luca Ferrari: PostgreSQL ascii logo for FreeBSD boot loader

12. November 2019 - 1:00

I spent some time making an elephant logo to be used as FreeBSD boot loader logo.