In the last few days I’m experimenting with substituting CRUD API code with Stored Procedures which directly produce the endpoints JSON as a single-row scalar value. API is then just a wrapper that authenticates, validates input and streams the DB’s JSON directly to the client.
No ORMs, no SQL generators etc.
All SQL is where it should belong: in the database
API does only single „CALL myfunc(…)“ db calls
A simple centralised error handler can accurately report errors from the database
No weird mixed row/json columns scanning into structs and re-marshalling everything to JSON
Codebase is collapsing to 20% (by LOCs)
Stored Procedures can use wonderfully declarative SQL code
Response times in the microseconds, even for multiple queries, all happens inside the DB
More side effects:
the data model can change and evolve without touching the API at all
Zero deploys mean zero downtime
the API application is so tiny, I could easily switch it to any programming language I want (yes, even Common Lisp) without worrying about available databases libraries, type mapping and rewriting tens of thousands of lines of intermixed language/SQL-code.
The general direction of the dev industry is heading in the opposite direction. More ORMs, more layers, more database abstraction. More weird proprietary cloud databases with each their own limited capabilities and query language.
So you tell me: Is it crazy? Is it wrong? Why do I have doubts despite everything working out beautifully?
Sometimes #postgresql feels like the #emacs of #databases. It has so many crazy features and yet I've never had comfortable muscle memory in it, so it always takes me longer to do about anything vs some flavor of #mysql (or #vim, depending on which tract you followed to get here).
:skull360: Heads up! I need to rescale the database drive soon. It’s growing and I want the disk to be able to hold more data. The current max size is about 40 GB and I will grow it to 500 GB to make it more future proof.
This means I have to shut down my instance for this operation to make sure there will be no data corruption. The downtime will be probably only mere minutes, but there will be visible downtime. I’ll let you know the exact time for this to take place.
Database operations always make me kinda anxious for some reason despite the fact there has not been a single issue with them for me in the past.
Did I mention that it would be really nice to have #PostgreSQL as an embeddable library similar to #SQLite? Size wise that should be fine, I think the whole PG daemon is just ~5MB.
I know that the architecture doesn't lean to it, but someone has to do this eventually! 🙂
I'm now almost through migrating PG to MySQL with Stored Procedures only. Ended up with 140 Stored Procedures. The insights I gained into the business domain are incredible.
Now there are some bigger challenges:
How to test an API that literally has hundreds of different endpoints + parameter combinations against the new version
How to transfer data of a 100GB+ sized PG to MySQL in a timely manner so that downtime is reduced to < 15 minutes.
Or even more challenging: how to transfer 60 PG tables to MySQL with a "slightly" optimised schema and a buggy pg_dump exporter, that wrongly decodes JSON values into unreadable data (bug filed 2015, maintainers not interested)? Or a buggy PG_MySQL Foreign Data Wrapper that fails with Boolean and JSON columns (bug filed in 2020, maintainers not interested)?
I've tried 10 different tools that advertise themself as a solution to this and not a single one was able to overcome these challenges (issues with JSON, Timestamp and Boolean columns). Any hints?
So if "interoperability" is a goal of the SQL standard, it clearly failed. If "interoperability" is a benchmark for open source databases, Postgres doesn't shine at all. All the features that make Postgres "so good" (like ARRAYs which are unknown to every other SQL database, BOOLs and Custom Types) are in fact locking your project in like forever.
However, I'm not the one who gives up easily. I'll likely end up with a hand-rolled migration tool and then sell it to make a fortune off it, for all those non-existing devs who want to migrate away from Postgres. :neofox_evil:
I've got a PHP website with a PostreSQL backend. It's entirely read-only in production, and its largest table has about 10,000 rows. Postgres, PHP, and a Caddy proxy all run in separate Docker containers.
Is it crazy to think that a simple system like this would run just fine with SQLite instead? In the longer term I’d like to move the whole thing to running with the Django Rest Framework and rework the front-end bit entirely.
Anyone here used both #MySQL and #Postgres a lot and could tell me how they compare for larger DBs? Mostly in terms of performance, assuming I don't need some advanced features - talking about 10s or 100s of GBs, a lot of writes but not that many reads. 🤔 #PostgreSQL
What is your opinion on PRIMARY KEYs for database tables that are append-only (i.e. for logging) and have no natural primary key and are not referenced?
I learned more about GPT-based AI algorithms from this article than from literally everything else I've read about it so far combined. It implements a GPT algorithm using #PostgreSQL.
Can anyone recommend a #postgresql book? Audience is a small business owner with software development experience who will be managing his own instance for a web app until there’s enough revenue to hire someone to help with that. On version 14 if that matters