47 ms·
Some SQL Tricks of an Application DBA
- arethuza 6y agoThat gave me flashbacks to using a dreadful ORM years ago that was incredibly bad at doing updates. Combine that with code generated from UML diagrams resulting in tables with hundreds of columns and every time you updated a single field every column got written out.....
- pachico 6y agoORMs should probably be treated as technical debt in the sense that they help you deliver something more quickly but force you to refactor your code once you need scalability/performance.
- giovannibonetti 6y agoI would argue otherwise. The way I see it, technical debt is code that is more complicated than necessary for a feature. On the other hand, a good ORM makes your app code less complicated. You only need to refactor to use raw SQL in the rare edge cases where you need more performance. At least working with RoR I find these cases extremely rare (less than 1% of the cases by my estimate).
- mgkimsal 6y agomy 'go to' recently has been - use the basic ORM if I'm dealing with one (or maybe a small handful) of things where I'm doing work local to the request on those specific items. use just a query builder that returns a raw array for times when it's just being shipped down in JSON to a client. The overhead of full ORM - converting to full objects internally - often for hundreds or thousands of records - is not worth whatever small conveniences might be afforded via a full ORM usage.
- gmfawcett 6y agoSorry to hear about the flashbacks. Having written a couple dreadful ORMs in my day, I feel an odd kind of guilt right now. :) I know it's a new post, but it's funny to see what a large share of this discussion is about your ORM comment. You've touched a nerve, I think! I also wonder if there should be a corollary of Godwin's law -- "as an online discussion about relational databases grows longer, the probability of diverging into a discussion about ORMs approaches 1." :)
- btilly 6y agoNormally I read these and expect to learn nothing applicable. But the UNLOGGED table tip was exactly what I needed for the ETL project that I'm working on. Thanks!
- ZinniaZirconium 6y agoThings I learned while making a database of songs on Jango Radio: 1. Create covering indexes sorted by each column to be searched. 2. Enable sqlite_stat4 3. Use WAL mode. 4. Set a busy_timeout 5. Vacuum and analyze daily. 6. Nobody has heard of Jango.
- anamexis 6y agoSounds like a GIN index might be better suited for the search case.
- ZinniaZirconium 6y agoSQLite doesn't have GIN indexes.
- munk-a 6y agoCovering indexes are a really careful thing to investigate, sometimes you'll get big wins out of it but often times those indexes can easily be misaligned with really minor application changes so it's something I tend to approach in very limited situations. Vacuum & Analyze are two fun ones and I think postgres in particular would do well by investing a bit more into the default configuration of autovacuum since the out of the box settings are pretty conservative compared to the state most out of the box DBs are used - but I think that DB configuration is very much an unsolved problem in general and can be a quite surprising source of major performance gains (or losses!). I mentioned mat views elsewhere - but if you are really hitting quite predictable usage patterns (where covering indexes are stable enough) then some well defined mat views might go a long way to tuning performance that little bit more - just be aware of the fact that rematerialization isn't instantaneous and if you have a high rate of change in the data then you'll probably want time based re-materialization instead of trigger based which means you'll need to account for some inconsistent data (as is the case with caching in general).
- ZinniaZirconium 6y agoI wish there were different recommended configurations for SQLite when used as the backend of a web site versus the storage format for an embedded system. The defaults are really not suitable for concurrency or for tables with millions of rows. Also I wish I didn't have to recompile to enable sqlite_stat4 histograms. SQLite doesn't have materialized views.
- devmunchies 6y agoI was expecting a punch line on that comic strip. nothin. Regarding the "Always Load Sorted Data" section, it says to insert the data into the table so that the column you want to select on is already sorted, thus having a correlation of 1. What if I'm adding a single record to the database, how do you re-sort them all upon insert?
- deleted 6y ago[deleted]
- gmfawcett 6y agoBy "load" I'm pretty sure they mean "insert initial data into an empty table." So a different use case. In ETL work, for example, it's not uncommon to truncate and reload source and staging tables on each batch run. So the tip would be helpful in those scenarios, more than in an OLTP case (insert this record, update that one).
- say_it_as_it_is 6y agoThe role "Application DBA" doesn't seem right. Is this common?
- andyjenn 6y agoI've not heard it before. I was an Application"s" DBA for several years, but this was specifically for Oracle's ERP suite of business applications. The role mixed straight DBA responsibilities with middleware and business administration.
- munk-a 6y agoIt's a bit of a strange one - often times this sort of person is a back-end specialist and maybe part of the data architecting team depending on the size of the company. I'd not heard this title before and it is misleading to me since I strongly associate DBA with "has nothing to do with application code" so it's a bit of a contradiction to me personally.
- Izkata 6y agoI haven't heard it by name, but the description is something I've heard of before - usually when app devs are complaining about only being able to query/update the database through provided views/stored procedures, so it becomes a pain to support new features. I remember one story in particular where the app devs discovered one of the stored procedures could be manipulated to run arbitrary SQL, so they started using that instead of talking to the DBA...
- mgkimsal 6y agowar story: worked in at least 2 different shops with the same restriction: "developers can never touch production database". actually, it's been that way in most places large enough to have enough staff to separate things out. however, in most of those places, a dev or two would still have access to use, whether for emergencies or debugging or whatnot. In 2 places, there was an enforced rule, where the DBA (in both cases, one and only one person) had the sole password/credentials for production. "Devs can't ever be on production, they could write random SQL - everything has to be tested". The person who said this would routinely write his own sprocs on the DB - on production only - without tests. he acted as a support person for the owners - he'd just write sprocs and custom query on production only for any reports or data updates owners and upper mgt wanted. But... those sprocs were never documented, tested, or pushed back to dev, so we never knew what was running there. If we asked for a new table, or new index, or whatever, that might cause some trouble for his hidden/untested code, we'd get blanket denials. "that causes a performance bottleneck - won't do". The "complain about the DBAs" issue is real, but is just symptomatic of bad culture all around. It's just as bad to have any cowboy running rampant without any visibility in to what they're doing. But sometimes (often?) DBA folks seem to escape this scrutiny (perhaps just at smaller shops?)
- forinti 6y agoFirst trick shows that update and delete should have a special syntax for when you really mean all rows: UPDATE users SET email = lower(email) This should return an error: "do you mean all rows?" UPDATE users all rows SET email = lower(email) Or something like that.
- kogus 6y agoI've often thought the WHERE clause should be required. You could just say WHERE 1=1 if you really meant all rows.
- Izkata 6y agoThis is a settable option in mysql: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_safe_updates https://dev.mysql.com/doc/refman/8.0/en/server-system-variab...
- pmiller2 6y agoThat option is slightly less convenient than just requiring a WHERE or LIMIT clause, because it specifies that the WHERE clause has to have a key constraint. You can still do approximately the same as the 'WHERE 1=1' trick with something like this: UPDATE users SET email = lower(email) LIMIT COUNT(email) if you want. I kind of like that, because it requires you to be more explicit than just putting 'WHERE 1=1'. It seems like mistakes would be fewer, too, because putting LIMIT COUNT(email) isn't just some default boilerplate you can get used to typing.
- uhoh-itsmaciek 6y agoDoesn't that skip counting null emails (though not filtering them for lowercasing), meaning you may not lowercase all emails if you have any null emails?
- NicoJuicy 6y ago
- munk-a 6y agoAll of those tips and no mention of MATERIALIZED VIEWS[1] it's a pretty wonderful tool to use for data caching (potentially highly safe data if you use on action trigger based refreshing) that can allow you to have a nice normalized data layout to write into and a beautiful de-normalized cross-table index supporting place to read from. If you've never heard of mat views please do look them up[2] and play around a bit. 1. As they're called in postgres at least 2. https://www.postgresql.org/docs/current/sql-creatematerializedview.html https://www.postgresql.org/docs/current/sql-creatematerializ...
- gerhardi 6y agoMaterialized views are also nice for many modern DW applications where you don't live in legacy "once in a hour/day/week" ELT/ETL environment, but more in a real timeish or micro batch world. You can have data marts / reporting tables composed with these materialized views over many kinds of underlying DW models. Just dropping this to promote the Materialized View functionality :)
- chishaku 6y agodbt (data build tool) is awesome for managing this.
- aargh_aargh 6y agohttps://www.getdbt.com/ https://www.getdbt.com/
- djrobstep 6y agoI've found them to be pretty limited as a feature - really they are just some syntactical sugar over "create table as select ... ". If you start to need any kind of flexibility over refreshing the table, you'll need to move to using a plain old table anyway.
- asah 6y agoThis was my impression too - without incremental update, I don't see the benefit ?
- info781 6y agoUsually this job title is development DBA or PL/SQL developer.
- gav 6y agoI'm not sure if it's just me, but when I see DELETEs that use CTEs like that I start to get nervous. It's easy to get wrong and it's hard to undo. I like to have an intermediary table where I can verify what's going to be deleted (or updated) before it runs. Even better it lets you save the mappings of old ids so when you find out some downstream system was using them you can fix that too. I know it's just an example but soft deletes solve a huge range of problems.
- gmfawcett 6y agoRule of thumb for me is that every DELETE or UPDATE query should start life as a SELECT query. The eight stages of DELETE: - SELECT what you're deleting; - fix the mistake in the query (there's always one); - SELECT again; - BEGIN TRANSACTION; - DELETE; - SELECT to confirm the right deletion -- twice for good measure, and maybe ROLLBACK once or twice if you're feeling twitchy; - Hover finger anxiously over the un-entered COMMIT command for several seconds while resisting Dunning–Kruger effect; - COMMIT. :)
- gav 6y agoI do the same thing. However, it won't save you if realize that there's an issue after you commit. Maybe I'm just paranoid but I like to have both a snapshot before a major change, plus I'm fond of having a spare (via delayed replication or log shipping).
- gmfawcett 6y agoAh, I forgot to include the twelve stages of proactive, redundant backups. :) I usually do the same thing -- local snapshots if the database is small and the app is less critical... or a careful review of the central db backup history and a documented rollback plan if it's a bigger system.
- benjohnson 6y agoHere's my insanity Give the table an DeletedTimestamp and DeleatedReason column. Mark the records as deleted. Run around like a lunatic and fix queries so they don't show "deleted" columns to the rest of the app. Make a page in the application where people can "restore" deleted items. Because they do stupid things too once in a while.
- simonw 6y agoThis post was great - I've been using PostgreSQL for different projects for fifteen years and there were a bunch of neat tricks in here that I haven't seen before. Thanks very much.
- onnnon 6y agoWell written and informative, thank you for sharing.
- ak39 6y agoPlease add to this otherwise excellent list: LATERAL JOINs (PGSQL). In SQL Server & Oracle they are CROSS APPLY & OUTER APPLY. This will change your SQL habits forever. Lateral joins give you the power of iteration in set-based operations that are mind-numbingly easy to implement and understand.
- rrrrrrrrrrrryan 6y agoI think this is really only helpful in terms of readability if you come from a world where iteration is the norm, and really hurts readability for most database folks where set-based operations are the norm.
- sbuttgereit 6y agoLong time database person here (~20yrs), probably very much akin to the "Application DBA" in the article. The solve some classes of performance related issues, especially if you're joining to certain kinds of group-by sub-queries or need to join on the output of a function. If your surrounding query is defining the constraints, you can push that into otherwise unconstrained sub-queries. There was a minor learning curve, it took me a couple hours for it to be worked into my default mindset. In the end, wonderful technique that can save dropping into procedural code in any number of cases. The biggest danger is that it is a kind of iteration and where the performance can be greatly enhanced in say OLTP style queries, but it may not be a net gain in larger unconstrained queries that will cause that sub-query or function to get hit many times... I think getting that into your head is the larger part and the declarative nature of SQL will cause that to be a little outside of your vision no matter your background.
- ravoori 6y agoNot really - this fine SO answer has many examples of CROSS/OUTER APPLY elegantly solving problems that would otherwise have been inconvenient to deal with - https://stackoverflow.com/a/9275865/753731 https://stackoverflow.com/a/9275865/753731
- _bohm 6y agoThis is life-changing. Thank you so much.
- Izkata 6y agoHot damn, looks like CLUSTER is exactly what I need to handle a pending performance problem! I'd searched for this before, but not knowing the keywords just ended up with people saying "add an index", which was not useful: We have a table of denormalized data that's used as one of the inputs to an ETL system, and it has to be sorted by one of the columns in the table. This is the table's only purpose. Unfortunately, postgres refuses to use the index because random-access across the entire table is slower than loading and sorting it all, when retrieving all data in the table. This means the ETL system has to wait for about 15 minutes before any data is returned. Some preliminary tests on a copy of the database indicate that, using CLUSTER to order the data, it will do an index scan instead of a the table scan+sort, and start returning data immediately. As long as the correlation remains high enough with regular updates, I'm thinking maybe this can be a scheduled once-a-week operation...
- durkie 6y agoHighly recommended to check out the pg_repack extension for this situation. CLUSTER requires an access exclusive lock, preventing anything from reading or writing the table during the cluster operation. pg_repack works around this by creating new tables with your data, clustering those, recording/replaying all of the transactions that happened on the original tables during that time, and then renaming everything (something along those lines). Very noticeable speed improvement and reduction in read loads when I did it last.
- Izkata 6y agoIn my tests earlier, CLUSTER only took about 5 minutes (8 if you include creating the index (which would already exist) and an ANALYZE afterwards), and the only things inserting into this table are background celery processes. It would also be offset from when ETL runs by half a day, so I don't think there'll be any performance problems. My goal for now is minimal effort to set up, so it takes minimal maintenance later on, but I'll keep pg_repack in mind in case something weird does happen.
- ca_parody 6y agoI would also add, for data-science esc tasks (or bulk queries in general) - COPY {} TO is unreasonably fast - often much faster than executing the standard select (especially if that select is being executed by a driver in a slower language). _ [0] https://www.postgresql.org/docs/9.2/sql-copy.html https://www.postgresql.org/docs/9.2/sql-copy.html
- haki 6y agoSee this https://hakibenita.com/fast-load-data-python-postgresql https://hakibenita.com/fast-load-data-python-postgresql
- plasma 6y agoA few other tips: 1) A reminder that when you have a foreign key, such as Order table has a UserId column, and UserId has a FK to User -> Id table, there is an implicit lookup that will happen on the Order table if you attempt to delete a User, to validate its deletion wont violate the Order UserId FK. For example: DELETE FROM User WHERE Id = 1 This will require the database to first check that won't violate a FK on Order.UserId: -- Implicitly (something like this) is done to validate UserId = 1 is not in use on Order.UserId that has a FK to User. SELECT Count(*) FROM Order WHERE UserId = 1 This can lead to poor update/delete performance on the User table (if the row was deleted or the User.Id was ever changed-- a convoluted example), unless you remember to place an index on Order.UserId column to help with FK enforcement. 2) If you use SQL Server, and you have a large table, and a few times a month for some reason queries that were working fine yesterday suddenly timeout, but then start to run quickly again for a while, its probably because of SQL Server suddenly deciding to recalculate statistics for its optimizer, and the query that is timing out, is the lucky query chosen to require the recalculation to happen first. The recalculation usually involves SQL Server scanning the table before proceeding ahead with your query, and if the query times out, its re-computation of stats is rolled back (so it keeps failing, until it eventually gets to complete the query). A workaround that has worked for us is to enable asynchronous statistics, so that the re-computation won't block the query that happened to trigger the re-computation in the first place: https://www.mssqltips.com/sqlservertip/2904/sql-servers-auto-update-statistics-async-option/ https://www.mssqltips.com/sqlservertip/2904/sql-servers-auto...
- kmoppel 6y agoNice list, but maybe worth mentioning that the slightly dangerous "invisible" index suggestion can in many cases be avoided with planner hints if the query-under-tuning only touches one index. So a much better approach would be smth like: SET enable_indexscan TO off; SET enable_indexonlyscan TO off;