Skip to content

Instantly share code, notes, and snippets.

@lfittl
lfittl / pganalyze-enterprise.yml
Created March 22, 2024 17:53
pganalyze Enterprise Server - Scale out architecture example (with separate web/worker deployment)
apiVersion: apps/v1
kind: Deployment
metadata:
name: pganalyze-web
namespace: default
labels:
app: pganalyze-web
spec:
replicas: 2
selector:
@lfittl
lfittl / steps.md
Created August 7, 2023 18:54
INSERT vs COPY impact on shared buffers

COPY

podman run -d -e POSTGRES_HOST_AUTH_METHOD=trust --name pg16-copy postgres:16beta2

podman exec -it pg16-copy /bin/bash

pgbench -U postgres postgres -i -s 10 -Itg
# | ?column? | lines | pct_lines | commits | current_employer
----+------------------------------+-------+-----------+---------+------------------
1 | Tom Lane | 66210 | 26.09 | 438 | Crunchy Data
2 | Tomas Vondra | 15357 | 6.05 | 50 | EDB
3 | Dagfinn Ilmari Mannsåker [*] | 14715 | 5.80 | 10 | ?
4 | Peter Eisentraut | 12976 | 5.11 | 214 | EDB
5 | Robert Haas | 7035 | 2.77 | 46 | EDB
6 | Bruce Momjian | 7010 | 2.76 | 58 | EDB
7 | Peter Geoghegan | 6889 | 2.71 | 91 | Zenith Labs
8 | Amit Langote [*] | 6859 | 2.70 | 24 | EDB
/* Based on https://stackoverflow.com/questions/65213615/cdk-to-enable-dns-resolution-for-vpcpeering */
import { custom_resources } from 'aws-cdk-lib';
import { aws_ec2 as ec2, aws_iam as iam, aws_logs as logs } from 'aws-cdk-lib';
import { Construct } from 'constructs';
export interface AllowVPCPeeringDNSResolutionProps {
vpcPeering: ec2.CfnVPCPeeringConnection,
}
[Unit]
Description=Keeper for pg_auto_failover
After=network.target
[Service]
ExecStart=/usr/bin/pg_autoctl run --pgdata /path/to/pg/data
User=postgres
ProtectSystem=full
ProtectHome=true
CapabilityBoundingSet=
@lfittl
lfittl / .flowconfig
Created December 2, 2017 21:51
Place override in flow-typed/npm/react-apollo_vx.x.x.js and ignore shipped types using .flowconfig
[ignore]
.*node_modules/react-apollo/.*
@lfittl
lfittl / drop_all_tables.sql
Created September 15, 2017 21:08
Drop All Tables
DO LANGUAGE plpgsql $$
DECLARE
t record;
tables text[];
BEGIN
FOR t IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
tables := array_append(tables, quote_ident(t.schemaname) || '.' || quote_ident(t.tablename));
END LOOP;
IF array_length(tables, 1) > 0 THEN
@lfittl
lfittl / postgres12.md
Last active December 11, 2018 00:39
Postgres 12 patches worth writing

Postgres Patches for 12

  • queryid in log_min_duration_statement
  • queryid generation in core instead of contrib/pg_stat_statements
  • pg_stat_plans in contrib (think of pg_stat_statements + auto_explain, possibly just store the EXPLAIN output as the text)
  • pg_stat_statements in core
  • pg_stat_statements should have idx_scan and seq_scan counters
  • changefeeds (aka logical decoding for realtime updates, instead of LISTEN/NOTIFY)
  • JSON logical decoder in core
  • COPY from URL / Make COPY extendable (so there can be an S3 handler)
@lfittl
lfittl / copy_from_client.rb
Last active March 24, 2017 22:09
Helper method to use Postgres' COPY with ActiveRecord
# Put this in config/initializers/copy_from_client.rb
class CopyFromClientHelper
attr_reader :count
def initialize(conn, column_types)
@count = 0
@conn = conn
@column_types = column_types
end
@lfittl
lfittl / docker-compose.yml
Last active September 13, 2024 17:23
Enabling pg_stat_statements in a Docker container
version: '2'
services:
db:
image: postgres:16
ports:
- "5432:5432"
command: >
postgres
-c shared_preload_libraries='pg_stat_statements'