Did you know we're running a tip of the week at the bottom of each issue now? If not, scroll down and check it out! :-)

#294 — February 27, 2019

Read on the Web

Postgres Weekly

Playing with Parallel Queries — “Parallel queries in PostgreSQL allow us to utilize many CPUs to finish report queries faster. The parallel queries feature was implemented in 9.6 and helps. Starting from PostgreSQL 9.6 a report query is able to use many CPUs and finish faster.”

Nickolay Ihalainen

Don’t Miss Postgres Vision 2019 — Join us June 24 - 26, Boston, MA. Register now.

EnterpriseDB sponsor

The Current State of Open Source Backup Management for Postgres — A deep-dive into the most popular open source backup programs available for Postgres, what their current state is, and how they compare to one another.

Achilleas Mantzios

PostgreSQL BRIN Indexes: Big Data Performance With Minimal Storage — A block range index (or ‘BRIN’) can help you significantly reduce the amount of disk space required for high performance queries on big data. They were introduced in Postgres 9.5.

Jonathan S. Katz

If Postgres is The Fastest Growing Database, Why's the Community So Small? — Andrew Staller of Timescale notes that Postgres continues to be called the ‘fastest growing DBMS’ but that meetups and community groups tend to remain quite small.

Andrew Staller (Timescale)

PostgreSQL fsync Failure Fixed (And Why!) — Postgres recently fixed a long standing issue with its use of fsync. This post nearly explains what the problem was.

Avinash Vallarapu

Creating a Simple Contacts List with Laravel and Postgres — Modern PHP is certainly a lot nicer than the PHP I remember wrestling with!

Kamal Nasser

eBook: Best Practices for Optimizing Postgres Query Performance — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk in this free pganalyze eBook.

pganalyze sponsor

How We Used Delayed Replication for Disaster Recovery with Postgres — Replication is no backup. Or is it? Here’s a look at delayed replication and how GitLab used it to recover from accidental label deletion.

Andreas Brandl

Running a Bakery on Emacs and Postgres — Here’s what happens when a programmer turns their skills to organizing a bakery.

Piers Cawley

WAL-G: Archival and Restoration for Postgres — Created as a successor to WAL-E, WAL-G is a complete rewrite with better performance, parallelization, and compression options.

Citus Data

Proj6 in PostGIS — One for the geospatial crowd only! Proj is a library Postgres uses when converting spatial coordinates from one system to another. Proj6 is the latest version.

Paul Ramsey

supported by

💡 Tip of the Week

See how much space your tables (and indexes!) are taking up

I've been working on a small app (a Hacker News title edits tracker) and using a very small ElephantSQL instance for storage. Keeping an eye on my data usage is important to me, but how to do it?

Leaning on a provider's admin system or a DB management tool to show you your total usage is a fine idea, but if you want to quickly grab some numbers from psql, there are some ways to do the same thing with SQL:

SELECT
   relname AS table_name,
   pg_size_pretty(pg_total_relation_size(relid)) AS total,
   pg_size_pretty(pg_relation_size(relid)) AS internal,
   pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
   pg_size_pretty(pg_indexes_size(relid)) AS indexes
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

It's not the best looking query in the world, but gets you results like this:

Here we can see how much space a table is taking up in total (if you include both data stored in the table itself and in attached TOAST tables) as well as the indexes for each table.

If you want to look at each index in question, rather than having all the indexes for a table bundled into one, there's a slightly more complex query you can use.

This week's tip is sponsored by strongDM, the best way to secure access to your servers/databases. Schedule a 10 min demo.

If you have any ideas for your own tips, reach out here - we'll pay up to $100 for your tip if we use it in an issue.