Simon’s PlanetPostgreSQL

Mar132018 by Simon RiggsNo Comments

PostgreSQL – The most loved RDBMS

The 2018 StackOverflow survey has just been published, with good news for PostgreSQL. https://insights.stackoverflow.com/survey/2018/#technology-most-loved-dreaded-and-wanted-databases StackOverflow got more than 100,000 responses from people in a comprehensive 30 minute survey. PostgreSQL is the third most commonly used database, with 33% of respondents, slightly behind MySQL and SQLServer, yet well ahead of other options. Early in January, the DBEngines results showed PostgreSQL in 4th place behind Oracle, yet here we see that actually Oracle heads up the Most Dreaded list along with DB2, leaving PostgreSQL to power through to 3rd place. PostgreSQL at 62% is the second most loved database, so close behind Redis (on 64%) that they're almost even. But then Redis is only used by (more…)

Feb202018 by Simon Riggs3 Comments

PostgreSQL Maximum Table Size

Simon's PlanetPostgreSQL

Various limits on the PostgreSQL database are listed here: https://www.postgresql.org/about/ One of those limits is the Maximum Table Size, listed as 32TB. It's been that way for many years now. Only problem is that it has always been wrong, slightly. And now its got much, much bigger. The table size is limited by the maximum number of blocks in a table, which is 2^32 blocks. The default block size is 8192 bytes, hence the default limit was 32TB as listed. That was wrong in a two ways, because PostgreSQL has always had a configurable block size which allows up to 32768 bytes per block, which would give a maximum size of 128TB. Only problem is that requires an unload/reload to change the block size, so the effective limit per table was 32TB as advertized. PostgreSQL has always (more…)

Feb22018 by Simon RiggsNo Comments

PostgreSQL Developer Meeting Brussels

2ndQuadrant, Simon's PlanetPostgreSQL

The PostgreSQL Developer Meeting went well with attendees from UK, Czechia, US, Russia and Germany. The meeting is in Brussels, near the FOSDEM conference. Andreas Seltenreich was the new face at the meeting. Andreas' sqlsmith tool has found many PostgreSQL bugs, so we discussed extending that for the latest features in PostgreSQL 11. We discussed all of the patches in the queue and moved a few forwards quickly. With more than 200 patches in the queue there is much work still to do and the last Commitfest hasn't even started yet. Bruce was able to commit a patch to improve the usability of psql with commands exit and quit. Finally the usability annoyance of people not remembering "\q" command ends in 2018! I've taken on shepherding the new WAIT FOR command which should allow us (more…)

Jan302018 by Simon Riggs7 Comments

Upcoming Enhancements to Partitioning & Indexes in PostgreSQL 11

Simon's PlanetPostgreSQL

My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It's worth explaining what these are rather than trying to piece together what is happening from reading commit messages. The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks. To achieve that, we need to understand the structure of features in PostgreSQL. Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also. Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I'll write about partitioned indexes (more…)

Jan102018 by Simon RiggsNo Comments

PostgreSQL Meltdown

Simon's PlanetPostgreSQL

Spectre and Meltdown have caused severe alarm in recent days. You may have read about up to 30% impact on PostgreSQL databases, which I believe to be overstated because of misunderstandings in the media. Let's dig into this in more detail. TL;DR Summary: no PostgreSQL patch required, -7% performance hit In response to these new security threats various OS patches have been released. Various authors have published benchmarks around these and they have, in some cases, stated worst-case measurements as impact measurements. For example: stating a 30% hit when, in fact, we are seeing a 7% hit on a busy server. Regrettably, it looks to me like some people outside the PostgreSQL community have spread this news as a problem for PostgreSQL, without clearly stating the workload measured, or (more…)

Jan102018 by Simon Riggs2 Comments

PostgreSQL is the DBMS of the Year 2017

Simon's PlanetPostgreSQL

