Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Optimizing Your psql

October 23, 2014

While it’s tempting to use the latest tools, there’s often a powerful enough solution that already exists. For connecting to and querying a PostgreSQL database, that tool is psql.

Commands

Most psql configuration can be done once the program is started using slash commands. For the entire list of commands, use \?.

It’s important to remember that configuration only persists for the session. To keep your config across all your sessions, create a .psqlrc file in your home directory.

Timing

Our favorite config option is \timing on\timing on tells psql to print the runtime of the query after execution. It’s great for figuring out why your queries are slow.

Null Printing

Another useful setting is \pset null. By default, NULL values show up as empty strings. This makes it impossible to tell if a nullable varchar is ’‘ or NULL. With \pset null ‘[NULL]’, psql will print the word [NULL] instead.

For example:

Extended Output

Want a better way for looking at each record individually? \x on is your command. It sets extended output. This prints each record as a series of rows, rather than in a table.

\x auto will automatically choose extended output after a certain number of rows and columns.

For example:

Config Variables

Like many shells, psql uses config vars to control some behaviors. Use \set to set your variable values.

History control

We often run the same or similar queries on our database. Having psql keep track of our query history is a great way to save keystrokes. You can even use separate history files per database.

Sometimes, you have a query that shouldn’t be saved (such as a huge insert or select). By setting HISTCONTROL to ignorespace, a leading space before your query [“ select foo”] prevents that query from being saved.

Once you have a long and rich history, using ctrl-r to search for previous queries is extremely handy.

\set HISTCONTROL ignoreboth
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 20000 -- Huge history file -- ctrl-r is useful

Here we entered ctrl-r, then typed in select * from to find a previous query:

Quit Protection

Ever type ctrl-d only to accidentally close the wrong session and lose all your temp tables? Then you’ll appreciate \set IGNOREEOF 5. This stops ctrl-d from quitting. Instead, type \q to quit.

Logging

Adding -L will log your entire session to a file. This is great for when you want to remember how long a query took the other day, or what the results were from a query that has scrolled out of your terminal.

The easiest way to specify this is a shell alias.

alias psql="psql -L ~/psql.log"

Put this in your .bashrc to have it run when you start your shell.

All Together:

-- Put this alias in .bashrc to turn on session logging
-- alias psql='psql -eL /tmp/psql.log'
\timing on
\setenv LESS -imx4F
\x auto

-- Put a space in front of queries you don't want to save
\set HISTCONTROL ignorespace
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 20000
 -- Huge history file -- ctrl-r is useful
\pset null '[NULL]'
-- Don't exit with ctrl-d unless you press it 5 times
\set IGNOREEOF 5

Want to discuss this article? Join the Periscope Data Community!
Tags: 
Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?

Subscribe to our Newsletter