Archive for January 2021

With 2020 firmly in the rear view mirror, it is time to look forward and down the highway of 2021. The organizers of People, Postgres, Data have gathered over chat, email, phone, and even a few socially distant, in-person events to determine a strategy for continuing as the most influential and positive community for all things Postgres related.

Sad face

The goal is to resume in-person events. However, out of concern for the health and comfort of our global community, we have made the decision not to host any in-person events until Q4 of 2021. We are prepared to wait until 2022 if that is what the health officials recommend. We know that many will find this news disappointing and we are working diligently to ensure that the health and education of our community is the top priority.

Happy face

We are continuing our popular webinar series, adding new presenters with pertinent content for all of our attendees. We will be adding more professional development and data problem solving topics to our library, and we will no longer be limiting education to just Postgres, as many data and human problems are neutral in the particular platform we happen to enjoy. If there’s a topic you’d like to present or see, we’d love to hear from you!

RSVP for upcoming scheduled events

  • January 26, 1pm ET: All we need to work with SQL is SQL
  • January 27, 1pm ET: PostgreSQL Forks and Knives
  • February 3, 2pm ET: Postgres for SQL Server Users
  • February 4, 1pm ET: Configuring PostgreSQL for Faster Analytic Query Performance
  • February 23, 1pm ET: Blockchain as a Database

Ecstasy 

Postgres Conference 2021: Digital will be happening in May of this year! An overwhelming feeling of great happiness and excitement has our dopamine pumping, and the whole People, Postgres, Data team is basking in it. 

 

As an all digital conference, we will offer a similar environment to what our community has come to expect: best in class content, professionalism, and top-tier educational opportunities for all who attend! Keep your eyes peeled over the next few weeks for more information on speaking opportunities and how to attend!

Joshua D. Drake     January 20, 2021

We all know that we should produce good documentation, but we’ve all skipped out on it every now and then. It’s often a lot of  mundane effort when you could be doing new exciting things.

 

Then days, weeks, months or even later the questions start coming in from your team-mates or new hires: why is this column like this, what does this column really describe etc. You no longer remember and you have to spend time to understand the database you’ve made.

One of the great things about PostgreSQL is that documenting your database is a piece of cake and that it saves you more time than you spend on adding comments. It’s without exaggeration less time consuming per table than answering a single question like the ones above.
Thankfully the syntax isn’t horrible and adding comments won’t be a slow affair if you have a lot of existing data unlike some other DB engines I’ve worked with.

PostgreSQL has support for storing comments directly in your schema:
You can add a comment to a table, a schema, an operator and so on.

 

Adding them is simple:

 

COMMENT ON TABLE mytable IS 'This is my table.';

COMMENT ON COLUMN my_table.my_column IS 'Employee ID number';


Some tools have better support for showing them than others and I hope to see that being improved in the future, for instance: Datagrip, my editor of choice will not show comments in the database explorer unless you hover over the object it does include it in the in-editor information pop-up when hovering over a column, function or table.


My personal favorite for generating visually pleasing documentation with little effort is SchemaSpy it requires Java and the JDBC driver to run and it generates pretty documentation in about a minute for a complex schema:

java -jar "/home/me/database/tools/schemaspy-6.1.0.jar" -dp "/home/me/database/drivers/postgresql-42.2.16.jar" -t pgsql -db postgres -s SCHEMA_GOES_HERE -host localhost -port 5432 -u USERNAME_GOES_HERE -p PASSWORD_GOES_HERE -o "/home/me/database/documentation/" -hq  -nologo -vizjs


The output is in the form of a relatively pretty website like this : Sample

I would say that this is quite a few levels of quality above what most companies have for their internal databases and with a lot less effort. As you can see from the sample it even supports markdown.

 

If you have suggestions for even better tools for the job, please join the People, Postgres, Data: Discord server

This work is unrelated to my job at Bamboo Solutions AS

Magnus Brun Falch     January 07, 2021

Latest Posts

  • With 2020 firmly in the rear view mirror, it is time to look forward and down the highway of 2021. The organizers of People, Postgres, Data have gathered over chat, email, phone, and even a few s...
  • We all know that we should produce good documentation, but we’ve all skipped out on it every now and then. It’s often a lot of  mundane effort when you could be doing new exciting things.   Then ...