And you are here why?

It happened back in the early 2000s— the VoIP-based phone company I had founded back in 1999 had ( barely ) recovered from it’s near-death experience after the  Telecom Crash , and we had managed to get our heads (again,  barely ) above the water. I had taken our postgres server offline for reasons, and — horror of horrors! — postgres wouldn’t come back up! I know what you’re thinking, but you do need to remember a few things This was pre-AWS, we hosted everything ourselves We were  really  cash-strapped. Like, “making payroll for everyone out of savings” level cash-strapped We, literally, couldn’t afford a backup database server (they were  expensive! ) So yeah, postgres wouldn’t start up — it kept barfing with some seriously obscure error involving a corrupted file header, we had passed our downtime window, and customers were hammering our cell-phones like you wouldn’t believe. If I couldn’t get things back up, well, my company was toast. I...

It only matters whe it affects me  — Every #CowboyDeveloper Ever Isolation Levels You know about that the default isolation level in Postgres is “ Read Committed ”, right? You do? Well good for you Genius, ‘cos I sure as hell didn’t. But first, a digression. Isolation Levels , in a database, refer to the various ways in which concurrent transactions can impact each other. Say I’m  read -ing a bunch-a data from the database, and database, and during this you  update  one of the records, what happens? • Do I see the data  before  your  update ? • Do I see the data  after  your  update ? • If my  read  is actually a couple of table scans inside a single transaction, what happens? Do I get  both  the  before  and  after  version of the data? • Does my  read  just bork with a “ somebody is updating this table ” error? • Do I get a  NullPointerException ? (Sorry. PoorJava humor. M...

More  details on the whole thing by Adyen here , but the key part is below (Note, this assumes that you can take the whole DB offline. If not, well, don’t do the below!) Stop traffic to the database cluster. Run the PostgreSQL upgrade on the Master server, using a script to automate as many steps as possible. The advantages of this approach are to speed up the process and make it easily repeatable. ( 3–5 minutes ) Stop PostgreSQL on the upgraded master and create a snapshot of the volumes. ( Up to 10 minutes ) Copy the snapshot of the upgraded master to the storage device of the slaves. ( 2–5 minutes ) Restart the master. Import the snapshot to the slave server, mount the (already upgraded) master volume on the slave server. Reconnect the slave server to the master, by simply putting in the correct recovery.conf file. Only when there are at least two slaves online, connected, and up-to-date, we allow access to the cluster again. So there is never a moment w...

“Vacuum Stories” are pretty much a rite of passage for Postgres Peoples. Mind you, we don’t automatically flinch anymore when you whisper ”VACUUM” into our ears, but it’s still not an automagic thing — You really need to know what’s going on under the hood if you’re doing anything beyond the basics (•) The underlying issue in Postgres is that when a row needs to be updated, 1. A new version is created that is visible only to the transaction that created it 2. Once the transaction commits, the new row is visible to all subsequent transactions 3. Existing transactions will continue to see the old row 4. When there are no more “existing” transactions, the old row is deleted The ensuing space-management issues are the proximate cause for most of the vacuum stories out there, and  is based on the way Postgres implements MVCC . PostgreSQL’s approach to this problem is to store new row versions created by UPDATE in basically the same way that it would store a completely ...

 # TimescaleDB   -   https://github.com/timescale/timescaledb -   is implemented as an extension on #Postgres, exposing your #TimeSeries data as regular tables, while under the hood, these are actually split out into multiple tables (chunks). Each “chunk” is the time-series data partitioned by a time interval, and an optional partition key (location, user ID, device ID, etc) The obvious advantage — dropping data more than a week old is waaaaay simpler (just drop old chunks!) You also get Higher ingest rates : you’re only writing to — and indexing! — the latest chunks, which means you don’t have to worry about memory/disk issues, indexing rates, vaccuming, etc. etc. Query performance : as good as postgres, and better for time-series stuff (like “merge-append”) Time-oriented features : “time-bucketing” with arbitrary intervals (“ give me the sensor data in groups of 3 hours and 17 minutes ”), last/first aggregates (“ give me the last sensor data recorded in e...

Great, great  writeup here Ok, its funny In the previous section, we've said that some column can go beyond the size of a page if their types are TOAST-able. Let's do an overview of TOAST. First, TOAST stands for  The Oversized-Attribute Storage Technique , probably the best acronym in the history of Computer Science. but much, much  more importantly, it goes into - just enough - detail about what is actually  going on underneath the hood to be completely worth your while Just go read it...