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.
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”.
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!
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.
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?
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.
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.
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.
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.
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.
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
Fun. I spent an hour and made this and just tried to score words with vowels and wheel of fortune consonants to start to make a good initial guess. IRATE came up. I like the frequency idea and the entropy discussion and have more ideas.