Wednesday, August 16

Processing PostgreSQL JSON & JSONB data in Java

Umair's PlanetPostgreSQL
This is part of a series of blogs on Java & PostgreSQL. For links to other blogs in this series, please scroll to the end of this post. Starting v9.2, PostgreSQL is providing native data type support for JSON objects. Subsequent releases introduced JSONB (binary formatted JSON objects) and many data manipulation functions for JSONs, making it a very powerful tool for NoSQL operations. String manipulation and parsing are very expensive operations in a database, so although you could have potentially stored JSON objects in strings in PostgreSQL before, introduction of the native data type has taken away overheads and made throughput a lot faster for JSON manipulation. JSON & JSONB JSONB was introduced as a native data type in v9.4 of PostgreSQL and it stores JSON objects (more…)
Explaining CREATE INDEX CONCURRENTLY

Explaining CREATE INDEX CONCURRENTLY

2ndQuadrant, Pavan's PlanetPostgreSQL
This technical blog explains how CREATE INDEX CONCURRENTLY (CIC) works and how it manages to avoid locking the table from updates. A unique distinguishing factor of CIC is that it can build a new index on the table, without blocking it from updates/inserts/deletes. But even before that, let's understand how Heap-Only-Tuple (HOT) works. It was a landmark feature added in PostgreSQL 8.3 to reduce table bloat and improve performance significantly. But the feature also has some implications on the working of CIC. Heap-Only-Tuple (HOT) PostgreSQL uses multi-version concurrency control (MVCC) for transactions. In this technique, when a row is updated, a new version of the row is created and the old version is left unchanged. Each version has creator and destroyer information and transaction (more…)

Dataloss at GitLab

2ndQuadrant, Simon's PlanetPostgreSQL
GitLab, thanks for using PostgreSQL 9.6 and its replication and backup facilities. We're sorry that you lost your database: https://about.gitlab.com/2017/02/01/gitlab-dot-com-database-incident/ Thank you for posting this publicly to allow us to comment on this for your postmortem analysis. I'm very happy that you monitor Replication Lag, that is good. Replication lag of 4GB is at times normal, so shouldn't have caused major concern. I've recently fixed a bug in replication that caused replication to hang in some cases for up to a minute; we released a public fix to that and it will be included in the next maintenance release of PostgreSQL 9.6. It's not certain that the bug was hit and, if it was, whether that was enough to cause the slow down noted. The openness of your response (more…)
pgDay 2017 à Paris: conférence PostgreSQL internationale

pgDay 2017 à Paris: conférence PostgreSQL internationale

Cedric's FR PlanetePostgreSQL
Un événement communautaire Cette année encore 2ndQuadrant aide la communauté PostgreSQL en France en étant Partenaire du pgDay 2017 à Paris Cette journée de conférences, en anglais exclusivement, est une opportunité unique d'en apprendre plus sur le fonctionnement et l'activité de PostgreSQL. Sécurité, benchmarks, supervision, roadmap pour la version 10, réplication, ... de nombreux sujets, variés et d'actualité. A noter qu'il n'est pas nécessaire de savoir lire Shakespeare dans la langue pour comprendre ce qu'il va se dire, c'est donc également une bonne occasion de renforcer votre anglais technique! 2ndQuadrant tient à remercier Vik Fearing pour sa très forte implication dans l'organisation de cet événement communautaire appuyé par l'association Européenne (more…)

repmgr 3.3

Ian's PlanetPostgreSQL
repmgr 3.3 introduces a number of additional options for setting up and managing replication clusters, with particular emphasis on cascading replication support. These changes will also make it easier to set up complex clusters using provisioning scripts. Additionally there are changes to the repmgr command line utility's logging behaviour which you should take into consideration when running therepmgrd daemon. repmgr is also tracking developments in the next major PostgreSQL release, 10.0, which will bring a lot of changes to the way PostgreSQL handles replication. At the time of writing, repmgr will work with the current PostgreSQL development code, but this combination is of course not suitable for use in production. Changes to logging behaviour Traditionally the repmgr command (more…)

What does pg_start_backup() do?

2ndQuadrant, Simon's PlanetPostgreSQL
Reading mailing lists can damage your health, as I recently discovered on the PostgreSQL Performance list where backup was being discussed. First off, don't read blogs for finding out critical pieces of info. Read the docs because they are accurate, fully reviewed and well maintained. I should add that I was the initial author of them as well, so maybe it's OK to carry on reading... pg_start_backup() is a function we execute to start a base backup. It was part of the original API for physical backup introduced in PostgreSQL 8.0. It's now been mostly superceded by the replication command BASE_BACKUP, which is most frequently executed by the pg_basebackup utility. So what does a base backup actually do? Well, first we execute a checkpoint so that as many changed data blocks are (more…)

The rds_superuser role isn’t that super

Andrew's PlanetPostgreSQL
The Amazon RDS documentation blithely contains this statement: "When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions." But just how super is it? One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it's just an ordinary user. So if you're using more than one user in your RDS database, even if one or even all of them are rds_superusers, you're going to become very familiar with the GRANT command if (more…)

Barman 2.1 and the new –archive option

Giulio's PlanetPostgreSQL
Barman 2.1 Version 2.1 of Barman, backup and recovery manager for PostgreSQL, was released Thursday, Jan. 5. The new release, along with several bugfixes, introduces preliminary support for the upcoming PostgreSQL 10, and adds the --archive option to the switch-xlog command. switch-xlog --archive The new --archive option is especially useful when setting up a new server. Until now, the switch-xlog command used to force the PostgreSQL server to switch to a different transaction log file. Now, Barman also gives the --archive option, which triggers WAL archiving after the xlog switch, and forces Barman to wait for the archival of the closed WAL file. By default Barman expects to receive the WAL in 30 seconds, the amount of seconds to wait can be changed using the --archive-timeout (more…)

Managed Database Services – pros and cons

Andrew's PlanetPostgreSQL
Using a managed service is a very attractive proposition. You are offloading a heck of a lot of worry, especially when it comes to something as complicated and, let's face it, specialized as a database. Someone else will set it up for you, and back it up, and keep it running, without you having to worry too much about it. However, there are downsides. You can only get what the manager is offering. Often that's good enough. But needs change, and I have often seen people start with managed services, only to find that they want more than they can get that way. Just yesterday I received a complaint that the Redis Foreign Data Wrapper (which I have done a lot of work on) is not available on Amazon RDS. And that's completely understandable. Amazon only provides a limited number of extensions (more…)