Skip to content

Instantly share code, notes, and snippets.

View andyatkinson's full-sized avatar

Andrew Atkinson andyatkinson

View GitHub Profile
psql> create table t(a uuid, b text, c char(36));
CREATE TABLE
psql> insert into t values (gen_random_uuid(), 'some amount of text', 'some amount of text');
INSERT 0 1
psql> select pg_column_size(a), pg_column_size(b), pg_column_size(c) from t;
pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
16 | 20 | 37
@andyatkinson
andyatkinson / analyze_part_table.md
Created October 3, 2024 20:19
Analyzing ANALYZE

Timeline:

Goal: Understand WHY to ANALYZE ONLY on a root partition table.

The current version (17) documentation seems to be wrong. It says only the root table is analyzed, but in the commit message David Rowley describes the partitions of the root table are also analyzed when ANALYZE runs on the root.

@andyatkinson
andyatkinson / cte_as_active_record.rb
Last active April 26, 2024 20:15
CTE in SQL and Active Record
#
# CTE support in Active Record was added to Ruby on Rails 7.1
# https://blog.appsignal.com/2023/02/15/whats-new-in-rails-7-1.html#
#
# The example below produces an equivalent query to the SQL version below.
#
# Rails app: https://github.com/andyatkinson/rideshare
#
irb(main):395* Driver.with(drivers_recent_completed_trip: Driver.select('users.id AS driver_id').joins(trips: :trip_request).
irb(main):396* where.not(trips: {completed_at: nil}).
@andyatkinson
andyatkinson / enable_partitionwise_aggregate.sql
Created October 11, 2023 03:02
Exploring enable_partitionwise_aggregate
create table t (id serial, created_at timestamptz) partition by range (created_at);
create table t_202309 partition of t for values from ('2023-09-01') TO ('2023-10-01');
create table t_202310 partition of t for values from ('2023-10-01') TO ('2023-11-01');
postgres@[local]:5432 testing# \d t
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+-------------------------------
id | integer | | not null | nextval('t_id_seq'::regclass)
@andyatkinson
andyatkinson / postgresql.conf
Last active October 4, 2023 22:52
Enabling automatic query execution plans via auto_explain
# Prereq: auto_explain added to shared_preload_libraries
# Compute the Query Identifier
compute_query_id = on
# Logging duration
log_duration = on
# Logging Query Identifier (%Q)
log_line_prefix = 'pid=%p query_id=%Q: '
:quit
:qa
exit
exit!
quit
.quit
process.exit()
\q

Keybase proof

I hereby claim:

  • I am andyatkinson on github.
  • I am andatki (https://keybase.io/andatki) on keybase.
  • I have a public key whose fingerprint is 2BC1 EF01 7DEA 0698 523F 8A3C A6D9 3792 EFC7 54EE

To claim this, I am signing this object:

issues xfter a restart where postgres is running, but not with launchctl, not sure why stop manually:

pg_ctl -D /usr/local/var/postgres stop -s -m fast

restart with launchctl:

launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

# example config from a nginx + unicorn + capistrano deploy to a VPS
# "app" is deployed to /var/www/app
# replace "app" with the app name
# The rule of thumb is to use 1 worker per processor core available,.
# however since we'll be hosting many apps on this server,.
# we need to take a less aggressive approach
worker_processes 2
# We deploy with capistrano, so "current" links to root dir of current release