# PostgreSQL cheatsheet
Useful cheatsheet for PostgreSQL
# Upgrading
Updating a running server (from 10 to 11):
apt-get install -y postgres-11 # Install the latest version, which will start on port 5433
sudo pg_dropcluster 11 main --stop # Stop the instance on port 5433
sudo pg_upgradecluster 10 main # Upgrade the instance on port 5432
sudo pg_dropcluster 10 main # Drop the outdated instance
# Testing
# Query performance
create or replace function perf_query()
returns void
language sql stable
as $$
select true;
$$;
explain analyze
select perf_query()
from generate_series(1,1000);
/*
"Function Scan on generate_series (cost=0.00..100.00 rows=10000 width=1) (actual time=10.781..17.027 rows=1000 loops=1)"
"Planning Time: 4.880 ms"
"Execution Time: 23.155 ms"
*/
# Templates
# New tables
CREATE TABLE public.table_name (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
data jsonb,
inserted_at timestamp without time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
updated_at timestamp without time zone DEFAULT timezone('uc'::text, now()) NOT NULL
);
COMMENT ON TABLE public.table_name IS 'Some table name.';
COMMENT ON COLUMN public.table_name.inserted_at IS 'UTC timestamp of when the record was inserted.';
COMMENT ON COLUMN public.table_name.updated_at IS 'UTC timestamp of when the record was last updated.';
# Utilities
- Updating tables with dependent views. Usage exanple:
select deps_save_and_drop_dependencies('public', 'users'); select deps_restore_dependencies('public', 'users');
# Resources
Hacker News
- System design hack: Postgres is a great pub/sub & job server
- Designing the most performant Row Level Security schema
- Postgresqlco.nf: PostgreSQL Configuration for Humans
- Pgsh – branch Postgres like Git
- Postgres Health Check and SQL Performance Analysis
- pg_flame – flamegraph visualizations of PostgreSQL query plans
- More on Postgres Performance
- Highly Available PostgreSQL Database
- A user interface for Postgres EXPLAIN that also gives tips