Check all table size in Postgres

Posted on July 12, 2016 by ronaldpringadi

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 row count and size:

1
2
3
4
5
6
7
8
SELECT ist.table_name,
       reltuples AS "entries",
       pg_size_pretty(relpages::BIGINT * 8 * 1024) AS SIZE
FROM information_schema.tables ist
INNER JOIN pg_class c ON (ist.table_name = c.relname)
WHERE ist.table_catalog = current_database()
  AND ist.table_schema  = 'public'
ORDER BY relpages DESC, ist.table_name ASC;

The columns: reltuples is the planner’s row-count estimate (updated by ANALYZE / autovacuum), and relpages * 8 KB is the table’s heap size — Postgres pages are 8 KB by default. Tables come out biggest-first.

A few useful additions.

relpages is heap-only — there’s a built-in for the full picture. The query above only counts the main table heap. It does not include indexes, TOAST tables (where wide values overflow), or the FSM/VM bookkeeping. For “how big is this table really, on disk?” use pg_total_relation_size:

1
2
3
4
5
6
7
8
9
10
11
SELECT n.nspname AS schema,
       c.relname AS TABLE,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_size_pretty(pg_relation_size(c.oid))       AS heap_size,
       pg_size_pretty(pg_indexes_size(c.oid))        AS indexes_size,
       c.reltuples::BIGINT                            AS estimated_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

Three sizes side by side:

  • pg_relation_size — the main table heap only (what the original query approximates)
  • pg_indexes_size — every index on the table, summed
  • pg_total_relation_size — heap + indexes + TOAST + everything else attached to the relation

The gap between heap_size and total_size is often surprisingly large — a write-heavy table with several indexes can carry 3–5x its heap size in indexes. That’s where you start looking for indexes you don’t actually use (pg_stat_user_indexes with idx_scan = 0).

reltuples is an estimate, not a count. The number can be stale, and on a brand-new table it may even be -1 until ANALYZE runs. If you need the true row count, use SELECT count(*) FROM mytable — but that’s an exact scan, so reach for it only when you really need exact, on smaller tables. For a quick directional answer, reltuples is fine.

Skip the SQL entirely with \dt+ in psql. Inside psql, the meta-command \dt+ shows every table in the current schema along with its size:

1
psql -d mydb -c "\dt+"

Quick, readable, no joins to write. \dt+ schema.* for a specific schema; \di+ for indexes. 📐

Posted in Database, PostgreSQL | Comments Off on Check all table size in Postgres

Synchronize a postgres table through bash and csv

Posted on June 29, 2016 by ronaldpringadi

Please note that the csv export process does not escape commas.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!/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
    export PGPASSWORD="PASSWORD#1"
   
    echo "Read from SOURCE start $START end $END"
    FILE="${DIR}/dump${START}.csv"
    SQL="SELECT * FROM  ${TABLNAME} where id > $START and id <= $END"
    psql -U postgres -h SOURCEHOST -d DB1 -t -A -F"," -c "$SQL" > "$FILE"
   
    export PGPASSWORD="PASSWORD#1"
    SQL="DELETE FROM ${TABLNAME} where id > $START and id <= $END"
    psql -U postgres -h localhost -d DB2 -t -A -F"," -c "$SQL"
    psql -U postgres -h localhost -d DB2 -c "\copy ${TABLNAME} FROM '${FILE}' DELIMITER ',' CSV"
    echo "Injected to DESTINATION"
   
    START=$END
    END=$((START + SIZE))
    if [ $END -gt $STOP ]; then
        END=$STOP  
    fi
   
    rm $FILE

done
Posted in Database, PostgreSQL | Comments Off on Synchronize a postgres table through bash and csv

Export and Import Postgres query to CSV

Posted on June 24, 2016 by ronaldpringadi


# ===========================================================
# 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"
# psql -U YOURUSERNAME -h YOURHOSTNAME -d YOURDBNAME -c "\copy YOURTABLENAME FROM '/tmp/dump.csv' DELIMITER ',' CSV"

Posted in Database, PostgreSQL | Comments Off on Export and Import Postgres query to CSV

Assorted postgres queries

Posted on June 23, 2016 by ronaldpringadi
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;

Posted in Database, PostgreSQL | Comments Off on Assorted postgres queries

VirtualBox – Imporving usability on guest OS

Posted on May 17, 2016 by ronaldpringadi

GUEST OS: Centos 6.7 final
HOST OS: Windows 7
Virtualbox version: 5.0.4

1
yum install gcc kernel-devel kernel-headers dkms make bzip2 perl
Posted in Linux, Operating System | Comments Off on VirtualBox – Imporving usability on guest OS

RedHat or CentOS 6 iptables adding an open port

Posted on May 16, 2016 by ronaldpringadi

iptables –line -vnL
iptables -I INPUT 5 -p tcp –dport 80 -m state –state NEW,ESTABLISHED -j ACCEPT
service iptables save

Posted in Linux | Comments Off on RedHat or CentOS 6 iptables adding an open port

Postgres SSD optimization

Posted on April 13, 2016 by ronaldpringadi

If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost:

1
2
3
4
5
6
7
8
9
10
-- Change the tablespace cost
ALTER TABLESPACE pg_default SET ( seq_page_cost = 20,  random_page_cost = 1 );
-- Verifiy the change
SELECT * FROM pg_tablespace;


