# Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL

DevFeed: [Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL](<https://devfeed.tech/articles/my-top-10-postgres-features-and-tips-for-2016-41185.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2015/12/29/My-top-10-Postgres-features-and-tips-for-2016/>)

Author: Map

Published: 2015-12-29T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [etl](<https://devfeed.tech/topics/etl.md>)

Tags: [cte](<https://devfeed.tech/tags/cte.md>), [pg-stat-statements](<https://devfeed.tech/tags/pg-stat-statements.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [setup](<https://devfeed.tech/tags/setup.md>), [sql](<https://devfeed.tech/tags/sql.md>), [tips](<https://devfeed.tech/tags/tips.md>)

## AI overview

A practical collection of Postgres tips covering Common Table Expressions for readable queries, .psqlrc customization, pg_stat_statements for identifying indexing and performance opportunities, and caution around aggregating data through ETL when working across microservices or applications.

## Source excerpt

I find during the holiday season many pick up new books, learn a new language, or brush up on some other skill in general. Here's my contribution to hopefully giving you a few new things to learn about Postgres and ideally utilize in the new year. It's not in a top 10 list as much as 10 tips and tricks you should be aware of as when you need them they become incredibly handy. But, first a shameless plug if you find any of the following helpful, consider subscribing to Postgres weekly a weekly newsletter with interesting Postgres content. 1. CTEs - Common Table Expressions CTEs allow you to do crazy awesome things like recursive queries but even the most simple form of them I don't go a day without using. Think of a CTE or commonly known as with clause as a view inside the time that query is running. This lets you more easily create readable query. Any query that's constructed that's even 100 lines long, but with 4-5 CTEs is undoubtedly going to be easier for someone new to come in and understand than a 20 line query that does the same thing. A few people like writing SQL, but no one likes reading someone else's so do them a favor and read up on CTEs. 2. Setup a .psqlrc You setup a bashrc, vimrc, etc. Why not do the same for Postgres. Some of the great things you can do: Setup pretty formatting by default with \x auto Set nulls to actually look like something \pset null ¤ Turn timing on by default \timing on Customize your prompt \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' Save commonly run queries that you can run by name Here's an example of my own psqlrc: \set QUIET 1 \pset null '¤' -- Customize prompts \set PROMPT1 '%[%033[1m%][%/] # ' \set PROMPT2 '... # ' -- Show how long each query takes to execute \timing -- Use best available output format \x auto \set VERBOSITY verbose \set HISTFILE ~/.psql_history- :DBNAME \set HISTCONTROL ignoredups \set COMP_KEYWORD_CASE upper \unset QUIET 3. pg_stat_statements for where to index pg_stat_state