@sp_BlitzErik both!

a friend tells me there are 25k tables

3000 words later: orderbyselectnull.com/2017/11/28/containment-for-the-common-man

@EvanCarroll We do fun things like write foreign data wrappers for things like Kafka, and I am currently writing a distributed transaction coordinator that plugs into the back end for managing data copied between pg versions.

@dezso In Berlin.

So a lot of things require both a knowledge of C and an understanding of Pg internals and in particular memory management.

Morning or evening :-)

@ChrisTravers up early

yeah up early :-)

:-) Must be just one of those days. Got up at 5 and sitting in office now.

@deszo, I think our office is a block from your office, if you are at the Prenzlauerberg Allee office.

(we are at the Backfabriken)

You work with Oleksii?

@Evan

@EvanCarroll, we also write our own data types in C but most of those are simple except for istore.

@ChrisTravers Didn't know iStore was actually a custom data type in Postgres ;)

Morning

O.k. hands up: Who created the homework tag?

@hot2use dba.stackexchange.com/review/suggested-edits/98316

Do we need a homework tag? Isn't that a precursor to VtC as too localized/too basic?

evening and morning

Found an interesting repository on Github: BurntSushi/erd

> Translates a plain text description of a relational database schema to a graphical entity-relationship diagram.

example pdf

@ypercubeᵀᴹ Hmm. Read How do I ask and answer homework questions? (Meta.SO) and Is DBA a place for homework questions? (DBA.meta). Agree, but do we need the tag?

@AndriyM We wrote istore as an integer equivalent to hstore with arithmetic support.

morning

@ChrisTravers yeah, with Oleksii. We moved to another office, though. What you say sounds interesting otherwise, too bad I have no idea about C.

@hot2use we don't, and no, it's not, respectively

@dezso we are looking to fill two positions. One doesn't need C knowledge to my knowledge actually.

any good question is a good question, even if it is homework. On the other hand, the tag is really useless (and we (well, we) cleaned it up once.

@ypercubeᵀᴹ aha. The user profile still says Jakarta :D

@dezso Agree on the questions being good if they are asked in a professional manner, and thanks that you cleaned up once.

spooky repeating patterns: knowledge is knowledge, good is good, create index to create index

@dezso I need to fix that

Fixed

And by the way our data environment is 400TB for our primary analytics environment in PostgreSQL.

@ChrisTravers I knew it would turn out to be something as mundane and boring as that. The real-world iStore as a Postgres datatype would seem much more fun :) Oh well, can't have it all

@AndriyM Just remember how it is to name things. If I have a function called fmterror, is that fmt error? or fm terror?

@ChrisTravers The former is more likely, of course, but even in that case I guess nothing prevents you from amusing yourself by imagining the latter :)

@AndriyM If it segfaults does it suddenly become the latter?

Ha, indeed, so you don't even need to imagine things

@dezso by the way, programming in C is actually fairly simple: 1. you think about your data structures. 2. You think about your memory allocation. 3. you test/debug your code. The steps I have skipped over are largely insignificant in terms of time and effort.

(I find writing C code takes less time than writing Python or Perl code, but mostly because thinking about C code takes more time.)

@ChrisTravers "the sooner you start coding, the later you'll finish"

community.spiceworks.com/topic/… others

Yeah. I wrote a two phase commit module for one of our Pg extensions in C in basically a day (200 lines of C code). It took me about 2 hours to debug and fix it the next day and most of that was makefile framework stuff.

Next I am writing a background worker to handle what happens if the remote connections cannot be cleanly resolved during transaction commit. I will probably do that this week. But not rushing it.

I don't get to code today, which may be a good thing for this project.

> There is no such thing as scope creep, only scope gallop

> The nice thing about not planning is that failure comes as a complete surprise rather than being preceded by a period of worry and depression.

Hilarious.

@gbn Made my day

@hot2use we can all relate to most points there

(graphical version for windows users)

