Load and export data fast with COPY and \copy, then split a very large table into manageable pieces with declarative range partitioning.
Why: inserting millions of rows one INSERT at a time is slow. COPY streams a whole CSV file into a table in one fast operation — the standard way to import data. Note: server-side COPY reads a path on the database server itself.
COPY products (sku, name, price)
FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true);Why: server-side COPY needs the file to sit on the server. From psql, the \copy command reads/writes a file on your own machine instead, which is what you usually want. The same command runs in reverse to export a query’s results to CSV.
Import a local CSV from inside psql:
\copy products(sku, name, price) FROM 'products.csv' WITH (FORMAT csv, HEADER true)Export a query to a local CSV file:
\copy (SELECT * FROM orders WHERE total > 100) TO 'big_orders.csv' WITH (FORMAT csv, HEADER true)Why: when a table grows to hundreds of millions of rows, queries and maintenance slow down. Partitioning splits it into smaller child tables behind one logical table name — you query the parent, but PostgreSQL only touches the partitions that hold relevant rows.
-- one logical table, split by the created_at date
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);Why: each partition holds one slice of the data — here, one per year. Rows you insert into the parent automatically land in the matching partition, and a query filtered by date skips the partitions it does not need (called "partition pruning").
CREATE TABLE events_2024 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');CREATE TABLE events_2025 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');-- this insert is routed to events_2025 automatically (date falls in 2025)
INSERT INTO events (created_at, payload)
VALUES ('2025-06-15', '{"type": "signup"}');