Storage Optimizations: Techniques to Improve Your Blockchain Indexer

Working on blockchain projects always means tracking on-chain data and structuring it so it is actually queryable. The blockchain is an immutable record of everything that happened, but reading that record directly is difficult and sometimes impossible.

That is where indexing comes in. You follow the head of the chain, read LOGS, and reshape them into something a database can answer questions about. Take ERC20. There is one function to read a balance, balanceOf(address), and that is it. You can’t list every holder, you can’t get a historical balance, you can’t chart holder distribution. To have any of that you follow the Transfer event, write it to a database, and query the database instead.

The same goes for decentralized trading, protocol fees, and most of the things worth knowing.

And before you ask: yes, there are services that do this for you, or at least help, like The Graph and Moralis. But I like being in control of my infrastructure, and I don’t like how modern times made developers scared of touching a server, so I prefer to do it myself.

The techniques below are boring. They are also worth about half your disk. On the schema I benchmark at the end of this article, applying all of them took 1M swap rows from 484 MiB to 242 MiB, without dropping a single column or losing any precision.

What we will cover in this article

Storing hex as raw bytes instead of text
Right-sizing numeric columns, including the uint256 problem
Ordering columns so PostgreSQL stops padding your rows
Normalizing the addresses that repeat
Using BRIN where your data is already block-ordered
Compressing raw payloads, and knowing when bytea stops mattering
Partitioning, and why it is not a storage optimization

What will not be covered in this article

How to build a blockchain indexer from scratch

Every number in this article is measured, not estimated, and every section ends with a collapsed block containing the exact SQL that produced it. Nothing here asks you to take my word for it.

The benchmark harness

All measurements were taken on PostgreSQL 18.3 in a throwaway container, so nothing touches a real database:

throwaway postgres

docker run -d --name bench-pg18 
  -e POSTGRES_PASSWORD=bench -e POSTGRES_USER=bench -e POSTGRES_DB=bench 
  -p 55432:5432 postgres:18-alpine

docker exec -it bench-pg18 psql -U bench -d bench

Sections 3 through 7 all measure the same 1M synthetic swap rows. Building the data once, in a source table, means every variant below is populated from identical values and the only thing that differs is the storage layout:

the shared source table

CREATE TABLE src AS
SELECT
  i,
  (23000000 + i/40)::bigint                          AS block_number,
  (now() - (i || ' seconds')::interval)::timestamptz AS block_time,
  decode(md5(i::text)||md5((i+7)::text),'hex')       AS tx_hash,
  (i % 700)::smallint                                AS log_index,
  -- 50k distinct pools
  decode(substr(md5((i%50000)::text)||md5((i%50000+1)::text),1,40),'hex')   AS pair,
  -- 500k distinct senders
  decode(substr(md5((i%500000)::text)||md5((i%500000+3)::text),1,40),'hex') AS sender,
  -- 5k distinct tokens on each side
  decode(substr(md5((i%5000)::text)||md5((i%5000+5)::text),1,40),'hex')     AS token_in,
  decode(substr(md5((i%5000+11)::text)||md5((i%5000+13)::text),1,40),'hex') AS token_out,
  ((i%9000)+1)::numeric * 1000000000000000::numeric  AS amount_in,
  ((i%7000)+1)::numeric * 1000000000000000::numeric  AS amount_out
FROM generate_series(1,1000000) i;

Two settings matter when you measure. Turn off parallel query and JIT, so timings reflect the storage layout rather than the planner, and always VACUUM ANALYZE before reading a size — otherwise you are measuring dead tuples:

before every measurement

SET max_parallel_workers_per_gather = 0;
SET jit = off;
VACUUM ANALYZE your_table;

Clean up when you are done:

cleanup

docker rm -f bench-pg18

1. Raw bytes not strings

Always save your hex encoded data as raw bytes in your database, not as text.

Blockchains use a lot of hex data. Addresses, transaction hashes, topics, log data, almost everything is represented in hex. I can’t tell you how many times I’ve been brought onto a project that stores all of it as text or varchar.

Hex encoded data is not text. It is an encoding of binary data, used to make binary easy to transfer and read. Storing it as text is a crime in my opinion.

Hex is a 2:1 encoding, every byte of real data costs you two characters. Add the 0x prefix and the mandatory varlena length header PostgreSQL puts in front of every variable length value, and a single address goes from 21 bytes to 43. Below is a breakdown of the savings.

On average you save 50% of the storage space by storing hex data as raw bytes instead of text.

Some benchmarks

Value	`text`	`bytea`	Diff	Savings
Address (0x + 40 hex)	43 B	21 B	−22 B	51.2%
Address (no 0x)	41 B	21 B	−20 B	48.8%
Tx / block hash, bytes32	67 B	33 B	−34 B	50.7%

