For the last few months, we at 2ndQuadrant have been working on merging PostgreSQL 9.6 into Postgres-XL, which turned out to be quite challenging for various reasons, and took more time than initially planned due to several invasive upstream changes. If you’re interested, look at the official repository here (look at the “master” branch for now).There’s still quite a bit of work to be done - merging a few remaining bits from upstream, fixing known bugs and regression failures, testing, etc. If you’re considering contributing to Postgres-XL, this is an ideal opportunity (send me an e-mail and I’ll help you with the first steps).But overall, Postgres-XL 9.6 is clearly a major step forward in a number of important areas.(more…)
Let me discuss a topic that is not inherently PostgreSQL specific, but that I regularly run into while investigating issues on customer systems, evaluating "supportability" of those systems, etc. It's the importance of having a monitoring solution for system metrics, configuring it reasonably, and why sar is still by far my favorite tool (at least on Linux).(more…)
…and why I’m glad I did.It’s not all technical… Who knew?!Last year, Pycon7 was held right about the time I joined 2ndQuadrant. Seeing as I was new to the technology AND the Italian language (note: there was an English track), I opted out of attending. Well, after attending Pycon8, I can say that I won’t make that mistake again!Over the past year working in the Open Source community, I’ve learned more technical information than I could have ever imagined. Even then, attending a technical conference and understanding (completely) technical talks seemed a little far-fetched. Or so I thought!Since being introduced to the wonderful world of Open Source, PostgreSQL, and numerous other technologies - I’m continuously fascinated by the way that the communities intertwine. These te
A few weeks ago I explained basics of autovacuum tuning. At the end of that post I promised to look into problems with vacuuming soon. Well, it took a bit longer than I planned, but here we go.To quickly recap, autovacuum is a background process cleaning up dead rows, e.g. old deleted row versions. You can also perform the cleanup manually by running VACUUM, but autovacuum does that automatically depending on the amount of dead rows in the table, at the right moment - not too often but frequently enough to keep the amount of "garbage" under control.(more…)
It’s still appropriate though. Because that is what we are - thought leaders in open source PostgreSQL. But that's not what I am here to talk about.The name "2ndQuadrant" comes from "The Seven Habits of Highly Effective People" by Stephen Covey, specifically Habit 3 "Put First Things First", p.151. It refers to the classification of tasks in terms of 2 axes: Importance and Urgency. The second quadrant is the Important, Not Urgent quadrant. According to Covey, if you concentrate on doing work in that space, it leads to "vision, perspective, balance, discipline, control and few crises" - i.e. you focus on your long-term vision and your ability to execute short to medium term goals is also impeccable.These are all qualities highly valued by every individual at 2ndQuadrant; so th
Great conference! Paris is a great venue for travellers across Europe and worldwide. PgDay Paris 2017 was held in English and attracted a wide audience from many other countries: UK, NL, CH, BE, US, SE - and that was just the people I spoke to.Je suis desolee ne parler ou ecrit pas en francais. Je suis un developpeur seulement.I spoke in English about the new features in PostgreSQL 10 regarding Replication & Recovery. All very well received by a large technical audience. Logical replication, physical replication improvements, quorum commit, replication lag measurement and a ton of fine detailed improvements.No slides, sorry. Come to the conferences! Meet people, hear their stories and share yours.I travelled to Paris through London on a day of public murders that made news ...
Yet another edition of PGConf India came to conclusion in early March. You may have noticed the change from PGDay to PGConf, which signals a much larger gathering of PostgreSQL enthusiasts, now and in future. What started as a small meet-up of like minded people 4 years back, has now grown into a 2-day conference with a dedicated training day and a multi-track event at the main conference.The keynote this year was delivered by Simon Riggs, a major developer and committer at PostgreSQL. He presented his thoughts on why Persistence is key to PostgreSQL's success. Persistence is why users trust PostgreSQL to manage their critical data and persistence is why PostgreSQL community is able to deliver a solid product, release after release.This year's conference was attended by more th
A few weeks ago I covered the basics of tuning checkpoints, and in that post I also mentioned that the second common source of performance issues is autovacuum (based on what we see on the mailing list and at our customers under support). So let me follow-up on that with this post about the basics of autovacuum tuning. I'll very briefly explain the necessary theory (dead tuples, bloat and how autovacuum deals with it), but the main focus of this blog post is tuning - what configuration options are there, rules of thumb, etc.(more…)
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...
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 ...