-- Undo the tablespace cost
ALTER TABLESPACE pg_default RESET  ( seq_page_cost,  random_page_cost);
-- Verifiy the change
SELECT * FROM pg_tablespace;
Posted in Database, PostgreSQL | Comments Off on Postgres SSD optimization

Ant Junit debugging

Posted on April 13, 2016 by ronaldpringadi

Sometime we want to debug why ant build failed when executing a certain JUnit
Make sure your ant junit task look like the following

1
2
3
4
<junit printsummary="withOutAndErr" haltonfailure="yes">
:
:
</junit>

and not like

1
2
3
4
<junit printsummary="yes" haltonfailure="yes">
:
:
</junit>
Posted in java | Comments Off on Ant Junit debugging

Delete Postgres Cache

Posted on April 13, 2016 by ronaldpringadi
1
2
3
4
#!/bin/bash
sync
echo 1 > /proc/sys/vm/drop_caches
service postgresql-9.3 restart
Posted in Database, PostgreSQL | Comments Off on Delete Postgres Cache

Java Flight Recorder: a profiler that’s already in your JVM

Posted on March 22, 2016 by ronaldpringadi

If you’re running anything on the JVM (Java Virtual Machine) in production and you’ve never opened a Flight Recorder file, you’re leaving a free profiler on the table. Java Flight Recorder (JFR) is a low-overhead event recorder built into the JVM itself — no agent, no instrumentation, no extra dependency. You turn it on with a flag, you get back a .jfr file, you open it in JDK Mission Control and it tells you where your CPU time went, what the garbage collector (GC) was doing, which threads were contending on which locks, and which methods allocated the most memory. Originally a commercial JRockit feature, JFR was donated to OpenJDK as JEP 328 and has been free for production use since JDK 11. ☕

What it actually records

JFR is event-based. The JVM and the operating system fire events — GC pauses, thread state changes, exception throws, just-in-time (JIT) compilations, file I/O, socket reads — and JFR writes them to a ring buffer. You can also emit your own custom events from application code. The default profile aims for under 2% overhead, which is the magic number that makes it acceptable to leave running in production.

Quick start: capture a recording at JVM startup

The simplest way to capture a recording is to ask for one when you launch the JVM. Add -XX:StartFlightRecording to your java command:

1
java -XX:StartFlightRecording=duration=60s,filename=myapp.jfr,settings=profile -jar myapp.jar

This records for 60 seconds and writes myapp.jfr to the working directory. Two settings templates ship with the JDK:

  • settings=default — <1% overhead, fine to leave running indefinitely
  • settings=profile — <2% overhead, more sampling detail, what you want for a focused profiling session

For a longer-running “black box” recording that always has the last few hours of data on disk:

1
java -XX:StartFlightRecording=disk=true,maxage=6h,settings=default -jar myapp.jar

Capture a recording on a running JVM

Often you don’t want to restart the process — you want to attach to a JVM that’s already running and capture a slice. That’s what jcmd is for. First, find the process ID (PID):

1
jcmd -l

Then start, dump, and stop a recording on that PID:

1
2
3
4
5
6
7
8
9
10
11
# Start a 2-minute profiling recording on PID 12345
jcmd 12345 JFR.start name=debug settings=profile duration=2m filename=/tmp/debug.jfr

# Check what recordings are active
jcmd 12345 JFR.check

# Dump the current state of an in-progress recording without stopping it
jcmd 12345 JFR.dump name=debug filename=/tmp/snapshot.jfr

# Stop a recording explicitly (otherwise it stops at the duration)
jcmd 12345 JFR.stop name=debug

The JFR.dump trick is the one I reach for most often: keep a continuous low-overhead recording running, and when something interesting happens (a slow request, a memory spike), dump the in-flight buffer to a file for analysis. You get the events leading up to the moment, not just events from when you noticed.

View the result in JDK Mission Control

A .jfr file is a binary log of events — you need a viewer. The canonical one is JDK Mission Control (JMC):

  • Download from Adoptium or jdk.java.net/jmc — it’s a separate download from the JDK itself.
  • Open the .jfr file. JMC’s Automated Analysis Results tab is the one to read first — it scans the recording and surfaces likely problems with traffic-light severity (long GC pauses, lock contention, thread hot spots, allocation pressure).
  • From there, dive into the per-area pages: Java Application for method profiling, Memory for allocations and GC, Threads for contention and parking, Environment for CPU and OS-level events.

If you just want a quick text dump without firing up a graphical interface, the JDK ships a jfr command-line tool:

1
2
jfr summary myapp.jfr           # event counts and recording metadata
jfr print --events CPULoad myapp.jfr   # print all events of a given type

IntelliJ IDEA Ultimate also opens .jfr files directly with a built-in viewer if you’d rather not install JMC.

When to reach for it

JFR shines for the questions that a debugger or a print statement can’t answer cheaply:

  • “Why does p99 latency spike every few minutes?” (p99 = the slowest 1% of requests) — look at the GC Pauses view.
  • “Which method is the CPU actually spending time in?” — Method Profiling sampler view.
  • “Why is this app allocating so much?” — Allocation view, grouped by class and stack trace.
  • “Are my threads stuck?” — Thread Park and Java Monitor Wait events.

It’s not a replacement for application performance monitoring (APM) tools like Datadog or New Relic, or for distributed tracing — JFR sees a single JVM, not a whole fleet. But for a deep look at what one process is doing, with overhead low enough to leave it running in production, nothing else in the Java ecosystem comes close. 🚀

Posted in java | Comments Off on Java Flight Recorder: a profiler that’s already in your JVM