Those numbers are pg_column_size() on stored values: payload plus the 1 byte header PostgreSQL uses for anything shorter than 127 bytes. The bytea column is literally the raw 20 or 32 bytes the EVM handed you, no encoding tax at all.

Now put it in context. A minimal transfer row, from, to, tx_hash:

Layout	Row payload	Savings
`text`	153 B	—
`bytea`	75 B	51.0%

Per row payload is only half the story. Indexes are where hex encoding really taxes you, because every index entry carries a full copy of the key. Here is a 1M row transfers table with a btree on from and a btree on tx_hash, measured on PostgreSQL 18 with random keys:

Object	`text`	`bytea`	Diff	Savings
Heap	182.0 MiB	104.5 MiB	−77.5 MiB	42.6%
Index on `from`	64.7 MiB	38.8 MiB	−26.0 MiB	40.1%
Index on `tx_hash`	91.2 MiB	56.3 MiB	−34.8 MiB	38.2%
Total	337.9 MiB	199.6 MiB	−138.3 MiB	40.9%

The heap saving lands at 42.6% rather than the full 51% because the 24 byte tuple header and the 4 byte line pointer are fixed costs that hex encoding does not inflate. In practice it means the text table fits 43 rows per 8 KB page while the bytea table fits 75. Same data, 74% more rows per page.

Now the same measurement on a wider trades table, 1M rows with a btree on pair, on sender and on tx_hash, and this time with realistic cardinality: 50k distinct pools, 500k distinct senders, unique hashes.

Object	`text`	`bytea`	Diff	Savings
Heap	312.5 MiB	166.5 MiB	−146.0 MiB	46.7%
Index on `pair`	9.7 MiB	8.4 MiB	−1.3 MiB	13.1%
Index on `sender`	41.1 MiB	27.9 MiB	−13.2 MiB	32.2%
Index on `tx_hash`	91.2 MiB	56.3 MiB	−34.8 MiB	38.2%
Total	454.5 MiB	259.1 MiB	−195.4 MiB	43.0%

The heap does better here, 46.7%, because six hex columns per row means the fixed 28 bytes of tuple overhead get diluted. 25 rows per page as text, 47 as bytea.

The index numbers deserve an honest note though. Look at pair: only 13.1% saved, nowhere near the 38.2% on tx_hash. That is not a mistake. Since PostgreSQL 13, btree indexes deduplicate, and 1M rows across 50k pools means every key is stored once with a posting list of row pointers attached. The key width stops mattering when you are only storing 50k of them. sender sits in between at 500k distinct values, and tx_hash is unique so it pays the full price and gets the full saving.

Scale that to a real indexer: 500M transfer rows on the same schema is about 165 GB as text versus 97 GB as bytea. That is 68 GB you are paying to store the characters a through f.

Reproduce the per-value sizes

The values have to be stored in a table, not passed as literals. This trips people up:

per-value cost, text vs bytea

CREATE TABLE hdr (
  t_addr      text,
  b_addr      bytea,
  t_addr_no0x text,
  t_hash      text,
  b_hash      bytea
);

INSERT INTO hdr VALUES (
  '0x' || repeat('a',40),          -- address as text
  decode(repeat('a',40),'hex'),    -- address as raw bytes
  repeat('a',40),                  -- address as text, no 0x
  '0x' || repeat('a',64),          -- bytes32 as text
  decode(repeat('a',64),'hex')     -- bytes32 as raw bytes
);

SELECT pg_column_size(t_addr)      AS addr_text,
       pg_column_size(b_addr)      AS addr_bytea,
       pg_column_size(t_addr_no0x) AS addr_text_no0x,
       pg_column_size(t_hash)      AS hash_text,
       pg_column_size(b_hash)      AS hash_bytea
FROM hdr;

result

 addr_text | addr_bytea | addr_text_no0x | hash_text | hash_bytea
-----------+------------+----------------+-----------+------------
        43 |         21 |             41 |        67 |         33

To measure heap and index sizes at scale, build a table from the source data in the harness above and read the sizes back:

heap and index sizes

SELECT pg_size_pretty(pg_relation_size('your_table'))        AS heap,
       pg_size_pretty(pg_relation_size('your_index'))        AS idx,
       pg_size_pretty(pg_total_relation_size('your_table'))  AS heap_plus_indexes,
       pg_relation_size('your_table') / 8192                 AS pages;

-- rows that fit in the first 8 KB page
SELECT count(*) FROM your_table WHERE ctid::text LIKE '(0,%';

2. Stop storing numbers as strings

Same disease as hex, different column. Amounts, block numbers, log indexes and timestamps all end up as text in indexers, because the JSON-RPC response handed them over as strings and nobody changed the type on the way in.

