Thanks to Thomas Vondra s presentation CREATE STATISTICS What is it for? at Nordic PGDay 2018, my Trustly colleague Claes Jakobsson came up with the idea for a new extension. The presentation explained how to manually compare the cost rows with the actual rows output from manually executing EXPLAIN ANALYZE. Claes came up with the [ ]
I couldn t find any graph showing all the relations between all the pg_catalog tables, so just for fun I wrote a little script to parse the SGML and generate a graph using GraphViz.
If you are a DBA and ever have had to manually call pg_terminate_backend() to kill some misbehaving backend process, then this tool might be of interest. Long running queries are not a problem, as long as they don t force other important processes to wait. Another classic is a human user being forgetting to COMMIT. Please [ ]
I find myself quite often having to split a large write operation such as updating millions of rows in a heavily used table. This usually boils down to writing a small Perl script that runs a small one-off function that performs the task by updating a limited numbers of rows per run, and then committing in [ ]
By coincidence I stumbled upon a table where one of its columns didn t have a foreign key. I found this strange, since our convention is to always add foreign keys when appropriate. But humans are humans, and will eventually forget to add a foreign key, so we better have both belt and suspenders and find [ ]
Slides from my talk, How we use PostgreSQL at Trustly, are now available.
SET TIMEZONE TO 'UTC'; \t \a \pset fieldsep ' ' SELECT (('epoch'::timestamptz + 14*10^8 * '1 s'::interval)-now())::interval(0), (14*10^8-extract(epoch from now()))::int, extract(epoch from now())::int ; \watch 1 09:18:28 33508 1399966492 09:18:27 33507 1399966493 09:18:26 33506 1399966494 09:18:25 33505 1399966495 09:18:24 33504 1399966496 09:18:23 33503 1399966497
Over the last five years, our database at Trustly have kept growing in number of tables, columns and functions, where some of the tables and columns aren t being used by any database functions any longer. Getting rid of them is important, as otherwise people working with the database will be confused and annoyed. Database developers [ ]
As pointed out by Peter Eisentraut in a blog post named Schema Search Paths Considered Pain in the Butt, you need to make sure the search_path is explicitly set for all SECURITY DEFINER functions in PostgreSQL. Fixing this manually for, in my case, 2106 functions, is, indeed a pain in the butt , so I crafted [ ]
Learning PostgreSQL and SQL in general probably begins with the concept of TABLES. Then probably VIEWS, INDEXES and maybe TRIGGERS. Some users might not ever go any further, which is sad, because there is so much more to explore! I thought it would be cool to automatically generate a graph showing the dependencies between objects [ ]