Do you care and optimize your Databases for performance?

>>105675719 (OP)
I do if there was a performance issue but otherwise I could give less of a shit.

>>105675731
*couldn't

not doing basic DB queries correctly is one of the best indicators of a shitdev

>>105675731
the right answer

>>105675719 (OP)
That query is a non sense becase you can't use it to do pagination.

>>105675789
You can and you still don't need to query the entire database, by using:

View:
from django.views import generic
from .models import Question

class IndexView(generic.ListView):
template_name = "polls/index.html"
context_object_name = "latest_question_list"
paginate_by = 5 # This tells Django to paginate and show 5 items per page

def get_queryset(self):
# Return the full queryset, Django's Paginator will handle the slicing
return Question.objects.order_by("-pub_date")


Template:
{% if latest_question_list %}
<ul>
{% for question in latest_question_list %}
<li><a href="{% url 'polls:detail' question.id %}">{{ question.question_text }}</a></li>
{% endfor %}
</ul>

<div class="pagination">
<span class="step-links">
{% if page_obj.has_previous %}
<a href="?page=1">&laquo; first</a>
<a href="?page={{ page_obj.previous_page_number }}">previous</a>
{% endif %}

<span class="current">
Page {{ page_obj.number }} of {{ page_obj.paginator.num_pages }}.
</span>

{% if page_obj.has_next %}
<a href="?page={{ page_obj.next_page_number }}">next</a>
<a href="?page={{ page_obj.paginator.num_pages }}">last &raquo;</a>
{% endif %}
</span>
</div>
{% else %}
<p>No polls are available.</p>
{% endif %}

>>105675719 (OP)
As if wrangling SQL to do complex queries wasn't already hard enough.

>>105675868
>jinja

>>105676180
it's not jinja, but it's similar to it.
Django has it's own templating system, it's called DTL

>>105675868
>magic object variables
>code in HTML rather than HTML in code
I fucking hate hate hate Django
t. worked with Django at old job

>>105676231
when i see this shit im happy i didnt work one sole hour in an IT job
imagine learning all this spaghetti shit

when i need a db i just write one
it less work than fucking learning one

>>105676231
it's fine so far. What I hate the most is working with forms, it's still very prone to mistakes.
Other that that it's not that bad.

>>105676256
>when i need a db i just write one
you do not have transactions. you do not have a query planner. you do not have constraints. please, for the love of God, just use Postgres and learn fucking SQL. it's not that bad.

>>105676282
its extremely fucking bad
why would i bloat my head with this trash?
if you get paid, thats ok
but im not. why the fuck would i need this?
its a whole framework
its fucking bloated
and creating a db from scratch is dead fucking simple
to such an extent
i would really need constraints external to the project to use a pre made one

i also write in C, so its not like im stuck with python or some shit
i can write stuff from scratch in my lang,
and it usually is faster than the generalist solution bc im decent at it
and again
its less fucking work than *learning sql
why the hell should i bother? to minmax my brain into data retention?

>>105676311
actual retard
fuck off with your dumb ass bullshit. if you do not see the value in transactions, then you have never worked with concurrent behavior in your life (multiple users interacting with data in your database, often doing conflicting things).
pic related is a transaction lib that I started work on in Rust. this is just a lib mind you, nothing even close to being joined alongside an actual DB implementation.

>>105676282
I got asked to implement a basic database with transactions during a 45 minute job interview. Why are you so positive than a random custom-built database wouldn't have transactions? It just makes you look incompetent if you don't have a particular reason for it.

>>105676256
https://www.sqlite.org/testing.html
Let us know how your custom database compares

SQL now and forever
perhaps one day we will have a new language worthy of succeeding it but the chosen one has yet to reveal himself
until then i will love and embrace my god-emperor of IBM overengineering

you will NEVER make me use your worthless faggot ORM
the beauty of relational algebra shines on you and you fizzle and recoil like the time vampire you are

i do NOT care about your non-relational faggotry either. YOU WILL NEVER BE A REAL DATABASE, YOU ARE A JSON FILE IN A DRESS.

>>105676325
>t. thinks concurrent behaviour is hard
>calls others retards
>uses rust (bc of course he does lamao)
why is it always the crabs?

