3 ms·
There's a lot of interesting improvements in PG15, some of my favorites from https://www.postgresql.org/about/news/postgresql-15-beta-1-released-2453/ https://w
by dewey 4y ago
There's a lot of interesting improvements in PG15, some of my favorites from https://www.postgresql.org/about/news/postgresql-15-beta-1-released-2453/ 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.
- thejosh 4y agoI'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.
- baggiponte 4y agoThat’s fascinating! Totally orthogonal: how does PG store data? Do they use columnar specifications such as Arrow & Parquet?
- paulryanrogers 4y agoIt's row based with versions kept along side current data. Later to be vacuumed away. Unlike a redo log based approach.
- uhoh-itsmaciek 4y agoThere's a pretty good high-level overview of the implementation details in the official docs: https://www.postgresql.org/docs/current/storage.html https://www.postgresql.org/docs/current/storage.html
- chrsig 4y agoThe 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.
- tomnipotent 4y ago> 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.
- SigmundA 4y ago>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.
- BitPirate 4y agoI think they missed one of the biggest changes: ICU collations can finally be set as default for clusters and databases.
- mchusma 4y agoI use both SELECT DISTINCT and row_number a lot, so excited for that as well.
- 1500100900 4y agoSELECT 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).
- sa46 4y agoInteresting, 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
- 1500100900 4y ago- "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)
- richbell 4y agoCan you provide an example of how you'd rewrite a query using SELECT DESTINCT with EXISTS?
- LunaSea 4y agoAs 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.