uint256 is the interesting one, because the obvious fix is the wrong one. Most people reach for bytea — it is the raw EVM word, 32 bytes, done. But PostgreSQL’s numeric is variable length. It stores base-10000 digits and strips trailing zero groups, and token amounts are almost entirely trailing zeros.

Here is pg_column_size() on stored values, at magnitudes you actually see on chain:

Amount	`text`	`numeric`	`bytea` (32)
`1e18` (1 token, 18 dec)	20 B	5 B	33 B
`1234.5678e18`	23 B	9 B	33 B
`1500e6` (1500 USDC)	11 B	5 B	33 B
max `uint256`	79 B	43 B	33 B

A one-token transfer costs 5 bytes as numeric against 33 as bytea. numeric only loses at the very top of the range, and real transfer amounts do not live up there. Storing a max uint256 is the pathological case, not the normal one.

The scalar columns are less dramatic but they are free wins:

Column	as `text`	correct type	Size
`block_number`	9 B	`integer`	4 B
`log_index`	4 B	`smallint`	2 B
`block_time`	21 B	`timestamptz`	8 B

integer for a block number looks reckless until you check the range. int4 tops out at 2,147,483,647. Ethereum is around block 23.1M and produces one block every 12 seconds, which puts the overflow roughly 800 years out. bigint is 8 bytes for a problem you will not have.

Reproduce the amount and scalar sizes

uint256 at realistic magnitudes

CREATE TABLE amt (label text, t text, n numeric, b bytea);

INSERT INTO amt VALUES
 ('1 token (1e18)', '1000000000000000000', 1000000000000000000::numeric,
   decode(lpad(to_hex(1000000000000000000::bigint),64,'0'),'hex')),
 ('1234.5678 (1e18)', '1234567800000000000000', 1234567800000000000000::numeric,
   decode(lpad('42e0a9c5f8f7a00000',64,'0'),'hex')),
 ('USDC 1500.00 (1e6)', '1500000000', 1500000000::numeric,
   decode(lpad(to_hex(1500000000::bigint),64,'0'),'hex')),
 ('max uint256',
   '115792089237316195423570985008687907853269984665640564039457584007913129639935',
   115792089237316195423570985008687907853269984665640564039457584007913129639935::numeric,
   decode(repeat('ff',32),'hex'));

SELECT label,
       pg_column_size(t) AS "text",
       pg_column_size(n) AS "numeric",
       pg_column_size(b) AS "bytea32"
FROM amt;

result

       label        | text | numeric | bytea32
--------------------+------+---------+---------
 1 token (1e18)     |   20 |       5 |      33
 1234.5678 (1e18)   |   23 |       9 |      33
 USDC 1500.00 (1e6) |   11 |       5 |      33
 max uint256        |   79 |      43 |      33

And the scalar columns:

block number, log index, timestamp

CREATE TABLE scal (
  bn_t text, bn_i8 bigint, bn_i4 integer,
  li_t text, li_i4 integer, li_i2 smallint,
  ts_t text, ts_tz timestamptz, ts_i4 integer
);

INSERT INTO scal VALUES
  ('23145678', 23145678, 23145678,
   '137', 137, 137,
   '2026-07-23T11:04:31Z', '2026-07-23T11:04:31Z', 1784977471);

SELECT pg_column_size(bn_t)  AS blocknum_text,
       pg_column_size(bn_i8) AS bigint,
       pg_column_size(bn_i4) AS int4,
       pg_column_size(li_t)  AS logidx_text,
       pg_column_size(li_i2) AS int2,
       pg_column_size(ts_t)  AS ts_text,
       pg_column_size(ts_tz) AS timestamptz,
       pg_column_size(ts_i4) AS epoch_int4
FROM scal;

result

 blocknum_text | bigint | int4 | logidx_text | int2 | ts_text | timestamptz | epoch_int4
---------------+--------+------+-------------+------+---------+-------------+------------
             9 |      8 |    4 |           4 |    2 |      21 |           8 |          4

Worth noting: a Unix epoch as int4 is 4 bytes against timestamptz’s 8, but int4 overflows in 2038 and you lose every date function in the database. The 4 bytes are not worth it.

3. Order your columns widest first

This one costs nothing to fix and most people have never heard of it.

PostgreSQL aligns column values on their natural boundary. An int8 starts on an 8-byte boundary, an int4 on 4, an int2 on 2. If a 1-byte boolean sits in front of an 8-byte bigint, the 7 bytes between them are padding. They hold nothing. You still pay for them, on every row, forever.

Take a log table declared in the order you’d naturally think of the fields:

the natural order

CREATE TABLE ev_bad (
  removed      boolean,      -- 1 byte, then 7 wasted
  block_number bigint,       -- 8
  log_index    smallint,     -- 2, then 6 wasted
  block_time   timestamptz,  -- 8
  is_native    boolean,      -- 1, then 3 wasted
  tx_index     integer,      -- 4
  confirmed    boolean       -- 1
);