>>105676329
tell me how you solved it.
what was the structure of your DB. just a hash map?

>>105676350
>https://www.sqlite.org/testing.html
i dot even need to click your link and i know that whatever im gonna shit out- drunk and baked
is gonna blow your bloat out of the water

are you retarded?
how could yout hink otherwise
>webshitter
ah
you never actually programmed in your life
that explains things

>>105676353
but orm is for programmers, sql is for db masters.
Programmers don't need to know every single databate syntax and optimization, that's a waste of their time. They only need to know the basic. Do you agree?

>>105676282
Man, he's not even using the grammar constraints lol you're wasting your time

>>105676389
>group cope therapy
lamao

>>105675746
90% of /g/ is filtered by SQL
>>105676384
No. SQL is the api for your db. You don't need an abstraction/api on top of an api. Just learn SQL.

>>105676384
it is perfectly fine to not master every RDBMS known to man. but knowing the ins and outs of the one your org actually uses will save you endless headaches and allow your app to actually make use of its features.
webshits get away with ORMs and noSQL slop because their needs are usually extremely simple

>squeal

>>105676311
>its extremely fucking bad
>why would i bloat my head with this trash?
Your IQ is too low to understand it.

>i also write in C
That explains it. C makes you retarded. C should not have existed.

>>105676517
>ess cue elle

>>105675719 (OP)
I optimize by not using ORM. Instead of doing a million of updates I do one bulk insert and one update.

How do I learn to write half-decent queries?

My employer recently gave me access to the company datamart, and I've been able to put together some useful reports/extracts, but I really have no idea what the fuck I'm doing beyond using left joins between dimension tables and fact tables (plus or minus a few inner joins to subqueries) until the data is broadly in the general shape I need it.
eg. We have this very tall table containing customer data which for some reason is uploaded by the source system as unpivoted- kind of like this:
>[NATURALKEY], [CLIENTDATACODE], [VALUE], [OBJECTID], [CREATEDWHEN], etc.
>8974617, Client_Surname_SYS, KIMBALL, 524215, 2025-02-17 16:42:57.00
>8974618, Client_Forename_SYS, Ralph, 524215, 2025-02-17 16:42:57.00
>8974619, Client_Gender_SYS, Male, 524215, 2025-02-17 16:42:57.00
>etc
>8974645, Client_Postcode_SYS, 60098, 524215, 2025-02-17 16:42:57.00
>8974646, Client_Surname_SYS, ROSS, 524216, 2025-02-18 08:23:31.00
>etc etc.
I worked out I could pivot it back into columns by using a function combo of MIN(IFF([CLIENTDATACODE]='Client_Surname_SYS', [VALUE], NULL)) AS "Client_Surname" via GROUP BY but the table is so big that the performance was shit, until I also found I could make a mini dimension table using this other function called VALUES ('Client_Surname_SYS'), ('Client_Forename_SYS'), etc. and inner join that onto the aforementioned before the GROUP BY which seemed to encourage to the query optimiser (this is referring to Snowflake VQL by the way) to throw out all the millions of rows I don't need or care about.
Probably not a great example but if you have any advice or tips/tricks I'd really appreciate it.

>>105675719 (OP)
another thing I found

>>105675719 (OP)
Databases?

>>105675719 (OP)
No. I just have every user's data in their own json file. Optimization is only needed if you have hundreds of thousands of concurrent users who are constantly doing I/O onto your db.

>>105679767
Why would you wait to reach that point?
By doing it earlier you would be reducing costs

>>105676455
>90% of /g/ is filtered by SQL
to be fair, SQL is a shit declarative language.
where is my Datalog db?

>>105675789
I still kek how performant pagination is a dark art to this day for SQL cucks.
kv chads keep winning.

>>105676354
if you want high performance, concurrency and correctness, it's extremely hard and is still an unsolved field of trade-offs.

>>105679779
Approaching every project as if it's "the next big thing with hundreds of thousands or millions of users" is kinda retarded. It'll just lead you to making a bunch of pointless work.
The better approach is to structure your projects in a way where swapping out parts isn't a convoluted mess. For example, I can migrate my projects which use json files or sqlite into a more "enterprise" database whenever I want because my code base doesn't care what the db is.

