No video :(

Hash indexes | Postgres.FM 076 |

Подписаться 4,1 тыс.

50% 1

[ 🇬🇧_🇺🇸 Check out the subtitles - we now edit them, ChatGPT+manually! You can also try RU-vid's auto-translation of them from English to your language; try it and share it with people interested in Postgres!]
Nikolay and Michael discuss hash indexes in Postgres - what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.
Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable) www.postgresql...
Here are some links to things they mentioned:
* Index types (docs) www.postgresql...
* Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) hakibenita.com...
* Hash indexes intro (docs) www.postgresql...
* Hash indexes implementation (docs) www.postgresql...
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai postgres.ai/
- Michael Christofides, founder of pgMustard pgmustard.com/
~~~
This is the uncut version, with video. Check out Postgres.fm to find audio-only podcast episodes, edited for convenient listening.

Опубликовано:

27 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 5

@chralexNET 8 месяцев назад

Unrelated question: Are you aware of any good performance comparisons between a server running directly attached storage with magnetic drives and the same server but instead using SSDs on a SAN? I'd also be interested to know if you have any experiences with having the database on a separate machine from the database server.

@kirkwolak6735 8 месяцев назад

SAN with GB, 10GB or 100GB. Fibre? So many variables in that question. I run on SSDs by default. A VM with PG running is still fast. The speed difference with SSDs is so ridiculously better than Spinning Rust. But all you did was move the problem over the horizon. Change the bottleneck. But that is what Fibre is for. PG has a tool to check your drive speed. So, you could actually test this out. I believe Cybertec has an article about it. At the least Laurenz Albe wrote something about it somewhere. (pg_test_fsync) Not sure. The best part about PG is that it is free and really easy to setup some testing. Especially if you have the environment.

@chralexNET 8 месяцев назад

@@kirkwolak6735 I know that I've written a very open question, but I am wondering if in the best case it is even worth it, so that I could go and buy the hardware I would need to test it out. Anyway, it sounds like you're saying that it could work a lot better, so I guess I will be testing it in the future. My case is that I have an old Dell R710 at home that I bought second-hand some years ago and I thought I'd try to use that as a database server, but yeah, it has magnetic drives, and I am currently trying to figure out if it has anything better than Gigabit ethernet.

@chralexNET 8 месяцев назад

Looks like it only has 4 gigabit ethernet ports, no fibre.

@kirkwolak6735 8 месяцев назад

@@chralexNETSo, one thing to consider is how fast you need things to be. What type of access you will need, etc. I use iSCSI off of my Synology for a PG DB that has like 16 Billion rows. But I store the index on an SSD. I am not worried about full table scans, because I won't do them on such data. I have to say this is faster than I thought it would be. This is a testing DB for me to analyze huge data issues. (It took multiple days to load that data using pg_restore) But index creation on that table is well over 4hrs... My view is that Spinning Rust is fine in the right context (Old Logging Data, Rarely needed data). I worked with old systems that used offline storage on tape. You hit those "blocks", and a robot grabbed a tape, and inserted it, and then your read started... LOL. Latency was the killer. GB will be slower than SSD. BUT even SSDs can vary. I bought an 8TB SSD... But it turned out to be optimized for short reads/writes. Overall it is 20-30% slower when I pound on it, than the old 2TB SSD. (My laptop supports up to 4, but they run too hot, so I keep 2 in). [Which reminds me... I need to clone my drives as backups this weekend] Good Luck!