If you’ve heard the term “vacuum” and your eyes glaze over, watch the first 90 seconds of this YouTube video, and stop there. If you like what you’re learning and want to go deeper, switch over to Avinashh Vallarapu’s talk on MVCC and autovacuum internals.
If you need to compare PostgreSQL versions, the feature matrix is really well-done. Check the boxes at the top for what versions you want to compare, and check the box for “Hide unchanged features.”
When you need short YouTube-style IDs for small tables, there’s Pierre’s pg-shortkey, but be aware that there are collision risks even with just hundreds of millions of rows.
You can pronounce it POST-gres or POST-gres-cue-ell, and you’re fine either way. Just don’t pronounce it like this, as Laurenz Albe explains.
Technique of the Week:
Generating Ranges of Data
The built-in generate_series function lets you:
- Pass in start, end, and step values
- In integer, bigint, numeric, or even timestamp format
- And get back a set of values from start to stop, with your chosen step size
This comes in handy if you need to find missing values. Say I’m looking for unused ids in our users table. I can generate a series of numbers like this:
|
1 |
select generate_series(1, 1000, 1) as ids; |
And join it to the users table, looking for rows where the users.id is null:
|
1 2 3 4 |
select available.ids from (select generate_series(1, 1000, 1) as ids) as available left outer join users u on available.ids = u.id where u.id is null; |
Presto, the list of ids that are not in use – although of course we don’t know if these were in use at one time, and were since deleted:
