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

61–70 of 72 posts

Re: Unconventional PostgreSQL Optimizations

#61

Post by SigmundA »

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.

Not just maintained automatically, clustered indexes have no heap at all, the table is an index.

The CLUSTER command in PG just moves rows around in the heap so they match the still separate index order which can help a little bit with range operations because rows are close on disk, but otherwise doesn't do much.

So they are completely separate things that just happen to use the same term.

Re: Unconventional PostgreSQL Optimizations

#62

Post by SigmundA »

branko_d wrote: post #50
Tostino wrote: post #47

Earlier quoted context omitted.

Clustered indexes aren't just about write amplification. They also reduce the reads needed to get the data. Sometimes by quite a bit.

That's true for seeks into the clustered (primary) index because that index includes all fields, so you don't need to "jump" to the heap to get them. However, seeking into a secondary index, and then reading a column not included in that index incurs an additional index seek (into the clustered index), which may be somewhat slower than what would happen in a heap-based table. So there are pros and cons, as usual...

I have found very minimal penalty on secondary index reads in practice such that it has never made a difference.

Remember some databases always use clustered index internally (SQLite, MySql) such that even if you have no primary key they will create a hidden one instead for use with the index.

https://www.sqlite.org/rowidtable.html

It is nice to have the choice which way to go and would be nice if PG implemented this. It can have significant space savings on narrow table with one primary index and performance advantages.

Re: Unconventional PostgreSQL Optimizations

#63

Post by anarazel »

mxey wrote: post #54
SigmundA wrote: post #22

Earlier quoted context omitted.

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

> 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

That's part of it, another big part is that the transactional DDL makes it more complicated, as different sessions might require different plans.

Re: Unconventional PostgreSQL Optimizations

#64

Post by SigmundA »

mxey wrote: post #54
SigmundA wrote: post #22

Earlier quoted context omitted.

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

In MSSQL Server part of the plan match is the various session/connection options, if they are different there are different plans cached.

I believe the plan data structure PG is intimately tied to process space memory addresses since it was never thought to share between them and can even contain executable code that was generated.

This makes it difficult to share between processes without a heavy redesign but would be a good change IMO.

Re: Unconventional PostgreSQL Optimizations

#65

Post by Marazan »

My favourite PostgresSQL optimization was running a SELECT on a multi-billion row table before running a DELETE so that the shared_memory_buffer would be filled with the rows that would need to be deleted.

Postgres makes DELETEs single threaded, this includes the selection part of the DELETE. By running a completely separate SELECT first Postgres would multithread the SELECT and populate the cache fast. Then the single thread DELETE can operate on in-memory data and not endlessly block loading data from disk.

Re: Unconventional PostgreSQL Optimizations

#66

Post by hans_castorp »

themafia wrote: post #14

I moved into the cloud a few years ago and so I don't get to play with fixed server infrastructure like pgsql as much anymore. Is the syntax highlighting built into pgsql now or is that some other wrapper that provides that? (it looks really nice).

No relational database implements syntax highlighting. The tools you use to write and execute SQL queries implement that.

Re: Unconventional PostgreSQL Optimizations

#67

Post by kbolino »

da_chicken wrote: post #48
kbolino wrote: post #25

Earlier quoted context omitted.

It's kinda hard to handle MERGE failures gracefully. You generally expect the whole thing to succeed, and the syntax deceptively makes it seem like you can handle all the cases. But because of MVCC, you get these TOCTOU-style spurious constraint violations, yet there's no way to address them on a per-row basis, leading to the entire statement rolling back even for the rows that had no issues. If you are designing for…

I'm not sure why you'd expect partial updates of a single statement in the first place. I mean, if I run `UPDATE Account SET Status = 'Closed' WHERE LastAccess If you're experiencing things that smell like TOCTOU, first you need to be sure you don't have oddball many-to-one issues going on (i.e., a cardinality violation error), and then you're going to have to increase your transaction isolation level to eliminate no…

I don't want partial updates, I want full, conflict-free upserts.

At read committed (default) isolation level, INSERT ... ON CONFLICT handles concurrent, conflicting inserts just fine, while MERGE ... WHEN NOT MATCHED (e.g.) does not. This is surprising behavior from the syntax alone, one would assume the two statements, when written with the same intent, would have the same behavior. Of course, this difference is documented, see the last paragraph of the Notes section on MERGE: https://www.postgresql.org/docs/18/sql-merge.html#id-1.9.3.1...

I don't know this for sure, but I believe that the effect of raising the transaction isolation level will just be to turn the constraint violation into a serialization error. That's not any easier to handle gracefully.

Re: Unconventional PostgreSQL Optimizations

#68

Post by tizzy »

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.

That's super interesting and I am convinced by the dbfiddle but is not very intuitive or well documented? https://www.postgresql.org/docs/current/hash-index.html

Re: Unconventional PostgreSQL Optimizations

#69

Post by nicwolff »

sirfz wrote: post #20
philjohn wrote: post #16

Earlier quoted context omitted.

If you're doing large batch inserts, I've found using the COPY INTO the fastest way, especially if you use the binary data format so there's no overhead on the postgres server side.

That doesn't work well with conflicts tho iirc

COPY INTO a temp table and INSERT INTO... SELECT FROM... ON CONFLICT UPDATE...
Post reply on HN