What are some SQL redpills?

What are some SQL redpills?

Mike Stoklasa's Worst Fan Shirt $21.68

CRIME Shirt $21.68

Mike Stoklasa's Worst Fan Shirt $21.68

  1. 2 months ago
    Anonymous

    You don't need more than sqlite

    • 2 months ago
      Anonymous

      /Thread

      SQlite is great and it is where you should start. So many people use hosted/as-a-service databases, but there's no reason to do that when SQlite is easier and free.

      SQLite works great with high "concurrency" workloads. This is because it's very fast. It's many times faster than mongo and little bit faster than mySQL with 20 applications accessing the database with ~100 operations/s.

      The only real downside is that when you need more than one physical server can offer (64c, 2TB ram, etc) you'll have to move.

      What's the point of databases? Isn't that just Excel but with no UI?

      Databases are powerful computation engines, not just a place to store data.

      • 2 months ago
        Anonymous

        Yeah but Excel does computations too, no? And it's pretty powerful too if I recall. Idk since I don't use it.

        • 2 months ago
          Anonymous

          It is "powerful" in the sense that you can do basic operations fairly easily, but it's far from performant. We've had clients who've had gigabyte sized .xlsx documents and a single operation took no fewer than 38 seconds. After migrating to Mysql in 2010 that dropped to ~10ms

          • 2 months ago
            Anonymous

            Ohhh, that makes sense. Neat. I'll try to learn mySQL then.

          • 2 months ago
            Anonymous

            >gigabyte sized .xlsx documents
            My nose started bleeding from reading that.

          • 2 months ago
            Anonymous

            >middle manager who completed every Excel course his company offered getting turned on

          • 2 months ago
            Anonymous

            >gigabyte sized .xlsx docs
            they are really fun to work with :^) especially on critical systems of your corp when deadline is close

      • 2 months ago
        Anonymous

        >have to move
        >making more problems for yourself in the future instead of just using postgres or mysql from the start
        That's silly.

        • 2 months ago
          Anonymous

          That's only silly if the migration process from the simple tool to the advanced tool is a huge hurdle, which really isn't the case here.
          You're better off using the simple tool for your simple use case, and when your use case becomes advanced, you can spend an hour to migrate to the advanced tool.

          • 2 months ago
            Anonymous

            >Which isn't the case
            Except that it is if you have anything like stored procedures, or if you are using any flexible typing it can be very annoying. Hell the whole concept of flexible typing is just a giant source of bugs. The only way you get out of a giant migration pain is if you are literally only running basic CRUD operations on it.

      • 2 months ago
        Anonymous

        Then you go for Zen Enterprise from Actian https://www.actian.com/databases/zen/downloads/

        • 2 months ago
          Anonymous

          I've spent several minutes looking at this site and still have no clue what they're trying to sell me

          • 2 months ago
            Anonymous

            It appears to me they are selling a service, that service appears to be managing a companies entire IT department.

      • 2 months ago
        Anonymous

        >It's many times faster than mongo and little bit faster than mySQL with 20 applications accessing the database with ~100 operations/s.
        cite your sources, and no, I'm not being a snarky sarcastic phaggot. I want to see these benchmarks

    • 2 months ago
      Anonymous

      I do need Postgis Actually.
      Sqlite and it's derivative can't handle multiple user editing the same table at the same time in Qgis.

      • 2 months ago
        Anonymous

        SQLite has postgis extension

  2. 2 months ago
    Anonymous

    >SQL redpills
    I did a quick Google.com websearch for you, anon. I found this, which I think just might be exactly what you are looking for.
    https://blog.redpillanalytics.com/clear-the-clutter-find-drop-undead-dbt-created-tables-98034510b46d

  3. 2 months ago
    Anonymous

    MongoDB is good enough for majority of the majority of database use cases

    • 2 months ago
      Anonymous

      Even a very popular website with 100 posts per second?

      • 2 months ago
        Anonymous

        Is that the majority of database use cases?

        • 2 months ago
          Anonymous

          I don't know, that's why I asked with "even".

      • 2 months ago
        Anonymous

        The writes aren't a problem and neither is simple lookups, but depending on the complexity of the data relationships and what kind of queries you will need to perform, relational would be much more appropriate.

        Something like IQfy would have no issue running on MongoDB

        • 2 months ago
          Anonymous

          Yeah, I made a forum with MERN stack three years ago but the UI was godawfully stupid and it had live updates (changeStream) for no good reason. I'll give it another shot soon.

    • 2 months ago
      Anonymous

      you mean postgres?

    • 2 months ago
      Anonymous

      You can replace “mongodb” with “plaintext files” in any sentence without changing the meaning

  4. 2 months ago
    Anonymous

    Developers are frickasses and everything is .json files now.

    • 2 months ago
      Anonymous

      Yeah, please wtf is wrong with developers?
      You have something very optimized, multi-platform, open source, small and time-tested like Sqlite and you go out of your way to choose the absolutely most horrendous way to store data.

  5. 2 months ago
    Anonymous

    Dropping the table and re-importing it is the solution to every optimization problem

  6. 2 months ago
    Anonymous

    if your query has 30 cte you have actively delayed the enlightenment of humanity

    • 2 months ago
      Anonymous

      Would you prefer temp tables or sub queries? LMAO

      • 2 months ago
        Anonymous

        i object to the complexity not its form

        • 2 months ago
          Anonymous

          I sympathize, but you're objecting to reality

  7. 2 months ago
    Anonymous

    never use joins

  8. 2 months ago
    Anonymous

    >What are some SQL redpills
    >1.) Don't ever use sqlite.
    You will always outgrow it and then you'll be forced to do a migration. Sqlite is taking a stupid bet that you'll never need more than one writer. If you lose the bet then you are stuck migrating and that sucks. Especially if you actually optimized your database by using something like stored procedures that means rewriting everything.
    >2.) Don't use ORMs
    They seem super nice at first, but then you realize that whenever there is an optimization problem they are almost always the culprit. You'll find out it was some dev using the ORM in some way it wasn't intended for that or you'll have to do something hacky in order to get it to work well which just makes it a pain to maintain. You should just stick to stored procedures.
    >3.) Don't listen to the morons that say business logic has no place in the database
    People will say this shit and then write a script that will call SQL 7 times instead. This is less efficient both from the scripts POV and from actual server utilization. You'll waste bandwidth for no fricking reason and SQL will not be able to optimize the procedure making everything slower. You also get the bonus that once it is in the server that functionality can be used by any other application that has server access saving you a bunch of time.

    • 2 months ago
      Anonymous

      what about sqlite as storage for a program running on a user's computer?

      • 2 months ago
        Anonymous

        >what about storage on a users computer
        That's perfectly fine, but I am not a personal fan of it, because I just prefer to serialize the data or use json if I am expecting the users to edit it.

        >Sqlite is taking a stupid bet that you'll never need more than one writer.
        You've clearly never gotten into SQlite more than surface level. This is the kind of thing only clueless people on SO says.

        [...]
        He hasn't used SQlite in production, don't worry about what he says.

        >clearly never gotten into sqlite
        >"If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine. SQLite only supports one writer at a time per database file"
        Sounds like you never bothered to read their website and just decided to do hacky shit to avoid just using a regular SQL server that can adapt to your needs and won't gimp you later.

        • 2 months ago
          Anonymous

          A pity you didn't find out about WAL mode before you decided it was bad. Or more likely, you didn't try beyond surface level, you were a beginner and another beginner said "use something else" so you did.
          Writing is very fast, it's the least of your problems with SQlite.

          >won't gimp you later
          Why gimp myself with a buggy, slow and complicated server before I need to?

          • 2 months ago
            Anonymous

            >WAL
            >Doesn't actually solve the problem of 1 writer
            >"However, since there is only one WAL file, there can only be one writer at a time."
            >bonus it actually slows down sqlite if you are doing a lot of reads and not many writes
            Sqlite is good for when you want to make a user application and not deal with data persistence. It is not really good for stuff beyond that, because you are taking a bet that your database won't grow too large or that you won't need more concurrency in the future which is dumb. It also gimps you on networking if you actually need to share the data. There are plenty of free good SQL servers that are fast and using them means you won't have to migrate later. I would use sqlite for personal projects, but I wouldn't use it for a business or a website.

          • 2 months ago
            Anonymous

            nta but what is your argument here? that most businesses really write THAT much data concurrently?? i think thats simply not true outside of gayMAN tier companies.

          • 2 months ago
            Anonymous

            I work for a non gayman tier company that updates the database every second. You would be surprised how much concurrency you need if you are not working on a basic site that doesn't actually do anything.

          • 2 months ago
            Anonymous

            thanks for the perspective. not trying to be argumentative, but is that really necessary or just sloppy application programming on the other end?

          • 2 months ago
            Anonymous

            I can't get too much into it, but we had a real time requirement so because of that data was coming in all the time. We originally had a script to handle it, but the script was too slow so we ended up moving it to a stored procedure.

            We've had Postgres handle more than million updates/second fairly regularly, are you sure your application isn't doing something very unorthodox?

            I was just mentioning that the procedure was called every second honestly could have been more I don't remember the exact metrics.

          • 2 months ago
            Anonymous

            worked for a non big tech tier company that updates the database every second. You would be surprised how much concurrency you need if you are not working on a basic site that doesn't actually do anything.

          • 2 months ago
            Anonymous

            We've had Postgres handle more than million updates/second fairly regularly, are you sure your application isn't doing something very unorthodox?

          • 2 months ago
            Anonymous

            The idea that people use SQLite for anything beyond intermediate states in data pipelines or like, an actual micro-service where you KNOW it will only ever be one node and barely be used, is disturbing.

          • 2 months ago
            Anonymous

            >disturbing
            You are obviously either a junior or an ossified idiot. There are more usage scenarios out there than you appear to be able to think of.
            For example, I had a simulation system that produced binary output data. Lots of binary output data in many streams (around 100k of them, to a total of around 1TB of data). Working with that many files at once is really nasty (you hit all sorts of OS and filesystem limits if you're not very careful) but dumping that all into one SQLite DB is trivial. Even better, you can put in some metadata to make everything easy to find, instead of having to rely on packing that stuff into the filenames and hoping that nobody renames anything. The overall lifespan of the data (typically a few hours to a few weeks, but occasionally a lot longer) fit the single file model extremely well; sticking it into a centralized DB would have been madness, especially as we were doing several of these an hour.
            Yes, that server had a kick-ass disk array mounted. Fun stuff!

          • 2 months ago
            Anonymous

            >intermediate state in a data pipeline
            So what I said yes

    • 2 months ago
      Anonymous

      >Sqlite is taking a stupid bet that you'll never need more than one writer.
      You've clearly never gotten into SQlite more than surface level. This is the kind of thing only clueless people on SO says.

      what about sqlite as storage for a program running on a user's computer?

      He hasn't used SQlite in production, don't worry about what he says.

      • 2 months ago
        Anonymous

        In the environments I've worked in, I don't see how I could use sqlite in production. Disaster Recovery and High Availability are always a concern, which means at minimum to deploy an application in production I have to deploy 4 instances of it (2 instances each in 2 different data centers). I looked at using dqlite in the past, but that point why not use a real rdbms like mysql or postgres?
        And if I'm going to use a real rdbms in production I might as well use the same in dev/qa/uat/whatever-you-call-non-production for consistency.

    • 2 months ago
      Anonymous

      1) Never needed sqlLite so I don't know tbh.
      2) 100% agree. I'll add that some of those libraries are written by people with some kind of borderline/BPD disorder, as they feel the need to deprecate shit constantly. It doesn't help that ORM, by design, saps you of some of the most important DB features for the database you use
      3) Disagree. Unless you design you db to specifically be used by multiple (more or less related) programs, the BL should always be separate from the database. You can still do it otherwise, though I don't know why you would. Pick a choice and ride with it

      • 2 months ago
        Anonymous

        >disagree
        I guess I should clarify. I think you should design the databases structures whatever best fits the data, but when it comes to having an application handle changes to the database based on business logic vs the database itself I think you should always choose the database. I have seen plenty of applications that handle all the SQL on their end and it always ends up in this mix of SQl and whatever code they are writing. They are always forced to call the database several times for something that could have been handled by calling one procedure and letting it handle the operations. The only real danger that comes with adding business logic to the database is that you confuse business terms with programming terms and accidentally call the wrong procedure, but that's what documentation and label standards are for. Its easier to review the procedure when it is all in one place and it makes the application code a lot cleaner because all the application does is provide the arguments for the procedure and move on. The database gets to leverage a bunch of optimizations on the procedure and doesn't have to waste bandwidth.

    • 2 months ago
      Anonymous

      >Don't ever use sqlite
      More like
      >Don't fall for the memes of IQfytards who unironically shill sqlite as an alternative to an sql server
      Sqlite is great if you need to store data offline in the user's device or if you need arriving to store your own blog post on your website that only you will ever update. The moment you need concurrency, I would steer clear of sqlite. Gtards turn everything into a democrats vs republicans, Coke vs Pepsi, Christian cult 1 vs other cult 2, etc war and want you to believe that their favorite tool is the best too for every task.

      • 2 months ago
        Anonymous

        go back

    • 2 months ago
      Anonymous

      Nobody in this thread has ever had a job.

      >Don't use ORMs
      ORMs aren't just query builders. They allow you to represent/document your tables as classes and take care of translating query results into objects (and vice versa).
      >Don't listen to the morons that say business logic has no place in the database
      Implementing business logic on the database's side locks you to a single database unless you want to rewrite the queries.

      You don't need more than sqlite

      /Thread

      SQlite is great and it is where you should start. So many people use hosted/as-a-service databases, but there's no reason to do that when SQlite is easier and free.

      SQLite works great with high "concurrency" workloads. This is because it's very fast. It's many times faster than mongo and little bit faster than mySQL with 20 applications accessing the database with ~100 operations/s.

      The only real downside is that when you need more than one physical server can offer (64c, 2TB ram, etc) you'll have to move.

      [...]
      Databases are powerful computation engines, not just a place to store data.

      >Look at me, I'm a contrarian!

      • 2 months ago
        Anonymous

        NTA

        When did you lose your job, since you demonstrate that you yourself are a moronice NEET
        >They allow you to represent/document your tables as classes and take care of translating query results into objects
        No shit moron.
        >locks you to a single database unless you want to rewrite the queries
        migrating to a different SQL provider probably requires a lot of changes anyways concerning performance, how you connect to it and so on and so on. There is no such thing as seamless switching between DBMSes.
        >Look at me, I'm a contrarian
        I don't feel like explaining anymore to you since you're probably a bot so I'll just call you a moron.

        • 2 months ago
          Anonymous

          >There is no such thing as seamless switching between DBMSes
          The cost of moving data between DBMSes tends to be damn high too.

      • 2 months ago
        Anonymous

        >They allow you to represent/document your tables as classes and take care of translating query results into objects (and vice versa).
        In practice this isn't valuable

    • 2 months ago
      Anonymous

      >business logic has no place in the database
      This is completely correct.

    • 2 months ago
      Anonymous

      >3.) Don't listen to the morons that say business logic has no place in the database
      great idea in fairytale land

    • 2 months ago
      Anonymous

      I broadly agree with you but if you start having more than 100 stored procedures you will quickly enter hell. We have thousands on my project and it is a nightmare.

      Also figure out how your schema objects will live in source control and enforce it with an iron fist.

    • 2 months ago
      Anonymous

      >Sqlite is taking a stupid bet that you'll never need more than one writer.
      No, it's a design decision driven by wanting to be an embeddable library (a primary design goal of SQLite). Getting the locking to work right reliably enough to safely allow multiple writers (i.e., for write lock scopes to be less than whole-database) is really damn hard if you don't have a DB server to act as a single decision point.
      Design your use case to have only short write lock usage, avoid lock upgrading (deadlock city in multithreaded use), and you'll get good effective concurrency
      Also, if you're doing lots of writes and no reads, you can get a big speedup by turning off syncs. It's technically unsafe until you turn them back on, but for a big data import it makes using SQLite almost as fast as just writing a raw binary file.

  9. 2 months ago
    Anonymous

    H2 is fine for production actually

  10. 2 months ago
    Anonymous

    Mongo doesn't need to be "Good enough". It needs to be a big advantage for your use-case to justify how bloated it is (It's not easier/less work than using an ORM either).

    • 2 months ago
      Anonymous

      Most people use mongo with something like mongoose, that works exactly like a SQL ORM. In other words, they apply a static table schema to their mongo DB.

      A SQL db would offer them the same thing, but be faster and offer them the power of SQL when they get to the point that they need it.

      • 2 months ago
        Anonymous

        Correct, I'm literally getting $150/hr right now to rewrite an app that pajeets made using Mongoose to literally model relational data but in Mongo, and surprise surprise, everything is slow, works like shit, and there is 0 referential integrity.

    • 2 months ago
      Anonymous

      Also if you don't care about your data integrity there's no major downsides to using mongo anyway

  11. 2 months ago
    Anonymous

    My secret would be
    >no, you still don't need that fancy NoSQL for that
    We thought we did, with some billion rows coming in dailly. It was still not worth the effort vs. a properly designed Postgres instance.

  12. 2 months ago
    Anonymous

    What's the point of databases? Isn't that just Excel but with no UI?

    • 2 months ago
      Anonymous

      If excel could handle billions of rows and had much richer querying power and speed

    • 2 months ago
      Anonymous

      >What's the point of databases? Isn't that just Excel but with no UI?
      Ask the British government why using Excel for processing even medium amount of data is pretty bad.
      https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england

      • 2 months ago
        Anonymous

        tbh this was mostly due to outdated Excel version afaik, still don't use it for data of that size lol

    • 2 months ago
      Anonymous

      >What's the point of databases?
      it's a base for data

  13. 2 months ago
    Anonymous

    uni student here
    >MongoDB
    >oh nah I don't want to design a data model, I just wanna be a frickboi and have unstructured data
    is there a use case for this or is it memetech?

    • 2 months ago
      Anonymous

      No. Do not use, and only learn it if you want to make money migrating people off of it. MongoDB was a marketing trick, nothing more.

    • 2 months ago
      Anonymous

      You can use postgres exactly like mongoloiddb by using json columns.

      • 2 months ago
        Anonymous

        Exactly. The best way to describe Mongodb is “Postgres if it only had a pkey and a JSONb column, for every single table, period”.

      • 2 months ago
        Anonymous

        Exactly. The best way to describe Mongodb is “Postgres if it only had a pkey and a JSONb column, for every single table, period”.

        And every time I use Postgres in that way, ex. for some experimental new thing I don’t want to nail down a data structure for, I wind up regretting it and wishing I’d defined a real data structure. Every time.

  14. 2 months ago
    Anonymous

    Not all data is relational
    Datalog is better
    Use a graph or EAV/SPO (rdf) database

    • 2 months ago
      Anonymous

      Then again, almost all data is relational, use RDBMS unless you have verified usecase for specific application for something else.

      • 2 months ago
        Anonymous

        Most data is relational, but these relations don't always matter. Sometimes all you really need is something like a key-value store.

        • 2 months ago
          Anonymous

          not if you are government

        • 2 months ago
          Anonymous

          Mosy often I've seen these sometimes grow into more and needing features of a real database anyway. You might not need a dedicates RDBMS, but something like SQLite is just such a safe bet if you can't predict your future for certain

        • 2 months ago
          Anonymous

          >Sometimes all you really need is something like a key-value store.
          As soon as you want to find your data again, and not just by knowing the primary key, relational DBs win. You can extract properties from the key and value to make lookup and correlation of things easier to do, and you can set up indices to make those kinds of search tasks extremely fast. Simple summarization? Built-in.

  15. 2 months ago
    Anonymous

    this is hidden knowledge but RIGHT JOIN is actually faster in postgre and MSSQL whereas LEFT JOIN is faster in mysql (/mariadb) and both are faster than INNER JOIN in sqlite

    • 2 months ago
      Anonymous

      Sounds like something that'd be workload and configuration, version specific. You got any numbers?

    • 2 months ago
      Anonymous

      forgot to specify innoDB for mysql/mariaDB, haven't checked on other engines

  16. 2 months ago
    Anonymous

    Trust the optimizer. Don't try to write an overly complex or minified query. Write what is natural. 9 times out of 10 the optimizer will generate the same query plan.

    • 2 months ago
      Anonymous

      But then, be prepared to optimize 1/10 times.
      >t. i'm a highly advance query tuner

  17. 2 months ago
    Anonymous

    After a certain size, it's faster to just dump the whole table to a file then use wc -l to count the rows than using COUNT(*) in MySQL/MariaDB.
    And no, TABLE_ROWS in INFORMATION_SCHEMA.TABLES isn't accurate.

    I don't understand database technology.

    • 2 months ago
      Anonymous

      >dumbing the whole table instead of just using a single column
      Just do a COUNT(id). That alone would make it faster than dumping to a file.

      Your software will never be big enough to worry about, whether you choose to use SQLite, Postgre or MongoDB
      >actuall-
      Congrats! This project now brings enough money to the table for you to stop crying about a migration

      >enough money
      Its not about the money anonman. Its about it being a pain in the ass that could easily be avoided by picking the right tech from the start instead of playing the role of an ostrich and suffering later.

      • 2 months ago
        Anonymous

        Any real database should have identical performance between COUNT(*) and COUNT(COL)

        • 2 months ago
          Anonymous

          Only if there's a UNIQUE constraint on the column (or something equivalent).

      • 2 months ago
        Anonymous

        >Just do a COUNT(id).
        Timed-out.
        Using PHP and the table is about 50M rows.

        • 2 months ago
          Anonymous

          >dumbing the whole table instead of just using a single column
          Just do a COUNT(id). That alone would make it faster than dumping to a file.
          [...]
          >enough money
          Its not about the money anonman. Its about it being a pain in the ass that could easily be avoided by picking the right tech from the start instead of playing the role of an ostrich and suffering later.

          Dumping the whole table using
          select * into outfile 'table.txt';

          takes 3 minutes, by the way.

        • 2 months ago
          Anonymous

          Shit like this is why I hate SQL honestly. I know some cucks will seethe, but when you run into problems like this all the time, you wish you could return to monkey instead and use a key-value store and meticulously craft loading and extraction from it.

          In these cases though, I usually just use a special cased metadata table that's updated by triggers and makes other dba gays seethe.

        • 2 months ago
          Anonymous

          Use a higher value in your php.ini?

        • 2 months ago
          Anonymous

          >Using PHP
          Use FastApi like any sane persorn.

        • 2 months ago
          Anonymous

          Sounds like you should raise the timeout variable in php or just set it not to timeout.

        • 2 months ago
          Anonymous

          Which db? And have you updated statistics? VACUUM-ed if PG? Optimized PK index if SQL server? What size is the db server?

  18. 2 months ago
    Anonymous

    Your software will never be big enough to worry about, whether you choose to use SQLite, Postgre or MongoDB
    >actuall-
    Congrats! This project now brings enough money to the table for you to stop crying about a migration

  19. 2 months ago
    Anonymous

    >people here unironically pushing for sqlite when you can easily just run postgres in docker
    Jesus christ

    • 2 months ago
      Anonymous

      >Introducing network ipc latency to your application for no reason
      Why?

  20. 2 months ago
    Anonymous

    It's probably made me more money directly than all other technologies combined.

  21. 2 months ago
    Anonymous

    Don't mind me, just enjoying my
    application:start(mnesia).

  22. 2 months ago
    Anonymous

    it's the best

  23. 2 months ago
    Anonymous

    Why don’t we have anything like SQLite but for graph data or triple stores

    • 2 months ago
      Anonymous

      because you can represent graph data in sqlite3 using a triple store

  24. 2 months ago
    Anonymous

    I think M$ Access is not as bad as people say it is.
    I've only used it for small projects, though.

    • 2 months ago
      Anonymous

      It's not that bad if most interactions to it are through MS Office but it's not really worth learning if your environment allows you to install a proper database server.

    • 2 months ago
      Anonymous

      access is extremely based to btfo dumb Black DBAs who will literally not let you do anything without 6 months delays. And I get that DBAs, where they still exist, actually have a big responsibility and their autistic stonewalling is more understandable than when it comes from a moronic Black Sysadmin tech Jannie .

      But still, anything that makes shadow IT easier is giga based, and the funny part is that it's usually the same losers who didn't want to actually help or allow going through official channels that will seethe eternally when they realize that the random excel or access database is now vital for business and that the people who wanted it actually are more useful than them. like, yes you know have to actually support it, should've done so from the beginning.

      • 2 months ago
        Anonymous

        without those dbas your database integrity will be fricked in a month flat. every access database i've ever touched has been a shitshow

        • 2 months ago
          Anonymous

          yes as I said DBAs can be very useful. but some of them act like just admins in the "Sysadmin" sense, whereas they should help in deploying new databases, providing database services etc instead of just maintaining a single fricking huge database that they barely understand so they'd rather just not let anyone touch. DBAs used to be more than just "update the database, gate it behind inane processes, and pay for consultants if the performance is trashed".

        • 2 months ago
          Anonymous

          >without those dbas your database integrity will be fricked in a month flat
          doubtful. I've never seen this in practice.

          • 2 months ago
            Anonymous

            I have seen it firsthand if you let devs and analysts create objects and perform db ops with no oversight

      • 2 months ago
        Anonymous

        >shadow IT
        That's the first time I hear this name, and I like it very much.
        It's been very much a large part of my experience.

    • 2 months ago
      Anonymous

      At my old job several departments used Access instead of our DBs and all those users had local admin because of ODBC. Never again

    • 2 months ago
      Anonymous

      have a nice day

  25. 2 months ago
    Anonymous

    Breaking subqueries into CTEs makes your query easier to read and understand.

  26. 2 months ago
    Anonymous

    WHAT DO YOU GUYS USE FOR CONFIGURING THE DATABASE IN THE USERS COMPUTER? DO YOU VERIFY IF THE TABLES ARE CORRECT EVERYTIME THE APPLICATION STARTS?

    • 2 months ago
      Anonymous

      I always query some dummy record to make sure everything is working.

  27. 2 months ago
    Anonymous

    Learn CROSS/OUTER APPLY and you will be better than 90% of other SQL programmers.

  28. 2 months ago
    Anonymous

    can you use json/xml files as simple as using sql? i tried but it was too much complicated to do something like edit rows, remove rows, so i went to sqlite

    • 2 months ago
      Anonymous

      You can in theory but you should use SQL because you can implement integrity checks and it binds you to standards. If it's a small project with minimal data you'd be fine but if you're working with other people or with a lot of data then use a SQL database. You can also simplify things by just inserting json into your SQL database and dating the record inserts. When you need it, query the json, then parse it in either SQL or language of your choice

    • 2 months ago
      Anonymous

      If it's just one bunch of data, it's not too big, you don't care about integrity too much, and you don't need indexes or joins, you can just have one structure in memory and manipulate that. If you frequently edit the data, the data is complex, you care more about integrity, or the volume is too much, sqlite is a good choice.

    • 2 months ago
      Anonymous

      moron

  29. 2 months ago
    Anonymous

    I am a data engineer. I have worked with both PySpark and Snowflake using dbt.

    SQL guys more about data than software engineers that move into a data engineer position. Also dbt is destroying PySpark. Yes, it still shitty to debug SQL but no need to frick with pods, memory and all that shit.

    I know that PySpark is used for very large big data projects but in reality, not everyone consumes that kind of data and they follow trends/use it because pandas sucks.

  30. 2 months ago
    Anonymous

    SQL is Turing complete. Actual SQL Wizards are rare but if you ever meet one they can show you some crazy shit

    • 2 months ago
      Anonymous

      SQL isn't Turing complete.

      • 2 months ago
        Anonymous

        What is missing?

        • 2 months ago
          Anonymous

          The SQL standard doesn't have operations for iteration and conditional branch. There are procedural languages that can extend SQL and allow loops and conditionals, but not every database has that capability, for example sqlite doesn't. In theory you could use recursive CTEs in sqlite to achieve turing completeness but it would be absurdly impractical when you could just pipe data to a proper turing complete language for computation

  31. 2 months ago
    Anonymous

    MS Sql is overpriced.

  32. 2 months ago
    Anonymous

    Subqueries slow your performance. Put them in your joins.
    There are only two joins...INNER and LEFT OUTER with a very very rare UNION.
    If you have to use DISTINCT, your data and/or your query is bad.
    Triggers slow performance and complicate changes. In 10 years I have not found a single spot where a trigger was ever required.

    • 2 months ago
      Anonymous

      >In 10 years I have not found a single spot where a trigger was ever required.
      How do you add timestamps on database operations e.g. on record update update_timestamp reflects the time of update?

      • 2 months ago
        Anonymous

        I don't understand...are you updating things directly in the database? If so, why?
        In my series of use cases over this decade, I have any changes coming from code send a date or I just use GetDate() if I am recording it somewhere.

        • 2 months ago
          Anonymous

          >I don't understand...are you updating things directly in the database? If so, why?
          1. it's faster to do directly on DB
          2. timestamps + user, are usually the main reason to use triggers
          >In 10 years I have not found a single spot where a trigger was ever required
          that's ok, maybe your DB is not big/complicated enough to need them, on the other hand i use them on every table

        • 2 months ago
          Anonymous

          moron

    • 2 months ago
      Anonymous

      Finally some legit redpills from someone who clearly has worked with SQL before.

      • 2 months ago
        Anonymous

        I don't even use views.

        but we have extra rows that get returned without distinct

        I've heard that a number of times, except they were serious.

        why if I always use LEFT JOIN and it just werks
        >inb4 graph explanation my smooth brane wont ynderstand

        okay here's the way it was taught to me long ago...
        Inner join - you know it is data that WILL be there. Think last name or something like that (providing you or the devs validate for it).
        Left Outer - You're not sure the data will be there. Think address or secondary phone number

        • 2 months ago
          Anonymous

          clarification: You can and probably should also consider an inner join like a filter. If the data is not there, ignore it, you don't want it.
          So let's say I have people applying for a job but they need to make an account and then fill out an application. If i'm looking for a list of people who have submitted applications, I can join on where there is an application id associated with that user id. The inner join filters everyone that does not have an application.

    • 2 months ago
      Anonymous

      but we have extra rows that get returned without distinct

    • 2 months ago
      Anonymous

      why if I always use LEFT JOIN and it just werks
      >inb4 graph explanation my smooth brane wont ynderstand

    • 2 months ago
      Anonymous

      >put subqueries in your joins
      ok but what if my subqueries have calculated fields

      • 2 months ago
        Anonymous

        Its been a while but i'm pretty sure you can calculate in a join if it is required. I'm pretty sure you can. Maybe there are some instances it can't be avoided but I'd probably use a set variable to calculate depending on the performance. I'm also a readability nazi.

      • 2 months ago
        Anonymous

        create the fields in a CTE and use simple joins instead of subqueries, CTEs do inherit indices and primary keys

    • 2 months ago
      Anonymous

      My only use of triggers is when I realize after merging that I fricked up and instead of explaining that to someone I just add a trigger for whatever I fricked up, then delete it after I sneak in a fix to the next unrelated merge.
      Yes I have avoidant personality disorder why do you ask

    • 2 months ago
      Anonymous

      >COUNT(DISTINCT x)
      i have btfo your argument

    • 2 months ago
      Anonymous

      >In 10 years I have not found a single spot where a trigger was ever required.
      Required, or just very useful? I've used triggers to record timestamps for when specific changes happen; that's extremely useful, as it means that I don't need the rest of the code to know when to write those timestamp fields (corresponding to key state transitions). Without triggers, you need to put more of that logic in the applications that use the DB, and that's quite annoying (yes, I had to rewrite some code to do that).

      • 2 months ago
        Anonymous

        > triggers, you need to put more of that logic in the applications that use the DB
        You didn't have column defaults? I'm pretty sure they're triggers under the hood, but they hide the trigger well enough to make life just as easy as not having them at all. Custom user triggers are where things start to go horribly wrong.

        • 2 months ago
          Anonymous

          NTA but a column default doesn't solve the update timestamp problem. your options are a custom trigger or application logic

          • 2 months ago
            Anonymous

            Good point. Even in that case I'd rather wrap updates in a sproc (as much as I hate them) than use a trigger.

  33. 2 months ago
    Anonymous

    What exactly is a database? Is it just a spreadsheet with no GUI?

    I track my diet and am thinking of learning how to use, maybe make website for me to use it from. I currently have 44000 rows of data with 30 columns.

    • 2 months ago
      Anonymous

      you have it backwards. a spreadsheet is a database with a gui

  34. 2 months ago
    Anonymous

    I recently finished a small-scale, single-machine ETL project at work using SQLite. Once configured, it managed 30-50 connection instances without any issues. So it has its place at smaller scale and was for the most part pleasant to work with.

    I’d be interested to see examples of larger-scale applications using SQLite because I’ve never once heard of it used in that context, nor have I seen it recommended anywhere outside this shithole

    • 2 months ago
      Anonymous

      I've heard of one company/project that had a central postgres db and used one sqlite replica per node in their application so they could make reads without any network overhead.

  35. 2 months ago
    Anonymous

    really you should write your code in such a way that your BL doesn't care what data engine backs it. you should be able to test everything with NO database, just one serving dummy data.

    also SQL databases are an image-based programming environment. Your program isn't talking to SQLite or MySQL, it's interfacing with a specific running environment that happens to be implemented with those engines.
    Theoretically you should be able to support two versions of the same schema simultaneously, though in practice there's almost never a good reason to, outside of extremely weird migration requirements (i.e. a customer CAN'T migrate EVER.)

    Because the database is a specific image, it can contain lots of stored procedures and optimizations for it's work. Nothing stops you from supporting two schemas as well, one that's optimized for one use case, and one that's optimized for a different one.
    Databases are co-operating programs after all, treat them like one.

    • 2 months ago
      Anonymous

      honestly in every code base I've made that has a data layer that's more complex than serialize struct to file, I always implement some kind of Object(s) that gates access to data in methods.
      I have at times sporadically decided to go from one database to another and this way I just change up a few places instead of everywhere.
      giving people escape hatches to write sql or whatever directly in procedures is just a disaster waiting to happen, imo.

      I will now accept the naysayers who think spaghetti and coupling data models is acceptable now.

      • 2 months ago
        Anonymous

        >giving people escape hatches
        you misunderstand. you basically should never specify the schema in code or have the actual program generate it. the database is it's own software project written in SQL, with it's own API and migration rules.
        you should absolutely have a data layer that stops your moronic team members from writing SQL directly without going through the repo that carefully considers all of the SQL.

    • 2 months ago
      Anonymous

      Nah frick that, maybe if you have months of lead time and know exactly what you’re building. In the agile hellscape of instantly DM’d user feedback putting anything more structured than sqlalchemy in front of your db will just kill you with increased dev time.

  36. 2 months ago
    Anonymous

    >What are some SQL redpills?
    SQLite is actually good, like, it would fit 80% of systems out there.
    There are very few things worse than a poorly tuned DBMS, and I have sen complete aberrations made with pirated versions of SQL Server or Firebird, just because the stupid windows developers wanted to store a few thousand records.

  37. 2 months ago
    Anonymous

    SQLite creator was right about everything

  38. 2 months ago
    Anonymous

    i'm using select to make a temporary table with identifiers + days, which i am then using to select all entries in another table at those identifiers on those days

    is this moronic? it kind of works, i just constantly having python/pandas being a worthless piece of shit with memory usage

    • 2 months ago
      Anonymous

      I don't know what the frick you mean exactly, send example code

  39. 2 months ago
    Anonymous

    It's dead. Datalake has replaced it

    • 2 months ago
      Anonymous

      >datalake
      you're 10 years late

      • 2 months ago
        Anonymous

        NTA but SQL is 50 years late

        • 2 months ago
          Anonymous

          SQL is a language for interacting with relational databases, and neither SQL nor databases are dead. "Datalake" is a meaningless marketing buzzword (like data science, which is also dying). it's just a cloud file store

      • 2 months ago
        Anonymous

        to what, idiot?

        • 2 months ago
          Anonymous

          to datalakes being relevant. it's a fake and gay concept used to sell cloud services to morons. you didn't fall for it, right anon?

          • 2 months ago
            Anonymous

            Pretty sure I can query bigger data faster by just having pure python delta lakes on an actual data lake and not pay 1000$ a day for ~~*SQL*~~ and its dogshit concurrency

          • 2 months ago
            Anonymous

            Anon muh data lake is just fricking parquet files with in memory caching
            You can’t use it for an app if you aren’t insane for the same reason you can’t use json files written to disk

          • 2 months ago
            Anonymous

            Sure I can. Delta Lake with Z-Order on my key lets me skip files and find what I need very fast. If I need ultra fast latency, I definitely wouldnt use SQL but NoSQL like Mongo/Cosmos.

          • 2 months ago
            Anonymous

            You’re what’s wrong with this country

          • 2 months ago
            Anonymous

            >makes "apps"
            >im the problem
            yep, thats goin in my cringe collection

  40. 2 months ago
    Anonymous

    what does IQfy think about DuckDB?
    to me (heavy analytical workload) it is a fricking godsend gift from the almighty

    • 2 months ago
      Anonymous

      It seemed useful when I used it a thousand years ago, but I think all that functionality is probably built into pandas by now.

      • 2 months ago
        Anonymous

        >pandas
        rly homie

        • 2 months ago
          Anonymous

          Oh boohoo is pandas not cool anymore

          • 2 months ago
            Anonymous

            Pandas isn't an in-memory db, what the frick mayne.

          • 2 months ago
            Anonymous

            I mean, isn’t it?
            What’s a database really?

          • 2 months ago
            Anonymous

            One thing you expect from a database is being able to run it as a service and have multiple applications do requests on it.
            Unless I'm really out of touch with pandas, it can't do that.
            Unless you mean "have your data in some parquet file and do queries on it with pandas", but then it's not an in-memory database, you'll always be reading from disk.

          • 2 months ago
            Anonymous

            Also I don't think pandas has any way to do atomic updates of a file. If two processes try to dump the same file at the same time, you're getting a datarace. It's also presumably completely inefficient, it's gonna write the whole thing every time.

            I wasn’t actually calling it a database don’t worry

            >people with actual jobs have found that pandas is dogshit project with a Black personlicious api design
            >i know, I will be a unemployed contrarian homosexual and say that its GOOD, ackshually
            you can keep playing with your jeet notebooks on datacamp, leave the actual work and positions to the competent whites

            How do the cool tech hipsters pull shit up into memory from parquet and train a classifier or whatever now then?

          • 2 months ago
            Anonymous

            >How do the cool tech hipsters pull shit up into memory from parquet and train a classifier or whatever now then?
            Not him but I guess you could read it directly in duckdb

          • 2 months ago
            Anonymous

            Also I don't think pandas has any way to do atomic updates of a file. If two processes try to dump the same file at the same time, you're getting a datarace. It's also presumably completely inefficient, it's gonna write the whole thing every time.

          • 2 months ago
            Anonymous

            >people with actual jobs have found that pandas is dogshit project with a Black personlicious api design
            >i know, I will be a unemployed contrarian homosexual and say that its GOOD, ackshually
            you can keep playing with your jeet notebooks on datacamp, leave the actual work and positions to the competent whites

    • 2 months ago
      Anonymous

      It's top-tier tech. I've never actually been a user, but I've worked on HW accelerators with it.
      It's just so well coded.

  41. 2 months ago
    Anonymous

    SQL databases vs distributed key value stores like s3?

  42. 2 months ago
    Anonymous

    you could've just used excel

  43. 2 months ago
    Anonymous

    Just use an ORM

  44. 2 months ago
    Anonymous

    >ORMs are for fricking morons, people who like these things should be beaten with a stick

    >Postgres and MariaDb are the best

    >People who make tables without a primary key should be beaten with a stick

    >People who try to make a primary key out of some random column like DateModified should also be beaten with a stick

    >People who store dates as ints or varchars or anything other than the built in type should be beaten with a stick

    >People who are supposed to be software developers and don't understand SQL... You guessed it, beaten with stick!!

    • 2 months ago
      Anonymous

      Agree with all but the orm.
      It isn’t that I can’t do it raw but that I don’t have to.

      • 2 months ago
        Anonymous

        In the long run it causes more issues, even with something as tuned as ef core. People need to stop pretending that the database can be lifted into the application or vice versa.

  45. 2 months ago
    Anonymous
  46. 2 months ago
    Anonymous

    Your database should only have 1 table.

  47. 2 months ago
    Anonymous

    1. Postgres handles 99% of all use cases fine. Yes, even yours. Yes, even if your website gets 100 visitors a minute. SQL Server is actually a decent alternative, but its costs keep it from being anywhere near as high utility as PG.
    2. WHERE IN () is almost always a performance bottleneck if the subquery returns more than a few hundred results. WHERE EXISTS () is almost always better, LEFT JOIN WHERE x1.id IS NULL is almost always best.
    3. Use UUID as your PK. If you can, use UUIDV7.
    4. Store a created_at and updated_at with every record.
    5. Use UTC for your timestamps.
    6. Store currency as an integer (cents if USD). Render it in dollars in your view layer, but store it as an integer.
    7. If you MUST use auto increment for your PK, use a BIGINT. Just do it.
    8. NULL is a pain, though often necessary. Remember to always use (IS NULL or IS NOT NULL) rather than relying on other, similar boolean comparisons. This WILL bite you and it WILL hurt.

    • 2 months ago
      Anonymous

      >LEFT JOIN WHERE x1.id IS NULL is almost always best.
      Holy based. I die every time I have to tell one of my autistic coworkers this and they argue with me.

    • 2 months ago
      Anonymous

      >Use UUID as your PK
      Oof yeah that's gonna be a yikes from me

      • 2 months ago
        Anonymous

        Werks on my machine.

    • 2 months ago
      Anonymous

      >UUIDV7
      Just don't try to overload your field with multiple purposes. Have a unique identifier and have a date.

      • 2 months ago
        Anonymous

        My recommendation is always store a timestamp created_at, even with UUIDV7. The UUIDV7 is strictly to keep the clustered index stored in order on disk.

    • 2 months ago
      Anonymous

      >5. Use UTC for your timestamps.
      also if you're using postgres, learn the difference between timestamp and timestamptz and share that knowledge with your coworkers before you end up with a quagmire of mixed timezones

      • 2 months ago
        Anonymous

        Yes this is good advice to add. I think the new standard is generally to use timestamptz, and just always use UTC as the TZ.
        (There are legitimate use cases to store the originating TZ with a timestamp, but I tend to also just store a TZ along with each user).

    • 2 months ago
      Anonymous

      >even if your website gets 100 visitors a minute
      Is that supposed to be a little or a lot?

      • 2 months ago
        Anonymous

        Very little. But for some reason, people start panicking about db performance at absurdly low numbers. Twitter and Facebook use MySQL. Your blog can too.

  48. 2 months ago
    Anonymous

    le epic test 🙂 gppd servers asiaticmoot

  49. 2 months ago
    Anonymous

    create local temporary table SHIT_MEET_FAN on commit preserve rows as SE__CT id,date F__ schema.table1 WH__ on_fire = 'severely'

    sel__t * fr_m schema.table2
    w_ere (table2.id,table2.date) not in (select id,date from SHIT_MEET_FAN)

    fricking got me cloudflare banned

    its not actually select * but you get the idea
    it's vertica so idk if the dialect is way different than most other DBs

  50. 2 months ago
    Anonymous

    i want to use sql at my company but we're on dataverse and i can't figure out how to merge duplicate entries and it's making me want to kill myself. i can do it in sql but we HAVE to use dataverse i can't write to the db someone help me please. we're using a crm, azure sql. help me

    • 2 months ago
      Anonymous

      Pay me $300 an hour and I'll help

Your email address will not be published. Required fields are marked *