Skip to content

Instantly share code, notes, and snippets.

@lfittl
Created August 7, 2023 18:54
Show Gist options
  • Save lfittl/61f7203be2624ee32644ee41301a318e to your computer and use it in GitHub Desktop.
Save lfittl/61f7203be2624ee32644ee41301a318e to your computer and use it in GitHub Desktop.
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

INSERT

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

podman exec -it pg16-insert /bin/bash

pgbench -U postgres postgres -i -s 10 -ItG

Same steps for both:

psql -U postgres

SELECT context, reads, writes, extends FROM pg_stat_io WHERE backend_type = 'client backend';

CREATE EXTENSION pg_buffercache;

SELECT n.nspname, c.relname, count(*) AS buffers
FROM pg_buffercache b JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database
                        WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname
ORDER BY 3 DESC
LIMIT 10;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment