Even before the Community Summit in Montreal, I wanted to organize a Developers Summit at one of our meetups. My inspiration came from the Open spaces at DevOps Day Chicago. I saw how productive the smaller group discussions could be, and wanted to try to discuss different aspects of interactions.
We finally made it happen on May 22! In the meetup announcement, I asked those signing up to vote for topics we wanted to suggest and name additional topics. Then, when a meetup started, we re-voted the topics, and same as in Montreal, the outcome was different :).
I talked with several meetup members about leading group discussions, so we were prepared. However, a person who volunteered to lead the AI discussion ended up not making it to the meetup, so those who wanted to discuss AI had to choose a different topic. We ended up with three discussions:
- Using ORM with Postgres
- Application-driven design
- Monitoring Postgres applications
The discussions lasted about 45 minutes, and I had to force-end them. 🙂 Below are the summaries provided by the discussion leads, and a recording is coming soon!
Doğaç Eldenk, Application-driven design
In this discussion, we have talked about common table design strategies to increase performance and enrich developer experiences. We’ve talked about adding standardized columns such as “created_at” and “updated_at” to record some metadata about the row. Those columns are convenient for ETL jobs and developers. We discussed whether those should be assigned by the application or the database using default values or triggers; we mostly favored database-managed values for uniformity and control. Moreover, we went through unique identifiers, IDs, UUID v4, and time-based UUID v7, and how they can benefit the developer and the database. Also, semi-structured data is pretty common in applications; for this reason, using JSON columns is a good option. For semi-structured repeated data, you can use array type over normalization of data into a separate table with foreign keys. Finally, we talked about differences between timestamps, timestamp with timezone, and period. Besides the table design, we also discussed ownership and responsibility on databases. There has been some interesting arguments about ownership, such as application teams owning the schema and business folks, product managers, owning the data.
Sami Imsei, Monitoring Postgres
The conversation was started with an anecdote about an issue in which a common query was consistently at 20 active sessions( on a 36-core machine ). It was mentioned that it was not easy to figure out which query was the problem and that they “stumbled” upon the bad query and figured out the fix. We then spoke about pg_stat_* views such as pg_stat_activity, pg_locks, and cumulative stats, and how to use them effectively. The interesting part of the conversation was when we discussed OpenTelemtry and how it is not practical to log every query with the SPAN_ID for Open Telemtry to be able to consume.
Steve Zelaznik, Using ORM with Postgres
Object relational mappers provide a lot of value. Much as it pains us to say, a lot of web developers are averse to using pure SQL. If developers don’t have a tool that easily maps the objects to the relational db, they’ll probably end up choosing one over the other, and the RDBMS will be the loser.
For us application developers, ORMs make our life easier 80% of the time. There are some caveats we all agreed on.
If you know how to do it in pure SQL but you’re fighting to get it to work in the ORM, just use SQL.
– Be careful using “SELECT *”. You might end up pulling way more data than you need
– Postgres can store large binary files, but it’s not its strength, so it’s better for Postgres to have a link. That link could point either to your local file system or to something better designed for to store large blobs.
– Make sure to test your database constraints in your CI/CD pipeline. The way to do this is to disable the ORMs validation, save a record to a database, and assert that Postgres raises its own exception.
– There are 3rd party libraries that look for N+1 queries in your test suite. Use them if at all possible. But the best immunization against N+1 queries is education. Also, not all ORMs are created equal. More niche ORMs, such as Ecto in Elixir, will not allow you to build an N+1 query. ActiveRecord in Ruby is the big behemoth that gives DBAs nightmares.
Many thanks to everyone who participated, especially the group discussion leads! We will do it again 🙂
The World of Data 