10 ms·
How times have changed for PostgreSQL
- DrJokepu 13y agoIf you want job security and fat paychecks for the next decade, this is a very good time to learn PostgreSQL well and specialise as a PostgreSQL consultant.
- dragansah 13y agoWhat extra knowledge would you advice that one should combine with postgres. Web development vs OS knowledge for example?
- delluminatus 13y agoHeck, you don't need anything else. I've worked with SQL consultants who are paid big bucks to come in and optimize a company's "enterprise analytic reporting" (i.e. HUGE queries run on non-production databases). All these guys do is optimize SQL queries. They're valuable because they can reduce the runtime of a naively-written report from 100 hours to 1 hour.
- dragansah 13y agoAre you talking about SQL Server analytic environments like MS Business Intelligence suite? Yes that's a lot more tooling than postgres, probably 10x of times bigger than all the postgres tools out there. Most of postgres tools I've used are command line tools like pgtop or pgbadger.
- delluminatus 13y agoMmm, the company I met them at was using Business Objects (Clarity/Crystal, etc.) which was where the inefficiency arose. But the consultants would come in and write optimized queries against the underlying SQL database because the Business Objects reporting was so inefficient. Basically they dealt with optimizing some very long, complex queries. And yes, I think typically orgs. today are using SQL Server or Oracle for these databases.
- danielweber 13y agoHow does "big bucks" compare to the "big bucks" in other computer specialties, like security? There's this thing I wish existed but AFAIK doesn't: a guide that shows computer people the relative salaries of a bunch of different specialties. So people who have one specialty but would enjoy something else can see what the salaries are like. Glassdoor is a very rough start on this.
- delluminatus 13y agoI have no idea. All I meant to imply there was that they are highly valued by the companies that contract them, because they typically are called in as a last resort when the organization's reporting situation has degenerated to being basically unusable.
- doppel 13y agoFrom my experience tackling a postgres server that is under-specced, a good knowledge of file systems (block devices, NFS, SANs, etc.), OS caching and kernel settings for maximum memory and such (check the postgres docs). Ofcourse, this only becomes an issue once you have a decent amount of data that does not fit into memory - before that, postgres will keep everything running relatively smooth without much tinkering. For postgres itself, make sure you know what all the settings in the configuration means, why they make sense 90% of the time and definitely do not make sense in 10% of the time (such as a low memory server with super-fast SSD disk arrays). And in general ofcourse a good knowledge of SQL, index usage/performance. Postgres extensions (arrays, JSON, etc.) are decent, but in my experience it's something you can get into relatively fast if you are solid on the rest.
- iagooar 13y agoThe question I always ask myself is: how does one learn specialized stuff like managing a DB without working with it professionally? This also applies to Big Data, scaling, etc. I would love to work building highly scalable systems, but I don't get to do it at my current position, and all the job offers out there require having experience doing it. Looks like a chicken-and-egg problem to me.
- rch 13y agoStart by tuning single-server instances for performance. There is quite a lot to learn from that before worrying about replication, for example.
- marcosdumay 13y agoJust do it. Normaly a job is not so specific that you can't start into some path like that, and once you are on the path, you can change positions. But, if your job is that specific, well, that sucks. You'll have to build something on your free time.
- DrJokepu 13y agoContribute to open source software, attend meetups, talk at meetups.
- iagooar 13y agoCan you recommend some interesting open source projects for an experienced Ruby / Rails developer, but with very little open source experience?
- ris 13y agoStart working with OpenStreetMap data.
- iagooar 13y agoDo you mean processing the data and getting meaningful information out of it?
- nieve 13y agoAs someone who's used PostgreSQL for 15 years and had to evangelize it almost everywhere I've gone that's a bit surreal to hear. Good, very good, but a little surreal. I've found that if you've got Ruby & Perl skills and you've worked on large-ish systems you'll get pigeon-holed as a non-database dev and it's hard to escape that without seriously warping your resume.
- iagooar 13y agoI have been using Postgres since I was a student, but I must recognize that being a better programmer than sysadmin, I always used it after some kind of abstraction layer. Today, this layer is Rails with ActiveRecord. I want to convince my boss to use Postgres instead of MySQL in a new project we've just started. Does anybody know about a good comparison of both databases, or a nice list of unique Postgres features? I have been googling a little bit but the findings weren't that good.
- tragic 13y agoA list of reasons not to use MySQL: http://grimoire.ca/mysql/choose-something-else http://grimoire.ca/mysql/choose-something-else
- matwood 13y agoI was just complaining about mysql yesterday. Data truncation and silent failures should not be warnings! They should be errors. For example, 'str1' + 'str2' results in '0' and a warning in the default mysql config. A real rdbms should fail and tell you + is not for string concatenation in that system instead of running your query and perhaps overwriting your data and then warning you about it after the fact ('That query you just ran is broken, but we overwrote your data anyways - hey, here's a warning.'). Broken, horrible design.
- davidw 13y agoOne that has bitten my colleagues lately: Auto-increment primary keys get reused when, say, you create a record, then erase it, then pull the power plug. Mysql does not save the highest number used, meaning that you turn on the machine again, add a new record, and it has the same ID as the previous one. My very subjective impression is that every time I have anything to do with Mysql, sooner or later, some weird thing like that jumps out and bites someone in the ass.
- ericcoleman 13y agoThis effects InnoDB, but not MyISAM
- PublicEnemy111 13y agoWhat are the best resources for one to learn PostgreSQL?
- captainmojo 13y agoI'd start here: http://www.postgresql.org/docs/9.3/static/index.html http://www.postgresql.org/docs/9.3/static/index.html I've always found this documentation to be great. If you've worked with RDBMS' before, you can skip around the chapters. If not, I'd read up through chapter 14 and go from there. You should be able to easily install it on whatever OS you're running.
- abstrct 13y agoThe Schemaverse (http://schemaverse.com http://schemaverse.com) Play a space battle MMO, but using SQL. The game is also completely open source so if you want to find out how to write an entire application layer in PostgreSQL, it is a pretty good example of that.
- craigkerstiens 13y agoThis is a shameless plug, but hopefully all some good resources: http://www.craigkerstiens.com/categories/postgres/ http://postgresguide.com/ http://www.postgresweekly.com
- new_test 13y agoSince we are on the subject of PostgreSQL, does anyone know of a good resource that would teach me how to create webapps where each "customer" would get their own "database"? What are the best practices for that? Also, what are the best practices for ALTER'ing tables in production (e.g. adding/deleting columns). Here's a concrete (made-up) example: I want to write a webapp where people would sign up to do some personal tracking. They create variables they are interested in (weight, mood, calory intake, etc.) And then enter their data daily. So for each customer I need to have a different database with different columns, and I want them to be able to add/delete variables "on the fly". Is it very straightforward, and hard to get wrong? Or are there "best practices" for this sort of thing? Thanks.
- dragansah 13y agoYou can use a generic data model (example [1]) with appropriate metadata etc. What you are proposing seems more difficult to maintain (separate databases for different customers). If this are separate deployments than different databases are an option with some core module and than specific databases for each customer. I don't recommend doing DDL updates in realtime because of other issues for example: logging of data in some log tables, the data structures in your application (consider what changes would be implied to your ORM entities or other domain entities) etc. [1] Generic Data Model: http://c2.com/cgi/wiki?GenericDataModel http://c2.com/cgi/wiki?GenericDataModel
- bananas 13y agoLook up the following terms 1. SaaS tenancy models. This is the data separation. 2. Custom fields would be usually represented as an EAV model (Entity-Attribute-Value). 3. Don't ALTER in production if you can help it. If you're going to do it, use migrations which are scripts which first add columns, then transform data, then reapply constraints.
- arethuza 13y agoI've also seen applications basically just assign a fixed number of additional columns on the relevant tables for custom fields - although this approach doesn't win any awards for elegance it can be pretty straightforward. From what I've seen, applications that use EAV tend to evolve to suffer from bad cases of the "inner platform effect": http://en.wikipedia.org/wiki/Inner-platform_effect http://en.wikipedia.org/wiki/Inner-platform_effect NB There is nothing "wrong" with using EAV - just that it seems prone to misuse (a bit like XML).
- deleted 13y ago[deleted]
- njharman 13y agoI was 76% expecting a treatise on how various time and date fields and functions have evolved over PostgreSQL versions.
- juretriglav 13y agoYes PostgreSQL is awesome. From my experience with a large installation, we had many more issues with the actual hardware on EC2, than we had with postgres which ran on said hardware. However, I was pleasantly surprised Amazon's RDS now supports PostgreSQL too, so you can abstract away most of the hardware considerations, assuming they've optimized their stack: https://aws.amazon.com/rds/postgresql/ https://aws.amazon.com/rds/postgresql/
- tuxychandru 13y agoIf I have a bunch of records that need to be fetched together often and can't be dumped in a hstore of JSON column, how can I ensure data locality on disk in postgres? Since primary key is clustered in MySQL, I choose an appropriate key. Is there an equivalent mechanism or is periodically running the cluster command the only option?
- jeltz 13y agoYou cannot do this other than relying on covering indexes and index only scans. PostgreSQL does not support index organized tables.
- mverwijs 13y ago" I see experienced enterprise software professionals who are versed in operating systems and Windows, for example, taking the training to advance their careers. " That had me laughing.
- iblaine 13y agoIt is worth pointing out that AsterData and Redshift are built on pgSQL. From what I have read, this is due to licensing issues. MySQL is ok but I'm unconvinced that MySQL is a good option for single instance terabyte size databases. I'm not sure if pgSQL is any better but you hardly see large unwieldy pgSQL instances. Large slow MySQL instances are everywhere. And it's frustrating.