- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Well said!
Admin
.... and if you are better at it then either go work at one the db companies or contribute to one of the open-source dbs.
Admin
True. However, Postgres comes with an easy to use installer (both for Windows and for Linux), works out of the box, has every feature you could possibly wish for (but feel free to flame me on some missing esotheric bit it may not have), comes with a great admin tool that allows you to see what is going on inside the database, doesn't do anything "funny" (I'm looking at you MySQL!), and best of all, it has excellent documentation on its website.
Oh, and it is free, without restrictions.
We're using it professionally (for an industrial control application where the repercussions of database failure can be quite dramatic), and we are perfectly happy with it.
Admin
No, but I will flame you for spelling "esoteric" wrong :P
Admin
In Communist Russia, MySQL WTFs you!
Admin
I once saw the back end of a similar system in a dark back room of an Estate Agents' office. We had been asked to quote for some "enhancements".
It was designed the same way as this example - except for one thing: it had been "built" in Access.
The system had been running for a year or two - and Access plus thousands of tables = catastrophically bad performance. There was no documentation. We overquoted massively so that we didn't get the work.
Admin
Talking of abnormal forms ...
My neighbour claims to have seen a system with one database per customer.
Admin
I can beat that. I worked on a system where they had 2 sql server instances, 1 "common" and 1 "retailer". The instances had multiple databases per retailer, involved replication, and cross-instance as well as cross-database queries.
This was because to maintain multiple retailers data integrity, it was sold as more secure for their data to be in a completely different database instance.
The possibility that the cross-instance/database queries and replication issues making development harder and thus more likely to have bugs allowing you to see data you shouldn't can't have been considered...
Admin
Yes, it does and has had for years thanks to InnoDB. We have many tables in MySQL databases that grow by millions of rows a day - the reporting that is done on that data each day wouldn't be feasible without MySQL's excellent index usage (clustered or otherwise).
Kindly know your databases before you comment on them.
Admin
It may surprise some people, but this sort of thing happens with worrying regularity and irrespective of DB choice.
For those who don't know though, the benefits of a clustered index are afforded to all queries that fetch the entirety of their columns from any BTREE index (as in, no additional passes for GROUP BY and ORDER BY clauses if they use a prefix of the specified index).
Of course, you're pretty much buggered in this case as the merge and sort operation that happens after the union is likely to ruin everyone's day. Indexed views, anyone?
Admin
While you joke about abnormal forms...
The sad thing is that yes, these people don't grok databases.
Yet there may be a use case / business requirement that requires that the database maintain each account and each month seperately.
There are things that they could do via meta data and views that would help things. (Satisfying one requirement while allowing for better access.)
Does the database in use support data partitioning? Can you partition on an attribute like MONTH(DATE) or just a hash partition? (ref DB2 v8.)
The point is that before makign a judgement on such a short snippet, it is better to ask why they did what they did so that you can better box the problem and find an optimum solution.
But hey! What do I know? I refuse to call myself a DBA cause I'm not really in to the physical side of things. ;-)
-G
Admin
i'd also say: check out sqlite - it's an embedded database who's code you can link directly into a simple c/++ app.
while it's certainly not as feature full as an enterprise db ( which in some respects is a good thing if you're just starting out ) you can get it setup and be working with it in 10-15 minutes.
Admin
It's okay for datawarehouses to be denormalized or flattened. OLAP is different than OLTP.
CAPTCHA: quake (Makes me want to take a BFG10k to the "dba"
Admin
Jeeze, what is so hard about all this?
I once read a short intro to SQL once on a web page, and I think I'm more qualified to design these databases that show up on TDWTF....
Admin
Heh... If you didn't call yourself "Some newbie", I would say you win the prize for most understatedly funny comment this month. But I must assume you meant this seriously. Either that or the "Some newbie" handle is part of the joke.
Admin
Except without the catchy theme song.
Admin
So does XE.
Check. (for Oracle)
Let's see about those "esoteric" features:
Parallel Query (SE/EE) Analytic Functions (XE/SE/EE) Tightly integrated SP-Language, including native compilation (XE/SE/EE) Job Scheduler (XE/SE/EE)
To mention a few.
BTW, do I still have to VACUUM in postgres?
Don't get me wrong: I'm aware of the improvements in OSS-DBMS development, but some commercial products do have their merits.
So does EM, plus you'll get all kind of statistics and reports, performance tuning tools, etc., for a price of course.
otn.oracle.com - 'nuff said.
Yes, that's one advantage where postgres (and others) do shine.
l.
Admin
We are using a web server based system with a SQL server database, and we gave each customer their own copy of the database because:
Bad for hundreds of customers but works well for a few.
Admin
At the place where I work, we now have a production database (managing production data for thousands of individual items) all stored in a Postgres database. Although I'm now ashamed of the job i id 2 years ago about the programming and table design, the database has been working flawlessly since deployment(It's a Postgres + Apache + Perl job)
The reason I'm now ashamed of the programming and design (varchars as primary keys, no database constraints etc) is that at that time i had only a few months experience with databases (and no formal Comp Sci. education whatsoever) so i made a number of bad desisions due to lack of knowledge..
I'm however happy to say that the next database design i'm currently making (for the same department) will be uch more robust (3'rd normalised). Durring the last 3 years i have never encountered something that would be nice to have in Postgres that it didn't have. Most of the time I had to find out what would be nice (aka i wonder if XXX id possible in a database, and sure enough, in all cases Postgres had already implemented it).
Yours Yazeran
Plan: To go to Mars one day with a hammer
Admin
Surely this is partitioning, not clustering?
Admin
Aaaaargh, a spelling error! Damnit, 20 years of posting, and now this spot on my otherwise unblemished record of perfect spelling! How can I possibly recover from this disgrace?
Captcha: "ninjas". Yes, that's the thing. I'll hunt ninjas to hunt you down and kill you. Shoot the messenger and everything...
Admin
...so? I'm presuming you are referring to some alternative database vendors' product here, and you seem to think we should know which one it is simply by mentioning a version suffix.
Anyway, you seem to feel mightily threatened by my praise for Postgres. Makes me wonder why.
No, you don't. Tell me, do you still have to configure a hundred parameters just to get your database to work in Oracle? Does it even install without requiring a ten-year old version of glibc? Will it work without crapping out with ORA-600 on a regular basis? Has Oracle finally decided on a licensing scheme that doesn't change every month? Can I finally use normal columns longer than 2000 (or 4000) characters? Can I drop columns from tables already? And finally, do you actually use any of that stuff you mentioned or is just namedropping?
And with Oracle, you're gonna need them... I've been there and done that. I'm happier now that I don't have to anymore.
What are you trying to prove? Why would Oracles documentation invalidate my statement that Postgres is an excellent tool that is great both for serious production databases and people trying to learn something?
Really, some people...
Admin
Wikipedia is your friend
Admin
I was still in the Oracle context. XE = eXpress Edition.
Actually you started to praise postgres on my thread that described some entry level versions of commercial DB vendors. Makes me wonder why.
Really? It's still in the documentation of 8.2.
No. And there were never a hundred parameters in a standard init.ora file, even in the old days.
Does a current gentoo installation with XE or SE suffice?
Sure. Have you ever worked with Oracle?
Look for yourself: http://oraclestore.oracle.com
What is a "normal" column? CLOBs come with a limit between 8TB and 128TB if you need it really big.
Sure.
Of course, otherwise I could use just plain old postgres, couldn't I?
Must've been a long time since you were using Oracle - or you had to work with a desupported version. It's fascinating, that even today, some companies have a working 7.x.
It doesn't, I was just mentioning it. After all, it was you who had to come up with some "esoteric" stuff on my suggestions, not the other way around.
Looking in the mirror again, are you?
l.
Admin
I don't know what a clustered index is but I know how to do select count(*) from names. You guys are a bunch of snobs.
Admin
Ah, Oracle is so big in your little universe that you only need to utter those two magic letters and everything becomes clear to you. I understand your problem a bit better now.
The thread, you dimwit, was about someone asking about a nice database for beginners. You named all these big, complex, expensive products, and I thought I'd give the guy a hand by pointing him to something that is free and just as good. Where's the harm in that? Do you have some commercial interest in pushing Oracle?
If you had bothered to read that documentation you would have seen it is optional; autovacuuming can do the job for you.
Oh dear... Listen, if you are playing with some toy database with a handful of tables and a dozen records, maybe Oracle is overkill for you anyway. But if you have a real database with hundreds of tables and millions of records, Oracle simply won't work on the standard settings. You'd better brush up on your Oracle knowledge before you try to flame on the subject.
Well, you claim to the expert. Does it? Last time I checked, some three years ago, installation was a major pain - you had to install an old glibc and you had to abort the installation halfway through and move some stuff around because the installer was broken.
I have worked with it continuously from about 12 years ago to about 3 years ago. And since I moved to Postgres I haven't missed it for a second. Never once did I think, "oh it is so nice that I have to tell Oracle how many cursors to use". Or "I just love the way it gives me this ORA-600 error on a production database". Or "these admin tools are so great, how will I ever live without them". It was more like removing a constant annoying ache: once Oracle was gone from my life everything became a little bit better.
Ok, that's certainly an improvement on the old day. When we couldn't even get a quote from Oracle because they desperately wanted a percentage of our project budget, as opposed to simply a fixed sum. I mean it, this is really better than it was.
Maybe I'll spend that E30,000 to replace the excellent free functionality I already have... On second thought, no I won't.
A normal column is a column like any other. One that doesn't have a restriction like "only one per table". One that may be indexed if I want it to be. One that can be dealt with in the context of the same SQL query that deals with the other columns, without requiring special instructions to access. I don't think CLOBs qualify for that.
...without dropping and rebuilding the table? Because last time I checked, you couldn't.
Well, best of luck with it then. I don't mind you using Oracle, see? I do mind annoying assholes though, which is why I am flaming you now.
But hey, nothing personal. One day you may see the light, and until then, have fun with your chosen database.
Admin
hmmm... free databases. Well, I'll suggest one that I think is very good! Of course, you might have to consider yourself a MS fanboy... :D
MS Sql Server Express. Free, easy to move to other versions when needed, pretty good on compliancy of latest ansi. (yeah, I'm a convert to the later style)
Oracle put out a freebie too, afaik.... Could be wrong, but pretty sure it's out there.
Don't understand the big deal really. "What do you need a database for?", is the first question you need to ask, because perhaps then, you'll know the level of tools you'll need, OR, the amount of time you'll want to spend thinking about it, OR, just say screw it and go with the first buzzword that works for ya.
understanding execution plans and all that jazz is nice, but really not necessary unless you REALLY like database stuffs, or you are a DBA, or you work for a small company that has no DBA and you partially have to fill that role, and you are not writing apps for hum-drum use, in which case it's important that SOMEONE understands cached plan maintenance, sql injection attacks, execution plans, indexes, clustered indexes and the multitude of ways they can be used, blah blah blah blah blah.. DB is too complicated to argue about on the daily wtf. Brand X vs Brand Y is a silly argument.
ABout Oracle, of which I'm merely a tourist..
Oracle is a beast with some VERY powerful functions, abilities, you name it. I'm always perusing for sql server's implementation of oracle's power functions. (Percentile_cont anyone? and ty sql server blog!)
Admin
Trying to 'GROUP BY' and 'ORDER BY' on thousands of UNION select statements will grind your system to a halt. That process is never going to die.
Even if you are able to select from these tables, how are you be able to process them in a logical structure? That's gonna make your system going to smoke too. Megabytes of multidimensional array's and such.
I love it when people are just inventing stupid stuff and just blame the hardware for not performing enough. Plugging in more and better expensive hardware just trying to solve the core problem. Also the time it takes to manually generate these tables is awesome. Keeps the job alive....
Captcha: burned, that is what the system is going to be when finishing these UNIONs.
Admin
I don't have a problem using a search engine and type "XE database" as a keyword, do you?
Ah, getting personal now. Since those entry level versions I mentioned do come for free (even for production purpose), it's hardly commercial interest. I could ask you the same: are you trying to find customers for EnterpriseDB?
Which is only a daemon running VACUUM periodically. So nothing new there.
I didn't write that you hadn't change any settings, I wrote that there aren't hundred's of parameters to set. BTW. Postgres does have some parameters too, which also have to be changed, depending on the requirements, last I've heard.
I'd assume that you're talking about an old 8.x or 9.x version. 10.1 and 10.2 don't need such workarounds, same goes for XE, which is also 10.2.
Can't say I experienced such troubles, but then I do read the user guides and some articles on best practices. Cursors? One parameter, and it could be good idea to check on the application that causes "too many open cursors". ORA-600? Well if it was in production, there would have been a service contract with metalink access, where one could look up for patches regarding that matter. Admin tools, like OEM? Nice for checking on the health of database, finding the top 5 ressource consuming SELECTs, etc.
You could spend a lot less, depending on the features you need. RAC, for example, already comes for Standard Edition. Don't see that feature in postgres yet. PL/SQL, you'll get it in any version, and no pgSql doesn't even come close to it. Again, I do think postgres is a useful product, but I wouldn't count out it's commercial competition yet.
You're talking about LONG data type then. Goes back to 8.x where the user guide already mentions to replace LONG with LOB, that doesn't come with that restriction.
With what index? What would a B-tree Index on a large CLOB/VARCHAR column accomplish? For that, there's Oracle Text which allows for full text search capability on such columns. Oops, another builtin feature missing in postgres.
Last time I tried a WHERE ... LIKE clause on a CLOB column, it worked. Maybe you were using an old version?
Again, it must have been an old version, "ALTER tablename DROP columnname" works.
Well, I certainly don't mind you using postgres either.
I do mind people who blame a product for their own inabilities - that's why I'm ridiculing you.
One day you'll do your homework first before mouthing off, until then, have fun blaming others for your inabilities - you would make a "good" manager, you know.
l.