Neues vom PostgreSQL Planet

jsonb supports, unsurprisingly, JSON

jsonb is, undeniably, king. It is a very flexible data type, that allows for unstructured/schema-less storage. It has very powerful indexing mechanisms, and its internal representation is reasonably compact and efficient.

Daniel Gustafsson has done some terrific work on using NSS as an alternative TLS library to OpenSSL for PostgreSQL. I’ve done some work making that build and run on Windows. Daniel recently asked how to get a working NSS on Windows to use for development, and this blog is about that process. First you need […]

Have you ever wondered how invalid or ambiguous times are handled by Postgres? For example, during a daylight saving time transition in the USA, time switches either from 2AM to 3AM, or from 2AM back to 1AM. On a fall-forward day (the former), how would 2:30am be represented? Is 1:30AM during a fall-back day represented as the first or second 1:30AM of that day?

The extension plpgsql_check can be used like linter, validator for PLpgSQL language. It can be used like profiler with possibility to calculate some basic coverage metrics. And now it can be used for code tracing. In this mode, plpgsql_check raises notice when function or statent is starting or is fininshing: Example of output in default verbosity level:
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;

An introductory story (with some slight “drama” added for fun) from those good old days of on-site consulting So…I’m at a client where the database is not behaving nicely among other things…what a crappy DB product indeed I hear, it gets cranky every morning although there should be a constant workload and is saturating the CPU and making some important queries time out!

PostGIS is a "geospatial database" and the "geo" in "geospatial" is an important qualifier: it means that all the coordinates in PostGIS point, lines, and polygons can be located somewhere on the earth.

What is the difference between two dates? You would think there was one answer, but there isn't. You can give an answer in calendar terms (years/months/days), the number of days, or the number of seconds. Postgres offers all of these options:

PostgreSQL Person of the Week Interview with Tatsuo Ishii: I live in Kanagawa prefecture of Japan, which is adjacent to Tokyo, with my wife. I love to walk beaches, listen to music (classical, rock and Jazz) from my favorite audio set (mainly consisting of TANNOY speakers and Luxman amplifiers), and read Sci-Fi books. I am working for SRA OSS, Inc. Japan as a branch manager and an engineer since 2005.

The INTERVAL data type stores time duration as months, days, and seconds. Years are represented as a fixed number of months, and hours and minutes as a fixed number of seconds. Using INTERVAL values makes time computation very simple:

Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II.

One of the interesting new features in PostgreSQL for some time now is the ability to control removal of WAL files using replication slots. The dark side is that replication slots can cause disks to fill up with old WAL, killing the main production server. In this article I explain PostgreSQL replication slots, and how a new feature in PostgreSQL 13 helps prevent this problem.

PostgreSQL 13 has introduced a simple but extremely useful capability to log a stack trace into the server logs when an error is reported. Let's see the details.

There is a GUC to enable stacktrace generation : backtrace_functions. Set it to a comma-separated function names.

SET backtrace_functions TO 'func1,func2';

If the error is thrown from one of these functions, a backtrace will be generated and logged into the server log.

A lot has been written about effective_cache_size in postgresql.conf and about PostgreSQL performance in general. However, few people know what this famous parameter really does. Let me share some more insights.

In my years with Postgres, I have seen some amazingly complex queries posted to the email lists. I have never understood how people can read complex queries with no formatting, e.g., no keyword capitalization, no indenting, no line breaks for new clauses:

Backups are a key staple of running any database. Way back in the day, a good friend and colleague wrote one of the most used Postgres backup tools called wal-e. Wal-e was initially written in just a few days, and rolled out to the fleet of databases we managed in the early days at Heroku.

The PostgreSQL 13 Beta is out in the testing phase with a rich feature set. It is a very good learning effort to participate in the testing of one of the finest databases in the world. It does not matter how much development, coding, and administration experience you have for the testing of the PostgreSQL version; you can participate in reviewing the documentation, validation of features, and on some small tasks. The complete testing guide is also available on the wiki page.

Making security easy to use is crucial because hard-to-use security is likely to be neglected entirely. SCRAM with channel binding is a variation of password authentication that is almost as easy to use, but much more secure.

In basic password authentication, the connecting client simply sends the server the password. Then the server checks that it’s the right one, and allows the client to connect. Basic password authentication has several weaknesses which are addressed with SCRAM and channel binding.

There seem to be as many methods of writing SQL queries as ways of writing essays. While spacing, capitalization, and naming are all personal preferences, there are also logical arguments for why certain styles are better than others.

PostgreSQL Person of the Week Interview with Gilberto Castillo: My name is Gilberto, I live in Havana, Cuba. I’m married, with three sons. I love sports and I exercise together with my sons. My favorite sports are handball, baseball and basketball. I also enjoy dancing to all kind of music. Havana is the Capital of all Cubans, my recommendation to everyone is: do not miss the opportunity to visit the history places Havana has to offer!

Quite a lot of people have been bugging me about it, and finally got some time, and worked on it. Long story short – it works. You can add your query below plan (in separate text area), and then it will be displayed as one of tabs For example, check this plan. It also works … Continue reading "You can now add query to your plan on explain.depesz.com"