The annual DBEngines ranking have just been updated, with PostgreSQL winning the "DBMS of the Year 2017" award. https://db-engines.com/en/blog_post/76 PostgreSQL was a runner up last year. https://db-engines.com/en/blog_post/67 So across the year, we have made more gains in popularity than all other databases. Looking at the detail we see that all of the other 4 databases that make up the Top 5 have reduced in popularity from last year. PostgreSQL has gone up, others have gone down. What's even more interesting, is that PostgreSQL is the only database in the top 5 systems that has increased steadily over the last 4 years. Slow, steady progress. The word is out! Why is that? PostgreSQL is multi-talented, offering relational features when needed, as well as JSON features for (more…)

Nov32017 by Simon Riggs4 Comments

How long does it take to change your mind?

2ndQuadrant, Simon's PlanetPostgreSQL

You're clever, which means you're mostly right about things. But everybody is wrong sometime, so how long does it take for you to change your mind? People don't often change their minds quickly. A snap answer is always whatever you were thinking currently. If it was a No, you say No. If it was a Yes, you say Yes. If you answer too quickly you can't possibly have taken in what was being said to you. Can you change your mind? "When the facts change, I change my mind. What do you do, sir?", often misattributed to John Maynard Keynes. https://quoteinvestigator.com/2011/07/22/keynes-change-mind/ For Humans, changing your mind based on new information takes days or months. If you have emotional objections, it can take months or years. If anyone has research on that, I'd be interested (more…)

Oct262017 by Simon Riggs11 Comments

Oracle High Availability concepts in PostgreSQL

2ndQuadrant, Barman, PostgreSQL, Simon's PlanetPostgreSQL

Sometimes people ask about certain Oracle High Availability features and whether they exist in PostgreSQL. In most cases, very similar features exist. The reason for the similarity is that PostgreSQL and Oracle have very similar architectures and so the mechanisms to protect data have also developed along the same lines. For example, Oracle Data Guard is streaming replication of the transaction log, so is very similar in concept to physical streaming replication in PostgreSQL. Active Data Guard is where Oracle users can run queries on a standby server, which again is similar in concept to Hot Standby. Oracle RAC? You don't really need Oracle RAC. Or at least qualified Oracle experts tell me so and my own hands on experience confirms that. Not least because disaster recovery for (more…)

Oct112017 by Simon Riggs32 Comments

Why PostgreSQL is better than MySQL

Simon's PlanetPostgreSQL

Someone recently tweeted about the fantastic news that MySQL fixed a bug. Now in my world, bugs get fixed quickly and well. Bugs happen and they need to be fixed. It never occurred to me that we should ever tweet or blog about the fixing of a bug. I guess I assume it's just quality: bugs get fixed, no drama - people depend upon us to do just that so that the (literally) millions of PostgreSQL servers out there run well. That's our job and I'm happy and proud to do that job alongside my colleagues at 2ndQuadrant and my colleagues in other PostgreSQL companies and in the wider community. So the bug in question was "number 199"... check this out http://lefred.be/content/bye-bye-bug-199/ It's always been a big argument in the PostgreSQL community about whether we need a bug tracker (more…)

Oct62017 by Simon RiggsNo Comments

PG10 at the London PostgreSQL Meetup

Simon's PlanetPostgreSQL

We recently hosted the London PostgreSQL Meetup at a new location in Covent Garden. Room was great and its about 100m from Leicester Square tube, so very centrally located and easy to get to. https://www.meetup.com/London-PostgreSQL-Meetup-Group/ I've been the organizer of the group for more than 6 years now, in my role as the PostgreSQL Project's UK Press representative, though a few others have also helped out, principally Bart Swedrowski, though now also Sam Marshall. Anybody interested in PostgreSQL is welcome to come along and we have many speakers from a variety of companies and types of company. Please come along and join in with the other 849 members. At our recent meeting we discussed all the new features of PostgreSQL 10. Lots of interest in the new SCRAM authentication (more…)