Hacker News Reader

Comment by dewey | Hacker News Reader

There's a lot of interesting improvements in PG15, some of my favorites from https://www.postgresql.org/about/news/postgresql-15-beta-1-r... are:

>PostgreSQL 15 introduces the jsonlog format for logging. This allows PostgreSQL logs to be consumed by many programs that perform structured log aggregation and analysis.

>pg_basebackup, a utility used to take full backups of a PostgreSQL cluster, now supports server-side compression using Gzip, LZ4, or Zstandard compression.

>This includes the introduction of parallelization for SELECT DISTINCT statements and improvements in performance to window functions that use row_number(), rank(), and count().

>PostgreSQL 15 builds on its existing support for the SQL/JSON path language by including more standard SQL/JSON functions. These include SQL/JSON constructors, query / introspection functions, and the ability to convert JSON data into a table.

View on HN

I'm also super excited for the points you raised, looks like some great features being released, I really like that JSON is becoming a first class citizen.

I'm also looking forward to the \copy improvements, especially around loading data into PG.

I think they missed one of the biggest changes: ICU collations can finally be set as default for clusters and databases.

I use both SELECT DISTINCT and row_number a lot, so excited for that as well.

SELECT DISTINCT is rarely the best idea. In most cases it means someone is using joins to filter out rows that should have been removed with EXISTS (a semi-join).

Interesting, I hadn't thought of it that way. Looking through our codebase:

- We get a lot of mileage out of DISTINCT ON to get the most recent version of a row for some subset of columns. I think this a different use case than what you refer to.

- We typically use DISTINCT to get PKs for a table when starting from another table. Like, find all user IDs that commented on a given story by looking at the join table.

    SELECT DISTINCT user_id FROM story_comment WHERE story_id = 4

- "most recent" is usually best done with a max() with an adequate index or, when that's not possible, with a LATERAL JOIN, an ORDER BY and a LIMIT 1

- SELECT id FROM users WHERE EXISTS (SELECT 1 FROM story_comment WHERE story_id = 4 AND story_comment.user_id = users.id)

Can you provide an example of how you'd rewrite a query using SELECT DESTINCT with EXISTS?

As far as I know, nested loop semi joins using EXISTS aren't always capable of using indexes.

Like in the case of a GIN indexed array intersection.

That’s fascinating! Totally orthogonal: how does PG store data? Do they use columnar specifications such as Arrow & Parquet?

It's row based with versions kept along side current data. Later to be vacuumed away. Unlike a redo log based approach.

There's a pretty good high-level overview of the implementation details in the official docs: https://www.postgresql.org/docs/current/storage.html

The main table is stored in a 'heap', which is more similar to the dynamic allocation region of a program rather than the data structure.

If it needs to write to the table, it has a free space map listing all of the available fixed size pages. Each page can hold some number of fixed size tuples.

I'm surprised they've gotten so far with this. It seems ripe for fragmentation issues. I'm surprised they don't at least have a b+tree implementation to provide clustering based on the primary key. They have a b+tree implementation for indices, but not for tuple storage. IIRC, there's a feature to allow fixed size data row to be stored directly in the index, to help alleviate the need to look in the heap table. Perhaps that's what's kept the heap as viable?

Variable length strings are kept in a separate file (TOAST tables), referenced by the tuple. This keeps records fixed size, and allows for circumventing disk reads for string data for queries that don't require them. I'm not quite sure how the allocation scheme or data structure works there. My (very speculative) guess is that they're stored as a linked list, and on write, the allocator tries to give contiguous pages.

fwiw, I don't use postgresql on a regular basis, I've just spent some good amount of time squinting at the file format and related code, soley due to my own curiosity. It's been a while, so it's very possible that I'm misrepresenting or misremembering something. Someone has already posted their documentation on the file format -- it's a good read if you're interested.

> but not for tuple storage

Both tables and indexes use the same b+-tree implementation based on Lehman and Yao (prev/next links on internal nodes).

> Each page can hold some number of fixed size tuples

Variable size.

> Variable length strings are kept in a separate file

Only if the row exceeds the page size, otherwise everything is stuffed into the data leaf node.

> I'm not quite sure how the allocation scheme or data structure works there

Values are broken up into DataSize/PageSize "virtual rows" and stitched back together, but everything is still in the same slotted page structure.

>Both tables and indexes use the same b+-tree implementation based on Lehman and Yao (prev/next links on internal nodes).

Tables in Postgres are not btree's they are unordered heaps. The row id in the b-tree index points to a page number + item index in the heap.

A true clustered index would simply expose the index itself as a table without secondary heap storage, reducing I/O and storage space. Row visibility seems to complicate this in PG as index only scans may require checking the heap for visibility.

>Variable length strings are kept in a separate file (TOAST tables), referenced by the tuple. This keeps records fixed size...

That's not entirely true: variable-width row values under a size threshold are stored inline (possibly compressed).

The beauty part about TOAST is that they're mostly just regular tables (any table that needs its data TOASTed gets its own TOAST table). You can look them up in the system catalogs and query them directly.

The schema for these is (chunk_id, chunk_seq, chunk_data). Chunk "pieces" are limited in size so they can be stored inline (obviously there's not much benefit to a TOAST table itself having a TOAST table), so any row values that need to be TOASTed may also be broken up into multiple pieces. Row values stored inline in the "owning" table reference a chunk_id where the TOASTed data is stored, and queries that need to return that data look up all the chunks for a chunk id, ordered by chunk_seq.

You're right that this scheme avoids reading TOASTed data when you're not actually using it (a good reason to avoid "SELECT *" if you've got big TOASTed data).

Reply on news.ycombinator.com