pganalyze Blog

From my position

MySQL pros:

The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that.

MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3]

If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4]

Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that.

Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6]

Postgres pros:

Postgres supports 3rd party extensions which allow you to add features…

wow! awesome tips. I knew COPY rocks but didn't realize it would win vs INSERT INTO SELECT FROM ! https://pganalyze.com/blog/5mins-postgres-optimizing-bulk-loads-copy-vs-insert

1/10th scale tests:

psql -c 'create table temps_int2_copy as select * from temps_int2_copy where 1=0;' psql -Xq -c 'COPY (SELECT (1000*random())::int2 as city, (random()*random()*1100)::int2 temp FROM (SELECT generate_series(1,1e8::int8))) TO STDOUT WITH BINARY' | psql -Xq -c 'COPY temps_int2_copy FROM STDIN WITH BINARY' ==> 32sec

psql -c 'create table temps_int2_copy2 as select * from temps_int2_copy where 1=0;' psql -c 'INSERT INTO temps_int2_copy2 (city, temp) SELECT (1000*random())::int2 as city, (random()*random()*1100)::int2 temp from generate_series(1,1e8::int)i;' ==> 90sec

psql -c 'create UNLOGGED table temps_int2_copy3 as select * from temps_int2_copy where 1=0;' psql -c 'INSERT INTO temps_int2_copy3 (city, temp) SELECT (1000*random())::int2 as city, (random()*random()*1100)::int2 temp from generate_series(1,1e8::int)i;'; date ==> 45sec (still not faster!)

Of course, if we really want to "go fast" then we want parallel loading, which means firing up N postgresql backends and each generate and write the data concurrently to different tables, then each compute a partial summary in N summary tables, and finally merge them together.

echo "COPY temps_int2_copy_p1 from '/var/lib/postgresql/output100m.txt' with csv;" | /usr/lib/postgresql/16/bin/postgres --single -D…

pganalyze | Senior Solutions Engineer | REMOTE (US/Canada) | Full-time | $160-200k + equity | https://pganalyze.com

At pganalyze, we're redefining how developers optimize one of the world's most popular databases, PostgreSQL. Our software gives companies like Atlassian, Robinhood, and Notion the tools to solve their most complex Postgres performance challenges, ensuring their mission-critical applications run smoothly at scale.

For our Solutions Engineering role you will directly work with our VP of Sales, as well as our Founder & CEO (me!) on strategic deals, and develop the playbook for integrating pganalyze in complex customer environments. You will also actively contribute to both our documentation and the product itself. Our team is cross-functional, and our goal is to improve the pganalyze product where we can, to allow repeated delivery of a great customer experience.

We are looking for candidates with a strong background in working on Linux VMs in the cloud or on-premise, and with Kubernetes or containers. Go experience is a big plus, since our agent (the pganalyze collector) is written in Go, and this role may involve making the collector work in different environments, and improving cloud provider API integrations.

Details on https://pganalyze.com/careers/senior-solutions-engineer

Interested? Email me (the Founder & CEO) at lukas AT pganalyze.com - or fill out the form on the website.

hey HN. this is a Language Server[0] designed specifically for Postgres. A language server adds features to IDEs (VSCode, NeoVim, etc) - features like auto-complete, go-to-definition, or documentation on hover, etc.

there have been previous some attempts at adding Postgres support to code editors. usually these attempts implement a generic SQL parser and then offer various "flavours" of SQL.

This attempt is different because it uses the actual Postgres parser to do the heavy-lifting. This is done via libg_query, an excellent C library for accessing the PostgreSQL parser outside of the server. We feel this is a better approach because it gives developers 100% confidence in the parser, and it allows us to keep up with the rapid development of Postgres.

this is still in early development, and mostly useful for testers/collaborators. the majority of work is still ahead, but we've verified that the approach works. we're making it public now so that we can develop it in the open with input from the community.

a lot of the credit belongs to pganalyze[1] for their work on libg_query, and to psteinroe (https://github.com/psteinroe) who the creator and maintainer of the LSP.

[0] LSP: https://microsoft.github.io/language-server-protocol/

[1] pganalyze: https://pganalyze.com/

I think this is partly why Amelia suggests rewriting these examples to let React handle the rendering and avoid DOM manipulation in D3. I rewrote a bunch of charting components at work a while ago, and that general approach worked really well for us. In fact, I barely ended up using d3 at all, but the bits that I did use were very handy. I found some of Amelia's other writing very useful in the process, and I blogged about what I learned [1] (for what it's worth, I do talk a bit about handling mouse interactions there).

This is really the central point:

>The crux of the issue is that they both want to handle the DOM.

If you want to use D3 and React together, you need to figure out how to handle this, and there are several reasonable ways to do that. Letting React handle rendering is a natural approach, and works especially well if you are embedding your SVG components in an existing React application.

But the "hand a ref off to D3 and keep React out of that element" approach can also work, especially if you are more familiar with D3 DOM manipulation already.

[1]: https://pganalyze.com/blog/building-svg-components-in-react

I did not use a script (my environment is bare metal running ubuntu 24).

I read these and then wrote my own scripts that were tailored to my environment.

https://pganalyze.com/blog/5mins-postgres-zero-downtime-upgrades-logical-replication

https://www.pgedge.com/blog/always-online-or-bust-zero-downtime-major-version-postgres-upgrades

https://knock.app/blog/zero-downtime-postgres-upgrades

Basically

- Created a new cluster on new machines

- Started logically replicating

- Waited for that to complete and then left it there replicating for a while until I was comfortable with the setup

- We were already using haproxy and pgbouncer

- Then I did a cut over to the new setup

- Everything looked good so after a while I tore down the old cluster

- This was for a database 600gb-1tb in size

- The client application was not doing anything overly fancy which meant there was very little to change going from 12 to 17

- Additionally I did all of the above in a staging environment first to make sure it would work as expected

Best of luck.