Now the same columns, widest first:

widest first

CREATE TABLE ev_good (
  block_number bigint,
  block_time   timestamptz,
  tx_index     integer,
  log_index    smallint,
  removed      boolean,
  is_native    boolean,
  confirmed    boolean
);

Identical data, 1M rows each:

Table	Heap	Pages	Rows / page
`ev_bad`	73.0 MiB	9,346	107
`ev_good`	57.4 MiB	7,353	136

21.3% saved by reordering a CREATE TABLE. Same columns, same types, same values, 27% more rows per page.

The rule is simple: declare 8-byte columns first, then 4-byte, then 2-byte, then the 1-byte flags, then variable-length columns like bytea and text last. You can check what you’re currently paying with pg_column_size() on a whole row versus the sum of its parts.

Reproduce the alignment measurement

Create both tables exactly as shown above, then fill them with the same values in each column order:

fill both tables

INSERT INTO ev_bad
SELECT (random()<0.01), 23000000 + i, (i%700)::smallint,
       now() - (i || ' seconds')::interval, (random()<0.3), (i%300), true
FROM generate_series(1,1000000) i;

INSERT INTO ev_good
SELECT 23000000 + i, now() - (i || ' seconds')::interval, (i%300),
       (i%700)::smallint, (random()<0.01), (random()<0.3), true
FROM generate_series(1,1000000) i;

VACUUM ANALYZE ev_bad;
VACUUM ANALYZE ev_good;

read the sizes

SELECT 'ev_bad' AS t,
       pg_size_pretty(pg_relation_size('ev_bad')) AS size,
       pg_relation_size('ev_bad')/8192            AS pages,
       (SELECT count(*) FROM ev_bad  WHERE ctid::text LIKE '(0,%') AS rows_per_page
UNION ALL
SELECT 'ev_good',
       pg_size_pretty(pg_relation_size('ev_good')),
       pg_relation_size('ev_good')/8192,
       (SELECT count(*) FROM ev_good WHERE ctid::text LIKE '(0,%');

-- exact percentage
SELECT round(100.0 * (pg_relation_size('ev_bad') - pg_relation_size('ev_good'))
             / pg_relation_size('ev_bad'), 1) AS pct_saved;

result

    t    | size  | pages | rows_per_page
---------+-------+-------+---------------
 ev_bad  | 73 MB |  9346 |           107
 ev_good | 57 MB |  7353 |           136

 pct_saved
-----------
      21.3

To find the padding in a table you already have, compare the real row width against the sum of the columns. The gap is padding:

find your own padding

SELECT pg_column_size(t.*) AS actual_row_size,
       pg_column_size(t.block_number) + pg_column_size(t.block_time)
     + pg_column_size(t.tx_index)     + pg_column_size(t.log_index)
     + pg_column_size(t.removed)      + pg_column_size(t.is_native)
     + pg_column_size(t.confirmed)    AS sum_of_columns
FROM ev_bad t LIMIT 1;

4. Normalize the addresses that repeat

Section 1 got an address down to 21 bytes. You can do better when the same address shows up over and over.

A swaps table is the clearest case. Every row has a token_in and a token_out, and across millions of trades those point at a few thousand distinct tokens. You are storing the same 20 bytes hundreds of thousands of times.

intern the addresses

CREATE TABLE tokens (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  addr bytea UNIQUE
);

-- in the swaps table
token_in_id  integer,
token_out_id integer,

21 bytes becomes 4. On 1M swap rows with 10k distinct tokens, that took the heap from 173.6 MiB to 142.0 MiB — 18% off the whole table for two columns. The tokens lookup table costs 1.3 MB, including its indexes.

The reason this works is cardinality, and cardinality is also the reason it sometimes doesn’t. Apply it to tx_hash and you get a lookup table with as many rows as the original, plus a join, plus an index, for nothing. The test is the ratio: a few thousand distinct values across millions of rows is a clear win, and anything approaching unique is a clear loss.

Reproduce the normalization measurement

Build the lookup table from the distinct addresses on both sides, then join through it:

build and populate

CREATE TABLE tokens (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  addr bytea UNIQUE
);

INSERT INTO tokens(addr)
SELECT DISTINCT token_in FROM src
UNION
SELECT DISTINCT token_out FROM src;

CREATE TABLE v_norm (
  block_time   timestamptz,
  block_number integer,
  tx_hash      bytea,
  pair         bytea,
  sender       bytea,
  amount_in    numeric,
  amount_out   numeric,
  token_in_id  integer,
  token_out_id integer,
  log_index    smallint
);

