Hacker News: jhas78asd

New comment by jhas78asd in "Ask HN: Could you share your personal blog here?"

jhas78asd — Tue, 04 Jul 2023 16:36:22 +0000

PostgreSQL and Ruby on Rails. https://andyatkinson.com/

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:49:48 +0000

Any posts on this? Are there bulk data loads that make table stats more stale and affect plans? I’m wondering what would suddenly make a plan selection change a lot that might be a contributing factor.

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:34:40 +0000

If you’re talking about the command line client that’s built in, it’s psql. If you can’t remember the command name to launch it or regularly type those other commands when you meant to type psql, you could add aliases to your shell that point to psql. :)

Learning any new CLI client is a bit daunting at first. With repetition and intention, I think the commands become very memorable. Eg “describe table” is “dt”.

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:25:47 +0000

Hi Craig! :) https://twitter.com/andatki

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:24:33 +0000

There is a way to provide some type of planner hints https://pghintplan.osdn.jp/pg_hint_plan.html

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:21:54 +0000

Each of the bullets you listed have very straightforward and memorable meta commands that I use on a regular basis with psql. It may be worth learning them just for when you use Postgres. There is also a built in help. These can also be saved into your dot files so you don’t need to memorize them. Happy to show you if you’re interested!

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:18:23 +0000

Find the SQL from meta commands. Example: https://dba.stackexchange.com/a/131031

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:16:54 +0000

You can likely get the SQL for a meta command, and you could run the SQL from your preferred client if you don’t use psql. Here is one example: https://dba.stackexchange.com/a/131031

I also highly recommend investing in psql skills though if you are a Postgres user.

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:10:55 +0000

What’s the reason though for Vitess? Postgres supports tables up to 32TB but hopefully you’re splitting them up using declarative partitioning in one or more ways before that. If you have tables that are smaller than a TB and a large memory DB (>1 TB RAM) Postgres should run ok right? I’d also imagine you’re splitting up your database into multiple databases and multiple instances (the writers) well before that as well right?

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 04:05:33 +0000

Thanks for calling out table partitioning. Besides implementing it at one level, multiple levels can be used simultaneously (eg list and range). Tables can be grouped and split out to their own database (aka functional sharding/vertical sharding) and again partitioned. This all takes more effort and investment but keeps you on PostgreSQL. As you said fillfactor can be tuned, more HOT updates. Even analyzing whether the Updates could be turned into inserts that are written at a high rate, not incurring bloat, and then fewer updates are made at a rate that does not outrun Vacuum.

New comment by jhas78asd in "Ask HN: It's 2023, how do you choose between MySQL and Postgres?"

jhas78asd — Fri, 12 May 2023 03:50:53 +0000

Did you implement table partitioning with Postgres or consider that before moving?

New comment by jhas78asd in "PostgreSQL Zero Downtime Primary Key Conversion"

jhas78asd — Wed, 10 May 2023 16:30:43 +0000

We didn't write up our rollback plan, but here was the gist. Since we first had to remove the primary key from all children in order to add the conflicting composite primary key to the parent (that propagated to children), if we aborted the whole process, we'd then need to restore the single column PK on children again by creating the PKs we'd just removed.

In both cases, success or failure, before swapping a second time we needed to copy forward all the rows being inserted into the placeholder table.

Other disaster mitigations are capturing a dump of rows for the relevant partitions being modified with pg_dump. And having physical database backups with snapshots enabled and available if things really go wrong.

New comment by jhas78asd in "PostgreSQL Zero Downtime Primary Key Conversion"

jhas78asd — Wed, 10 May 2023 15:19:38 +0000

In Part 2 of this 2 part PostgreSQL Table Partitioning series, we'll focus on how we modified the Primary Key online for a large partitioned table. This is a disruptive operation, so we had to use some tricks to pull this off.

PostgreSQL Zero Downtime Primary Key Conversion

jhas78asd — Wed, 10 May 2023 15:19:38 +0000

Article URL: https://fountain.engineering/2023/05/04/postgresql-partitioning-primary-key/

Comments URL: https://news.ycombinator.com/item?id=35888652

Points: 4

# Comments: 3

New comment by jhas78asd in "PostgreSQL Table Partitioning – Growing the Practice"

jhas78asd — Tue, 09 May 2023 17:25:54 +0000

Recently we faced a challenge working with a large table where query performance had worsened. This is a high growth database table that tracks applicants as they move through their hiring process. Find out how we used PostgreSQL table partitioning to help solve this.

PostgreSQL Table Partitioning – Growing the Practice

jhas78asd — Tue, 09 May 2023 17:25:54 +0000

Article URL: https://fountain.engineering/2023/04/26/postgresql-partitioning-growing-practice/

Comments URL: https://news.ycombinator.com/item?id=35877570

Points: 1

# Comments: 1

New comment by jhas78asd in "Puny to Powerful PostgreSQL Rails Apps"

jhas78asd — Tue, 02 Aug 2022 16:23:05 +0000

This talk covers 5 challenging areas when scaling Rails applications on PostgreSQL databases. Slides and a video recording of the presentation are available, as well as a supplemental content page with examples. Feedback is appreciated.

Puny to Powerful PostgreSQL Rails Apps

jhas78asd — Tue, 02 Aug 2022 16:23:05 +0000

Article URL: https://andyatkinson.com/blog/2022/05/23/railsconf-2022

Comments URL: https://news.ycombinator.com/item?id=32320734

Points: 1

# Comments: 1

New comment by jhas78asd in "Ask HN: Share your personal site"

jhas78asd — Thu, 07 Apr 2022 04:44:46 +0000

https://andyatkinson.com

New comment by jhas78asd in "Delayed Job vs. Sidekiq"

jhas78asd — Wed, 16 Feb 2022 13:44:23 +0000

I helped maintain delayed_job_web as a UI. It has fallen behind substantially now though. If folks are looking for a UI - it would be great to update it for modern versions of Rails. https://github.com/ejschmitt/delayed_job_web