@hot2use If only we managed scope of requirements the same way we manage scope of variables.

@ChrisTravers We would have global variables everywhere?

@TomV Or we would have well managed expectations. One or the two.

*of the two

@ChrisTravers True, but you don't see a lot of "project managers | stakeholders" wanting to do proper requirements engineering

@hot2use "We'll do agile"

@hot2use The big banks in Zurich have Requirement Engineers for that very purpose. Especially in private/retail banking

Hmmm. That's why I have to install 200+ applications a week because they have "improved performance".

This whole App business is way too agile IMHO.

Crap in = Crap out

@hot2use what kind of firm do you work for? 200 per week?

FIFO = Fast In Fast Out

Fit In or Fack off

Oh I'm generalising. It's when I open up my iPhone or iPad to download updates.

Therein is your problem. An iThingy

Just gets on my nipples

Android is the same.

@TomV Ah, yes - the 'We don't need to do requirements' school of Agile project management.

The new Review top bar icon looks much better than the previous one.

@AndriyM how did you notice the change so fast?

and yes, quite better

@Lamak still should show the queues on hover, not on click

@Lamak If it was very recent, then I guess I was lucky to refresh the page at the right moment.

@gbn A little risk management saves a lot of fan cleaning.

@dezso you think so?

I don't really like auto expanding things on hover

Is there a way to use a variable with BULK?

I get a Syntax error

dynamic SQL

@Lamak thanks

dynamic Lamak

thanks

dynamic thanks?

SQL

or Lamak

yeah, exactly

I wonder if this is part of where the (now obsolete) 'page life expectancy should be >300 seconds' guideline for SQL Server started

i think it came from an old internals book

2005 or 2008 maybe

cya

@sp_BlitzErik I think I remember hearing it before 2005, but don't remember for sure.

Some of the Wikipedia content about data modeling is really bad

"These databases are more scalable and easy to query with as they do not involve expensive operations like JOIN."

maybe, but it persisted until... well... i suppose it still does

@sp_BlitzErik you can still remember those years?, thankfully the booze hasn't completely destroyed it

@Lamak i can only remember things about sql

that's....sad

memory optimized

my earliest memory is the slammer worm

is that from jail or from tequila?

good question

both?

both and this

yes. i remember that well

what well?

the sql slammer worm

ah, yeah....I mean...

speak clearly and directly. i'm old (just turned 40)

happy halfway to death

depends on if my liver has a say.

lots of people in this chat don't drink, we should start harvesting them

lots?

great idea.

a plethora, even

@Lamak you're not getting my donor list

well, I don't drink

but I'm taking myself out of your list for that comment

no backsies

was there a givesy?

it was implied. TOS of The Heap

"there may someday be a takesy of whichever organ another member of the heap deems necessary"

I always forget about that

thankfully i have nothing to worry about because all my innards are useless

i need a good and interesting data set. i need a large one that would be "batchy" and i need another that would be more transactional. the stackexchange set is not quite what i'm looking for

there are a bunch here

opendata.cityofnewyork.us

the turnstile data might be okay for your batchy one

also this: data.gov

confirmed that JOINs are slow and that you shouldn't do them

thanks @sp_BlitzErik

I got a really dumb question on foreign keys

just looking for confirmation on the answer

go on

@TomV But Agile only works when you are starting on a solid foundation.

@sp_BlitzErik so I got my fact table

with a PersonKey column or something

that points to PersonDim

and I define a foreign key

I need an index on PersonKey on PersonDim

that seems reasonable

glad you think so

but an index on the fact table has nothing to do with the foreign key, right?

unless I wanted to delete rows from the dimension table?

what do you mean 'has nothing to do with'?

start with "not required"

move to "is it a good idea"

or rather, what do normal people do?

well, personkey in your fact table would have to be a candidate key column in a pk or unique constraint anyway, no?

or do i have your relationship backwards?