INSERT INTO v_norm
SELECT s.block_time, s.block_number::int, s.tx_hash, s.pair, s.sender,
       s.amount_in, s.amount_out, ti.id, to_.id, s.log_index
FROM src s
JOIN tokens ti  ON ti.addr  = s.token_in
JOIN tokens to_ ON to_.addr = s.token_out;

VACUUM ANALYZE v_norm;
VACUUM ANALYZE tokens;

read the sizes

SELECT pg_size_pretty(pg_relation_size('v_norm'))       AS heap,
       pg_size_pretty(pg_total_relation_size('tokens')) AS lookup_total,
       (SELECT count(*) FROM tokens)                    AS n_tokens;

result

  heap  | lookup_total | n_tokens
--------+--------------+----------
 142 MB | 1288 kB      |    10000

Before committing to this, check the ratio on your own table. Anything near 1.0 means normalizing will cost you more than it saves:

is normalizing worth it?

SELECT count(DISTINCT token_in)::numeric / count(*) AS distinct_ratio_token,
       count(DISTINCT tx_hash)::numeric  / count(*) AS distinct_ratio_hash
FROM src;

5. BRIN where the data is already ordered

Indexers append in block order. That is the single most exploitable property of indexer data and almost nobody uses it.

PostgreSQL tracks how well a column’s physical order matches its logical order, as a correlation between -1 and 1. On the swaps table:

Column	Correlation
`block_number`	1.0
`block_time`	-1.0
`sender`	-0.005

block_number is perfectly correlated, because rows arrived in block order and never moved. A btree ignores that and stores a pointer for every single row. A BRIN index stores the min and max per block range instead — a handful of values per 128 pages.

The difference on 1M rows is not subtle:

Index on `block_number`	Size
btree	6,952 KiB
BRIN	24 KiB

290× smaller. Not 29%, 290 times.

Warm, over five runs of a 1000-block range scan, the btree actually wins:

Index	Warm range scan
btree	~2.4 ms
BRIN	~2.8 ms

BRIN is slightly slower here, because it is lossy. It narrows the search to candidate page ranges and PostgreSQL rechecks every row in them — on that query it read 40,040 matching rows and threw away 9,249 it didn’t need. The bitmap index scan touches only 8 buffers, because the whole BRIN index is 24 KiB, but the heap recheck gives that advantage back.

So BRIN is not a speed optimization. It is a 290× size optimization that costs you a few percent of range-scan latency, and the size is what keeps your index resident in cache as the table grows into the hundreds of millions of rows.

Reproduce the BRIN comparison

Start with the correlation, because it decides whether BRIN is viable at all. Anything near ±1 is a candidate, anything near 0 is not:

check correlation first

ANALYZE v_norm;

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'v_norm'
  AND attname IN ('block_number','block_time','sender','tx_hash');

result

   attname    | correlation
--------------+-------------
 block_number |           1
 block_time   |          -1
 sender       | -0.00458477

Then build both indexes and compare:

btree vs BRIN size

CREATE INDEX i_bn      ON v_norm(block_number);
CREATE INDEX i_bn_brin ON v_norm USING brin(block_number);

SELECT pg_size_pretty(pg_relation_size('i_bn'))      AS btree,
       pg_size_pretty(pg_relation_size('i_bn_brin')) AS brin;

result

  btree   | brin
----------+-------
 6952 kB  | 24 kB

Note that CREATE INDEX ... ON t(col) always builds a btree, whatever the column type. BRIN is never the default and only happens when you write USING brin.

Now the timing, and this is where it is easy to fool yourself. Drop one index so the planner has no choice, warm the cache, then run several times and take the median:

fair warm-cache timing

SET max_parallel_workers_per_gather = 0;
SET jit = off;
\timing on

-- warm the cache first, then run 5x and take the median
SELECT count(*), sum(amount_in) FROM v_norm
WHERE block_number BETWEEN 23010000 AND 23011000;

To see why BRIN is slower, read the plan rather than the clock. Rows Removed by Index Recheck and Heap Blocks: lossy are the cost you are paying for the 290× size reduction:

where the time goes

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*), sum(amount_in) FROM v_norm
WHERE block_number BETWEEN 23010000 AND 23011000;

BRIN plan

Bitmap Heap Scan on v_norm (actual rows=40040.00)
  Recheck Cond: ((block_number >= 23010000) AND (block_number <= 23011000))
  Rows Removed by Index Recheck: 9249
  Heap Blocks: lossy=896
  ->  Bitmap Index Scan on i_bn_brin (actual rows=8960.00)
        Buffers: shared hit=8            <- the index scan touched only 8 buffers

A btree on the same query produces Index Scan using i_bn with no recheck line at all. If you ever see a plain Index Scan on a BRIN index, something is wrong — BRIN is lossy and can only ever produce a bitmap scan.

