dpirotte

# With appendStringInfoStrings

wal2json version:   7da3e5060acaa5615d20f390ecc5eedb0e62d116
pg version:         PostgreSQL 11.0 (Debian 11.0-1.pgdg90+2)    
pgbench scale:      5
pgbench txns:       50000
pgbench throughput: 1053.523630
wal size:           218103808
slot read realtime: 0m9.017s        
slot read systime:  0m4.432s      

dpirotte

Latency Comparison Numbers
--------------------------
L1 cache reference                           0.5 ns
Branch mispredict                            5   ns
L2 cache reference                           7   ns                      14x L1 cache
Mutex lock/unlock                           25   ns
Main memory reference                      100   ns                      20x L2 cache, 200x L1 cache
Compress 1K bytes with Zippy             3,000   ns        3 us
Send 1K bytes over 1 Gbps network       10,000   ns       10 us
Read 4K randomly from SSD*             150,000   ns      150 us          ~1GB/sec SSD

dpirotte

lol=# select
  null = true as null_eq_true,
  null = false as null_eq_false,
  null != true as null_neq_true,
  null != false as null_neq_false,
  null = null as null_eq_null,
  null != null as null_neq_null,
  null is true as null_is_true,
  null is false as null_is_false,
  null is null as null_is_null,

dpirotte

with primary_server as (
  select split_part(x.full,'/',1) as xlog,
  split_part(x.full,'/',2) as offset
  from (select '88DC/113BBDA0'::text as full) x
),
standby_server as (
  select split_part(x.full,'/',1) as xlog,
  split_part(x.full,'/',2) as offset
  from (select '88DB/113BBDA0'::text as full) x
)

dpirotte

WITH btree_index_atts AS (
    SELECT nspname, relname, reltuples, relpages, indrelid, relam,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid as index_oid
    FROM pg_index
    JOIN pg_class ON pg_class.oid=pg_index.indexrelid
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    JOIN pg_am ON pg_class.relam = pg_am.oid
    WHERE pg_am.amname = 'btree'
    ),

dpirotte

SELECT
    (100 * checkpoints_req) /
        (checkpoints_timed + checkpoints_req) AS checkpoints_req_pct,
    pg_size_pretty(buffers_checkpoint * block_size /
         (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_write,
    pg_size_pretty(block_size *
        (buffers_checkpoint + buffers_clean + buffers_backend)) AS total_written,
    100 * buffers_checkpoint /
        (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_write_pct,
    100 * buffers_backend /

dpirotte

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    pg_size_pretty(pg_relation_size(i.relname::regclass)) as index_size,
    st.null_frac as null_frac
from
    pg_class t,
    pg_class i,
    pg_index ix,

dpirotte

create or replace function sequence_positions(OUT sequence_name regclass, OUT last_value bigint) returns setof record as $$
declare
  sequence regclass;
begin
  FOR sequence IN SELECT format('%s.%s', n.nspname, c.relname) AS sequence FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE (c.relkind = 'S')
  LOOP
    return query execute format('select ''%s''::regclass, last_value from %s', sequence, sequence);
  END LOOP;
end
$$ language plpgsql;

dpirotte

Keybase proof

I hereby claim:

• I am dpirotte on github.
• I am dpirotte (https://keybase.io/dpirotte) on keybase.
• I have a public key whose fingerprint is F949 3D7B 4B8C 8F6A DF27 B9A1 18AF 6F32 F6F7 7816

To claim this, I am signing this object:

dpirotte

SELECT
    l1.*,
    l2.virtualtransaction,
    l2.pid,
    l2.mode,
    l2.granted
FROM
    pg_locks l1
JOIN
    pg_locks l2 ON (