Table size in PostgreSQL

Disk space seems endless, until you run out and/or have to pay the bill. The question is how to find tables with a high disk storage usage and with the query below it shows the table and index size, but also the size of TOAST data for PostgreSQL.

SELECT schemaname, tablename,
  pg_size_pretty(tsize) AS size_table,
  pg_size_pretty(size) AS size_index,
  pg_size_pretty(total_size) AS size_total
FROM (SELECT *,
        pg_table_size(schemaname||'.'||tablename) AS tsize,
        pg_relation_size(schemaname||'.'||tablename) AS size,
        pg_total_relation_size(schemaname||'.'||tablename) AS total_size
      FROM pg_tables) AS TABLES
WHERE schemaname='public'
ORDER BY total_size DESC;

After running this query on the development schema and exporting the results to CSV, we can see that a ManyToMany table consumes a total of 39 MB. With over 330.000 entries this seems numbers seem to be fine as the table size is in line with the amount of data stored in it.

schemaname,tablename,size_table,size_index,size_total
public,domain_asset_domain_asset_group,12 MB,12 MB,39 MB
public,domain_account_domain_function,2960 kB,2936 kB,9720 kB
public,domain_account,1760 kB,1728 kB,4088 kB
public,domain_command,2016 kB,1992 kB,3528 kB
public,person,832 kB,792 kB,1736 kB
public,domain_command_collection,712 kB,688 kB,1248 kB
public,domain_asset_group,648 kB,624 kB,1160 kB
public,domain_asset,544 kB,520 kB,1088 kB
public,domain_function,440 kB,416 kB,784 kB
public,sessions,64 kB,32 kB,80 kB
public,asset_application,8192 bytes,8192 bytes,56 kB
public,domain_authority,8192 bytes,8192 bytes,40 kB
public,asset_function,8192 bytes,0 bytes,24 kB

Collecting this data and graphing it may help spot problems and predict storage needs. It may help DevOps teams to figure out if their databases are growing and with what speed.

Increasing Inotify Watches Limit

After upgrading to PyCharm 2017.2 the notice came that inotify value was too low and the IDE would fallback to recursive directory scanning. For now the following commands increase the inotify limit to 512k of files.

$ cat <<EOF | sudo tee /etc/sysctl.d/idea.conf
fs.inotify.max_user_watches = 524288
EOF
$ sudo sysctl -p --system
...
* Applying /etc/sysctl.d/idea.conf ...
fs.inotify.max_user_watches = 524288
...

It is still interesting why PhpStorm wasn’t complaining while the Symfony projects are much larger.

Emoji in URLs are probably a bad idea…

On the dns-operations mailing list there were already discussions about parties who bought domains like ♀.com (xn--e5h.com), but the following is also an interesting development.


When will we find pages with “special” Web Open Fonts and that become active when you press Ctrl-Shift?

Using explicit SSH authentication methods

For many SSH is a magic sauce to get access to a server and to transfer files between servers. But when things go wrong this magic sauce becomes a problem. Let start with one an example when things go wrong and how to debug it. First we start to add to option -v to our command to connect to another server to get some basic debug information about the SSH handshake and getting to the point the user has to authenticate.

$ ssh -v user@host.example.org
...
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic,password
debug1: Next authentication method: password
user@host.example.org's password:

Just before the SSH-client prompts for the users password two interesting debug lines are shown. The first line is about the authentication methods we can use and next line shows the our client selected method password as we don’t have any methods configured in our SSH-client like publickey. So we manually disable publickey authentication and set the preferred authentication methods to keyboard-interactive.

$ ssh -v -o PreferredAuthentications=keyboard-interactive -o PubkeyAuthentication=no user@host.example.org
...
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic,password
debug1: No more authentication methods to try.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).

We now get a permission denied as our client doesn’t has a matching set of authentication methods. Over a decade ago some commercial SSH-servers would require keyboard-interactive as authentication method as the client must than ask the user to type in the password instead of getting it from a password file as was allowed with the password authentication method. Al lot of SSH-clients start to ignore this convention, but some enterprise environments still depend on this convention. If we add password to the list of preferred authentication method we see the password prompt is offered again.

$ ssh -o PreferredAuthentications=keyboard-interactive,password -o PubkeyAuthentication=no user@host.example.org
user@host.example.org's password:

This method can also be used to temporarily disable public key authentication without changing any SSH configuration to test of the account is still working correctly or the password of the target account is still working.

Massive file update with sed

Recently I generated kickstart files for a virtual environment where people could experiment and virtual machines could be rebuild quickly. Sadly enough a typo slipped into the generated files that would make the anaconda installer stop. Every kickstart file could be corrected by hand off course, but one sed command could also correct the typo in all files in one go.

$ sed -i 's/namesever/nameserver/' *.ks

The Unix toolkit is full of handy tools and options like this and it pays to get to know your environment. Specially when it is your work environment and you’re familiar with the Unix philosophy.