>>105679124
ask questions. hash out something, ask the planner for the plan, tweak til it looks good. that's about all you can do.

>>105679767
I hope you mutex guard writes and fsync writes, but I still don't understand why you wouldn't just use sqlite from the start, unless you're a scripting language cuck.

>>105679850
>you're a scripting language cuck.
You're right. I use JS for everything, including backend code, because every other language is just shit.

>>105675719 (OP)
Two works
>composite
>keys

>>105676384
>but orm is for programmers, sql is for db masters.
Look at this guy and laugh

>>105679850
even if you have mutex guards, it gets all fucked when you read one value, then write another value based on the read value. in fact, the only way to solve this is to use a sorting algorithm for your locks. when you acquire a new lock, locks that are sorted before the new lock must be unlocked then relocked.
this all gets extremely fucking complicated, and you end up needing a transactions system to do things optimally.

>>105680009
honestly, have you tried Rust? it's probably the easiest to use low level language and the -sys style crates take care of building stuff like sqlite3 from source and linking it into your executable. It really just works. no OO bloat or anything either. If you dabble with windows programming too, having a proper low level language that can call WIN32 is nice.

>>105675719 (OP)
In my long years on IT, I came to the conclusion that the biggest performance issue (apart from shitty modelling) people have with DB is overnormalisation.

>>105675719 (OP)
This is basic shit that is only an issue for retards who use ORMs. If you are writing all your queries by hand or using a builder, you would never do something like this.

Database optimizations go way beyond that. To be honest, at work I was never in situation where it would matter. Simple webapps with few mbs of data and few queries per minute doesn't really have to worry about any of this. But my FOSS project attracted people with databases reaching hundreds of GBs and it was the first time I had to look through explain analyze dumps, understand how query planner works, manage caching etc. I'm still not entirely done because people keep bringing larger and larger databases.

>>105680009
Try better-sqlite3.

>>105680148
>easiest to use low level language
Take this with a grain of salt. Although it is powerful and reliable, it has very steep learning curve. Rust is to C what Typescript is to Javascript, except there is no implicitAny or other escape hatches, you have to write code that is provably correct from the start, which often requires well designed abstractions and complex types. But once you get a grasp on it, it's a really comfy development environment.

>>105680212
I never had any issues with normalization and I always try to make everything properly normalized. From my experience, performance generally suffers from lack of indexes or bad indexes and just badly written queries.

>>105680365
When you have a big database it becomes an issue.

BTW, next time you are deciding the tech stack, don't assume the client will not go crazy and start to record a shitload of useless data because one day a manager may build a graph to show in a pointless meeting...

>>105675719 (OP)
all wrong.

>>105680449
>next time you are deciding the tech stack, don't assume the client will not go crazy
Postgresql and no retarded queries is enough for their needs. I can optimize it further if a need arise, but as long as there is no issues I have more urgent things to work on. Overengineering is an issue too.

>>105675719 (OP)
Managers go brrrrrrr

>>105675719 (OP)
Avoid this: python

>>105680365
>I never had any issues with normalization
Then you're not normalizing over 3NF which is fine, 6NF is schizo tier.

>>105681082
Oh yeah. I didn't even knew there were such degrees of normalization, I am a self taught.
I think I only go until 4NF

>>105675719 (OP)
You should know SQL even if you work with ORMs so there is no point having ORMs to begin with. Every ORM have different footguns, different features, lack of certain features of the DB (generic queries are meme and a mistake, different SQL servers optimize differently and query planner might be surprising at times even if you know what you are doing). The first one is obvious, but the fact that slicing the query somehow changes the generated query if it actually does it is just oscured. It is nonintuitive. How does it know to make a paging query after taking a slice from already created object? IIs that some kind of pending object that resolves when you take a slice (termination statement of some kind)? ORMs just obscure what queries are generated and it is just not worth it. You will use different languages and libraries in your work. Why bother learning intricacies of some ORM every time you work on a different stack? Literal trivia, useless knowledge.