Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Unconventional PostgreSQL Optimizations

hakibenita.com

Post reply on HN
Print view View original

51–60 of 72 posts

Re: Unconventional PostgreSQL Optimizations

#51

Post by wreath »

SigmundA wrote: post #30
zamalek wrote: post #27

> The index is 214 MB! That's almost half the size of the entire table. So the analysts are happy, but you? Not so much... This is part of a broader choice: write amplification. You'd want to, of course, have the most precise index possible - but no matter how you cut it, you are incurring extra I/O for writes - one for the tuple, one per index. How you index things is heavily influenced by the mix of reads and write…

Would be nice if PG supported clustered indexes (Index Organized Tables in Oracle speak) as an option if you have a table thats accessed mostly the same way you can get a index without the write amplification because the table is the index.

It does support index organized tables with the CLUSTER command, or you meant something else?

Re: Unconventional PostgreSQL Optimizations

#52

Post by fuy »

wreath wrote: post #51
SigmundA wrote: post #30

Earlier quoted context omitted.

Would be nice if PG supported clustered indexes (Index Organized Tables in Oracle speak) as an option if you have a table thats accessed mostly the same way you can get a index without the write amplification because the table is the index.

It does support index organized tables with the CLUSTER command, or you meant something else?

CLUSTER command is not the same as index organized tables, it's a one-time "physical sort" operation. New data is not organized until you run CLUSTER again. Index organized tables are maintained automatically by Oracle/SQL Server.

Re: Unconventional PostgreSQL Optimizations

#53

Post by mxey »

Diggsey wrote: post #29
danielheath wrote: post #24

The hash technique for uniqueness isn’t supported for indexes because it doesn’t handle hash collisions. The authors proposed solution suffers the same problem- values which do not already exist in the table will sometimes be rejected because they have the same hash as something that was already saved.

This is completely untrue. While the index only stores the hashes, the table itself stores the full value and postgres requires both the hash and the full value to match before rejecting the new row. Ie. Duplicate hashes are fine.

This is very good to know because it means this exclusion constraint workaround is a better approach over using a SQL hash function and a btree if you want to enforce uniqueness on values too long for a btree index.

Re: Unconventional PostgreSQL Optimizations

#54

Post by mxey »

SigmundA wrote: post #22
singron wrote: post #11

Earlier quoted context omitted.

PG does reuse plans, but only if you prepare a query and run it more than 5 times on that connection. See plan_cache_mode[0] and the PREPARE docs it links to. This works great on simple queries that run all the time. It sometimes really stinks on some queries since the generic plan can't "see" the parameter values anymore. E.g. if you have an index on (customer_id, item_id) and run a query where `customer_id = $1 AND…

Yes manual query preparation by client [1] is what you did in MSSQL server up until v7.0 I believe, which was 1998 when it started doing automatic caching based on statement text. I believe it also cached stored procedures before v7.0 which is one reason they were recommended for all application code access to the database back then. MSSQL server also does parameter sniffing now days and can have multiple plans based…

PostgreSQL shares other caches between processes so they probably could have a global plan cache if they wanted. I wonder why they don’t though.

One possible reason is that the planner configuration can be different per connection, so the plans might not transfer

Re: Unconventional PostgreSQL Optimizations

#56

Post by wreath »

fuy wrote: post #52
wreath wrote: post #51

Earlier quoted context omitted.

It does support index organized tables with the CLUSTER command, or you meant something else?

CLUSTER command is not the same as index organized tables, it's a one-time "physical sort" operation. New data is not organized until you run CLUSTER again. Index organized tables are maintained automatically by Oracle/SQL Server.

Interesting, i didnt know that. Thanks!

Re: Unconventional PostgreSQL Optimizations

#57

Post by MarkusWinand »

msdrigg wrote: post #12

The most interesting thing for me in this article was the mention of `MERGE` almost in passing at the end. > I'm not a big fan of using the constraint names in SQL, so to overcome both limitations I'd use MERGE instead: ``` db=# MERGE INTO urls t USING (VALUES (1000004, ' https://hakibenita.com ')) AS s(id, url) ON t.url = s.url WHEN MATCHED THEN UPDATE SET id = s.id WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.i…

INSERT ... ON CONFLICT has fewer surprises in context of concurrency: https://modern-sql.com/caniuse/merge#illogical-errors

Besides portability, there is IMHO nothing against INSERT ... ON CONFLICT if it does what you need.

Re: Unconventional PostgreSQL Optimizations

#58

Post by [deleted] »

Diggsey wrote: post #29
danielheath wrote: post #24

The hash technique for uniqueness isn’t supported for indexes because it doesn’t handle hash collisions. The authors proposed solution suffers the same problem- values which do not already exist in the table will sometimes be rejected because they have the same hash as something that was already saved.

This is completely untrue. While the index only stores the hashes, the table itself stores the full value and postgres requires both the hash and the full value to match before rejecting the new row. Ie. Duplicate hashes are fine.

[deleted]

Re: Unconventional PostgreSQL Optimizations

#59

Post by [deleted] »

Diggsey wrote: post #29
danielheath wrote: post #24

The hash technique for uniqueness isn’t supported for indexes because it doesn’t handle hash collisions. The authors proposed solution suffers the same problem- values which do not already exist in the table will sometimes be rejected because they have the same hash as something that was already saved.

This is completely untrue. While the index only stores the hashes, the table itself stores the full value and postgres requires both the hash and the full value to match before rejecting the new row. Ie. Duplicate hashes are fine.

[deleted]

Re: Unconventional PostgreSQL Optimizations

#60

Post by gmcabrita »

danielheath wrote: post #24

The hash technique for uniqueness isn’t supported for indexes because it doesn’t handle hash collisions. The authors proposed solution suffers the same problem- values which do not already exist in the table will sometimes be rejected because they have the same hash as something that was already saved.

Your comment is 100% not true: https://dbfiddle.uk/Iu-u886S
Post reply on HN