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 10s
  • work_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
  • 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 transactions
    • pg_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 shell
  • pg_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 each page of a table.
  • pageinspect
    • Can be used to inspect a page
    • Useful to see previous versions in MVCC

7. Other cheatsheets

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

Created: 2024-07-16 Tue 16:44

Validate