From The Crunchy Data Team

Postgres Tips & Tricks

Just a few helpful tips how to use Postgres to it's fullest. Have one you'd like to share or something is unclear? Share with us on twitter @crunchydata

Logging

Control which statement types get logged

Control the types of statements that are logged for your database.

ALTER DATABASE postgres SET log_statement = 'all'

Valid values include all, ddl, none, mod

performance

Use statement timeouts to control runaway queries

Setting a statement timeout prevents queries from running longer than the specified time. You can set a statement timeout on the database, user, or session level. We recommend you set a global timeout on the database and then override that one specific users or sessions that need a longer allowed time to run.

ALTER DATABASE mydatabase SET statement_timeout = '60s';

Monitor connections in Postgres

This query will provide the number of connection based on type.

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

If you see idle is above 20 it's recommended to explore using PgBouncer.

Query size of specific table

Will give you the size of the specific relation you pass in.

SELECT pg_relation_size('table_name'); # For prettier formatting you can wrap with: SELECT pg_size_pretty(pg_relation_size('table_name'));

Query all relation sizes

Will report on all table sizes in descending order

SELECT relname AS relation, pg_size_pretty ( pg_total_relation_size (C .oid) ) AS total_size FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND C .relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size (C .oid) DESC

Check for unused indexes

Will return the unused indexes in descending order of size. Keep in mind you want to also check replicas before dropping indexes.

SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size", idx_scan as "index scans" FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;

Get approximate counts for a table

Will return the approximate count for a table based on PostgreSQL internal statistics. Useful for large tables where performing a `SELECT count(*)` is costly on performance.

SELECT reltuples::numeric as count FROM pg_class WHERE relname='table_name';

Non-blocking index creation

Adding `CONCURRENTLY` during index creation, while not permitted in a transaction, will not hold a lock on the table while creating your index.

CREATE INDEX CONCURRENTLY foobar ON foo (bar)

Psql

Automatically log query time in psql

Will automatically print the time it took to run a query from within psql. *Of note this is the round trip time not simply query execution time.*

\timing

You can save this in your `.psqlrc` to be a default setting

Autoformat query results in psql

Will automatically reorganize the query output based on your terminal window for better readability.

\x auto

You can save this in your `.psqlrc` to be a default setting

Set a value for nulls

Will render the nulls as whatever character you specify. Handy for easier parsing of nulls vs. blank text.

\pset null 👻

You can save this in your `.psqlrc` to be a default setting

Continually run a query with watch

Will automatically run the last query ever 2 seconds and display the output. You can also specify the query that will run after watch as well.

\watch

Provide clean border within Psql

Will give you a border around the output of your queries when in psql

\pset border 2

You can save this in your `.psqlrc` to be a default setting

Set linestyle to unicode

Changes the linestyle to unicode, which when combined with above tip leads to much cleaner formatting

\pset linestyle unicode

You can save this in your `.psqlrc` to be a default setting

SQL

Replace nulls with other value

Coalese will use the value and if the value is null display your specified string.

SELECT id, coalesce(ip, 'no IP') FROM logs;

You can supply two columns as well prior to your replacement value and the function will use first not null value.

Generate data with generate_series

Will generate rows from the start to the end values supplied. Can use numbers or timestamps.

SELECT * FROM generate_series(now() - '1 month'::interval, now());

Round dates with date_trunc

Will truncate the date to the specified level of precision. Some example precision levels include: month, week, day, hour, minute.

SELECT date_trunc('day', now());

Perform time math with intervals

You can add or subtract specific amounts of time of a timestamp by casting the value you want as an interval.

SELECT now() - '1 month'::interval;

utility

Create read only user

Once already creating a specific user role, you can user the `pg_read_all_data` to grant read only access to all tables.

GRANT pg_read_all_data TO username;