6. Compress the raw payloads

If you keep the raw data field of a log — and you should, it is the only way to re-decode when you get an ABI wrong — that column dominates everything else on this list.

PostgreSQL compresses values that exceed roughly 2 KB, via TOAST. Below that threshold values sit inline, uncompressed. That threshold produces a result worth knowing about.

50k rows of a 100-word event payload, 3,200 real bytes each, 152.6 MiB of actual payload:

Storage	Total
`text` + pglz	36.1 MiB
`text` + lz4	38.9 MiB
`bytea` + pglz	35.1 MiB
`bytea` + lz4	33.9 MiB
`bytea`, compression off	200.1 MiB

Two things fall out of this.

First, compression matters far more than the type does at this size. The gap between text and bytea is 36.1 against 33.9 MiB, about 6%. The gap between compressed and uncompressed is 33.9 against 200.1 MiB. Once a value is big enough to TOAST, the compressor squeezes out the same hex redundancy that section 1 was fighting, and the bytea advantage mostly evaporates. Section 1’s 50% is real for small inline columns and indexed columns. It does not extend to large blobs.

Second, be careful benchmarking this. My first attempt used a 1,280-byte payload and reported text at 17.6 MiB against bytea at 65.2 MiB, which looks like text beating bytea by 4×. It wasn’t. The hex text version was 2,562 bytes and crossed the TOAST threshold, so it got compressed. The bytea version was 1,280 bytes, stayed inline, and got no compression at all. I was measuring the threshold, not the encoding.

Reproduce the compression comparison

The payload has to be big enough that every variant clears the TOAST threshold, otherwise you measure the threshold instead of the encoding. 100 uint256 words is 3,200 real bytes and 6,402 hex characters, which is safely over it on both sides:

five storage strategies

CREATE TABLE c_text_pglz  (id int, data text  COMPRESSION pglz);
CREATE TABLE c_text_lz4   (id int, data text  COMPRESSION lz4);
CREATE TABLE c_bytea_pglz (id int, data bytea COMPRESSION pglz);
CREATE TABLE c_bytea_lz4  (id int, data bytea COMPRESSION lz4);
CREATE TABLE c_bytea_ext  (id int, data bytea);

-- STORAGE EXTERNAL forces out-of-line TOAST with no compression
ALTER TABLE c_bytea_ext ALTER COLUMN data SET STORAGE EXTERNAL;

-- realistic event payload: 40+ uint256 args, mostly leading zeros
CREATE TEMP TABLE payload AS
SELECT i, string_agg(lpad(to_hex((i*w)%100000), 64, '0'), '') AS hexdata
FROM generate_series(1,50000) i, generate_series(1,100) w
GROUP BY i;

INSERT INTO c_text_pglz  SELECT i, '0x'||hexdata FROM payload;
INSERT INTO c_text_lz4   SELECT i, '0x'||hexdata FROM payload;
INSERT INTO c_bytea_pglz SELECT i, decode(hexdata,'hex') FROM payload;
INSERT INTO c_bytea_lz4  SELECT i, decode(hexdata,'hex') FROM payload;
INSERT INTO c_bytea_ext  SELECT i, decode(hexdata,'hex') FROM payload;

pg_total_relation_size is the one to read here, not pg_relation_size — the payload lives in the TOAST table, and pg_relation_size would show you a nearly empty heap:

read the sizes

SELECT 'text  + pglz' AS v, pg_size_pretty(pg_total_relation_size('c_text_pglz'))  AS total
UNION ALL SELECT 'text  + lz4',  pg_size_pretty(pg_total_relation_size('c_text_lz4'))
UNION ALL SELECT 'bytea + pglz', pg_size_pretty(pg_total_relation_size('c_bytea_pglz'))
UNION ALL SELECT 'bytea + lz4',  pg_size_pretty(pg_total_relation_size('c_bytea_lz4'))
UNION ALL SELECT 'bytea, no compression', pg_size_pretty(pg_total_relation_size('c_bytea_ext'));

result

           v           | total
-----------------------+--------
 text  + pglz          | 36 MB
 text  + lz4           | 39 MB
 bytea + pglz          | 35 MB
 bytea + lz4           | 34 MB
 bytea, no compression | 200 MB

Check what a column is actually doing today. A column set to PLAIN or EXTERNAL is never compressed, no matter what default_toast_compression says:

audit your own columns

SELECT attname,
       CASE attstorage
         WHEN 'p' THEN 'plain (never TOASTed, never compressed)'
         WHEN 'e' THEN 'external (TOASTed, NOT compressed)'
         WHEN 'm' THEN 'main (compressed, kept inline)'
         WHEN 'x' THEN 'extended (compressed + TOASTed) — the default'
       END AS storage,
       attcompression AS codec   -- 'p' = pglz, 'l' = lz4, '' = server default
