And you are here why?

“ We store everything as JSON, that way we don’t worry about schema ”  —  #CowboyDeveloper You’ve probably heard some variation of the above too — probably from some of the more religious of the   Schemaless   crowd. Oh there are many   good reasons for doing this — things like deferring decisions to the last responsible moment, being agile, and so forth. There is, however, a much darker — and sadder — possibility, that   they just don’t get it . The thing is, a database schema defines it’s   structure , the types/formats of data that are permitted, the relationships between them, their integrity constraints, etc. In relational databases, most of this is directly codified in the form of tables, views, indexes, triggers, and whatnot. What we tend to forget, however, is that all of these exist for a reason — they provide the   rules , the framework if you will, of how we interact with the database. These   rules   need to exist   somew...

“ We use levelDB because it’s fast”  (followed by incoherent rambling where ‘ fast ’ is never actually defined)  —  #CowboyDeveloper If you’re doing any kind of development, you’ll eventually run into a situation where you need to persist data. At this point, you’ll probably end up doing one of three things 1.   Files : Easy one right? Just write your data to a file, and it’s there when you need it. That is, until you need to   find   stuff, which is when you discover 2.   SQLite : Which gives you all sorts of fun query capabilities, and is great till you suddenly realize that you need to “scale”, or store “documents” (scare quotes because you usually don’t, but whatever). That’s when you discover 3.   MongoDB : Ok, that’s a joke, and a bad joke at that. The thing is, you end up writing data to   some   sort of database. For most people, that seems to be the point at which all rational cogitation ends. For whatever reason, the choice ends ...

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...

Come migration time, if you’re (very!) lucky, you can go offline, and  Just Take Care Of It . Odds are, however, that it isn’t going to be that simple — systems that can’t go offline, customers that scream really loud, money, whatever. In that case, the general strategy tends to be some variation of the following 1. Migrate Creates / Updates / Deletes to the New Every time you insert/update/delete data into your old database, you do the same on the new database. You’re still  Read ing from the old database however, so in case things go south, you find a bug, etc., you can just totally nuke the new database and start again from scratch And yeah, theres a whole bunch of detail here around preloading, metrics, alerts (•), and so forth that I’m eliding  2. Serve (some!) Reads from the New In -1- ,  all  your  Read s came from the old environment. Now, you can, and should, start serving  Read s from the new environment. Mind you,  if at all pos...

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...

How do you  guarantee  that the data in your database is immutable? In particular, I’m talking about data that you  don’t want to change — things like call-records, historical data, transaction logs, etc. Of course, you could use something like  Datomic , or you could architect your application/data-structures/tables/… from the ground up to be immutable, but that could be … problematic. Especially problematic, in fact, given that I’m referring to your  existing  databases that have all this stuff in it — good luck trying to convince the business side of the house that you need to re-architect  everything ! Theoretically , this data should already be immutable, but, the reality is that there are  so many  attack vectors. 1. One of your developers could go in and tweak the data — you’d never know 2. Oh, only the DBA has access? What if  they  tweak it? 3. Worse, what if somebody hacks into your system? 4. Even worse, ...

“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 ...