1. 3

    As long as you don’t have hundreds of millions of jobs, yes. If you have that many jobs, things become interesting. It is very nice ot be able create jobs inside transactions.

    Using a varchar() type instead of text in PostgreSQL in 2019, hmmm.

    1. 3

      Using a varchar() type instead of text in PostgreSQL in 2019, hmmm.

      Hah, it’s funny to see this comment, I was just today wondering which one of these I should be choosing for a column that contains a URL fragment - eg oregon in /usa/oregon/portland. Being a newbie to Postgres (my SQL background is in MySQL) I have been choosing text essentially because of the name. Would you please expand on why text is the better choice than varchar in 2019?

      1. 3

        why text is the better choice than varchar in 2019

        It isn’t, they’re equivalent. TEXT is considered idiomatic in Postgres community, and that’s it.

    1. 1

      This has got to be PR, right? This guy completely glosses over the existence of Docker.

      1. 3

        We don’t need docker, we’ve had jails since 1999

        1. 3

          There is no Docker for FreeBSD.

          1. 2

            It’s in progress, as far as I know: https://reviews.freebsd.org/D21570

            1. 2

              I meant like, he’s complaining about how you can’t use Linux to have reproducible environments, and he completely glosses over Docker

              1. 1

                Was Docker already widely-used in 2016? (Genuine question)

                1. 2

                  I don’t have as good of an eye on the space as others, but from what I remember, it started picking up steam in 2014-2015

            2. 2

              Early in 2016 somebody tried out kubernetes, got a long way on having it up & running, but couldn’t keep up with the maintenance burden vs moving the environment onto it. At the time docker was a too-fast-moving target for a 3 person dev team to pick up the burden of maintaining this. Now, it would be a different kettle of fish, with mainstream stable distros providing this. LXC would have been an option at the time, though.

              Don’t get my wrong, I’m violently in favour of containers, but violently against FLOSS using a commercially controlled service as the main way of building and consuming software. Feel free to enlighten me if Docker’s moved on now and isn’t beholden to the VC behemoths for a sweet exit & cash windfall?

              1. 1

                To be clear, I have never used Docker (other than messing around with it for an hour a few years back). I have no place to say if it’s good software or not.

                I just find it fishy that Docker didn’t get a mention at all.

            1. 8

              The fact that you can’t apparently compose functions seems a bit disappointing for something that aims to mimic sql, but oh well. The only thing that seems really bad there is that in the example given, it returned a nonsensical result instead of an error.

              $ fselect "MIN(YEAR(modified)) from Downloads"
              -1
              

              This looks really really nice though because it looks like the syntax may be a lot easier to use and remember than find’s minilanguage, plus it looks like they’ve done a really good job of making output formats be helpful and easy.

              One of the things that’s endlessly painful with scripts that start with a find invocation is feeding filenames into pipelines that then get messed up by funny characters in filenames.

              Stoked to try this out later.

              1. 2

                hey @0x2ba22e11, the author of this tool read the blog post and corrected the bug that prevented function composition here- I haven’t tried the fix yet but it’s being worked on at least!

                1. 1

                  Nice! ❤

                2. 1

                  You could use something like this, which doesn’t require a 5MB Rust binary and probably some insane dependency chain (tested on OpenBSD):

                  find . -print0 | xargs -0 stat -f "%m%t%Sm%t%N" | sort -n | sed 1q | cut -f 2-
                  
                  1. 4

                    Yeah, that is precisely the sort of thing I hate writing. What does that snippet do if one of the filenames has a newline in the middle of it? Nothing good, I’d wager.

                    1. 2

                      What does that snippet do if one of the filenames has a newline in the middle of it

                      Wait, how often do you encounter filenames with characters like newlines in them? Is foo\n.bar really a thing?

                      1. 2

                        Anything except / or \0 is legal on some filesystems. Just dealing with spaces in shell scripts is already a disaster.

                        1. 1

                          Well, sure. I didn’t ask if it was possible, I asked how likely it was you would see files with \n in the name… I’ve never come across this, which is why I ask.

                1. 1

                  No spec means there’s nothing keeping rustc honest.

                  On this topic, I found this to be interesting and informative: https://users.rust-lang.org/t/is-there-a-plan-to-get-a-formal-rust-language-specification-at-some-point/21622/8

                  1. 2

                    There is also https://github.com/n-t-roff/heirloom-ex-vi which is: “The Traditional Vi (vi with many enhancements from Gunnar Ritter)”

                    1. 1

                      I have a feeling that vim would make a great part of her programming environment due to its telegraphic style of interaction. I wonder what options for HCI will be there if I get both RSI and glaucoma. I also wonder if this talon thing could be a little easier to use if it worked like a trie or something similar.

                      1. 1

                        I immediately thought of vim when she explained how “delete 4” is ambiguous. In vim it never is and I agree that it could be very useful for her.

                      1. -1

                        Still better than Postgres where you CANNOT delete a database if there exist at least one connection to it, even if it is only the connection that tries to delete the database.

                        1. 1

                          Interesting approach, but I’m curious about in which ways this is better than a prefix tree?

                          1. 2

                            This is just a fun approach. As another comment mentions, use INTEGER[]. Having to do factorization for every comment whose parents you want to display is perhaps not expensive, but definitely not free.

                            1. 2

                              Recursive CTE is the solution in this case. Solves both “would need N joins” and “can’t count(*) comfortably”.

                            2. 2

                              I don’t think it is better than a prefix tree. Using prime numbers is kind of funny and original, but I’ve never used this approach in a real product and I normally wouldn’t!

                              1. 1

                                Ah, okay. Sorry for being confused :o

                            1. 3

                              The last bit is the only thing I like here. If one up it and say putting ptr = NULL after the free is good practice in many cases.

                              I’d probably reject any change trying to introduce the first two though.

                              1. 1

                                I’d probably reject any change trying to introduce the first two though.

                                Without even a hint of the reason for doing that, this feels to me like argument from authority.

                                1. 3

                                  Am I required to like changes because somebody writes a blog post about them?

                                  1. 4

                                    The blog post expresses an opinion, but it is an opinion on a technical matter and it does provide arguable reasons for preference of one way of doing something over another. I admit it might have been pretentious of me to expect disagreements to be based on reasoning as well, but I don’t think anything else leads to productive conversation.

                              1. 2

                                There is no chance that I’m letting git know my email password.

                                With this guide, you’ll be contributing to email-driven projects like […] PostgreSQL […]

                                I suggest you don’t post your patches inline in emails to pgsql-hackers.

                                1. 16

                                  Having interviewed a lot of people, SQL is one of those things that everyone thinks they know well and very few actually do. In interviews now, if I see them claiming expertise, I ask if they can do something more than “SELECT * FROM foo” because so often that’s all it takes to “know SQL” on your resume.

                                  Good database knowledge can be extremely valuable. I can’t tell you how many times I’ve seen code that makes four or five round-trips to the DB, grabbing data, processing it client-side, and sending it back that could’ve been done in one round-trip with a well-crafted SQL query.

                                  There’s the other side too: I’ve seen some truly awful database designs that have required multiple round trips because the DB designer didn’t understand foreign keys or constraints or whatever.

                                  1. 4

                                    Depends on how you interview me.

                                    If you sit me down with a pen and paper and tell me to write a complex query…. I might well fail.

                                    If you sit me down with say sqlitebrowser… and an editor side by side, I will rapidly iterate starting from very simple queries (yes, I will start with select * from foo) to as complex as need be.

                                    And yes, I might occasionally refer to the syntax for the gnarlier corners.

                                    But I will get it done pretty rapidly in a single very well crafted query.

                                    Conversely, I’m a true believer in what CJ Date has been saying for decades…. so if you allow me to redesign the table definitions…. you’d be shocked by how simple my queries will be.

                                    1. 3

                                      Imo one of the best arguments against relying on ORMs is performance: in some situations a CTE or window function can get you one or two orders of magnitude improvement over a naive orm solution.

                                      1. 2

                                        Nothing prevent you from implementing those edge case in your ORM though. I personally use SqlAlchemy, and I feel like it cover what I need as-is 90% of the time, and the 10% of the time left it gives me the tool to build more complexe queries and even allow me to keep using the “object mapping”. SqlAlchemy supports CTE and Window function!

                                        For even too complexe query, it might also be possible to wrap them into SQL Function and simply map your ORM to this function.

                                        1. 2

                                          Oh, to clarify, I think ORMs are great, I just don’t think it’s great to rely on them. They do 90% of the things for you, but you need to know SQL for the other 10% of cases. Some things aren’t like that, where there’s not such a wildly significant benefit as knowing SQL gives you in this case.

                                          1. 1

                                            This is very true. It’s also helpful to know (in rough terms) what the ORM is doing under the hood, something that’s only really possible if you understand the SQL behind the operations.

                                          2. 1

                                            Yep, Peewee orm also supports things like CTE, window functions, insert/on conflict, etc. The query builders for both libraries are very flexible, but you pretty much have to know SQL and then translate it into the APIs the query builder exposes. For experienced programmers this is no problem, but someone less fluent with SQL is going to have no idea how to use this advanced functionality, regardless of how it is exposed.

                                            1. 1

                                              Definitely! My point was mostly about ORM and “advanced”/performant SQL not being mutually exclusive.

                                              1. 1

                                                I like ORM or query builders not because I don’t know SQL, but rather because I detest the SQL syntax. I wish there were a better relational language. Maybe Tutorial D in front on Postgres’ storage engine.

                                        2. 2

                                          What would you consider beyond “SELECT * FROM foo"? I don’t touch SQL on a daily basis, but I could throw together a left, right, and full join if I needed to, and I’m aware of how to use subqueries. What SQL skills would someone have in order for you to consider them competent / good at SQL?

                                          1. 4

                                            JOINs, GROUP BY, that sort of thing. If they’re going to be building DBs, understanding indexes, unique constraints, and foreign keys.

                                            If you’re gonna be my lead database programmer/DB admin/DB architect, I’d want to see understanding of good database design (normal forms, good foreign key relationships, good key choices), CTEs, triggers (in your preferred database), being able to spot when a full table scan might happen, understanding when and how to use precomputed values for future queries, and so on.

                                            1. 4

                                              The use of aggregate functions, defining new aggregate functions, the various joins, the 3VL truth table, grouping, subselects, CTEs, that kind of thing.

                                              1. 3

                                                I like asking candidates

                                                • find me the employee with the highest salary (entire row, not just the salary) - will usually use sub select with max.
                                                • find me the employee with the second highest salary - expected to use window function but can still get away with two subquries.
                                                • find me the employee with the second highest salary in every department - expected to use window with partition.

                                                If you found a guy that thought about equal values ( rank / dense rank / row number ) you know he did some work. Hire him.

                                                Haven’t touched joins yet.

                                                1. 2

                                                  I don’t know window function, but believe that I know join

                                                  1. 3

                                                    It’s well worth the time learning window functions.

                                                    As Maruc Winand (who created use-the-index-luke.com and the newer modern-sql.com) says in his talk on Modern SQL there’s life before windows functions and life after windows functions.

                                                  2. 1

                                                    I’d much prefer lateral joins be used in some of these cases.

                                                  3. 1

                                                    I’ve seen no mention yet of EXPLAIN queries (Or similar operation in other RDMS than Postgresql?). Never been doing a lot of SQL in the past, but lately I had to work with some complexe queries over large dataset and most of my learning involved playing with EXPLAIN [ANALYZE], understanding the result and tweak the base query to fix the performance issue. Once you understand that, you can work from there to find the best index, where to place subqueries, set large enough working memory, etc.

                                                1. 6

                                                  Really nice post, regarding:

                                                  Writing C code and trying to keep it indented was a bit of a pain and I wish I would have done something else. I believe some compilers write ugly code and then “pretty it up” with a library before writing it out. This is something to explore!

                                                  you can use clang-format.

                                                  1. 3

                                                    Cool, I’ll check it out! It’d be nice to find some library code or something I can depend on cross-platform though. Maybe I’ll consider writing my own bit of C tidying code.

                                                    1. 3

                                                      There’s also indent, multiple forks of it actually. Most advanced are (in no particular order) GNU indent, FreeBSD indent(1), and cindent.

                                                      1. 2

                                                        Oh cool! This looks like something I can use!

                                                    2. 2

                                                      The nice thing about writing your compiler in elisp is you have all the indenting functionality you could ask for all built-in.

                                                      (please don’t do this)

                                                      1. 2

                                                        Steve Yegge famously(?) wrote a JavaScript Parser / analysis engine in Elisp to make js2-mode.el. Can’t be that bad. :)

                                                    1. 5

                                                      strlcpy(3), strlcat(3) aren’t “Kernel functions” AFAIK.

                                                      Great website, very fun to read all these lesser known features.

                                                      1. 3

                                                        strlcpy is often just marginally less wrong than strncpy and less verbose than memcpy() + finishing with 0. But at least, whenever someone changes strncpy to strlcpy, it can prompt thinking about truncation errors.

                                                      1. 5

                                                        While OpenBSD is nice, I always wonder why NetBSD doesn’t get more love. Surely because it’s more portable, it’s subsystems must be really clean? It might not be as secure, but doesn’t this mean things must be predictable as well?

                                                        Sell me OpenBSD over NetBSD friends.

                                                        1. 6

                                                          At a certain point you basically achieve maximum portability for all the machine independent code. New CPU X comes along, and it’s got some new bit patterns for its instructions, but it’s otherwise just another model of something that already exists. The code for ls and tar and nfs is ready to run on any CPU that might possibly exist within the target space (so no 8 bit microcontrollers). The build system supports N different platforms, with N > whatever number you might manually hack around. N + 1 is just another name in an array.

                                                          1. 6

                                                            Portability has not been the main point of NetBSD for a long time. Now the OS is supposed to be correct, well structured, with sane architecture, etc. For example, the fuzzing/sanitizing work that is being done is not directly targeted at improving portability.

                                                            1. 4

                                                              NetBSD wants to run on everything, no matter the cost. OpenBSD wants to run well.

                                                              1. 2

                                                                NetBSD has some cool features, but in my experience I’ve found OpenBSD to run on more hardware more easily than NetBSD.

                                                                OpenBSD’s focus on simplicity and security, help make OpenBSD my preferred OS - but I’m biased - my personal desktop machine has been OpenBSD since 2001…

                                                              1. 4

                                                                Python has massive use in a diverse array of fields with lots of educational resources helping beginners. There is and will be for a while lots of Python code. It’s increasingly used in business-critical systems, like at Bank of America. That it’s built on an unsafe language puts that all at risk of unreliability and security vulnerabilities. Long ago, I wanted to rewrite it in Ada with all safety features on to mitigate that risk while maintaining its convenience. Rust is another safe, systems language with extra measures for temporal errors. So, this project is doing the same thing.

                                                                Full security for Python apps would require consideration of each layer of abstraction:

                                                                1. User’s code in Python.

                                                                2. The interpreter and extensions.

                                                                3. How these interact.

                                                                4. If added for performance or security, any assembly code plus its interactions.

                                                                Rewriting Python interpreter in Rust mainly addresses No 2. An example of a method to address all of them would be Abstract, State Machines which can represent simultaneously language semantics, software, and hardware. Tools like Asmeta exist to make them like programming languages. The verification would probably be manual, specialist work. Whereas, Rust’s compiler gives you key properties with just annotations for many and working with borrow-checker for a few.

                                                                1. 7

                                                                  I’ve wondered if a Rust implementation would be a good option to overcome the global interpreter lock. Fearless concurrency is one of Rust’s claims to fame. It would be a nice additional benefit.

                                                                  1. 5

                                                                    The GIL is not there because of the programming language the interpreter is written in, so why would Rust change that?

                                                                    1. 2

                                                                      The GIL is there because it’s the easiest way to ensure thread safety. Rust provides other easy ways to ensure thread safety (ownership).

                                                                      1. 3

                                                                        Initially yes, but it is more complicated now. If you don’t want to rewrite lots of C extensions, then RustPython also needs a GIL. The alternative is to be incompatible with plenty of useful libraries.

                                                                        https://wiki.python.org/moin/GlobalInterpreterLock

                                                                        1. 2

                                                                          It doesn’t have to be incompatible: you can avoid using a GIL until you hit a C extension. Pure Python scripts get real parallelism (and better single-threaded performance due to fewer syscalls), and Python code calling C is no slower.

                                                                          1. 3

                                                                            How much pure python code is parallel thread safe?

                                                                            1. 1

                                                                              The GIL doesn’t provide pure Python code with thread safety: any thread can still preempt any other thread at any point in time, causing all of the danger of threads with none of the benefit. So — any code today in pure Python that uses threads is either threadsafe, or already broken. Removing the GIL only improves performance, it doesn’t add bugs. This is why Jython and IronPython can avoid having a GIL.

                                                                              But, that doesn’t hold for C extensions, because the API itself (apparently) isn’t threadsafe. So that’s why you still need the GIL when using those.

                                                                              1. 1

                                                                                Ah, I’d forgotten about the byte code preemption.

                                                                          2. 1

                                                                            Interesting point. Perhaps there could be two implementations: one that does have a solution, maybe GIL, that works with extensions that need it; other methods for when such extensions aren’t needed. Maybe it’s a Multicore vs Legacy mode or even two interpreters in one with the switch flipped based on what it sees during parsing.

                                                                          3. 1

                                                                            GIL is there due to:

                                                                            • Single threaded scripts run faster with a coarse lock than fine-grained locks
                                                                            • Complexity of adding fine grained locking to CPython

                                                                            Nothing prevents these developers from attempting GIL-less models (excepting for C extensions). There may be a solution to that was well - JRuby developers chose to interpret the C-Extension which allowed them to have their cake and eat it too (with some limits).1 2

                                                                        2. 3

                                                                          Totally forgot about GIL. Thanks! Yeah, that was one of things I was considering back then. Particularly a port of SCOOP to Ada to get safe concurrency with fewer restrictions than Ravenscar.

                                                                          Rust has a few options for concurrency and parallelism. It would arguably be better not just for safety but maybe ease of expressing the algorithms with good performance. As in, no low-level hackery. SCOOP has great usability but I don’t know its performance. I know Meyer’s research group had peopke working on improving its performance with one using slices or something.

                                                                        3. 1

                                                                          That it’s built on an unsafe language puts that all at risk of unreliability and security vulnerabilities.

                                                                          Theoretically, you don’t have to use CPython to run Python code.

                                                                          1. 1

                                                                            The point would still apply if the alternative generated C or was a managed language depending on C/C++ with significant TCB (esp Java or .NET).

                                                                            1. 1

                                                                              So the point applies to Rust as well, as it depends on “C/C++”.

                                                                              1. 1

                                                                                I said significant TCB. How much C and C++ inside an app’s process do Rust apps depend on these days? I thought Rust compilers and libraries were mostly written in Rust.

                                                                        1. 1

                                                                          I think MergeJoin in Postgres is currently implemented using something that I’ve seen called “zipper algorithm”.

                                                                          1. 5

                                                                            I must admit I didn’t know about Citus before reading this article, nevertheless I’m surprised by Microsoft’s moves towards libre software friendliness in the last couple of years; the acquisition of Github, support for SQL Server over Linux (which I remember thinking as unnecessary since Postgres improves upon it by a big margin) and now Citus Data acquisition, strategic moves that look (at least on the surface) as steps on the right direction for a company that big, considering how hostile they were to the GNU/Linux community back at the beginning of 2001.

                                                                            1. 7

                                                                              Don’t forget about WSL, which was by far the biggest surprise to me.

                                                                              1. 4

                                                                                Thanks you. And I almost forgot when they joined LOT and OIN, to fight against patent trolls.

                                                                                1. 6

                                                                                  We’d be wisd to remember Embrace, Extend, Extinguish as well…

                                                                                  1. 4

                                                                                    Plus, them extracting billions in patent royalties from Android. I don’t trust anything they say about patents. They have both lobbyists trying to change patent law to maintain/enable their lawsuits along with excellent lawyers who wield it profitably.

                                                                                    1. 1

                                                                                      I’m also thinking it might be another chapter of a practice MS is expert on, but I hope to be proven wrong. Time will tell eventually.

                                                                                  2. 2

                                                                                    Although it was a surprise, hindsight shows me we shouldn’t be surprised that they built something to run Linux’s apps on a paid product of theirs. They did it before with OS/2 subsystem for Windows NT. They were even also a UNIX shop at once with Xenix. SCO and OpenServer still exist despite IBM and Linux stomping them.

                                                                                    If anything, we should assume they’ll do more if it results in users either staying on or going to Windows.

                                                                                  3. 6

                                                                                    All right, I’m going to raise my hand and say after dealing with Oracle and SQL Server professionally, they’re both terrible at making things easier for a normal developer. SQL Server can at least fit in the Visual Studio/.NET toolchain/ecosystem comfortably, but using it outside of that can be awkward. Oracle documentation is made for people that must be getting paid to read it and tries to make its error messages provide no understanding of problems.

                                                                                    Yes, I’m probably biased because I’m working on ~20 yr old code and the DBA’s I work with don’t seem to know much besides moving archives around, but I shouldn’t have to create a single char column with a constraint of ‘Y’ or ‘N’ to fake a boolean column type (and that was according to the best practices that I’ve found thus far) - this makes me think that developer productivity isn’t relevant to Oracle. So Postgres is appealing to me as a pretty conventional Java/C# dev because they give me features (array types, json types/functions, and not requiring me to go to weeks of training) and let me forget about the database as much as possible.

                                                                                    1. 3

                                                                                      I’m probably biased because I’m working on ~20 yr old code

                                                                                      We are facing that same issue at my current job, there we have databases running for production systems using Oracle 8i (last supported release is from 1998), which is not adequate for current data demands, but still Oracle 11g is not that different from 8i, at least for the most used features we make use of at work.

                                                                                      1. 2

                                                                                        Database upgrades aren’t too bad (we’ve done them once or twice at work) as long as the features you’re using were deprecated in favor of something Oracle can charge more for (CDC Goldengate I’m looking at you).

                                                                                        1. 4

                                                                                          This is maybe the only time I’ve ever seen people who actually use Oracle databases pop up in a thread. Even on HN, with it’s much bigger readership (though maybe all drawn from the same bubble) you’d swear it was 95% Postgres and a few people who think they ought to feel ashamed for using MySQL even though it’s working fine for them.

                                                                                          So, if you don’t mind me asking, are there plus sides to an Oracle database? Does it do things that no other DB does, is it crazy reliable, or crazy fast, or is the on-call support so damn good that when there’s big money on the line you can’t go wrong with Oracle? Or is their revenue really due to legacy lock-in / switching costs, tech dinosaur inertia, and expert CTO shmoozing? Because Larry Ellison’s still a billionaire, so they’re presumably getting something right…

                                                                                          1. 3

                                                                                            There are many places that have Oracle but aren’t really Oracle shops per se, as Oracle is a big part of a lot of enterprise-y software packages that are sold to CTO types.

                                                                                            Too, the contortions Oracle partisans wrap themselves into to justify the lack of a Boolean datatype are hilariously stupid.

                                                                                            1. 2

                                                                                              Upsides? Maybe corporate inertia? We don’t do anything special (mostly big CRUD, and reporting), but the database decision(s) got made over a decade ago and nobody seems to think evaluating the costs would be a worthwhile exercise (which is odd considering Oracle licensing costs…).

                                                                                              I’m firmly convinced that lots (maybe even most) decisions about software on a executive level (especially outside of the valley) are based on shmoozing. The software that my company makes (SaaS) is mostly made on that level, but there’s a point in going after a customer where the salespeople give up and say “(CxO) is an SAP guy, so this has no chance.” Which has nothing to do with cost/features/support. So we use Oracle for the same sort of reason.

                                                                                              C’est la vie

                                                                                              1. 1

                                                                                                This is maybe the only time I’ve ever seen people who actually use Oracle databases pop up in a thread.

                                                                                                I really think this is just a subculture issue. MS SQL is a bit similar, but one of the advantages of that solution is that MS is relatively more open. My (conspiracy) theory is that Oracle consultants keep knowledge secret to increase revenues ;)

                                                                                                But there’s always a cut-off point where the maintenance costs exceed the costs of moving to another solution. I think Oracle has a stable base of satisfied customers, but the growth potential is low - hence buying Sun etc.

                                                                                        2. 5

                                                                                          which I remember thinking as unnecessary since Postgres improves upon it by a big margin

                                                                                          I keep seeing people say stuff like this and it drives me bonkers. Yes, PostgreSQL has lots of convenient features, data types, and functions. But SQL Server’s query execution engine is much faster than PostgreSQL’s, and it’s query optimizer blows PostgreSQL’s out of the water. Yes, PostgreSQL has improved performance a lot in 9.x, 10, and 11. No, it’s still not anywhere close to SQL Server, especially for hugely complicated queries.

                                                                                          It’s like saying C++ is unnecessary since Python improves on it by a big margin. Good for you if Python meets your performance and scale requirements, but that doesn’t make C++ unnecessary.

                                                                                          1. 6

                                                                                            sophisticated query optimizer

                                                                                            That’s nice and all, but have you heard about our lord and saviour, clustered indexes on schema-bound views!? They’re the one MSSQL feature that I can now barely imagine living without. (Tiny inconvenience: they very-often aren’t used without a WITH (NOEXPAND) hint but oh well no biggie.) All whole bunch of aggregations suddenly magically become possible to make O(1-ish) per row selected, at the cost of adding O(1-ish) cost to data modifications.

                                                                                            1. 1

                                                                                              Yeah, I mean, people who have used Postgres – people who understand how much better it is at the basic job of preserving data, when compared to MySQL – often have never used a serious commercial database.

                                                                                              ETA: But that said, Postgres is a wonderful tool, and significantly more comfortable for developers.

                                                                                              1. 1

                                                                                                I must admit I spoke without being specific on which points postgres (which from my point of view are mainly richer features) improves upon, nevertheless I trust your experience on this topic (since mine hasn’t being enough with SQL Server to speak properly, much of what I know about RDBMS is from Postgres, Oracle and Sybase [poor me]) and thanks for pointing this out. Where is a good source of unbiased information I can learn more about what you’ve mentioned here?

                                                                                                1. 4

                                                                                                  Unfortunately, there aren’t really any good sources of information, or any way to acquire this knowledge other than industry experience.

                                                                                                  Trouble is, database vendors have every incentive to avoid publishing benchmarks or other performance data. It’s difficult to get right, usually easy to see where they’ve biased the sample scenario in their favor, it almost always appears juvenile, and some salty web hipster will get a gajillion upvotes on HN for vilifying the publication regardless of whether said web hipster has the slightest clue what they’re talking about.

                                                                                                  The best you can do is read vendor documentation that explains how capitalize on certain optimizations, or avoid certain caveats. If you really know what you’re looking at and can read between the lines, you can figure out who does what well. Even then, lots of the juiciest details are undocumented and given to customers on a need-to-know basis.

                                                                                                  I’ve seen SQL Server generate incredible plans that I couldn’t even begin to explain how it knew to choose. And I’ve seen PostgreSQL throw it’s hands up in the air and hash join several full table scans when a simple lateral join obviously would work better. It just isn’t a competition.

                                                                                                  Good commercial databases cost buckets of money for a reason. When you need that kind of power, there usually isn’t any other way to get it.

                                                                                                  1. 1

                                                                                                    any other way to get it

                                                                                                    Pay someone to improve Postgres for you? :)

                                                                                                    1. 1

                                                                                                      acquire this knowledge other than industry experience

                                                                                                      It seems to me like an appropriate scientific and fair method to know the internals and the advantages of each engine. Thanks for your objectivity.

                                                                                                  2. 1

                                                                                                    Many of my database-related tasks have more to do with SQL ergonomics than with performance. I currently work using Oracle 11 and I miss so many things that are natural using Postgres. They include SELECT FROM VALUES (instead of silly SELECT FROM dual), array types, jsonb_agg, SELECT a>b and probably some other features (I’m also missing LIMIT OFFSET, but newer Oracle has FETCH FIRST, so it doesn’t really count as an advantage over Oracle). Sure Oracle has great features that Postgres lacks, but those are what I would use every day if I could.

                                                                                                    1. 1

                                                                                                      [MSSQL’s] query optimizer blows PostgreSQL’s out of the water

                                                                                                      Is this recent? I’ve DBA’d postgres, and have (distant past) MSSQL experience, but back then, postgres’ query optimizer seemed far more advanced. I seem to recall MSSQL’s bad query planning being one of the reasons the stored proc became such an important part of MSSQL culture.

                                                                                                    2. 3

                                                                                                      That surprising cuz we did get a lot of submissions, some authored by @craigkerstiens. I thought we were one of goto sources for Citus news and write-ups.

                                                                                                    1. 2

                                                                                                      It’s not optimal to run PostgreSQL on zfs on your master. Every disk write causes b-tree updates in the PostgreSQL layer, as well as the zfs layer. This basically exponentiates your IOPS per write, killing performance. It’s better to run your master on ext4 or xfs. You can use a streaming replication to asynchronously keep a zfs-based standby updated, and delegate all your backup operations to that machine.

                                                                                                      1. 9

                                                                                                        There’s a lot in this thread, but to sum it up…

                                                                                                        1. I agree with trousers, there are ways to tune psql on ZFS and I have applied them and it works well. ZFS has much more sophisticated recovery tools than PostgreSQL. PostgreSQL would have to be applied on top of RAID, for example, to be resilient to disk failure, but ZFS has this built-in and I can take advantage of many of its other nice features for free if I use it.
                                                                                                        2. I have a performance budget and I’m willing to spend it. It’s not necessary to squeeze every drop of performance out of the system when postgres isn’t your bottleneck.
                                                                                                        1. 1

                                                                                                          Ignore my advice at your peril. When your master starts hitting IO capacity long before you expected, you’ll come back to this thread. There’s a reason btrfs added the “nocow” option.

                                                                                                        2. 4

                                                                                                          I dont know… People run postgres on zfs all the time, and get good performance. The benefits of zfs (protection from bitrot) are real and shouldnt be discounted.

                                                                                                          You do need to tune zfs a bit though.

                                                                                                          https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices

                                                                                                          1. 4

                                                                                                            You do need to tune zfs a bit though.

                                                                                                            This is true. The most important thing is matching the recordsize to the PostgreSQL page size; i.e., 8KB. Any mismatch at all can put you in the RMW hole, where writes to cold records can first involve a random read – not a good place to be.

                                                                                                            https://www.slideshare.net/SeanChittenden/postgresql-zfs-best-practices

                                                                                                            Please beware of this slide deck. It contains things like messing with logbias and sync which are almost certainly wrong; at best your performance won’t improve, and at worst you may be risking corruption.

                                                                                                            1. 2

                                                                                                              Please beware of this slide deck. It contains things like messing with logbias and sync which are almost certainly wrong; at best your performance won’t improve, and at worst you may be risking corruption.

                                                                                                              You are right – thanks for pointing that out. 👍

                                                                                                              1. 2

                                                                                                                It contains things like messing with logbias and sync which are almost certainly wrong

                                                                                                                hm. About logbias it says “controversial” right in the title of the slide, and I can’t find anything about sync in there right now…

                                                                                                                matching the recordsize to the PostgreSQL page size; i.e., 8KB

                                                                                                                If write performance is what you’re after. I’m using 64k on my Matrix server to get that sweet 3.34x compression and pay less money for storage :)

                                                                                                              2. 0

                                                                                                                People do and they could be getting much better performance. No amount of ZFS tuning is going to eliminate the exponentiation of IOPS that I mentioned. No one is saying the benefits of ZFS should be discounted, just that a more effective use of ZFS in a PostgreSQL setup would be in a asynchronous slave machine.

                                                                                                                1. 4

                                                                                                                  You can disable pg full_page_writes on zfs though, can’t you? That saves a lot of overhead that you can’t ditch on non-COW fses.

                                                                                                                  An ssd slog can also help a lot for speeding up synchronous writes on a “spinning rust” pool.

                                                                                                                  1. 0

                                                                                                                    You can save even more by switching to ext4 and keeping full_page_writes than keeping zfs and turning off full_page_writes. PostgreSQL was already architected to handle crash recovery before the advent of ZFS. Using ZFS to avoid crash recovery with PostgreSQL is redundant.

                                                                                                                    1. 6

                                                                                                                      ZFS was designed to avoid silent disk corruption as well. I guess we’ll just have to disagree on appropriate deployments at this point.

                                                                                                                      1. 2

                                                                                                                        That’s why I suggest storing your database backups on ZFS, but not your master.

                                                                                                                        1. 6

                                                                                                                          This of course doesn’t stop your master db from silently returning corrupt data if you have bad sectors – expensive raid hardware /may/ detect it though. Flipped bits on the master may also result in backups with flipped bits (depending on how you do backups I guess), because you may be backing up garbled data.

                                                                                                                          It depends on how valuable your data is though, for sure.

                                                                                                                          1. 1

                                                                                                                            You rarely find out about disk corruption fast enough to do anything about it, and your backups would be destroyed in a silent corruption with zfs for backups scenario

                                                                                                                            1. 0

                                                                                                                              No, I keep hourly back ups going back a year. The kind of failure being suggested here is exceedingly rare with today’s storage technology (which automatically remaps bad blocks). If you’re running PostgreSQL in a virtualized environment (e.g. on top of something like AWS EBS) then this failure mode is non-existent.

                                                                                                                        2. 1

                                                                                                                          You can save even more by switching to ext4 and keeping full_page_writes than keeping zfs and turning off full_page_writes.

                                                                                                                          Can you explain why this is the case? This isn’t obviously true to me.

                                                                                                                          If your workload consists of updating many different rows each on a different page, disabling full_page_writes can reduce the amount of WAL generated by an order of magnitude. It seems plausible that the reduction in WAL could make up for the increase in IOPS.

                                                                                                                          1. 1

                                                                                                                            If your workload consists of updating many different rows each on a different page, disabling full_page_writes can reduce the amount of WAL generated by an order of magnitude. It seems plausible that the reduction in WAL could make up for the increase in IOPS.

                                                                                                                            Plausible, yes. In practice, no. Each page write on ZFS results in log(n) page writes within ZFS. Whereas a full page write is still one page write, not to mention a single disk seek (i.e. virtual IO op), compare to the log(n) seeks in ZFS. Of course measure it for yourself. I’ve run PostgreSQL in production on both ZFS and non-ZFS so I already know the difference.

                                                                                                                            1. 1

                                                                                                                              As I understand it, synchronous writes will be written to the ZIL, then asynchronously coalesced and written back to main storage. So, isn’t this incorrect?

                                                                                                                              Also, do you have experience using an external SLOG device with a PostgreSQL setup? How does that affect things?

                                                                                                                              1. 1

                                                                                                                                It’s not incorrect that a write of a single block will translate into a write of log(n) blocks in ZFS. How many IOPS that translates into at run-time depends on how often data is being synchronized. No experience using a SLOG device since I run on virtualized hardware.

                                                                                                                  2. 2

                                                                                                                    It’s not optimal to run PostgreSQL on zfs on your master.

                                                                                                                    As with many pieces of advice, it really depends on your specific workload. If you are optimizing for write performance, sure, maybe ZFS isn’t the right choice.

                                                                                                                    On the other hand, if you have a right intensive workload, ZFS can dramatically improve performance due to compression. ZFS compresses both data on disk and data in the in-memory cache. This allows you to read a lot more data from disk faster and cache a lot more data in memory than you would be able to otherwise.

                                                                                                                    Heck, I can even imagine scenarios where ZFS improves write performance. If your workload is primary bulk updates and inserts, ZFS may reduce write volume by compressing the data before it’s written to disk.

                                                                                                                    As always, it all depends.

                                                                                                                    1. -2

                                                                                                                      Yup, live write-heavy loads will be non-workable. Though, someone once said all live database are also write-heavy databases, it’s just a matter of time.

                                                                                                                    2. 1

                                                                                                                      Every disk write causes b-tree updates

                                                                                                                      What does that mean?

                                                                                                                      1. 1

                                                                                                                        Exactly what it says it means. ZFS stores all data in COW fashion using b-trees. Every write must update block pointers all the way up the tree.

                                                                                                                        1. 1

                                                                                                                          I was asking about “the Postgres layer”. What does that mean in the context of PG?

                                                                                                                          1. 1

                                                                                                                            When postgres writes a new row, it has to update every node up the postgres-layer btree. Each of those page writes in consequence causes ZFS to do log(n) writes since it also uses a btree.

                                                                                                                            1. 1

                                                                                                                              I don’t know where you got that from. Thinking it might be my ignorance, I asked a few people who know PG internals well, but they didn’t know either.

                                                                                                                              1. 1

                                                                                                                                What do you mean where i got that from? Postgres indexes are btrees. That’s how btrees work.

                                                                                                                                1. 1

                                                                                                                                  I don’t think that that’s how Postgres Btrees work. They aren’t binary trees, they are an improved implementation of Lehman and Yao’s Btrees.

                                                                                                                                  1. 1

                                                                                                                                    Which still essentially have log(n) insertion time. You need to read more. https://en.wikipedia.org/wiki/B-tree

                                                                                                                                    1. 1

                                                                                                                                      Ordinary inserts require a search and then an insertion into the chosen page. Postgres doesn’t have to update every node up the btree. And certainly it’s not true that every disk write causes btree updates (there might not be an index).

                                                                                                                                      1. 1

                                                                                                                                        I mistyped, I was referring to row updates that modify indexes, nonetheless an extremely common use case.

                                                                                                                    1. 4

                                                                                                                      As a dyed-in-the-wool rustacean, I love it. “No don’t Rewrite It In Rust. It already works, what’s the gain? Now if you’re writing something new, maybe consider Rust as an option…”

                                                                                                                      1. 9

                                                                                                                        I’ll give this presentation as a counterpoint showing how they’re constantly adding more bugs with both new code and changes to existing code. We need to make sure these people making these changes are using a language immune-by-default to common problems they can’t seem to avoid. So, that justifies gradually rewriting existing code.

                                                                                                                        Getting them doing it for new code first is a smart strategy, though. They’ll also see the benefits of the safe language as they deal with problems in modifications to unsafe, lower-level code more than modifications to safe, higher-level code.

                                                                                                                        1. 3

                                                                                                                          That’s a very good point and a convincing presentation. That said, Rust and other safe-by-default languages are not a panacea. I would expect this to be especially true in things like operating systems where you will have a fair amount of unsafe code floating around, trying to have safe semantics while often not quite managing it. That said, while OS dev in Rust is definitely of interest to me it’s not something I have dug into too much yet, so I shouldn’t try to extrapolate too much.

                                                                                                                          1. 3

                                                                                                                            Good point. I’ll note that a conversion with unsafe’s means it will still be safer with the unsafes being, at worst, no less safe than the original. However, unsafe Rust still has some safety over unsafe C. The unsafe parts might improve. Finally, there are automated techniques for verifying a lot of those unsafe components that can be applied. It will be more feasible to do it once the part that needs such verification is a tiny part of the codebase.

                                                                                                                            So, there’s still some potential to reduce risk even in presence of unsafes. The unsafes will still have risk, though. Far as OS dev, you might find the book on embedded Rust useful since it targets lots of low-level interactions. That community is both trying to do them and figure out ways to leverage the type system when doing them.

                                                                                                                          2. 2

                                                                                                                            Is there any evidence that people are adding more bugs than people rewriting things in Rust are? Rust isn’t immune to bugs, far from it, nor is it immune to security bugs. Everyone knows rewriting code will result in introducing some bugs. Can you really be confident they’re fewer and less severe than the bugs already there? Can you be confident they won’t just reintroduce the same bugs into the Rust version?

                                                                                                                            1. 1

                                                                                                                              Yeah. Rust is immune by default to problems many codebases keep having. Many lead to code injection whereas Rust’s just lead to panics or something. The default going from hackers controlling our boxes to applications crashing would be an improvement.

                                                                                                                              I’ll also note that the temporal errors the borrow checker catches are the source of many heisenbugs. Those are errors that are just hard to find or reproduce. Even OpenBSD had a bunch of them despite their attention to code quality. So, a language and/or tooling that prevents them makes more sense than trying to hunt for them.

                                                                                                                              1. 2

                                                                                                                                It’s just not true that Rust is immune to security bugs. Isolating a particular set of security bugs and claiming they’re worse, conveniently also being the ones that Rust can’t have (if you follow a long set of restrictions that nobody follows, like not using unsafe code)? I think that’s intellectually dishonest.

                                                                                                                                Rust fixes some things, sure, but there are lots of issues it doesn’t fix and cannot fix, and many of them are just as bad or worse than the issues it does fix.

                                                                                                                                1. 1

                                                                                                                                  “It’s just not true that Rust is immune to security bugs.”

                                                                                                                                  You’re being intellectually dishonest by misquoting me, setting up a strawman like that, and knocking it down. I’ll restate what I said so you can reply to that instead:

                                                                                                                                  “Rust is immune by default to problems many codebases keep having. Many lead to code injection whereas Rust’s just lead to panics or something”

                                                                                                                                  I didn’t say all security problems: just many that are common. Like described here, Safe Rust blocks spatial errors (i.e. memory-safe) and common types of temporal errors (eg null dereference, use-after-free, some races). These are blocked by design where the compiler either adds checks or forces code to be structured to make detection automatic. C allows these problems by default. Most vulnerabilities people find are these kinds of vulnerabilities. Most of the really, clever attacks start with one of them before building a chain. So, making a language immune to the specific classes of vulnerability that turn up all the time in C code will reduce those classes of vulnerability. That’s what you need to argue against.

                                                                                                                                  The next claim I make is there will be reliability and security failures left due to stuff the type system can’t cover. The benefit a safe, system language retains is you can spend your bug hunting time on those other things. You don’t have to check the code for the same stuff that keeps getting people in C. I’ll add that arrays, stacks, and so on are common primitives that people have to use constantly. Whereas, the esoteric errors will be in less common code. It’s easier to find them when one has more time with less code to look at. So, making the majority of code safe even helps one potentially catch the other errors for those reasons. Obviously, we’ll also develop more checkers for stuff like that on the side.

                                                                                                                                  1. 2

                                                                                                                                    Rust is immune to an arbitrary subset of security issues. We both agree with that.

                                                                                                                                    What I am saying is that taking that fairly arbitrary subset and suggesting they’re the more important issues is just not true. ‘The issues that Rust happens to prevent’ is actually characterised by anything other than that Rust happens to prevent them.

                                                                                                                                    The other thing I’m saying is that there’s no evidence, as far as I am aware, that taking away those issues reduces the overall prevalence of security issues in software, or reduces their severity. Maybe it does? But I haven’t seen any evidence of it. And even if such evidence exists, is there evidence that Rust is the best way of achieving that reduction? Could the same reduction in security issues be achieved by doing something much simpler and smaller like standardising some safer string and buffer operations and types in the next C standard and promoting their use?

                                                                                                                                    1. 1

                                                                                                                                      “arbitrary subset of security issues”

                                                                                                                                      We do not agree on that. Rust followed the path of many safe languages to look at most common failures to block them first. You saying arbitrary implies it’s as if they picked stuff at random with unknown effects on the code out there. No, they picked memory-safety and temporal errors that were hitting people constantly, including experts at secure coding. The errors that are in CVE’s with code injections all the time. That’s not arbitrary: it’s evidence-based mitigation focusing on stopping the most attacks with the least language or security features.

                                                                                                                                      “The other thing I’m saying is that there’s no evidence, as far as I am aware, that taking away those issues reduces the overall prevalence of security issues in software, or reduces their severity.”

                                                                                                                                      Most of the reported vulnerabilities that lead to code injection are due to unsafe languages having no mitigations. Rust mitigates those by design. That’s evidence it reduces overall, code injections. I’ll add I keep mentioning code injections since a hacker taking over your box in secret is much worse than it crashing, optionally telling you where it crashed. Both Ada and Rust prioritized stopping the most common bugs and severe outcomes.

                                                                                                                                      1. 2

                                                                                                                                        We do not agree on that. Rust followed the path of many safe languages to look at most common failures to block them first.

                                                                                                                                        That’s literally just not true. That’s not what the design process for Rust looked like.

                                                                                                                                        Most of the reported vulnerabilities that lead to code injection are due to unsafe languages having no mitigations. Rust mitigates those by design. That’s evidence it reduces overall, code injections.

                                                                                                                                        Again, what Rust mitigates by design is not a special class of security issue to anyone except Rust advocates who like to pretend that ‘memory safety’ is a special class of security issue that far surpasses any other.

                                                                                                                                        A hacker taking over the box so they can DDOS someone is a far less serious security issue than personal data of customers being leaked, IMO. But because Rust allegedly prevents one and not the other, Rust advocates reorient their world view around the former being qualitatively worse.

                                                                                                                            2. 2

                                                                                                                              We need to make sure these people making these changes are using a language immune-by-default to common problems they can’t seem to avoid.

                                                                                                                              That sounds scary, to be honest.

                                                                                                                              1. 6

                                                                                                                                I bet. Even more scary to me was a cliche that said we’ll keep programming like we only have PDP-11’s no matter what changes or happens. Lots of damage followed that mindset. Whereas, hardly any damage followed alternative practice of giving people stuff that was safe-by-default. So, I’m less scared about pushing it given the better results.

                                                                                                                                1. -1

                                                                                                                                  There are no positive results from attempts to do back-seat driving.

                                                                                                                                  1. 1

                                                                                                                                    You mean seatbelt laws and regulations improving crash safety? Definitely been positive results from those.

                                                                                                                                    Then again for software in DO-178B cuz it mandates quality with high penalties for mistakes. Folks naturally started using better tooling.

                                                                                                                                    1. 2

                                                                                                                                      I mean you telling people to write operating systems in your language of choice.

                                                                                                                                      1. 5

                                                                                                                                        Really? The topic is about whether it’s worthwhile to write operating systems in Rust, and you’re trying to call someone out for saying “yeah, it makes sense to write operating systems in Rust”? When he’s even got a reasonable supporting argument?

                                                                                                                                        1. 2

                                                                                                                                          The topic is about whether it’s worthwhile to write operating systems in Rust, and you’re trying to call someone out for saying “yeah, it makes sense to write operating systems in Rust”?

                                                                                                                                          No, I never objected to writing new software (specifically operating systems) in Rust.

                                                                                                                                        2. 1

                                                                                                                                          Maybe. Let’s judge different contexts:

                                                                                                                                          1. If it’s a company paying for it, they can dictate the language to developers. We saw this with Microsoft’s .NET snd Sun’s Java. It’s happening selectively at Mozilla with Rust and some companies who do Ada/SPARK (eg for Muen).

                                                                                                                                          2. If it’s FOSS and mostly paid developers (eg Linux), they might be able to get more code in a specific language if offering to match rewrites or threaten to pull support. This has lower odds of success than No 1.

                                                                                                                                          3. If it’s FOSS and done by volunteers, then odds of pushing a switch is close to nothing. In that case, the route would be to fork it rewriting legacy and recently-added code incrementally.

                                                                                                                              2. 8

                                                                                                                                As a Rustacean, I agree with you and with Bryan. The economics of taking an existing piece of software that works and rewriting it in Rust aren’t good and the second system syndrome might make Rust look like a terrible choice. Rather, we should include Rust in the pool of options when building new software, and not be zealous when it turns out that Python is a better choice.

                                                                                                                              1. 3

                                                                                                                                I loathe extra dependencies, and attempted to use this approach twice. One time on a medium sized database (low double-digit TBs) and one on a small one (under 1TB). On the small one it was relatively successful, eventually replaced but not due to any horrible issue, just ergonomics and feature support. On the large one, it was a fairly tough to puzzle out performance problem, and we ended up going with Sphinx (which I love!) as a replacement with shockingly improved performance.

                                                                                                                                So personally 0/2 on this approach, or possibly 0.5/2? It is a fine place to start, but if you feel you are going to outgrow it fairly quickly might not be worth the stop in the middle.

                                                                                                                                1. 7

                                                                                                                                  It’s, as always, a choice. I do Elasticsearch and SOLR consulting for a living and frequently run into the issue of people using them too soon, too badly. For example, I just had to save an under-performing ES with 1GB of data. You have to work for breaking that. The ES docs being quite poor at the small details and the book being unmaintained since 2.0 does not help.

                                                                                                                                  So, what I like about Postgres is that the feature is there, but it’s quite okay with having a ceiling. It’s not meant to be the big competitor in the search space. It’s built for being an okay implementation where everything that’s there is reliable and fulfilling its promises. The documentation is good and it’s rather easy to get going without much operational overhead.

                                                                                                                                  In contrast to, for example, MySQL and MongoDB, that ship shoddy implementations that lead to frequent and subtle bugs.

                                                                                                                                  In my book, it’s not even the additional dependency, but people miss that every item called “data sync” in an infrastructure should be circled with a red marker, underlined three times with a bigger marker and used as a target for a paintball gun with red bullets.

                                                                                                                                  1. 1

                                                                                                                                    There is no “too soon” in “we need faceted search”.

                                                                                                                                    1. 3

                                                                                                                                      Faceting is overrated IMHO. Not that it’s not important, but I definitely don’t agree that everything needs faceted search (as a database feature).

                                                                                                                                      There’s tons of interfaces based on a limited set of filtered tables (CMSes, apps essentially caring about 5-6 data types, etc). Many of them don’t need facets or a unified result view. In these cases, “faceted search” is easily replaced by “fire off N low-cost queries” if needed. Many don’t even need high-performance search. (they need search as an interface, though!)

                                                                                                                                      Faceted search is important with an unbouded number of categories to facet by. Essentially: Shops.

                                                                                                                                      Interestingly, Elasticsearchs way of dealing with queries as essentially a program also leads to the interesting effect that many of our clients miss the point where they should stop bashing everything in one query and start using multiple.