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).
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,
INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
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?
The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some
…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
I have released version 4.19 of the PostgreSQL Buildfarm client. It can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-4_19.tgz
Apart from some minor bug fixes, the following changes are made:
Include the script's path in @INC. That means you can usually run the script from anywhere rather than just its own directory.
Set TZ after "make check" is run. This makes for slightly faster initdb runs in later stages.
make TAP tests run with --timer
change default log_line_prefix in config file to use %p instead of %c. That makes it easier to tie log messages to other messages that mention a pid
Add a module to log running commands to a file as it runs and replace critical uses of `` with the new procedure. That means we have better traces if
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.
A few weeks ago I explained
Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl's prove tool and Test::More to test initdb, pg_basebackup, etc.
Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more.
You can now use this to test your extensions.
pg_regress and its limitations
Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file
PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash.
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
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