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

Ask HN: It's 2023, how do you choose between MySQL and Postgres?

news.ycombinator.com

Post reply on HN
Print view View original

361–366 of 366 posts

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#361

Post by za3faran »

yakshaving_jgt wrote: post #355
za3faran wrote: post #352

Earlier quoted context omitted.

Probably in certain countries, given that it is mainly an STD. There are many conservative nations where this won't be an issue hopefully.

As it says in the linked article, it’s a global epidemic. If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes. That doesn’t mean you have contracted it — carriers aren’t always shedding the virus. I’m not sure I see any correlation between a country being conservative and an absence of sexually transmitted infection; the 10 countries where HIV is mos…

> If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes.

Again, not in conservative populations where marriage is the typical way to have sexual relations. Syria is one example that you quoted. A smart person would get his/her partner tested if they suspect anything before getting married.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#362

Post by yakshaving_jgt »

za3faran wrote: post #361
yakshaving_jgt wrote: post #355

Earlier quoted context omitted.

As it says in the linked article, it’s a global epidemic. If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes. That doesn’t mean you have contracted it — carriers aren’t always shedding the virus. I’m not sure I see any correlation between a country being conservative and an absence of sexually transmitted infection; the 10 countries where HIV is mos…

> If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes. Again, not in conservative populations where marriage is the typical way to have sexual relations. Syria is one example that you quoted. A smart person would get his/her partner tested if they suspect anything before getting married.

I'm trying to work out which logical fallacy you're employing here. No True Scotsman? Personal Incredulity?

In any case, assuming that infidelity or anything else sinful/haram doesn't occur in "conservative" populations strikes me as frightfully naïve.

The data is in. Arabs get herpes too.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#363

Post by za3faran »

yakshaving_jgt wrote: post #362
za3faran wrote: post #361

Earlier quoted context omitted.

> If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes. Again, not in conservative populations where marriage is the typical way to have sexual relations. Syria is one example that you quoted. A smart person would get his/her partner tested if they suspect anything before getting married.

I'm trying to work out which logical fallacy you're employing here. No True Scotsman? Personal Incredulity? In any case, assuming that infidelity or anything else sinful/haram doesn't occur in "conservative" populations strikes me as frightfully naïve. The data is in. Arabs get herpes too.

Infidelity does happen, but it's much much less common and is severely looked down upon.

Edit: it seems that HSV-1 is not an STD, but it causes genital herpes if engaging in oral sex? Which can explain the big gap between the two in conservative cultures.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#364

Post by srcreigh »

singron wrote: post #303
srcreigh wrote: post #147

Earlier quoted context omitted.

Postgres is >50x slower for range queries(example below) and is akin to using array-of-pointers (ie Java) whereas MySQL supports array-of-struct (C). Illustration from Dropbox scaling talk below. Sneak peek photo [1] (from [2]). Just imagine its literally 500-1000x more convoluted per B-tree leaf node. That's every Postgres table unless you CLUSTER periodically. [1]: https://josipmisko.com/img/clustered-vs-noncluster…

If your pg database improves with CLUSTER, you can use pg_repack instead to achieve the same effect without downtime. Besides reordering the heap, it will also clear out bloat from the heap and indexes. I highly recommend partitioning if you have heavy write traffic on large tables since that will keep overhead low and make it complete faster.

Thanks. I actually thought of similar pg_repack concept on my own a couple days ago (influenced by gh-ost probably..) I was googling stuff like how to switch a PG replica to master. I was imagining having 2 dbs and running CLUSTER on the inactive one. Probably wouldn't work but anyways I found about pg_repack after researching.

I'm now super interested in the perf aspects of running pg_repack. It would definitely require scratch storage space to be able to copy over the largest table in the DB (I'd guess 2.1x the largest table vs 2x the total DB size). I imagine repacking isn't as efficient as putting stuff into a B-tree. But I wouldn't expecting it to be anything like 50x worse like I portrayed above.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#365

Post by srcreigh »

frodowtf wrote: post #301
srcreigh wrote: post #147

Earlier quoted context omitted.

Postgres is >50x slower for range queries(example below) and is akin to using array-of-pointers (ie Java) whereas MySQL supports array-of-struct (C). Illustration from Dropbox scaling talk below. Sneak peek photo [1] (from [2]). Just imagine its literally 500-1000x more convoluted per B-tree leaf node. That's every Postgres table unless you CLUSTER periodically. [1]: https://josipmisko.com/img/clustered-vs-noncluster…

That was a great read in contrast to all the "there's no reason to use mysql" nonsense in this thread

That's what I'm going for! I love this type of info, so against the grain and useful it's basically a scandal. :-) Check out your sibling comment about pg_repack though, it's a really awesome PG perf improvement tool.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#366

Post by singron »

srcreigh wrote: post #364
singron wrote: post #303

Earlier quoted context omitted.

If your pg database improves with CLUSTER, you can use pg_repack instead to achieve the same effect without downtime. Besides reordering the heap, it will also clear out bloat from the heap and indexes. I highly recommend partitioning if you have heavy write traffic on large tables since that will keep overhead low and make it complete faster.

Thanks. I actually thought of similar pg_repack concept on my own a couple days ago (influenced by gh-ost probably..) I was googling stuff like how to switch a PG replica to master. I was imagining having 2 dbs and running CLUSTER on the inactive one. Probably wouldn't work but anyways I found about pg_repack after researching. I'm now super interested in the perf aspects of running pg_repack. It would definitely req…

It does CREATE TABLE ... AS SELECT * FROM table ORDER BY, which isn't too bad. Then it runs CREATE INDEX for all your indexes, which is usually faster than CREATE INDEX CONCURRENTLY. However, while these are running, a trigger is inserting records for all new writes into a special log table. Once all the indexes are created, it replays that log table until it's empty, and then it briefly locks the source table, flushes the last bit of the log table, and swaps the tables.

If you have a lot of write traffic, that log table will get really big, and pg_repack will have to do a lot of work to replay it. It's possible that the log table grows faster than pg_repack can replay it, which will cause it to never finish and eventually exhaust your storage space. You can also run into an issue where the log table never gets completely empty, and pg_repack never initiates that final lock and swap.

Partitioning helps a lot because it divides both the size of the tables and the write traffic to each table.

Post reply on HN