Environment

Install by apt

follow instruction of postgres wiki.

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sudo sh -c 'echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
apt-cache show postgresql-15

Note: arch should be specifed on sources.list.

Run on docker container

PostgreSQL image

docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:15.3

Attach running container

docker exec -it pg bash

Flyway for PostgreSQL

Use -postgresql.transactional.lock=false option

Administration

Get storage size of relations

A query to claculate table size with aggregating partitioned tables into the parent table.

select
  coalesce(pg_partition_root(c.oid)::text, relname) relname,
  count(relname) n_relations,
  pg_size_pretty(sum(pg_table_size(c.oid))) relsize,
  sum(relpages) relpages,
  sum(reltuples) reltuples
from pg_class c
left join pg_namespace n on (n.oid = c.relnamespace)
where nspname = 'public'
group by coalesce(pg_partition_root(c.oid)::text, relname)
having sum(pg_table_size(c.oid)) > 8192
order by sum(pg_table_size(c.oid)) desc

Query execution

Generic vs custom plans for prepared statement

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost.

PreparedStatement of PostgreSQL JDBC

An internal counter keeps track of how many times the statement has been executed and when it reaches the prepareThreshold (default 5) the driver will switch to creating a named statement and using Prepare and Execute.

Portal (cursor)

Buffer management for large relation scan

For large relation scan, a small ring buffer is used.

Synchronized scan across multiple queries

Index

check operators supported by B-tree

SELECT
  am.amname AS index_method,
  opf.opfname AS opfamily_name,
  amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid
AND amop.amopfamily = opf.oid
AND am.amname = 'btree'
ORDER BY index_method, opfamily_name, opfamily_operator;

DDL

Locks

confirmed on PostgreSQL 14.9

test=# select pg_backend_pid();
 pg_backend_pid
----------------
          20944
(1 row)

test=# begin;
BEGIN
test=*# create index if not exists test_idx on test (key);
NOTICE:  relation "test_idx" already exists, skipping
CREATE INDEX

-- another connection
test=# select pid, relation::regclass, mode, granted, query from pg_locks join pg_stat_activity using (pid) where locktype = 'relation' and pid <> pg_backend_pid() order by query_start;
  pid  | relation |   mode    | granted |                       query
-------+----------+-----------+---------+----------------------------------------------------
 20944 | test     | ShareLock | t       | create index if not exists test_idx on test (key);
(1 row)

The Query Rewrite Rule System (views and materialized views)

Security invoker view

By default, access to the underlying base relations referenced in the view is determined by the permissions of the view owner. If the view has the security_invoker property set to true, access to the underlying base relations is determined by the permissions of the user executing the query, rather than the view owner. (from: https://www.postgresql.org/docs/current/sql-createview.html)

Clients

Set GUC parameters via libpq connection string

e.g.

postgresql://user@localhost:5433/mydb?options=-c%20synchronous_commit%3Doff

JDBC

JDBC automatically executes begin a transaction if required

BEGIN is called unless QUERY_SUPPRESS_BEGIN flag is set. (code).

For example, QUERY_SUPPRESS_BEGIN is set when auto commit is true.

Links