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

11–20 of 72 posts

Re: Unconventional PostgreSQL Optimizations

#11

Post by singron »

SigmundA wrote: post #9

>Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning via inheritance trees. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. PG's lack of plan caching strikes again, this sort of thing is not a concern in other DB's that reuse query plans…

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 item_id = ANY($2)` ($2 is an array parameter), the generic query plan doesn't know how many elements are in the array and can decide to do an elaborate plan like a bitmap index scan instead of a nested loop join. I've seen the generic plan flip-flop in a situation like this and have a >100x load difference.

The plan cache is also per-connection, so you still have to plan a query multiple times. This is another reason why consolidating connections in PG is important.

0: https://www.postgresql.org/docs/current/runtime-config-query...

Re: Unconventional PostgreSQL Optimizations

#12

Post by msdrigg »

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.id, s.url); MERGE 1 ```

I use `insert ... on conflict do update ...` all the time to handle upserts, but it seems like merge may be more powerful and able to work in more scenarios. I hadn't heard of it before.

Re: Unconventional PostgreSQL Optimizations

#13

Post by gshulegaard »

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…

IIRC `MERGE` has been part of SQL for a while, but Postgres opted against adding it for many years because it's syntax is inherently non-atomic within Postgres's MVCC model.

https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert...

This is somewhat a personal preference, but I would just use `INSERT ... ON CONFLICT` and design my data model around it as much as I can. If I absolutely need the more general features of `MERGE` and _can't_ design an alternative using `INSERT ... ON CONLFICT` then I would take a bit of extra time to ensure I handle `MERGE` edge cases (failures) gracefully.

Re: Unconventional PostgreSQL Optimizations

#14

Post by themafia »

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).

Re: Unconventional PostgreSQL Optimizations

#15

Post by folli »

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).

You can use an IDE like IntelliJ and you get syntax highlighting, code completion etc.

Re: Unconventional PostgreSQL Optimizations

#16

Post by philjohn »

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…

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.

Re: Unconventional PostgreSQL Optimizations

#17

Post by tuetuopay »

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).

I generally use pgcli to that end. Works well, has a few niceties like clearer transaction state, better reconnect, syntax highlighting, and better autocomplete that works in many more cases than plain psql (it can even autocomplete on clauses when foreign key relations are defined!).

My only gripe with it is its insistence on adding a space after a line break when the query is too long, making copy/paste a pain for long queries.

Re: Unconventional PostgreSQL Optimizations

#19

Post by masklinn »

zenmac wrote: post #8
pksunkara wrote: post #3

I think a stored generated column allows you to create an index on it directly. Isn't it better approach?

>I think a stored generated column allows you to create an index on it directly. Isn't it better approach? Is it also possible to create index (maybe partial index) on expressions?

That's the first solution (a function based index), however it has the drawback of fragility: a seemingly innocent change to the query can lead to not matching the index's expression anymore). Which is why the article moves on to generated columns.

Re: Unconventional PostgreSQL Optimizations

#20

Post by sirfz »

philjohn wrote: post #16
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…

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
Post reply on HN