Posts

Featured post

pgCenter - stress free Postgres stats.

pgCenter has seen a new release with several significant changes. If you used pgCenter before, you might have noticed that I stopped issuing updates for a while. This is because I was working on this new release with several new features and overall improvements that will make the use of the tool easier and will allow additional functionality to make your life easier when working with Postgres stats.

Keeping Postgres tidy with partitioning

If you are in doubt whether partitioning is a useful tool with this one example I'm hoping you won’t wonder any further.

Why avoid long transactions?

The majority of PostgreSQL community clearly understands why long and idle transactions are “bad”. But when you talk about it to the newcomers it’s always a good idea to backup your explanation with some real tests.

While preparing slides for my presentation about vacuum I have made a simple test case with long transaction using pgbench. Here are the results.

Global shortcuts and PostgreSQL queries.

Image
Using your favorite hotkeys on queries in LinuxOne of my colleagues often talks about using hot keys for his favourite SQL queries and commands in iterm2 (e.g. for checking current activity or to view lists of largest tables). 
Usually, I listen to this with only half an ear, because iterm2 is available only for MacOS and I am a strong Linux user. Once this topic came up again I thought perhaps this function could be realised not only through iterm2 but through an alternative tool or settings in desktop environment.

Autovacuum slides from PgCon 2018 Ottawa

Slides from our talks at PgCon 2018.
This week was busy for me and my colleague Ilya Kosmodemiansky as we made our way to Ottawa, Canada to attend one of the focal events in Postgres - PGCon. This PostgreSQL conference for users and developers runs annually and gathers PostgreSQL enthusiasts from across the globe. Aside fascinating and inspiring talks it’s a fantastic networking opportunity.

This year’s conference started on 29th May with tutorials, one of which was Ilya’s tutorial Linux IO internals for database administrators which was really well attended. The main conference started on 31st May and on the 1st June I gave my talk about autovacuum.

The aim of my talk was to address some of the key vacuum misconceptions and to make the audience realise that autovacuum is actually an essential tool for making database faster, more efficient and problem free and that disabling it would actually cause more damage than good. Below are my slides and some photos from the conference.

I must …

Let’s speed things up.

Image
Parallelism settings and how to accelerate your database performance.Few days ago, when performing a routine audit for our client, we noticed that parallel queries in the database were disabled. It wouldn’t be surprising but it’s important to note that our client has powerful servers that run not only lightweight OLTP queries. When we brought it to our client’s attention his first question was "How can we enable it?" and after that he reasonably added "Will there be any negative consequences if we do?"

This kind of question pops up more often than one thinks, hence this post where we look into parallel queries in detail.




pg_wal is too big… what's going on?

What’s common between DBA and detective? They both solve murder and mystery while trying to make sense of the nonsense. Recently, one of our clients had Postgres crash due to the lack of free space on the database server. Client's reaction was instantaneous - a hot standby was immediately promoted to master and the application continued working. Some details about the crash were saved and since it’s the age of clouds, devops and microservices, the failed host was recycled into a new hot standby.

Once the dust has settled, the client gave us saved information about the failure asking: What was it? How can I avoid it in the future?
The first thing that we’ve done is what any good detective would do - started looking for clues. Client provided us with crash logs and said that pg_xlog directory was suspiciously big while there were only around 2 thousand files with 16MB size each. We looked into postgresql.log to see what were the last lines before crash:

PANIC: could not write to fi…