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

31–40 of 72 posts

Re: Unconventional PostgreSQL Optimizations

#31

Post by giovannibonetti »

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.

Clustered indexes only save up to 2x write amplification in the very rare case where you're indexing the entire table (e.g. if it has very few columns).

However, that is usually the least of your concerns with write amplification. If you don't batch your writes, you can easily get 100x write amplification. For any primary key or any other index not strongly correlated with your INSERTs, you can get perhaps another 100x write amplification even if you batch you writes.

Re: Unconventional PostgreSQL Optimizations

#32

Post by SigmundA »

giovannibonetti wrote: post #31
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.

Clustered indexes only save up to 2x write amplification in the very rare case where you're indexing the entire table (e.g. if it has very few columns). However, that is usually the least of your concerns with write amplification. If you don't batch your writes, you can easily get 100x write amplification. For any primary key or any other index not strongly correlated with your INSERTs, you can get perhaps another 10…

>in the very rare case where you're indexing the entire table (e.g. if it has very few columns).

Not sure I follow most tables are accessed primarily in one way (primary key) while maybe sometimes in others for analysis. Having the PK written twice because it's almost always indexed is normally a waste and good candidate for a clustered index. So much so that many DB's like SQLite and MySql always do clustered indexes on primary key because their storage engine is built such that tables are a b-tree anyway vs PG that has separate b-tree indexes and heap tables. MSSQL and Oracle give you a choice whether the table is a index structure or a heap.

If you have very specific use case tables they can typically have a clustered index and no secondary indexes, you can still scan them for ad-hoc analysis but you get better insert performance and space usage because you aren't double writing to the heap and a PK index like you would in PG.

As far as batch writes that is a separate issue and has to due with whether that even makes sense for durability, if you need to commit a single random row due to something occurring you can't batch that up and maintain consistency, if your bulk loading data sure and is common practice to do commit batches there, clustered indexes could still be a 100 vs 200x write amplification if you have to insert both an index row and heap row vs just a single clustered index row.

Re: Unconventional PostgreSQL Optimizations

#33

Post by mistrial9 »

whalesalad wrote: post #23
sc68cal wrote: post #7

Great article, shows a lot of interesting PostgreSQL features. I have used PostgreSQL and MySQL for decades, and this article showed me that I have barely scratched the surface of what is possible.

PostgreSQL is like Emacs. It's an operating system disguised as something else.

the way PG was originally implemented does have some overlap with operating systems design IMHO.. PG internals define and use PG tables in internal schema to implement core architectural features. The PG code that bootstraps the PG environment is minimal in important ways.

Re: Unconventional PostgreSQL Optimizations

#34

Post by cies »

booi wrote: post #21
sc68cal wrote: post #7

Great article, shows a lot of interesting PostgreSQL features. I have used PostgreSQL and MySQL for decades, and this article showed me that I have barely scratched the surface of what is possible.

I've used Postgres for more than a decade and everytime I wade into the docs I feel the same way, I'm barely scratching the surface. It's so immensely powerful.

I love what LLMs are doing for me in PG's SQL. I discovered many features by having LLMs write them for me, often spot-on 100% on first prompt.

Since I know conceptually how RDBMSes work, I can ask vey specifically what I want. Also asking for feedback on schemas/queries really helped me. I use a lot more of PGs features now!

Re: Unconventional PostgreSQL Optimizations

#35

Post by ComputerGuru »

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.

Another option would be a good way of placing indexes on a different physical disk. You could use fast, ephemeral storage like you can for a WAL without amplifying the writes to the same device that is your expensive bottleneck. You could rebuild on data loss.

But it would add complexity to detect out-of-sync indexes and tables.

Re: Unconventional PostgreSQL Optimizations

#36

Post by SigmundA »

ComputerGuru wrote: post #35
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.

Another option would be a good way of placing indexes on a different physical disk. You could use fast, ephemeral storage like you can for a WAL without amplifying the writes to the same device that is your expensive bottleneck. You could rebuild on data loss. But it would add complexity to detect out-of-sync indexes and tables.

You mean tablespaces: https://www.postgresql.org/docs/current/manage-ag-tablespace... ?

Re: Unconventional PostgreSQL Optimizations

#38

Post by ComputerGuru »

The no unique constraint using hash index thing is something I always run into. Am I missing something or is it just glue code missing to translate that syntax to a exclusion constraint with a hash index, allowing foreign keys to reference the column in the process, and possibly also fixing the ON CONFLICT approach in one go?

Re: Unconventional PostgreSQL Optimizations

#39

Post by ComputerGuru »

SigmundA wrote: post #36
ComputerGuru wrote: post #35

Earlier quoted context omitted.

Another option would be a good way of placing indexes on a different physical disk. You could use fast, ephemeral storage like you can for a WAL without amplifying the writes to the same device that is your expensive bottleneck. You could rebuild on data loss. But it would add complexity to detect out-of-sync indexes and tables.

You mean tablespaces: https://www.postgresql.org/docs/current/manage-ag-tablespace... ?

Maybe? I wasn’t under the impression these could be reliably lost or out of sync without risking data loss?

Re: Unconventional PostgreSQL Optimizations

#40

Post by ComputerGuru »

pphysch wrote: post #18

Is the Hash Index method strictly superior to creating a unique "hash" column and precomputing the hash in the application or query?

Sibling comment mentions less space but also your alternative doesn’t (naively) handle supporting values that differ but hash to the same value.

Of course the hash index also outperforms a unique (btree) index on top of separately calculating the hash, in addition to the storage overhead, row bloat, lack of guarantees regarding the hash unless you expose it to Postgres as a user-defined function AND add a check constraint.

Post reply on HN