The Laravangular Stack (with Postgres)

Let’s start March with some tech talk.

I’m going to be talking about technologies that we use to develop new web applications from scratch. For normal applications (ie. the 98% thereof which contrary to what hipster devs on your team believe, do not have a legitimate use case for mongo), this is the technology stack which I feel is far superior to any other choice. I call it the Laravangular Stack.

To begin with, all modern web applications must be designed API first with a separate backend and frontend. The reasons for this are obvious;

  • Separation of concerns
  • Easier automated testing
  • Code once for multiple consumers (ie. web, mobile, device, API for 3rd parties, etc)
  • Performance
  • Easier to upgrade components
  • Easier to debug
  • Easier to divide dev workload
  • Your application architecture isn’t a sack of shit
  • Ability to have a rich, interactive frontend (and stop pretending that jQuery can accomplish this)

The question then becomes, what specific technologies do we use for such an application? In my view these answers are obvious.

Laravel has you covered on the backend
Laravel is the best thing that’s ever happened to web programming period. The most popular framework has you totally covered for API-first projects, and will deliver more features with more simplicity than anything else in existence – while the community is hard at work churning out thousands of packages for you to leverage in your project. It’s an obvious choice.

Angular for a superb user experience
Angular, at least for now, is the only proper actual front-end framework. Yes, it’s far from perfect, and some have even gone so far as to call it a tech demo…. however until Angular 2 comes out, there isn’t anything which can compete with it because everything else is essentially a bit of wet lettuce. Frontend JS frameworks are a very recent invention, and it’s an interesting new space – but the sooner you come to terms with the fact that Angular at the moment simply shits on everything else out there, the sooner you can begin to create world-beating applications.

PostgreSQL for RDMS
Many people still use MariaDB (or god forbid MySQL), however I’ve felt for a long time that it is rather inferior to PostgreSQL. A project I’m working on now has cemented my view, with 2 rather sharp examples.

Our first problem stemmed from having a very high write workload for one logging aspect in our system. The use case involves a very large number of external devices writing logs to our system every few minutes. In our testing, InnoDB is a severe bottleneck for write-heavy workloads. I have read up on this, and the tl;dr is that there is no way to fix it – it’s just the way it’s designed. That rather sucks.
The second problem are UUIDs. UUIDs are pretty much the gold standard now, especially when it comes to anything API. How do you store them in a database though? Well, in the case of MariaDB, there is no perfect solution. If you want optimal performance, you can choose to store it as a binary 16 – and say goodbye to ever being able to manually read or write any UUID. Alternatively you could store it as varchar 36 (or 32 without the dashes), but take a non-trivial performance hit.

PostgreSQL on the other hand has had a naitive UUID field for a while now, which is internally stored as if it were binary, but behaves like a char. Likewise, it has no performance problems with write-heavy workloads. Perfect!

Of course when you come down to the bottom of it, there are many more differences between MariaDB and PostgreSQL – and you’ll likely find that PostgreSQL can do everything MariaDB does, but also more. It just so happens that PostgreSQL is always first to the party with any number of features that it’s had first in the last decade or more, including being an actual database system with full ACID compliance. It’s only pitfall if any is the lack of tools (relatively speaking) compared to MariaDB. If you can get past that barrier however, I would definitely recommend it.

Everything else is irrelevant
Whether you use Apache or Nginx, they both proxy pass PHP requests straight to php-fpm in all modern configurations, and let’s face it, nobody serves static assets anymore – it all comes from CDNs. Whatever other technologies used, wherever you are hosted – it’s less relevant than getting the fundamentals right.

PostgreSQL vs MariaDB – a recent experience

In developing API-centric backend applications, there are two main points of contention which make me want to use PostgreSQL over MariaDB (or MySQL) right now.

Native Support for the UUID data type
Why is this important? Well, every backend system should only make it’s users (be it an Angular frontend or a mobile app or a 3rd party consumer) aware of the UUID of a given object. Internal incrementing IDs are not the business of the consumer of the API, for all sorts of security and privacy reasons. Plus, dealing with UUIDs allows API consumers to create related objects themselves including their UUIDs and send them straight to the API all together.

To that end, MariaDB simply has no nice way of dealing with them. Performance wise, the best thing to do is to make them a BINARY 16 length field. However, this will make them unreadable in any sort of interface, and certainly not editable – which is simply unacceptable for development and testing. And yes, I am aware it’s possible to filter them down to readable format, but who the hell has the time to do that everywhere and everytime you need to see a UUID? Ridiculous!

That just leaves the option of making them a char 32 length field – but that has a significant performance drag over binary (which is slower still than an integer field already!).

Enter PostgreSQL. It has a naitive UUID field whereby it is internally stored as a binary for maximum performance, but as far as it’s displayed to the user, it will in all situations act like a character field type. This is precisely the sort of functionality developers want in modern applications. I know it’s on the bucket list for MariaDB, but who knows when it will be implemented.

Horrible Performance for mass inserts
InnoDB is a terrible engine – no two ways about it. That’s why MariaDB devs are working furiously on XtraDB, to replace it. Meanwhile, one of the biggest problems users are stuck with is horrendous insert performance.

Let’s say you have an application that is constantly receiving a large number of records, which must all go into a certain table. Sure you can store them in memory as they come in – concurrency in the application itself need not be a problem. But what happens when you have too much data for InnoDB itself to insert in a timely manner? A really big problem is what.

This is another use case where PostgreSQL simply does not suffer from the same performance issues that InnoDB does. And don’t even dare to mention MyISAM, you may as well use floppy disks if you want to go down that route.