Postgres is an incredibly powerful database that’s packed with many different features. Some of its most interesting and productive capabilities, however, aren’t always evident. This collection of Postgres tips highlights some of the more obscure features users might not be familiar with but can greatly enhance the use of Postgres.
1. S.M.A.R.T. Tools
S.M.A.R.T. is an industry-standard way of monitoring the health of storage hardware. Effectively, S.M.A.R.T. behaves like the warning lights on your car when it says low tire pressure, low oil, or your engine’s overheating. When S.M.A.R.T. is running, it will periodically wake up and monitor the health of your storage. You like to know those things, and by installing S.M.A.R.T., you effectively get monitoring of your storage hardware. S.M.A.R.T. tells you if the drives report anything that could indicate a problem coming down the line. It’s not perfect, just like the lights in your car aren’t perfect, but it’s a nice tool to have if want a very reliable database on very reliable hardware.
Pg_test_fsync is a useful feature to determine which fsync method is the fastest for your operating system and hardware. Running pg_est_fsync quickly performs several fsync tests on a particular drive and returns performance numbers, which can be useful for attaining high performance.
3. Backend flow chart
If you’re curious about the way Postgres runs internally, the backend flow chart provides you with a peek behind the curtain by showing a flow chart of how queries are run. When you select a single section, the flow chart opens a wiki page that explains exactly what a particular box is doing. From there, you’re even able to access the source files within a particular module.
4. Command line control
Postgres is very good at giving you control over what it’s doing at the operating system level. When running a benchmark, commands like ps and top can actually show you what Postgres is doing. While these commands don’t show you the full query, they do display the command update type and status to help you determine how long each stage took.
5. GUC levels
Many users understand how to control Postgres through the postgresql.conf, but GUC levels offer additional methods of control. GUC (“Grand Unified Configuration”) allows you to set server variables or the user level, database level, session level, functional level, transaction level, and subtransaction level. Each lower level overrides the level above it, so if you set something in postgresql.conf but you change it at the session level that session value overrides what’s in Postgres conf. These additional layers of configuration offer fine-grained methods of control.
Work_mem effectively allows you to use more memory for sorting and hashing. By using more memory, you won’t spill to disks for large result sets, offering dramatic speed improvements.
7. Transactional DDL
Transactional DDL is one of Postgres’ more interesting capabilities. Effectively, transactional DDL makes it possible to do multiple DDL statements, such as renaming or adding columns, creating tables and drop tables, and creating indexes, all within a single transaction. Using this, you can effectively roll out new schemas atomically, guaranteeing applications won’t see a partial schema. This functionality is very difficult to do in other database systems.
8. Virtual columns
If you wish to create a column that doesn’t already exist, and you just want it to be computed, you can effectively create a function that makes it appear as though it’s a column in a table. Postgres 12 will include a feature called virtual or generated columns, but if you’re not running Postgres 12, virtual columns is a nifty feature.
*EDB Postgres Advanced Server 12.0 Beta is built on open-source PostgreSQL 12.0 Beta2.
9. Time zones
SQL offers time zone capabilities that makes it possible to take a timestamp without a timezone, and switch it to another timezone. For instance, if you have a time and you want to determine the same time in Tokyo, the current data can be switched to appear as Tokyo time.