I'm too dumb to understand that

I require that every PersonKey value in the fact table have a matching row in PersonDim

but not the other way around

correct

so an index on the fact table wouldn't be required?

do me a favor? pastebin your script to set this up.

writing the script was going to be the next step

you don't need to populate the tables

this feels like a trap

valid

oh

I didn't think about the CASCADE stuff at all

careful with them there cascades

I don't need any cascading

dandy

@sp_BlitzErik pastebin.com/UkhpKngx

I think that's correct

@JoeObbish are you only processing and/or reporting on that design or are you looking for incremental loads/updates /deletes in your dwh?

I would like to have the foreign keys exist during ETL

dimperson is an outlandishly good table name for you

Or aren't the fact and dim used as the basis of a cube?

@JoeObbish too broad

no cubes

cubes are the worst

Why? Indexes are often disabled during load

@TomV will do incremental updates

on a cci?

madman

@sp_BlitzErik don't get me started

@TomV elaborate on "often"?

didn't MS make foreign key stuff way more efficient in 2016?

When bulk loading foreign keys are often disabled

yeh when you use bulk insert you have to tell it to use triggers iirc

wait no

now i have to look

Depends on the order you are loading and things you are updating obviously hence the question 'why'

not using bulk insert on fact tables

the dims get updated first

nothing is "obvious" here, I'm really dim on this subject

i dunno why i thought about triggers when we're talking about FKs

oh well

@sp_BlitzErik the tables are in the same database

anyway, this all sounds bad, and you should hire someone to straighten you people out

you can see that in the pastebin you asked for

@sp_BlitzErik what is "this"?

updating the dims first is bad? why?

i don't care about that part

are you building suspense?

@JoeObbish hire a professional

that's not up to me

@JoeObbish exactly

I try to keep things like this very simple

no, i'm working and feeling smug about my new monitor setup

I figure that since I've answered 250 questions here that I'm entitled to the occasional dumb question

if you don't want to help, fine, but don't activity non-help

sounds like a good ratio

i'm not sure what your question is anymore

@sp_BlitzErik he doesn't need an index on that fact table

if you don't know what the question is then it's even more ridiculous to suggest hiring a professional

no, you don't need it, but doesn't that help with join elimination?

from the last time i messed with it i seem to recall that the constraint on a non-nullable column wasn't quite enough

but with an index where the fk was the leading key i got join elimination

it should, but I thought the question was about needing not needing

well, need for what?

shrug

@sp_BlitzErik I read that question as "need it" to make the whole foreign key work

@JoeObbish I probably wouldn't create the index on the fact table unless you need it to delete stuff from the fact table ie when loading incremental

@Lamak yeah no, you don't need an index just to have a foreign key, but you may need it for additional optimizer goodies

But my experience in that setup is mostly cube related not for direct queries

well

the implementation of this sucks

it's not optimized for CCIs

"this" meaning making sure that the constraint is valid when inserting data into the fact table

But surely you are smart enough to understand what the possible impact (both positive and negative) could be

@JoeObbish ah, yeah, that's true

that sucks

@TomV On this? no

@JoeObbish unless the source system already validated that and you could be fairly sure creating the key after loading won't fail

on this I'm in a bubble, so I'm trying to break out of that bubble

if I need to do a bunch of reading instead that's completely reasonable feedback

only a mother could love that plan

I still don't see what the end result should be

that sort >_>

@TomV end result of what?

You have a fact and some dims

And you're going to run ad hoc queries then relying on CCI?

yes

oh I think I need that WITH CHECK CHECK CHECK thing

did sp_blitz warn you about untrusted foreign keys?

:D

no, but I created the constraint in 0 s

Exactly. That's what I meant when saying 'if you can be reasonably confident it won't fail'

query optimizer won't use untrusted FKs, right?

correctamundo

got me a real keyboard

@JoeObbish exactly

@JoeObbish what is your main concern? query time or loading time?

