You probably know that Postgres-XL is a distributed database based on PostgreSQL. A few days ago we pushed the XL 9.6 code into the public git repository. Additional details about the new stuff available in Postgres-XL 9.6 are available here.The topic of this blog post is quite different, though. I'd like to discuss some changes to the project management and development practices, and why (and how) we plan to tweak it.(more…)
In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year.SortingSorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you ha...
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…)
For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns:CREATE TABLE test_old ( id serial PRIMARY KEY, payload text );INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;andCREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, payload text );INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;do pretty much the same thing, except that the new way is more verbose. ;-)So why bother?CompatibilityThe new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some...
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…)
I just committed a patch by Pavel Stěhule that adds the XMLTABLE functionality to PostgreSQL 10. XMLTABLE is a very useful feature dictated by the SQL/XML standard, that lets you turn your XML data into relational form, so that you can mix it with the rest of your relational data. This feature has many uses; keep reading for some details on it.(more…)
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…)
It has taken a lot of planning and efforts, but I'm happy to see that PGConf India 2017 is coming along very well and promises to be a great event. This is our third year in a row in Bengaluru, but there are many distinguishing factors that make this year stand out:For the first time, we're having a dedicated training day ahead of the main conference. All seats for the trainings are sold out and we had to turn away many interested folks. For the first time, we're having a multi-track conference. And for the first time, we've crossed 225 conference delegate registrations, and there are still a few days left.When we started planning for the conference, we were a bit nervous about whether we could get a good response to the training programme, but we’re almost stumped by the p
While tweaking postgresql.conf, you might have noticed there's an option called full_page_writes. The comment next to it says something about partial page writes, and people generally leave it set to on - which is a good thing, as I'll explain later in this post. It's however useful to understand what full page writes do, because the impact on performance may be quite significant.Unlike my previous post on checkpoint tuning, this is not a guide how to tune the server. There's not much you can tweak, really, but I'll show you how some application-level decisions (e.g. choice of data types) may interact with full page writes.(more…)