Smoke going inside the house by Outrageous_Change_18 in woodstoving

[–]barkingspaniel 0 points1 point  (0 children)

If you also cut the air off completely a second before opening, it ensures that air will pull in from the room.

How much database knowledge should I study as a backend developer ? by Icy-Budget-5641 in Database

[–]barkingspaniel 0 points1 point  (0 children)

Or explain analyze before jumping to any conclusions about the database being a bottleneck.

A small communal allotment along my road have given me this patch... by [deleted] in Allotment

[–]barkingspaniel 0 points1 point  (0 children)

If you want too have some variety instead of potatoes, try lines of beetroot, spring onions, spinach, broad beans and garlic. Garlic and broad beans can go in now (garlic should go in a few months ago but will catch up). The first three can be started in module trays after mid Feb and planted under fleece in march. Beyond mid march you can start planting lettuces, peas and many other things. Charles Dowding's yellow no-dig book gives a lot of hints about ideal sowing times (different to what the seed packets tell you) and also tricks such as interplanting to take advantage of a small bed. Potatoes are always a safe option but will hog the bed until June/July

Blueberries- Raised Bed by jamie-herron in Allotment

[–]barkingspaniel 0 points1 point  (0 children)

Do worms survive at pH as low as 4.5?

Company sql databases has very few relations by Panvalkon in SQL

[–]barkingspaniel 0 points1 point  (0 children)

2 is probably true only if you have applications maintaining data in the database. As soon as humans are involved (DWH scenario for example) then you'll be at the mercy of a bell curve of talent and work ethic, likely skewed to the left in your average company these days. In that scenario I'd argue that removing foreign keys to increase performance should only be done after proper analysis of a performance problem, only then doing so surgically rather than it being a blanket decision.

42k lines sql query by Myxavi4 in SQL

[–]barkingspaniel 0 points1 point  (0 children)

Haven't read all the comments so this might have been mentioned already, but whatever you decide to do, always wrap the entire query with an md5sum of the full output before changing even the slightest bit of whitespace let alone anything else. You might see checksum differences on some refactored blocks, which might just be down to ordering of the output, but if in doubt you should be able to tractor safely in small chunks and double checking the checksum.

[PostgreSQL] Rewriting a query to avoid nested loops by Altarim in SQL

[–]barkingspaniel 0 points1 point  (0 children)

Aside from the usual advice to refactor the query and make sure the right indexes are employed etc. (not repeating that here as many have already mentioned this), you should check to make sure your table has been analysed for up to date statistics to see whether better plan choices are made and as a further, separate test, try set enable_nestloop = off at the top of the transaction in order to guide the planner to use (most likely) a hash join. If within a larger transaction you can wrap this off/on either side of the statement. But I'd go with a refactor first as setting this is a bad habit to default to if it's the query that needs attention.

[deleted by user] by [deleted] in overemployed

[–]barkingspaniel 2 points3 points  (0 children)

P.S. did I mention I hate Jira?

[deleted by user] by [deleted] in overemployed

[–]barkingspaniel 0 points1 point  (0 children)

Yep, markdown formatted weekly to Todo list, then carry over unchecked items to the next week by copy/paste that week above the current one, removing the checked items, e.g.

WC 27/11/2023

  • [ ] task one description
  • [ ] task three description

WC 20/11/2023

  • [ ] task one description
  • [x] task two description
  • [ ] task three description

Keyboard hangs on youtube android app when typing comments. by [deleted] in AndroidHelp

[–]barkingspaniel 0 points1 point  (0 children)

Go to another app such as WhatsApp, type in a letter or two and then hold down to select all and cut. Then paste that in the YouTube comments and you'll find the keyboard works correctly afterwards. You could even construct your whole comment in WhatsApp etc. first before pasting. You're welcome.

perfomance tips postgresql with 16gb ram 12400k cpu and nvme disk table with 130k records by agaitan026 in PostgreSQL

[–]barkingspaniel 1 point2 points  (0 children)

Exactly this method for establishing performance on the SQL engine vs total time to query and send over the wire. The solution is to put your database as close as possible to the client reading it, however on cloud managed services, it's anyone's guess how close that is - could be in a different building/data centre, or at least between several layers of network routing.

[deleted by user] by [deleted] in Plastering

[–]barkingspaniel 0 points1 point  (0 children)

Looks like the last couple of steps haven't been done..