it would be like create maybe kinda unique constaint uq_u

@TomV my concern is that we removed all foreign keys

now we no longer have any

I'm seeing some really unfortunate things happen to query plans

@JoeObbish Ah I see

in an existing solution?

yeah

ok, so the foreign key is marked as trusted

it took 0 seconds to do so

@JackDouglas How have you been?

that's, uh, surprising

@JoeObbish when did you remove the fks?

@ChrisTravers Good thanks — you've moved to Europe, is that right?

@Lamak it wasn't me personally

were there any modifications between when you disabled and enabled?

a year ago?

wonder if it looks at rowmodctr

@JoeObbish it was a "you guys" you

informatioin sips through

@sp_BlitzErik I created the table, added the constraint, then did the check constraint thing

@JackDouglas Yeah. Doing stuff with big databases. First in life sciences (12 TB) and now in ad-tech (herds of 20TB Pg dis)

*dbs

That doesn't sound like LedgerSMB, is this a new job too?

20tb. i've got that much ram in my light up sneakers.

@JackDouglas yeah. but still doing LedgerSMB stuff.

@JoeObbish Can you still ask that guy why he removed them?

@TomV no

@sp_BlitzErik It is fun :-D

no longer works here

also don't trust him

that query plan is atrocious

total data environment of around 400TB for analytics and transient log analysis of about 1.5PB

@JoeObbish well...there seems to be good reason for that

there are only 28k distinct values for the foreign key

just build a hash table

@Lamak Ya load times

(high enough velocity we migrated from ElasticSearch to PostgreSQL)

if MS made this better I'd hate to see how it works before

@JoeObbish the plan was without the fk, right?

@Lamak the insert one is with the FK

@TomV I can't believe how right you are

@JoeObbish this plan?

@Lamak yes

yikes

how big is the fact table?

@tomV not as in you aren't a smart guy

but the difference is 2.5 minutes versus 1 s

for 120 M rows

1 s to check a constraint on a 120 M row CCI table

@JoeObbish That's why I asked what you cared most about

@Lamak So, sorting is 93% of your work

And if you can be reasonably sure it's valid anyway (so you can just add it post-load without bombing out) I would maybe go down that route

I just can't believe that it takes a second

well I can, but I can't believe the difference

Could it be, but I'm no CCI expert, maybe it compresses after each second instead of just validating the compressed end result?

good lord why would it choose a merge join

@sp_BlitzErik yeah that's what I was wondering

there are all kinds of problems

serial, row mode, merge join, sort

this seems like an interesting read:

blogs.msdn.microsoft.com/sqlcat/2016/07/21/…

don't know if it helps much though

I think I can figure out the one second thing

what happens if you hint a hash join? does it error?

so this query finishes in under a second:

and something like that is all that's needed to validate the constraint

so there you go

But that probably gets executed on every insert when the fk is enabled when loading?

@TomV well no, I'm saying that you'd disable when loading

you'd be crazy not do, unless you needed the FK as part of your ETL process

hence the "I can't believe how right you are"

@Lamak which part?

the "Parallel INSERT and Clustered Columnstore Indexes" part

looks like a foreign key doesn't disable parallel insert

so that's something

@JoeObbish Ah yes exactly

an optimistic/pessimistic strategy thing

assessing the impact of the tuple mover?

@swasheck Mic drop

TIL

i have no idea what's going on. i saw some chatter about inserts but is that the problem we're trying to solve?

> When you import the data into CCI, the data is loaded either into delta rowgroup or compressed rowgroup based on the rule “If batchsize < 102400, the rows are loaded into delta rowgroup otherwise they are directly loaded into compressed rowgroup”. Minimal logging is only supported when data is directly loaded into compressed rowgroup. This means, to get minimal logging with CCI, you must use a batchsize >= 102400.

from: blogs.msdn.microsoft.com/sqlserverstorageengine/2016/01/10/…