Â
Â
Timing
You can have psql run a timer for your queries by setting
Â
Index Cache Hit Rate
Â
LOGGING
Log slow queries by setting log_min_duration_statement
Â
Control which statement types get logged
Control the types of statements that are logged for your database.
Valid values include all, ddl, none, mod
Â
Log when waiting on a lock
Log when database is waiting on a lock.
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.
Â
Use pg_stat_statements to find the queries and processes that use the most resources
Monitor connections in Postgres
This query will provide the number of connection based on type.
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.
Query all relation sizes
Will report on all table sizes in descending order
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.
Â
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.
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.
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.*
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.
You can save this in your
.psqlrc
to be a default settingÂ
Edit your psql queries in editor of your choice
Will automatically open your last run query in your default
$EDITOR
. When you save and close it will execute that query.Set a value for nulls
Will render the nulls as whatever character you specify. Handy for easier parsing of nulls vs. blank text.
You can save this in your
.psqlrc
to be a default settingSave your query history per database locally
Will automatically save a history file for each **DBNAME**.
You can save this in your
.psqlrc
to be a default settingClear your psql screen
Will clear your screen in current psql session
Â
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.
Â
Rollback to previous statement on error when in interactive mode
When you encounter an error when in interactive mode this will automatically rollback to just before the previous command, allowing you to continue working as you would expect.
Â
Export a CSV from directly in psql
Will run the specific query and return live a SQL for you
Run a query from a file in psql
Will execute the specified file when inside psql.
Provide clean border within Psql
Will give you a border around the output of your queries when in psql
You can save this in your
.psqlrc
to be a default settingSet linestyle to unicode
Changes the linestyle to unicode, which when combined with above tip leads to much cleaner formatting
You can save this in your
.psqlrc
to be a default settingSQL
Replace nulls with other value
Coalesce will use the value and if the value is null display your specified string.
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
Generates values from the start to the end values supplied based on the interval. Values can be numbers or timestamps. Can be used in a FROM or JOIN clause or CTE. Commonly used when building charts and reports that require all dates to be filled.
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.
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.
Â
Explain whether index has been used
Â
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.Â
LIMIT
Return the results
Function
Â
SQL efficiency
Â