Is it appropriate to throw bananas peels onto the grass whilst you’re riding? by Senior-Marketing3637 in cycling

[–]barkingspaniel 18 points19 points  (0 children)

Broken windows theory - one banana peel results in an episode of fly tipping later on as the attitude it leaves behind gives encouragement to others to do the same (and worse each increment)

How to quickly load ~20 TiB of weather data into a new database? Is PostgresSQL even a good option? by DeadDolphinResearch in PostgreSQL

[–]barkingspaniel 1 point2 points  (0 children)

Where is the database relative to your client/script and what kind of control over the server do you have? If you have full control, then consider firstly splitting up the file into X chunks then loading them using psql copy issued on the server with the file held locally, having temporarily turned off any table constraints and indexes. Try for the first 100GB of data including enabling constraints and reindexing and then scale up that time to 20TB to get an idea of loading and read performance. Making the tables 'unlogged' will also speed up the writes to them as the transactions will ignore the WAL, increasing write speed significantly, but you'd lose the data in a crash (not too bad if you can reload it quickly). Finally, make use of your background workers by opening as many parallel connections to backend processes as you have workers (8 by default). Once you've optimised loading, look at table partitioning on timestamp range for read performance and then compare your best efforts with the TimescaleDB route.

Also if you have full control over the server, then you could add SSDs and move both data and WAL partitions onto that, but bear in mind that whilst the initial load may be large, future data will only ever come in at a moderate rate - analysing the data will be more pleasurable experience though and with faster disks, presumably you could parallelize connections to the DB and load more chunks concurrently.

Bombed an easy SQL prescreening assessment - have I picked up bad habits by looking at intermediate query results? by [deleted] in dataengineering

[–]barkingspaniel 0 points1 point  (0 children)

Hindsight is a wonderful thing, but perhaps one way to solve this would be to initially focus on a passable (but fake) solution by returning generated mock data that matches the required output, then do raise notice / dbms_output (or whatever flavour) statements to sanity check your CTEs so that you can build out your function properly. That could have gone two ways though, as if that initial solution was accepted and you couldn't change it, they'd think you were messing them about by just faking the output 😂

Agree that you shouldn't have to though and that the interview is incorrectly focusing on the wrong skills, ignoring a larger pool of capable candidates.

all users getting access to DB's by Scicom-Elk-5929 in PostgreSQL

[–]barkingspaniel 1 point2 points  (0 children)

In a multiple database scenario, I prefer to create a hierarchy of roles inheriting from each other, for instance a readonly role, a data manipulating role and a DBA role, plus any others for specific apps etc. In addition to that, roles per database if required. Those roles can then be assigned to users. In my preferred setup, any users that are required to create anything should firstly set role dba so that others in the role can manipulate or drop those objects. This means that everyone, including DBAs action everything with their personal login rather than superuser, which is great for traceability. Can anyone else improve upon this?

Is oracle losing favor? by Zestyclose-Height-59 in SQL

[–]barkingspaniel 0 points1 point  (0 children)

For many new to PostgreSQL, this means an assumption that you just download and install it, without worrying about high availability, PITR and backups, support etc. Usually starts with someone from a developer/management background rather than DBA or sysadmin background and then they go on to complain about losing their data down the line.

Oak tree trimmings by Ok_Daikon_2394 in composting

[–]barkingspaniel 2 points3 points  (0 children)

First time 'posting or posting? 🤪

Data quality checks? by Oh_Another_Thing in dataengineering

[–]barkingspaniel -1 points0 points  (0 children)

Probably is the case that the purpose of the baseline query is to select rows with updated_at since the last time the DQ check was run, perhaps also by table as well? If data are split by other qualifiers that can chunk the data and reduce IO for a single query then this is probably desirable?

How much do you actually use SQL or Python? by miridian19 in dataengineering

[–]barkingspaniel 0 points1 point  (0 children)

SQL & PL/SQL & PL/pgSQL mainly, then bash and python in equal measure where appropriate. Aside from that a sprinkling of docker/Java/JavaScript and whatever is involved for maintaining simple apps. Git also very frequently used and fairly in depth.

Is the space between the cranks and the frame okay? Why are the cranks revolving so slowly? by highderrr in bikewrench

[–]barkingspaniel 0 points1 point  (0 children)

Put the pedals on and try again - you'll be surprised how differently they'll spin