postgres
Table of Contents
1. Basic
/var/lib/postgres/data
: Default data directory- Double quotes mean identifier like column name/table name. For string, use single quotes.
- pgctl vs psql??
1.1. Outputs and Predicate
1.1.1. String
- string concat happens with
single quotes
and||
.strings
are also case sensitive.
1.1.2. Date & Time
SELECT NOW(); SELECT CURRENT_TIMESTAMP; SELECT DATE('2022-09-02'); SELECT EXTRACT(DAY FROM DATE('2022-09-02'));
1.2. Settings
SET statement_timeout = '10s';
: Queries can run upto 10swork_mem
- Governs how much memory is available to each query operation before it must start writing data to temporary files on disk
- Useful to tune in prod
work_mem = ($YOUR_INSTANCE_MEMORY * 0.8 - shared_buffers) / $YOUR_ACTIVE_CONNECTION_COUNT
2. User and Roles
2.1. pghba vs pg roles
- pghba : Host-Based Authentication Configuration
- How client authentication is performed based on the connection parameters
- pg roles: inside pg
2.2. List databases without logging in
psql -l
3. Good practices
3.1. Users
- Default
postgres
user is super user- Never delete the
postgres
user - You should change the pass in prod
- You can disallow login as
postgres
user if you want making another superuser
- Never delete the
- Create a new user for each new database you make to manage that database.
3.2. Sessions
3.2.1. Having both RW & RO sessions for applications
- This is pretty useful
- Now you can do some queries to the primary, and some to the replicas if you want to. This can become difficult later in the evening.
- Can be done using
target_session_attrs
("read-write", and "any"). See Database Connection Control Functions
4. Query Management
4.1. Long running query
SELECT pid, now() - pgstatactivity.querystart AS duration, query, state FROM pgstatactivity WHERE (now() - pgstatactivity.querystart) > interval '5 minutes';
4.1.1. Cancel
SELECT pg_cancel_backend(__pid__); SELECT pg_terminate_backend(__pid__); -- kill -9 in PostgreSQL (might restart pg!)
5. CLI commands/Queries for metadata
5.1. List
- DB
\l
: List all dbs and their owners\db
: List all tablespaces
- User
\du
: List users
- Schema
\dn
: List all schemas\dt *.*
: List all relations inside that schema
- Locks
pg_locks
(System View): Transaction IDs of currently executing transactionspg_prepared_statements
: See all prepared statements available in the session
- Transactions
SELECT txid_current_snapshot();
5.2. Representation
\x
: Toggle expanded output mode\dt <schema_name>.*
: Column representation of the data types and indexes of a database\d+ <schema_name>.<table_name>
: Column representation of the data types and indexes of a database
5.3. Operations
\c <db_name>
: connect to a db
6. Extensions
\dx
: List installed extensions in pg shellpg_freespacemap
- pg uses FSM to choose the page where a tuple can be Inserted.
- FSM stores free space information of each page
- Using
pg_freespacemap
, we can see the freespace available inside eachpage
of a table.
pageinspect
- Can be used to inspect a
page
- Useful to see previous versions in MVCC
- Can be used to inspect a
7. Other cheatsheets
- Postgres Commands · GitHub
- Useful PostgreSQL Queries and Commands · GitHub
- postgresql database cluster vs one server with many databases
- Don't Do This - PostgreSQL wiki
- https://github.com/enochtangg/quick-SQL-cheatsheet#find
- https://antonz.org/sql-cheatsheet/
- https://github.com/supabase/nix-postgres/tree/main
8. Tools
8.1. pgbench
- benchmark unix vs tcp
pgbench -h /var/run/postgresql -p 5432 -b select-only -T 60 -c 10 -j 2 bench pgbench -h localhost -p 5432 -b select-only -T 60 -c 10 -j 2 bench
8.2. pgconfig
- See https://pgpedia.info/p/pg_config.html
- Helpful utility to understand how postgres config would behave