Temporary tablespace – small notes

Lately I did some research and tests regarding special dedicated temporary tablespace so here is small summary of what I have found: (I described it on stackoverflow too) PostgreSQL tablespace is just a directory – no big deal. Plus – if you will use it...

Check real sizes of columns in tables

Do you need to know which columns takes most disk space in your tables? Here is one useful statement: with srcdata as ( select table_schema||'.'||table_name as _table, column_name as _column, count(column_name) over (partition by table_schema, table_name) as...

Playing with JSON data in PostgreSQL

still in TODO Overview: JSON is right now very “fashionable” data format. And as they say “everyone uses it” or “it powers the whole web” etc. I remember times when XML format was “the coolest of all” and now it is...

PostgreSQL and time zones

If you do not feel anything special when someone says “timezones” then you can consider yourself to be lucky. If your application runs only in your local time zone a you never ever need to convert date/time between timezones then you are really lucky. But...

Find database creation date

We cannot find it directly but we can check attributes of directories which contain data files of each database. In PostgreSQL you can try this query: with mydir as (select setting||'/base' as _dir from pg_settings where name = 'data_directory'), mydbs as (select oid...