Neues vom PostgreSQL Planet

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.

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.

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.

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.

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.

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…

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

Whuuuut?!?!?

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).

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()).

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.

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!

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. 

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.

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.

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!

https://2020.pgday.paris/become-sponsor/

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] .

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.

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 explain.depesz.com"

Just now pushed new version of Pg::Explain Perl library that is handling parsing for explain.depesz.com. There have been many changes, but the short summary is: Fix display of heap fetches from json (https://gitlab.com/depesz/explain.depesz.com/issues/15) 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 explain.depesz.com"

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!

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