pganalyze Blog

This is indeed an awesome tool. We've used it quite a bit for local profiling of known slow queries. FYI because I wish someone had pointed this out to me when I stumbled across tools like this: For profiling really busy production DBs, I highly recommend https://pganalyze.com/ Not affiliated - just an ecstatic customer.

Much of this matches my own experience. A few thoughts: 1. Cost tracking meetings with your finance team are useful, but for AWS and other services that support it I highly recommend setting billing alarms. The sooner you can know about runaway costs, the sooner you can do something about it. 2. Highly recommend PGAnalyze (https://pganalyze.com/) if you're running Postgres in your stack. It's really intuitive, and has proven itself invaluable many times when debugging issues. 3. Having used Notion for like 7 years now, I don't think I love it as much as I used to. I feel like the "complexity" of documents gets inflated by Notion and the number of tools it gives you, and the experience of just writing text in Notion isn't super smooth IMO. 4. +1 to moving off JIRA. We moved to Shortcut years ago, I know Linear is the new hotness now. 5. I would put Datadog as an "endorse". It's certainly expensive but I feel we get loads of value out of it since we leaned so heavily into it as a central platform.

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/

A side note: Your RSS feed https://pganalyze.com/feed.xml misses most of your blog articles. I'm actually subscribed to your blog in my RSS reader, but I completely missed this article (any many others) when it was published because it didn't show up in the RSS feed.

Meanwhile there are a number of products emerging in the Postgres space that are focused on SaaS business models. - https://pganalyze.com/ - https://www.crunchybridge.com - https://www.pgmustard.com/ - https://supabase.io/ The Postgres consulting/support services landscape may be consolidating some, but I'm not even sure that is a broader trend. Meanwhile there are more tools and services popping up regularly.

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-logic… https://www.pgedge.com/blog/always-online-or-bust-zero-downtime-major-… 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.

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…

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

IIRC `MERGE` has been part of SQL for a while, but Postgres opted against adding it for many years because it's syntax is inherently non-atomic within Postgres's MVCC model. https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-confli… This is somewhat a personal preference, but I would just use `INSERT ... ON CONFLICT` and design my data model around it as much as I can. If I absolutely need the more general features of `MERGE` and _can't_ design an alternative using `INSERT ... ON CONLFICT` then I would take a bit of extra time to ensure I handle `MERGE` edge cases (failures) gracefully.

When you say cold cache, did you clear the os page cache as well as the postgres buffercaches? After setup.sql, the cache will be warmish - I get 4ms on the first run. I'm using postgres 17.5 See https://github.com/ethan-seal/ors_expensive/blob/main/benchmark.sh where I use dd to clear the os page cache. This article by pganalyze talks about it: https://pganalyze.com/blog/5mins-postgres-17-pg-buffercache-evict

The Postgres team incorporating io_uring into PG 18 is a good example of this: https://pganalyze.com/blog/postgres-18-async-io.

Would "essential internal abstraction, with the introduction of read stream APIs" evolve into full storage abstraction? https://pganalyze.com/blog/5mins-postgres-17-streaming-io

SEEKING WORK | Canada | Remote | Applied Research Scientist I have a PhD in computer engineering, focusing on operations research (OR). I specialize in developing solutions to OR problems and advising on OR matters for any type of company, but for startups in particular. In other words, I can translate real-world problems into mathematical models that can be solved via integer programming, constraint programming, or other optimization methods. I can implement and provide a production-ready solution for any type of problem you might have related to OR: scheduling, routing, etc. Recent work includes an optimization model for the selection of indexes in Postgres: https://pganalyze.com/blog/index-advisor-v3 Résumé/CV: https://github.com/PhilippeOlivier/curriculum-vitae/blob/main/OLIVIER_… Website: https://www.pedtsr.ca

Are these numbers the same for JSON/jsonb data? Ed: looks like it https://pganalyze.com/blog/5mins-postgres-jsonb-toast

For analytical queries note that you really have to learn how to express the queries efficiently with Postgres - unfortunately the optimizer is still missing lots of tricks found in more sophisticated engines [0][1][2]. JIT compilation alone can't get close to making up for those gaps. [0] https://pganalyze.com/blog/5mins-postgres-optimize-subqueries [1] https://duckdb.org/2023/05/26/correlated-subqueries-in-sql.html [2] "Unnesting Arbitrary Queries" https://cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf

SQL server: https://learn.microsoft.com/en-us/sql/relational-databases/automatic-t… Interesting read on a project implementing this for Postgres: https://pganalyze.com/blog/automatic-indexing-system-postgres-pganalyz…

I was researching this recently and came across this article: https://pganalyze.com/blog/gin-index The GIN index has some similarities to Elasticsearch's inverted indices (last I knew anyway), which also can be quite expensive to write to. If you're doing heavy writes, something to test and consider carefully. TLDR; writes get a lot more expensive with GIN indices.

Pg_query maybe? https://pganalyze.com/blog/pg-query-2-0-postgres-query-parser#fingerpr… Scroll down to the section marked "Fingerprints in...".

On the other hand, you're probably writing your SQL parser to parse SQL queries that are being fed into some DBMS. I found pg_query's argument convincing: "Our conclusion: The only way to correctly parse all valid SQL queries that PostgreSQL understands, now and in the future, is to use PostgreSQL itself." [0] [0] https://pganalyze.com/blog/parse-postgresql-queries-in-ruby

I know PG14 significantly improved this situation [0]. I'm not familiar enough with MySQL to know how it compares now though. [0] https://pganalyze.com/blog/postgres-14-performance-monitoring

> I would still rather work with Postgres AND have to juggle a connection pooler Is this comment relating to the overhead of idle connections, which has historically necessitated the use of a pooler in front of PG? If so, I believe this is resolved in postgres 14 https://pganalyze.com/blog/postgres-14-performance-monitoring

It was improved in 14 but may not eliminate the need for pgbouncer depending on the use case. https://pganalyze.com/blog/postgres-14-performance-monitoring

100% agree with brasetvik! So much efforts put in it! Postgres is not just a database, but "database OS" Discovered it a few months ago, and going through its last year posts and videos. Postgres ecosystem is clearly booming with all its extensions like pgvector, paradedb and many many amazing tools, and derived projects All videos annotated and accompanied with summary article https://pganalyze.com/blog Just look at the past 3 episodes - E118 https://pganalyze.com/blog/5mins-postgres-17-materialized-cte-scans - E117 https://pganalyze.com/blog/5mins-postgres-planner-join-equivalence-cla… - E116 https://pganalyze.com/blog/5mins-postgres-17-beta1 Scaling Postgres is a great too - https://www.youtube.com/@ScalingPostgres/videos - https://www.scalingpostgres.com/episodes/ Postgres is eating the database world https://medium.com/@fengruohang/postgres-is-eating-the-database-world-… PG Feature Matrix https://www.postgresql.org/about/featurematrix/

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-v… 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…

I discovered row-level security when I started using PostgREST [1] [2]. It was eye opening for me. In every traditional codebase I worked on, this is usually handled is such a slow and messy way, adding another layer of filtering on top of already slow and complex queries. This is always one of the first things that needs to be cached in Redis. Instead, row-level security solves the problem in a very elegant, simple and performant way in my opinion. Obviously it works better when all your logic is already at the DB level (e.g. PostgREST). I wouldn't imagine using DB roles and row-level security in a traditional backend where all the logic is at the application level (e.g. Django, Rails…). Edit: seems like there are workarounds to use RLS with Django [3]. [1] https://postgrest.org [2] https://postgrest.org/en/stable/auth.html#roles-for-each-web-user [3] https://pganalyze.com/blog/postgres-row-level-security-django-python