FROM pg_attribute
WHERE attrelid = 'your_table'::regclass AND attnum > 0 AND NOT attisdropped;

switch an existing column to lz4

ALTER TABLE your_table ALTER COLUMN data SET COMPRESSION lz4;
-- applies to NEW rows only; existing values keep their original codec
-- until rewritten by VACUUM FULL or a dump/restore

7. Partition for lifecycle, not for size

I’ll start with the number that surprised me, because it runs against how partitioning usually gets sold.

Same 1M rows, same indexes, flat table against 11 partitions by block range:

Layout	Total footprint
Flat	226.4 MiB
Partitioned (×11)	238.6 MiB

Partitioning made it 5.4% bigger. Every partition carries its own indexes, and each one rounds up to whole pages. Split a table 11 ways and you pay that overhead 11 times. If someone tells you to partition to save space, they haven’t measured it.

Partition anyway. Just do it for the right reason.

The reason is that blockchain data has a lifecycle, and deleting old data from a flat table is brutal. Dropping 2,500 blocks of history:

Operation	Time
`DROP TABLE` one partition	2.4 ms
`DELETE FROM` the flat table	32.7 ms

That 13× understates it badly. The DELETE touched 101,817 buffers and left 100k dead tuples behind for VACUUM to clean up, along with the index entries pointing at them, and the bloat stays until it runs. The DROP unlinks a file. It leaves nothing to clean up.

The second reason is pruning. A query against a block range only plans the partitions that can contain it:

Aggregate
  ->  Bitmap Heap Scan on p_norm_2      <- one partition, not eleven
        Recheck Cond: block_number >= 23005000 AND block_number <= 23006000

And the third is that per-partition indexes stay small. The sender btree is 28 MB flat; on a single partition it is 3,992 KiB. Reindexing one partition after a reorg is a completely different operation from reindexing 500M rows.

Reproduce the partitioning measurement

partition by block range

CREATE TABLE p_norm (LIKE v_norm INCLUDING DEFAULTS)
PARTITION BY RANGE (block_number);

-- 11 partitions of 2500 blocks each
DO $$
DECLARE lo int;
BEGIN
  FOR lo IN 0..10 LOOP
    EXECUTE format(
      'CREATE TABLE p_norm_%s PARTITION OF p_norm FOR VALUES FROM (%s) TO (%s)',
      lo, 23000000 + lo*2500, 23000000 + (lo+1)*2500);
  END LOOP;
END $$;

INSERT INTO p_norm SELECT * FROM v_norm;
CREATE INDEX ON p_norm(sender);
CREATE INDEX ON p_norm USING brin(block_number);
VACUUM ANALYZE p_norm;

A partitioned parent stores nothing itself, so pg_total_relation_size on it returns 0. You have to sum the leaves:

size a partitioned table correctly

SELECT 'flat' AS t,
       pg_size_pretty(pg_total_relation_size('v_norm')) AS total
UNION ALL
SELECT 'partitioned x11',
       pg_size_pretty(sum(pg_total_relation_size(relid))::bigint)
FROM pg_partition_tree('p_norm') WHERE isleaf;

result

        t        | total
-----------------+--------
 flat            | 226 MB
 partitioned x11 | 239 MB

Then compare the two ways of deleting history:

drop vs delete

\timing on

-- partitioned: unlink a file
DROP TABLE p_norm_0;                                    -- 2.4 ms

-- flat: rewrite 100k rows and leave them dead behind
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
DELETE FROM v_norm WHERE block_number < 23002500;       -- 32.7 ms

The BUFFERS line on that DELETE is the real story — 101,817 buffers touched, and 100k dead tuples plus their index entries left for VACUUM. Confirm pruning is actually happening with:

verify pruning

EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM p_norm WHERE block_number BETWEEN 23005000 AND 23006000;
-- exactly one partition should appear in the plan

Putting it together

Each of these is worth something on its own. Stacked, they compound, because they all reduce the same underlying unit: pages.

One benchmark, 1M synthetic swap rows, identical data in every variant. 50k pools, 500k senders, 10k tokens, unique hashes. Ten columns: block time, block number, tx hash, pair, sender, two token addresses, two amounts, log index. Each variant adds one technique to the one above it:

Variant	Heap	Rows / page
A — everything `text`, natural order	335.2 MiB	24
B — hex as `bytea`	211.2 MiB	37
C — typed scalars + column ordering	173.6 MiB	45
D — token addresses normalized	142.0 MiB	55

57.6% off the heap, and 2.3× the rows in every page you read.

With indexes included, on the same data:

Layout	Heap + indexes
Naive (`text` everywhere)	484.3 MiB
Optimized	241.6 MiB

