Vijaykumar Jain cabecada

The Ultimate PostgreSQL Guide for Software Engineers

	https://medium.com/@pranavt84/postgresql-page-structure-a-deep-dive-e82094a613de

	seek= how far you go ahead in the output file

	skip= how far you go ahead in the input file

	count= how many segments you copy (can be set via bs=)

	say you have 2 16 byte files like so:

	postgres=# set client_min_messages TO debug1;

	SET

	postgres=# create table t(col1 int primary key);

	DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"

	DEBUG: building index "t_pkey" on table "t" serially



	sudo apt-get -y -q install libipc-run-perl lcov build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache pkg-config libicu-dev


	sudo mkdir /opt/postgresql/17
	sudo chown -R postgres:postgres /opt/postgresql/17
	cd postgres
	./configure --prefix=/opt/postgresql/17 --enable-debug --enable-cassert --enable-tap-tests --enable-coverage CFLAGS="-ggdb3 -O0"
	make -j4 install

	https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
	https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
	https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
	https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/
	https://github.com/ardentperf/glibc-unicode-sorting


	2.27 > 2.31
	index corruption

	https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
	https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
	https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
	https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/

	https://www.youtube.com/watch?v=0E6O-V8Jato
	Sorting Out glibc Collation Challenges: Joe Conway - PGCon 2023


	glibc upgrades from 2.27 to 2.31, which changes the ordering

	https://www.enterprisedb.com/blog/partitioning-large-table-without-long-running-lock

	create table orig_table
	( id int generated always as identity not null,
	data float default random()
	);

	create index orig_data_index on orig_table(data);
	create index orig_id_index on orig_table(id);

	postgres=# create table t(col1 int, col2 int);
	CREATE TABLE
	Time: 3.670 ms
	postgres=# insert into t select x,x from generate_series(1, 1000000) x;
	INSERT 0 1000000
	Time: 3495.578 ms (00:03.496)
	postgres=# select 't'::regclass::oid;
	oid
	-------
	16395

	postgresql developer options are a list of configs users can play with in the sandbox environment for debugging.
	i used it to study various locks taken when a statement is executed on an object
	just to get things started, there is a GUC where one can enable lock tracing on a particular table only
	this is cool as i dont care what else is going on in the system otherwise, but i want to learn what locks are taken by what sql statements
	infact one can make use of https://pglocks.org/ to run the various statements on this site (by hussein) and understand the locks taken
	these would be very useful when one is doing migrations on large objects which are blocking in nature

	https://github.com/ankane/strong_migrations (is a good collection of those cases and the links to the blogs)