Author Archives: ronaldpringadi

You’re staring at a query that won’t finish, or a deploy that hangs on a migration, or a UI request that just sits there. Postgres is almost certainly waiting on a lock that another transaction holds. The classic query for … Continue reading →

You want a quick “which tables are eating my disk?” overview — usually before a vacuum, an archive purge, or a capacity-planning conversation. Here’s a one-shot query that joins information_schema.tables against pg_class to show every public table with its estimated … Continue reading →

Please note that the csv export process does not escape commas. 1234567891011121314151617181920212223242526272829303132333435#!/bin/bash DIR=’/root/sql_dump’ mkdir -p "$DIR" cd "$DIR" SIZE=100000 #START=611244350 START=0 END=$((START + SIZE)) STOP=189097000 TABLNAME="schema.tablename" while [[ $START -lt $STOP ]] && [[ $END -le $STOP ]]; do   … Continue reading →

# =========================================================== # Export to CSV # =========================================================== # export PGPASSWORD=”YOURPASSWORD” # psql -U YOURUSERNAME -h YOURHOSTNAME -d YOURDBNAME-t -A -F”,” -c “select * from YOURTABLENAME limit 2″ > output.csv # =========================================================== # Import # =========================================================== # export PGPASSWORD=”YOURPASSWORD” # … Continue reading →

Get table sizes: SELECT relname AS objectname, relkind AS objecttype, reltuples AS “#entries”, pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class WHERE relpages >= 8 ORDER BY relpages DESC;

If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost: 12345678910– Change the tablespace cost ALTER TABLESPACE pg_default SET ( seq_page_cost = 20,  random_page_cost = 1 ); — … Continue reading →