Crunchy Data

Announcing Crunchy Data Warehouse! A next-generation Postgres-native data warehouse. Full Iceberg support for fast analytical queries and transactions, built on unmodified Postgres to support the features and ecosystem you love. crunchydata.com/blog/crunchy-d…

psql tip: \h Did you know that psql has a huge help menu for SQL commands? \h + the command you want will pull up details about the syntax and the docs url. postgres# \h CREATE USER Command: CREATE USER Description: define a new database role Syntax: CREATE USER name [ […

Monitoring logical replication: Lots of folks use logical replication - to create a data copy for migration or to move data from one platform to another. To monitor this, Postgres has a catalog for the subscriber side that looks at what is happening per table,…

Let’s talk about Postgres views and materialized views. 🪟 Views are dynamic versions of data or joins created with a SQL statement. They are incredibly helpful for access control - you may only want some users to see some part of the data. Views are also great for…

Using a lot of JSON in Postgres? JSON is notoriously tricky to index. —> GIN indexes can be great for speeding up general searches or searching across many keys. However, often you’ll have queries that need to repeatedly access JSON for specific filtering or sorting on…

Postgres Performance Boost: HOT Updates and Fill Factor 🔥 HOT updates are when an updated row can fit on the same page as the old row. 🔥 HOT updates improve performance since they reduce IOPs, WAL, index updates and other resources 🔥 Consider your indexing strategy if you’re…

Using Postgres \copy but want to see a progress meter? There’s a pg_stat_progress_copy - but that’s a view that has to be queried specifically. If you’re looking for something inside the command line, mac and linux users can utilize the pv (pipe viewer) tool. This has to be…

Working with Postgres Foreign Data Wrappers & Performance ⚡ Use CTEs to send queries that have more information to limit full scans on the remote host ⚡ Use sub-queries for that same reason ⚡ If your foreign server has a lot of data, try to cache data if you can ⚡…

Postgres \timing vs EXPLAIN: EXPLAIN shows you the internal Postgres planner processes and query times. \timing shows the entire time something runs including local time to retrieve data from a remote database. When you put in \timing into a session or into your psqlrc…

Postgres TOAST: What is it and why should you care? crunchydata.com/blog/postgres-…

For production grade Postgres backups, we do not recommend pg_dump. However pg_dump and its sister utility pg_dumpall can be super helpful for a number of tasks. Let’s do a quick refresher on pg_dump and pg_dumpall: ➠ pg_dump This dumps a single database inside a Postgres…

HNSW Indexes with Postgres and pgvector: crunchydata.com/blog/hnsw-inde…

Postgres no longer has distinction between users and groups - it just has roles. An individual user is just a role with the login privilege and can be assigned to one or more additional roles as a way of creating groups of users. To create a new user in Postgres: 1️⃣ CREATE…

How to design a test suite that's fast and thorough: ● Don't mock databases. A little extra speed isn't worth the dramatic reduction in test fidelity. ● Make database use in tests easy with a fixture framework that does most of the work for you. It can even be homegrown…

We’ve all tried to write an aggregate in the WHERE statement … 🚫 SELECT department, SUM(salary) FROM employees WHERE SUM(salary) > 100000 GROUP BY department; But SQL doesn't work that way 😉 . HAVING works to filter based on aggregate values ✅ SELECT department,…

Row level security for multi-tenant Postgres: crunchydata.com/blog/row-level…

Need to measure the size of a Postgres table? `SELECT pg_relation_size('table_name');` will do it for the main relation file. But `pg_relation_size()` just gives you the size of the HEAP table itself. It doesn’t take into account all of the indexes, TOAST tables, free space maps…

See our hand's on tutorial for working with percentages in Postgres. crunchydata.com/developers/pla…

Postgres JSON tip: Use jsonb_pretty with any json filed to format output in a readable and indented format SELECT id, username, jsonb_pretty(profile) AS pretty_profile FROM users; id | username | pretty_profile…

Postgres MERGE. This was a long awaited feature introduced in Postgres 15. MERGE helps with upsert logic (aka the insert or update). Postgres folks are probably really familiar with INSERT ON CONFLICT logic. INSERT ... ON CONFLICT will not work with conditional logic that uses…

Postgres newbies - learn the basics of Postgres table creation, with primary keys, foreign keys, and data types on our tutorial site. crunchydata.com/developers/pla…