|
Agrikk posted:What are the implications of using a composite primary key versus creating an autoincrementing column for a PK and then creating an index on the columns? Why would one use one method over the other? The short answer is that composite primary keys are essentially never a good idea. For essentially all practical purposes, a composite primary key has nothing but downsides. I'd actually go even further than that and say that natural primary keys in general are pretty much always a bad idea and are best avoided. The hypothetical upside of a composite primary key is that you save some small amount of data per row because you avoid an extra column and its index. This is quite meaningless though, not least because a composite primary key will add at least one extra column to every row in all other tables that have a foreign key referencing it. In postgres the differences basically stop there, as far as indexing and index usage goes there's really no difference between a primary key and any other unique index AFAIK. However, in certain databases (MSSQL for example), the primary key is typically clustered, meaning that its ordering controls the physical ordering of the table pages on disk, and that the index pages for the clustered index also are the data pages for the entire table, which in turn means that finding a row by its primary key saves you one index read compared to looking it up by another index. In most cases this isn't really meaningful though, and clustering by a composite primary key also has a bunch of caveats to it. There's also the possibility to include non-indexed column data to any index (making it a covering index) if you have some particularly critical use case where you really need to save that one extra read. More generally, in the chaotic environment of human systems natural primary keys have an unsettling tendency to stop being natural primary keys. Primary keys must be guaranteed to be stable and immutable, but people tend to want to change data in databases at some point, so years down the line someone usually comes along and changes the requirements and now the previously natural primary key is an enormous pain you need to migrate away from. The general principle here is that identifiers should not carry information. I've hurfed and durfed about this ITT before so I'll stop there. TheFluff fucked around with this message at 20:19 on Jun 10, 2022 |
#
?
Jun 10, 2022 19:31
|
|
|
|
| # ? May 19, 2024 06:35 |
|
|
TheFluff posted:The general principle here is that identifiers should not carry information. I think this is the easiest point to remember. So as a rule of thumb, good table design should be some kind of row identifier, like an autoincrementing integer, and use secondary indexes for actual performance considerations. Is that correct?
|
|
#
?
Jun 10, 2022 19:56
|
|
|
Agrikk posted:I think this is the easiest point to remember. Yep, you got it, and don't forget that adding unique indexes just for data integrity reasons is usually a pretty good idea too. They can of course be multi-column too if you need it.
|
|
#
?
Jun 10, 2022 20:18
|
|
|
To reduce my own durfhurfing, I'll merely note that composite property keys are to be avoided as reasoned above. Composite ID keys, however, may well be necessary and appropriate. EG, (course INT, student INT) is preferred over an arbitrary course_student_id. (These are the internal integers, not the ID printed on the student's grade report.) imho. (I'm a strong proponent of an integer ID field (called 'table.id', not 'table.table_id'), but use a database that always puts IDs on rows in the pages, so even if a composite ends up in a weird state it can be repaired and the extra ID field is just a waste during joins.)
|
|
#
?
Jun 11, 2022 00:39
|
|
|
anyone migrated on-prem databases to aws rds? got a potential client who wants to migrate their 37 oracle databases to aws. i've never dealt with oracle before or migrated a database to aws. on top of that, they want this done by the end of the year. given all of this, i'm not sure if this is something i want to involve myself with. i feel like there's a lot of uncertainty given their setup and my experience. i've only really messed with sql server. i'd have some help with some of their people, but...still kind of wary. maybe i'm just being too cautious and this is fairly easy? pays really well, so i've got to ask.
|
|
#
?
Jun 15, 2022 04:24
|
|
|
Agrikk posted:What are the implications of using a composite primary key versus creating an autoincrementing column for a PK and then creating an index on the columns? Why would one use one method over the other? My philosophy is always use a surrogate key, for various reasons. Primary keys should never change in a database, and that's still true of composite primary keys, you think you'll never want to change that business key but eventually you will run into some weird use-case that requires you to. At that point you'll have to go through and update all associated foreign-key references to that business key, etc. You have denormalized your data across a ton of tables. Can you do it, yes, but surrogate key doesn't really do anything worse other than a slight performance/memory/size hit from having another index. They're also loving annoying to work with on every level... if you have three values, then each of your tables that reference it will have to have three columns of their own devoted to just that single many-to-one reference. And they're a pain in an ORM because you have to set up these MyClassId(varA,varB,varC) identifiers everywhere you want to look something up, there's nothing wrong with it but it generates a lot more boilerplate to write and to interact with on an ongoing basis. And I don't know how you'd serialize them to (eg) JSON...three fields on the object? a little identifier object like "myId : {varA: x, varB: y, varC: z}"? Having done them both ways, composite primary keys are a thumbs down from me, they're a big pain all around. Absolutely surrogate + unique index for business. Agrikk posted:I think this is the easiest point to remember. Sequence generators work mostly fine if you obey the rules and don't do stupid poo poo. The biggest rule is "do not assign semantic meaning to the sequence value": to make sequences work, you realistically need to use per-connection caching (every connection gets N sequence values at once when it increments the sequence so you don't hit disk every time). Because of this caching it is not chronological, a record with a higher sequence value may have come earlier. You shall not care about gaps or holes (every time you restart the client/drop a connection, you will increment the counter to get resources for that thread, and it doesn't ever roll back) or how fast you burn it (64b is a long-rear end time). If you need a value with semantic meaning that you don't want to have gaps ("item number X") then that is not your primary key, and you have a "gap-less" generator in a table or similar. If you need a chronological ordering, use a timestamp column. Don't write ID allocators into your default constructor because you're too lazy to debug which code flows aren't assigning a primary key and "the value will just get overwritten during the load if it's a stored object" (lol). (ohhhh boy that was a bad one, I originally patched it (cause lolwut spin the DB lock dozens of times per request?) when we ported the application's ORM layer and someone rolled back the patch because hey, these flows aren't populating their uids and it's blowing up when they go to save, and it never got fixed until it basically took down the prod database (oracle) server from resource contention going for the locks. I did warn them a couple times about that one too, but we had caching, so it wasn't that awful.. until it was.) UUIDs have some very nifty properties for the way software is written today and basically aren't any more painful than having an id number. You can have a default UUID (or guid?) generator in postgres on a column if you want, so it behaves exactly identical there if you want to use it as a DB-generated value. Also, the UUID property is basically that two computers can just randomly generate numbers forever and will never hit a collision because the space is so big - meaning you can have clients (your application, or other microservices) generate their own uuids without having to touch the database or even the owning service. You just assign a uuid and send it because hey, it's new every time, that's what uuids are, and the database will (again, assuming a proper implementation of the generator) never have seen that uuid. So you don't have to ask the database to assign you sequence values ever. RDBMS is still a performance-limited system, it's hard to scale well past single-machine designs, clustering just isn't good, so spending cycles inside the DB spinning in an RNG or waiting on locks is wasteful. Being able to push cpu load out of the DB (with DB generated sequences or UUIDs) to scalable commodity systems is a performance win and there's not really a big downside besides larger identifiers (eg 128b vs 64b long). Paul MaudDib fucked around with this message at 06:50 on Jun 15, 2022 |
|
#
?
Jun 15, 2022 05:44
|
|
|
uuids are actually universally unique. it says so on the tin. not even in another galaxy could your id possibly be repeated
|
|
#
?
Jun 15, 2022 06:47
|
|
|
DELETE CASCADE posted:uuids are actually universally unique. it says so on the tin. not even in another galaxy could your id possibly be repeated oops, all the universes were initialized with seed(0)
|
|
#
?
Jun 15, 2022 06:54
|
|
|
Just found out that azure's built-in pgbouncer listens on port 6432 instead of acting as a middleman on port 5432. Three months of mysterious random timeouts solved 
|
|
#
?
Jun 15, 2022 11:07
|
|
|
abelwingnut posted:anyone migrated on-prem databases to aws rds? got a potential client who wants to migrate their 37 oracle databases to aws. I migrated our MSSQL databases from on prem to RDS, was pretty straightforward*. For MSSQL you just make a backup (if the dbs are large break it up) upload to S3 and then do a restore, but for MySQL/Aurora Other than that do your research to make sure RDS is compatible with everything they need. I don't know Oracle, but in MSSQL for example, CLR functions are only supported for MSSQL 2016 and below, and there are some things disabled for security purposes no matter what the version (making calls to an API for example). *migration was simple in the end, but I did a good amount of prep work setting up the VPC and security for the databases and then testing the process of migrating, connectivity, etc. This was a year ago, they probably have a CloudFormation template or some easier method of doing the VPC/security stuff now. Just-In-Timeberlake fucked around with this message at 13:01 on Jun 15, 2022 |
|
#
?
Jun 15, 2022 12:51
|
|
|
abelwingnut posted:anyone migrated on-prem databases to aws rds? got a potential client who wants to migrate their 37 oracle databases to aws. It's very straightforward (posting from memory): 1. On a workstation with access to both source and target database machines install the AWS Schema Conversion Tool. 2. Launch the tool and configure source endpoint (on-prem oracle DB host) and target endpoint (AWS RDS Oracle, RDS Postgres, Aurora Postgres). 3. Select the database/schema/tables/functions/views on the source DB and create a conversion job to add these object to the destination DB 4. run the job (takes a few minutes, depending on the complecity of the source database). 5. Launch the AWS console and go to Database Migration Service 6. Configure your endpoints (specifying IP address and database engine) for source and target 7. create a replication instance. (This is the instance that does the actual work of copying data from source to target. The instance size will determine how long it will take to migrate the data.) 8. Create the migration task. This is the heavy lifting. You'll specify both endpoints and your replication instance, as well as create rules for what you want to migrate, using wildcards and masks to select schemas/tables/etc. This is the part that takes a few tries to get right. 9. Run the task. Once the task is active it will begin replicating data, starting with a full data load and then working on an ongoing basis to keep the data in sync from source to target for as long as you keep the job running. If you keep the job running it will continuously sync data forever, allowing you to verify target data as applicable. 10. Swing the workload from on-prem to AWS RDS while at the same time stopping the replication task.
|
|
#
?
Jun 15, 2022 17:23
|
|
|
that's all super helpful--thanks. yea, i thought they wanted to convert their oracle db to standard aws rds, but not even. what they clarified earlier today is they simply want to move their oracle db to rds oracle. as far as i can tell, i don't think that would require the conversion tool, though that is a pretty cool tool from aws. since learning that, info i've been looking at is here: https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-on-premises-oracle-database-to-amazon-rds-for-oracle.html seems pretty straightfoward.
|
|
#
?
Jun 15, 2022 17:57
|
|
|
Postgres timestamp question: I have a table: code:It is populated by grabbing the elapsed download time of a file from a target to a host every ten minutes and recording the result to a table. It contains the following: code:code:code:Agrikk fucked around with this message at 20:27 on Jun 15, 2022 |
|
#
?
Jun 15, 2022 20:25
|
|
|
not sure what the exactly function is in postgres, but what happens when you use datepart() for each element of the stamp? i've had similar trouble with sql server, and just resorted myself to always using datepart. always works.
|
|
#
?
Jun 15, 2022 20:44
|
|
|
https://www.postgresql.org/docs/current/datatype-datetime.html says the timestamp type has 1 microsecond resolution, but your textual representation only has 1 millisecond resolution. The stored values probably have more precision than gets displayed, so they don't produce an exact match with the copy-pasted textual value.
|
|
#
?
Jun 15, 2022 20:49
|
|
|
It turns out that the value of seconds was actually 40.389993 so yep, that was it. So now the question is either: how do I reveal the actual stored value in a select statement? or How do I store the value at less precision?
|
|
#
?
Jun 15, 2022 22:04
|
|
|
Agrikk posted:It turns out that the value of seconds was actually 40.389993 so yep, that was it. SQL code:Alternately, truncate the stored value to millisecond precision: SQL code:I gotta say though that selecting by exact timestamp is... sort of a weird thing to do, I think? TheFluff fucked around with this message at 23:47 on Jun 15, 2022 |
|
#
?
Jun 15, 2022 23:29
|
|
|
I'm curious what you're actually trying to do. "Get the bottom row" is not exactly a "data query", but of course it can be done with MIN or window functions if that's actually the intended logical operation. If you want "the earliest of the day", that's MIN. Now then, on to the more harsh question. What's the time source for the data? If it's atomic or Unix time it (should be) monotonic, but if it's system time then it can flop backwards during DST changes.  Since you didn't use timestamp with timezone, I'm a tad worried.If your situation is "I just screwed up a single row after using the database for 9mo and just need to repair it", well there's an answer for that that works without flailing around with formatting, but sheesh I haven't needed it in so long...  I had forgotten it was removed and replaced by (well seriously you shouldn't use it except when you've created an exact duplicate row accidentally but if you truly really absolutely must, look up ctid).
|
|
#
?
Jun 16, 2022 00:05
|
|
|
TheFluff posted:I gotta say though that selecting by exact timestamp is... sort of a weird thing to do, I think? PhantomOfTheCopier posted:If your situation is "I just screwed up a single row after using the database for 9mo and just need to repair it", well there's an answer for that that works without flailing around with formatting, but sheesh I haven't needed it in so long... This. The batch job runs every ten minutes to collect the data, and I occasionally run the job manually which collects data at odd times. I've found a couple of timestamps that I'd like to delete and was struggling with the WHERE statement. Now that I know it's the microsecond vs millisecond issue identified upthread I can grab the appropriate entries using DATE_PART and then delete them. PhantomOfTheCopier posted:I'm curious what you're actually trying to do. "Get the bottom row" is not exactly a "data query", but of course it can be done with MIN or window functions if that's actually the intended logical operation. If you want "the earliest of the day", that's MIN. My example had the specific line of data in the bottom row so I referred to it as such. In reality it's about a half dozen lines of non-standard times scattered thoughout a few thousand records. Also: The time source is NOW() run on the server which is set to UTC. I'm not using timezones because I shove all time entries as UTC and just say "all times in UTC" and call it good. 
|
|
#
?
Jun 16, 2022 00:39
|
|
|
I have a really stupid question, hopefully this is the right place. Let's say I have three tables: Users, CarMakes, and UserCarMakes. CarMake just has values like "Honda" and "Toyota" and UserCarMake is just a many-to-many join table between User and CarMake. How do I write a query to return all users that have a Honda AND a Toyota? I know I can do it pretty easily for an OR: code:code:code:
|
|
#
?
Jun 17, 2022 15:07
|
|
|
prom candy posted:
It's fine. You could just use an INNER JOIN and you won't have to check for NULL code:Just-In-Timeberlake fucked around with this message at 15:37 on Jun 17, 2022 |
|
#
?
Jun 17, 2022 15:34
|
|
|
Just-In-Timeberlake posted:It's fine. You could just use an INNER JOIN and you won't have to check for NULL Thanks, it seems like this doesn't actually work though. It does seem to work if I INNER JOIN the UserCarMakes table twice as well but it seems like it should work without having to do that.
|
|
#
?
Jun 17, 2022 16:15
|
|
|
prom candy posted:Thanks, it seems like this doesn't actually work though. It does seem to work if I INNER JOIN the UserCarMakes table twice as well but it seems like it should work without having to do that. ah yeah, that's my mistake, you do need that second UserCarMakes table join.
|
|
#
?
Jun 17, 2022 16:32
|
|
|
Just-In-Timeberlake posted:ah yeah, that's my mistake, you do need that second UserCarMakes table join. Okay, thanks for the help! Also I'll probably be back here on Monday when I can't figure out the best way to find users who DON'T have a Honda.
|
|
#
?
Jun 17, 2022 17:29
|
|
|
prom candy posted:How do I write a query to return all users that have a Honda AND a Toyota? Use an EXISTS in the WHERE clause instead of joining the tables twice. That's also easier to chain when you need to check for three or four makes.
|
|
#
?
Jun 17, 2022 17:39
|
|
|
nielsm posted:Use an EXISTS in the WHERE clause instead of joining the tables twice. That's also easier to chain when you need to check for three or four makes. I don't think I've ever used an EXISTS before (can you tell I hardly ever step out of my ORM?) Do I still join or do I just do it all in a subquery in the WHERE clause?
|
|
#
?
Jun 17, 2022 18:26
|
|
|
Phone posting here so can't write a full example. But join the tables you need to select data from, not those you only need to check conditions from. select * from foo where exists(select 1 from bar join quux on bar.z = quux.z where foo.x = bar.x and quux.v = 'fart')
|
|
#
?
Jun 17, 2022 18:49
|
|
|
prom candy posted:I don't think I've ever used an EXISTS before (can you tell I hardly ever step out of my ORM?) Do I still join or do I just do it all in a subquery in the WHERE clause? can't really dig into your specifics right now, but fyi if you've never used EXISTS then you should definitely consider using "EXISTS (SELECT 1 from subquery)" whenever you reach for the "IN (subquery)" tool in your toolbox... IN is basically optimized for small static values and EXISTS works better with large selects on dynamic data. You would think the planner would be able to optimize them to the same query plan but it doesn't. Generally, a good ORM will support EXISTS in its own language/criteria-builder as well, so you aren't forced to dig down to native SQL if you don't want. https://www.mssqltips.com/sqlservertip/6659/sql-exists-vs-in-vs-join-performance-comparison/ https://www.javatpoint.com/in-vs-exists
|
|
#
?
Jun 17, 2022 21:40
|
|
|
fyi select 1 can thwart some query optimizers, which all special case select * in semi/antijoins to not actually grab anything
|
|
#
?
Jun 17, 2022 23:17
|
|
|
exists() for semi-joins is cool and good and better than in(), which sometimes fucks up your query plan. but where it really shines is the use of not exists() for anti-joins, which is especially cooler and gooder and betterer than not in(), which practically always fucks up your query plan
|
|
#
?
Jun 18, 2022 04:19
|
|
|
Cool I'll dig into this on Monday and see if I can rewrite that query with EXISTS. The anti-join thing is gonna be key as well because i just know that's the next feature request. Thanks all!
|
|
#
?
Jun 18, 2022 06:03
|
|
|
I don't like EXISTS and NOT EXISTS. I prefer to use a join, because it's less complicated.
|
|
#
?
Jun 18, 2022 18:51
|
|
|
Hammerite posted:I don't like EXISTS and NOT EXISTS. I prefer to use a join, because it's less complicated. They don't serve the same purpose. Sure, you can use join to work like exists but it's not the same and the performance isn't the same and if the table you're joining to has multiple results but you're not selecting from that table you'll have to distinct the output of the query. e: assuming you weren't joking
|
|
#
?
Jun 18, 2022 19:10
|
|
|
exists also handles NULL fields better.
|
|
#
?
Jun 18, 2022 19:17
|
|
|
abelwingnut posted:exists also handles NULL fields better. loving NULL
|
|
#
?
Jun 18, 2022 19:56
|
|
|
Thread title
|
|
#
?
Jun 18, 2022 20:46
|
|
|
RandomBlue posted:They don't serve the same purpose. Sure, you can use join to work like exists but it's not the same and the performance isn't the same and if the table you're joining to has multiple results but you're not selecting from that table you'll have to distinct the output of the query. That's true, I think I had in mind a table with a uniqueness constraint on the relevant columns.
|
|
#
?
Jun 18, 2022 21:22
|
|
|
Okay so here's my original query with the multiple joinscode:code:I also wrote this query for finding users that don't own a Kia and it seems to work: code:
|
|
#
?
Jun 20, 2022 14:58
|
|
|
Is there a way in sql server to extract the data in a node of XML that is stored in an ntext column and expose it as a column in a view? Edit. I guess you can , r.XmlCol.value('(/parentnode//somenode//nodeiwantdatatodisplay//node()[1])', 'varchar(max)') from table outer apply (select cast(ntextcol as xml) as realxml) s outer apply s.realxml.nodes('parentnode') as r(XmlCol) Withnail fucked around with this message at 21:19 on Jun 20, 2022 |
|
#
?
Jun 20, 2022 20:44
|
|
|
|
| # ? May 19, 2024 06:35 |
|
|
This exists stuff helped me add a whole bunch of functionality to our app today, thanks again for all the help. I found a rails gem that makes it really easy to integrate into active record queries too.
|
|
#
?
Jun 21, 2022 02:17
|
|