Half the disk. Swap the block_number btree for BRIN and it drops another 6.8 MB. On 500M rows, that ratio is the difference between an instance that holds its working set in RAM and one that doesn’t.

Reproduce the whole stack

All four variants read from the src table in the harness above, so the values are identical and only the layout changes. Variant D is the v_norm table from section 4.

A — the naive schema

CREATE TABLE v_naive (
  log_index text, block_number text, tx_hash text, pair text, block_time text,
  sender text, token_in text, amount_in text, token_out text, amount_out text
);

INSERT INTO v_naive SELECT
  log_index::text, block_number::text, '0x'||encode(tx_hash,'hex'),
  '0x'||encode(pair,'hex'), to_char(block_time,'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
  '0x'||encode(sender,'hex'), '0x'||encode(token_in,'hex'), amount_in::text,
  '0x'||encode(token_out,'hex'), amount_out::text
FROM src;

B — hex as bytea, everything else unchanged

CREATE TABLE v_bytea (
  log_index text, block_number text, tx_hash bytea, pair bytea, block_time text,
  sender bytea, token_in bytea, amount_in text, token_out bytea, amount_out text
);

INSERT INTO v_bytea SELECT
  log_index::text, block_number::text, tx_hash, pair,
  to_char(block_time,'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
  sender, token_in, amount_in::text, token_out, amount_out::text
FROM src;

C — real types, widest-first order

CREATE TABLE v_typed (
  block_time   timestamptz,
  block_number integer,
  tx_hash      bytea,
  pair         bytea,
  sender       bytea,
  token_in     bytea,
  token_out    bytea,
  amount_in    numeric,
  amount_out   numeric,
  log_index    smallint
);

INSERT INTO v_typed SELECT
  block_time, block_number::int, tx_hash, pair, sender,
  token_in, token_out, amount_in, amount_out, log_index
FROM src;

compare all four

VACUUM ANALYZE v_naive; VACUUM ANALYZE v_bytea;
VACUUM ANALYZE v_typed; VACUUM ANALYZE v_norm;

SELECT 'A naive'      AS variant, pg_size_pretty(pg_relation_size('v_naive')) AS heap,
       (SELECT count(*) FROM v_naive WHERE ctid::text LIKE '(0,%') AS rows_per_page
UNION ALL SELECT 'B + bytea', pg_size_pretty(pg_relation_size('v_bytea')),
       (SELECT count(*) FROM v_bytea WHERE ctid::text LIKE '(0,%')
UNION ALL SELECT 'C + typed', pg_size_pretty(pg_relation_size('v_typed')),
       (SELECT count(*) FROM v_typed WHERE ctid::text LIKE '(0,%')
UNION ALL SELECT 'D + normalized', pg_size_pretty(pg_relation_size('v_norm')),
       (SELECT count(*) FROM v_norm WHERE ctid::text LIKE '(0,%');

result

    variant     |  heap  | rows_per_page
----------------+--------+---------------
 A naive        | 335 MB |            24
 B + bytea      | 211 MB |            37
 C + typed      | 174 MB |            45
 D + normalized | 142 MB |            55

For the heap-plus-indexes figure, put the same four indexes on the naive and the optimized table and read pg_total_relation_size:

with indexes

CREATE INDEX ON v_naive(pair);   CREATE INDEX ON v_naive(sender);
CREATE INDEX ON v_naive(tx_hash); CREATE INDEX ON v_naive(block_number);
CREATE INDEX ON v_norm(pair);    CREATE INDEX ON v_norm(sender);
CREATE INDEX ON v_norm(tx_hash);  CREATE INDEX ON v_norm(block_number);
VACUUM ANALYZE v_naive; VACUUM ANALYZE v_norm;

SELECT pg_size_pretty(pg_total_relation_size('v_naive')) AS naive,
       pg_size_pretty(pg_total_relation_size('v_norm'))  AS optimized;

result

  naive   | optimized
----------+-----------
 484 MB   | 242 MB

Takeaways

Store hex as bytea. Roughly 50% on small columns, and it hits every index that touches them.
Store amounts as numeric, not text and not bytea. Trailing zeros are nearly free; a 1e18 transfer is 5 bytes.
Order columns widest first. 21.3%, and it costs you nothing but the order of a CREATE TABLE.
Normalize values with low cardinality and high repetition. Tokens yes, hashes no.
Use BRIN on block_number and block_time. 290× smaller. Keep btree for point lookups on random keys.
Turn on lz4 and make sure your large payloads are actually being compressed.
Partition on block_number for reorgs, retention and pruning, and accept that it costs about 5% more disk.

Measure your own tables before and after. pg_column_size(), pg_relation_size() and pg_total_relation_size() are all you need, and every number in this article came out of them. Your cardinalities are not my cardinalities, and the normalization and BRIN sections in particular swing hard on that.